Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

learn more… | top users | synonyms

47
votes
9answers
57k views

How can I export the privileges from MySQL and then import to a new server?

I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server. For extra points, there are a couple of existing databases on the new ...
15
votes
2answers
2k views

Is it ok to replicate the `mysql` database?

When I originally set up my master-to-master replication, I used: binlog-ignore-db=mysql and have been syncing the user accounts and grants manually. This is simply how it was done in the how-to I ...
11
votes
3answers
2k views

MySQL replication across geographically separate servers

My organisation has been looking into how to spread our servers around geographically while keeping backups very up to date, and ideally spreading the load. The initial thing I have in mind is Rails ...
11
votes
4answers
7k views

MySQL Master-Master Replication of ALL databases. How?

Background: I have two MySQL 5.1 servers setup in Master-Master row-based-replication (RBR) following this excellent guide. I want all databases replicated, and will be adding new databases ...
8
votes
5answers
8k views

Can a MySQL slave be a master at the same time?

I am in the process of migrating 2 DB servers (Master & Slave) to two new DB Servers (Master and Slave) DB1 - Master (production) DB2 - Slave (production) DB3 - New Master DB4 - New Slave ...
7
votes
4answers
2k views

How to setup MySQL replication with minimal downtime

Basically we have a large MySQL database and we're looking to do replication to a slave (master slave setup). Do you guys have any step-by-step guide on how to do this with MINIMAL downtime on the ...
6
votes
8answers
1k views

Monitor MySQL Replication

What is the best practice for monitoring a slave to make sure that it is a) Still running b) Not too far being the master I would like to alert by email if it is behind, happy to write a script or ...
6
votes
3answers
15k views

What causes the MySQL error 1062 - duplicate entry when starting slave?

MySQL Master version: 5.5.16-1 MySQL Slave version: 5.5.18-1 The master's snapshot is created by: mysql> FLUSH TABLES WITH READ LOCK; shell> mysqldump --all-databases --master-data > ...
6
votes
3answers
1k views

Creating a *Consistent* *Online* MySQL Backup with InnoDB AND MyISAM tables

I just spent hours building a new database server to replace 2 failed servers from a mysqldump file created a month ago, then used my server's bin-logs to bring that mysqldump up to date. Now that I ...
6
votes
6answers
611 views

How can I centralise MySQL data between 3 or more geographically separate servers?

To explain the background to the question: We have a home-grown PHP application (for running online language-learning courses) running on a Linux server and using MySQL on localhost for saving user ...
6
votes
1answer
4k views

MySQL: Pacemaker cannot start the failed master as a new slave?

pacemaker-1.0.12-1 corosync-1.2.7-1.1 I'm going to setup failover for MySQL replication (1 master and 1 slave) follow this guide: ...
6
votes
2answers
2k views

MySQL replication issues after a power outage

After a power outage at our data centre, the slave MySQL databases are struggling. This is in the logs for one of the slaves: 100118 10:05:56 [Note] Slave I/O thread: connected to master ...
5
votes
4answers
678 views

Testing MySQL Database Replication / Synchronization

I'm setting up a MySQL Master database which replicates to a few Slave databases. My question is what are the best practices to monitor and / or test that the slave databases are up-to-date, and that ...
5
votes
4answers
1k views

Prevent non-replication writes to MySQL slave?

We have some MySQL database servers set up with row-based replication, for performance. The software writes to the master, and reads from either the master or the slave. Everything's working great, ...
5
votes
3answers
775 views

MySQL load balancing

I'm looking into ways to balance the load on our MySQL infrastructure, and can't seem to find an answer that works for me... :) So, I have one big and fat server, that handles everything. Many DBs, ...
5
votes
1answer
1k views

why use mixed-based replication for mysql

I am in the process of configuring MySQL replication and am intending to use row-based-replication but I was also reading up about mixed-based replication. This is where statement-based is the ...
4
votes
5answers
3k views

Diagnosing Mysql Replication Issues

We have a mysql replication client running on our backup server. Since a power failure last week it's stopped replicating. Before this it was running uninterrupted for several months. I've tried ...
4
votes
2answers
951 views

