Go to the first, previous, next, last section, table of contents.


11 Replication in MySQL

11.1 Introduction

One way replication can be used both to increase robustness and speed. For robustness you have two systems and switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

Starting in 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave upon connecting informs the master where it left off sinse the last successfully propogated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

11.2 Replication Implementation Overview

MySQL internal replication uses the master-slave approach. One server is designated as the master, while the other ( or others) as slave(s). The master keeps a binary log of updates. The slave connects to the master, catches up on the missed updates, and then starts receiving updates immediately as they come to the master. If the connection is lost, the slave will reconnect. If the master goes down, the slave will keep trying to connect every master-connect-retry seconds until the master comes back up and the connection can be established. The slave keeps track of where it left off in the replication process, so it can use the info in the case it goes down and gets restarted later.

11.3 HOWTO

Below is a quick HOWTO on how to set up replication on your current system:

After you have done the above, the master and the slave(s) should be in sync.

11.4 Replication Features

Below is an explanation of what is supported and what is not:

11.5 Replication Options in my.cnf

The table below explains the replications options in my.cnf . All of the are available starting in 3.23.15 unless indicated otherwise.

Option Description Where to set Example
log-bin Should be set on the master. Tells it to keep a binary update log. If a parameter is specified, the log will be written to the specified location. Master log-bin
log-bin-index Because the user could issue FLUSH LOGS command, we need to know which log is currently active and which ones have been rotated out and it what sequence. This info is stored in the binary log index file. The default is `hostname`.index . You can use this option if you want to be a rebel. Master log-bin-index=db.index
master-host Master hostname or IP address for replication. If not set, the slave thread will not be started Slave master-host=db-master.mycompany.com
master-user The user the slave thread will authenticate as when connecting to the master. The user must have FILE privilige. If the master user is not set, user test is assumed. Slave master-user=scott
master-password The password the slave thread will authenticate with when connecting to the master. If not set, empty password is assumed Slave master-password=tiger
master-port The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. Slave master-port=3306
master-connect-retry The number of seconds the slave thread will sleep before retrying to connect to the master in case the master goes down or the connection is lost. Default is 60. Slave master-connect-retry=60
master-info-file The location of the file that remembers where we left off on the master during the replication process. The default is master.info in the data directory. Sasha: The only reason I see for ever changing the default is the desire to be rebelious. Slave master-info-file=master.info
replicate-do-db Tells the slave thread to restrict replication to the specified database. To specify more than one database, use the directive multiple times, once for each database. Note that this will only work if you do not use cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. Slave replicate-do-db=some_db
replicate-ignore-db Tells the slave thread to not replicate to the specified database. To specify more than one database to ignore, use the directive multiple times, once for each database. You must not use cross database updates for this option. Slave replicate-ignore-db=some_db
sql-bin-update-same If set, setting SQL_LOG_BIN to a value will automatically set SQL_LOG_UPDATE to the same value and vice versa. Master sql-bin-update-same
log-slave-updates Tells the slave to log the updates from the slave thread to the binary log. Off by default. You will need to turn it on if you plan to daisy-chain the slaves Slave log-slave-updates

11.6 SQL commands related to replication

Replication can be controlled through the SQL interface. Below is the summary of commands:

Command Description Where to run
SLAVE START Starts the slave thread. Slave
SLAVE STOP Stops the slave thread. Slave
SET SQL_LOG_BIN=0 Disables update logging Master
SET SQL_LOG_BIN=1 Re-enable update logging Master
FLUSH MASTER Deletes all binary logs listed in the inded file, resetting the binlog index file to be empty. Master
FLUSH SLAVE Makes the slave forget its replication position in the master logs Slave
LOAD TABLE tblname FROM MASTER Downloads a copy of the table from master to the slave Slave


Go to the first, previous, next, last section, table of contents.