MySQL Replication

MySQL replication is essential to high availability and failover server scenarios. Depending on how you configure the replication MySQL data from one or more servers is distributed over a group of servers. The relations between the servers are master / slave or master / master depending whether you'd like both servers to be able to update the synchronized data (master / master) or only one should be able to do so (master / slave).

It is important to note that MySQL Replication is not a substitution to backup. While it will help in case of hardware / software failure on one node it will not in case of data anomaly or security compromise. Eventually there is a meachanism for delaying the replication which could be used as backup. But this compromises the idea of high availability / failover because data will not be syncronized and there will be losses.

Also, when designing your replication infrastructure make sure that all MySQL servers are the same version. Replication between MySQL servers with different major versions is unreliable and unpredictable.

As a whole MySQL replication is not a complex process. It has few configuration options for simpler scenarios and the main idea is that all updates / inserts on the master(s) are kept in binlog (log-bin directive in my.cnf). This same binlog allows data to be relatively easy and fast syncronized with other servers. The other important configuration option is server-id which distinguishes servers from each other.

Let's go through a few scenarios for MySQL replications

MySQL Replication between a Master and Slave

In this case we assume both servers are new and we are not replicating an already running MySQL server.

Changes to master server my.cnf:

log_bin         = master-bin
server-id       =  1

Once you make the above changes restart the MySQL server. After that add the necessary privileges for a 'replicant' user:

master> GRANT REPLICATION SLAVE ON *.* TO replicant IDENTIFIED BY 'password';

Changes to slave server my.cnf:

server-id       = 2
relay-log       = slave-relay-bin

Then start the replication issuing this query on the slave MySQL server:

slave> CHANGE MASTER TO MASTER_HOST = '', MASTER_USER = 'replicant', MASTER_PASSWORD = 'password';

Finally, to start the replication run:


You can verify the relication is working by creating a new database on the master server and inserting data into it. After that the same data should be available in the slave server.

A more 'scientific' approach for making sure the replication works would be ensuring that the master binlog position is the same as the Exec_Master_Log_Pos on the slave. An example:

master> show master status\G
Position: 329

slave> show slave status \G
Exec_Master_Log_Pos: 329

master> SHOW BINLOG EVENTS - shows the latest binlog entries on the master server. It shows the events from the first binlog file and NOT necessarily from the current one. Using 'SHOW MASTER STATUS' can tell you which is the current binlog file.

master> SHOW BINLOG EVENTS IN 'master-bin.000002' - see the events from the file master-bin.000002, possibly but not necessarily the current binlog.

MySQL Replication between a running master and a slave

In this scenario we assume that you have had a single MySQL server running already for a long time. Thus it is not feasible to replay all of the events in the binlog because it will take a lot of time and resources. Instead we will use a current backup and replay only the changes after the backup following these steps:

1. Lock the tables so that no new changes are recorded. This might be touch for a production server with high availability role.


2. Note the binlog position and file name. Later we will replicate only changes from this file after this position i.e. after the backup we are going to perform. 


File: masterbinfile-bin.000332
Position: 4563

3. Create a backup of all databases. If you don't need replication for all databases / tables you can backup only the needed ones. Later when you set the replication on the slave you have to ignore updates for the unneeded ones with the options 'replicate-ignore-db' and 'replicate-ignore-table'. 

master# mysqldump --all-databases > fullbackup_from_master.sql

Right after you create the backup unlock the tables on the master to avoid further downtime:


4. Restore the backup on slave MySQL server:

slave# mysql < fullbackup_from_master.sql

5. Configure slave server:

slave> CHANGE MASTER TO MASTER_HOST = 'master-name', MASTER_USER = 'slave_user_you_created', MASTER_PASSWORD = 'the_pass_you_specified', MASTER_LOG_FILE = 'masterbinfile-bin.000332',MASTER_LOG_POS = 4563;

6. Start replication on slave server:


General MySQL Replication Hints:

  • SHOW BINLOG EVENTS IN 'master-binlog.000003'\G - Always specify which binlog file to be read by the command 'show binlong events'. Otherwise the first binlog file will be read which is not likely to be the current;
  • RESET SLAVE - remove all files on the slave used for replication in order to get a clean start.
  • RESET MASTER - removes the binlog files and clears the binlog index file.
  • Binlog rotates in these cases:
    • When the server stops. Each time the server starts a new binlog file is used. This includes server incidents and server crashes.
    • When binlog reaches binlog-cache-size server variable.
    • When you flush it using FLUSH LOGS.
  • Purging binlog files:
    • Automatically when logs reach expire-logs-days.
    • Manually running PURGE BINARY LOGS BEFORE datetim or PURGE BINARY LOGS TO filename.
  • The mysqlbinlog tool can create SQL statements out of a binlog file:
# mysqlbinlog master-bin.000005 > go.sql

After that you can import go.sql directly with a mysql client.

blog comments powered by Disqus