Are Master-Master database collisions a problem even with auto increment?

I've done quite a lot of reading into this and I'm still I'm not sure where I stand. Am I going to get database collisions (and then corruption) with two Masters both receiving writes ? Now that ...
4
votes
2answers
3k views

MySQL slave replication reset with no Master Downtime (using MyISAM)

Root of the issue: In all instructions for creating a slave on a running master requires flush tables with read lock. We use MyISAM, so we can't just use --single transaction to get consistent table ...
4
votes
1answer
2k views

max_binlog_size & log-bin size

I have a problem with full disk -rw-rw---- 1 mysql mysql 1073741982 2012-07-03 18:14 mysql-bin.000034 -rw-rw---- 1 mysql mysql 1073741890 2012-07-04 14:39 mysql-bin.000035 -rw-rw---- 1 mysql mysql ...
4
votes
1answer
2k views

How to get rid of mysql binlogs?

I've been using mysql for development. I don't use replication but I guess I had binary-logging enabled for some time on one of my previous computers and I have unknowingly accumulated hundreds of ...
4
votes
2answers
539 views

MySQL replication: Getting the master in sync with the slave again

I have two computers on the same network, in a master-slave replication setup. Simply to test failover, I inserted some data straight into the slave database. So now the slave database contains more ...
4
votes
7answers
1k views

Mixing Multi-Master Replication (MMR) with Linux-HA

I'm interested in using MMR (http://mysql-mmm.org/) for high availability and replication purposes. The problem is, I'm also interested in using Linux-HA for other services, such as Apache. The two ...
3
votes
4answers
777 views

master-slave-slave replication: master will become bottleneck for writes

the mysql database has arround 2TB of data. i have a master-slave-slave replication running. the application that uses the database does read (SELECT) queries just on one of the 2 slaves and write ...
3
votes
2answers
13k views

How to sync two MySQL tables (on demand or via cron)

I have seen Cron sync mysql tables . But I cannot use replication. I used to use percona-toolkit and it worked perfectly. I could run the sync command on demand or just run it via cron. It would ...
3
votes
3answers
946 views

mysql master-slave setup with synchronous replication

I have a very trivial mysql master->slave setup going on between two servers. The problem is, replication is asynchronous, and this can cause issues (even on a low latency link), if the master server ...
3
votes
2answers
1k views

mk-table-sync in a master-slave scenario: Changes not replicated to the slave

I have being employing mk-table-sync to synchronise tables from a master to slave on mysql 5.1. Unfortunately, while differences are correctly detected, modifications done on the master ...
3
votes
5answers
4k views

How can I back up and replicate a large MySQL database?

How can I backup a large database in mysql? And what is the best way to replicate the database? what i'm looking for is backing up large database with large amount of data in a small time limit ...
3
votes
5answers
2k views

Using MySQL replication to enable a read-only copy of a database in a DMZ

I need to set up a MySQL database instance in a DMZ that is a read-only copy of the live master inside a secure network. MySQL replication seems ideal for this, except that it works by the slave ...
3
votes
3answers
2k views

mysql best practices with replication of the mysql db (grants storage)

is there a best practice to ensure that privileges do not get replicated to mysql slave servers? the problem is, there a maintenance users in the mysql table that have different passwords on each box. ...
3
votes
2answers
5k views

MySQL binlog format dilemma?

MySQL version: 5.5.13 If I set binlog format to STATEMENT, I got the following warnings on the Master: [Warning] Unsafe statement written to the binary log using statement format since ...
3
votes
3answers
4k views

Can I run mysqld on top of glusterfs?

I have been playing with glusterfs recently. What I want to try is to run mysqld on top of the glusterfs in a similar way as it is possible to run MySQL on top of DRBD. I am familiar with MySQL ...
3
votes
1answer
88 views

Is it safe to lock tables on a mysql replica slave?

I have a master mysql database where the application writes and read production data, and then I setup a slave database just in case something goes wrong with the master database server (and I can do ...
3
votes
1answer
2k views

MySQL slave server not removing old relay binlogs

I have a MySQL server with slave replication on another host. Today I stumbled across the high disk usage of the slave host and invastigated what takes up all the space. As it turns out this space is ...
3
votes
3answers
1k views

Database scalability with write-heavy application

I have a write-heavy application. The application is best compared to surveys - the customer creates custom questionares and this is saved to the database. Most of the requests are from their users ...
3
votes
1answer
5k views

Failed to connect from slave to master with error “error connecting to master (1045)”

I try to setup replication from slave to the master. CHANGE MASTER TO MASTER_HOST = 'master', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'xxx'; And I did grant privileges ...
3
votes
1answer
1k views

One way replication from MSSQL2005 to MYSQL 5.x

I have a mysql database acting as our websever backend. We have our production mssql server that we would like data pushed from, to our mysql server. I have successfully made the mysql server an ...
3
votes
1answer
289 views

Replication of multiple masters to a single slave server

I am running MySQL 5.1.36 in all my servers. I want to replicate database_A from master_host_A and database_B from master_host_B into a single slave server. All the three MySQL servers are remotely ...
3
votes
2answers
2k views

Improving MySql replication speed

i'm looking for pointers on how to improve the replication speed of a mysql slave. It is fast enough for normal production but needs a long time to catch up if it falls behind for some reason. (If the ...
3
votes
1answer
1k views

Recover a crashed MySQL master server from the slave

we are building a simple master/slave MySQL configuration using asynchronous replication, with MySQL enterprise 5.5.17 on both servers and innoDB based tables. In case of a crash of the master server ...
3
votes
2answers
526 views

Why does MySQL replication break on missing row, then continue after “START SLAVE”?

The title is a bit confusing, but I can't think of a better one. What I have is a simple vanilla MySQL replication, with the slave occasionally failing, with this error: Error 'Can't find record in ...
3
votes
1answer
162 views

Restore database by files from broken Ubuntu server

I'm pretty lost right now. The RAID controller auf my server broke and all attached hard drives were corrupted. Luckily I copied the plain files off the server right before the crash (phew). I set ...
3
votes
1answer
384 views

Add MySQL as a resource to pacemaker

I am trying to enable fail over strategy using pacemaker on a mysql Master-Master replication on two virtual machine running openSUSE 12 created by VritualBox. I managed to complete the mysql ...
3
votes
1answer
1k views

MySQL slave replication Seconds behind master increasing?

I started a MySQL slave using innobackupexand the Read_Master_Log_Pos: and the Relay_Log_Pos: are updating however the seconds behind master keeps on increasing (it's at Seconds_Behind_Master:496637 ...
3
votes
1answer
469 views

MySQL master-slave lag replication

Our company is using TokuDB on production and we are having a lot of problems trying to mitigate lag on our slave. Is very strange, because we're talking about very few rows... but with a few data it ...
3
votes
1answer
408 views

Using Amazon RDS (or similar) as a slave

Officially, RDS instances can't be directly set up to slave non-RDS databases. My production master is MySQL running on Hetzner and I want an RDS instance to closely follow it for backup purposes, ...
3
votes
1answer
1k views

MySQL on VMware for High Availability

Given a choice between the current database-level HA options for MySQL, like Master-Master, Cluster, or Galera, and vSphere HA for the machine running the MySQL master, which would you use or combine ...
3
votes
2answers
781 views

Replicate Oracle to MySQL

I am developing a web apps, this web application will be using MySQL. Now I need to replicate my client's Oracle database into MySQL, only a few tables will be involved.. a table can be up to 2-3 ...
3
votes
2answers
118 views

MySQL Replication 'Catchup' UPDATES keep newer record

I've got two MySQL clusters that have Master-Master replication setup between them. Most tables are log based, with only inserts and selects, so they don't have any problems with the replication. ...
3
votes
1answer
345 views

Failover strategy for a 4 servers scenario

I am trying to figure out how to set up replication & failover in a scenario with 4 servers (2 per location) where any server may assume the Master role. My initial scenario is the following one: ...