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:

Upgrade both slave and master to 3.23.15 or higher. We recommend that you use the latest 3.23 version on both the slave and the master. While 3.23 is in beta, the versions may be not backwards compatible. In addition, the newer version will fix some bugs and add new features.
Set up special replication user(s) on the master with the FILE privilege and permission to connect from all the slaves
Take a snapshot of all the tables/databases on the master that could possibly be involved in the update queries before taking the next step
In my.cnf on the master add log-bin and restart it. Make sure there are no important updates to the master between the time you have taken the snapshot and the time master is restarted with log-bin option
Load the snapshot of the master to all the slaves

Add the following to my.cnf on the slave(s):

master-host=<hostname of the master>
master-user=<replication user name>
master-password=<replication user password>

replacting the values in <> with what is relevant to your system.

Restart the slave(s)

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:

Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID, and TIMESTAMP values
LOAD DATA INFILE will be handled properly as long as the file still resides on the master server at the time of update propogation. LOAD LOCAL DATA INFILE will be skipped.
Update queries that use user variables are not replication-safe (yet)
If the query on the slave gets an error, the slave thread will terminate, and a message will appear in .err file. You should then connect to the slave manually, fix the cause of the error (eg. non-existent table), and then run SLAVE START sql command ( available starting in 3.23.16, in 3.23.15 you will have to restart the server).
If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages.
Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not synced before the system died. Your system fault tolerance will be greatly increased if you have a good UPS.
If the master is listening on a non-standard port, you will also need to specify this with master-port parameter in my.cnf .
In 3.23.15,all of the tables and databases will be replicated. Starting in 3.23.16, you can restrict replication to a set of databases with replicate-do-db directives in my.cnf or just excluse a set of databases with replicate-ignore-db.
Starting in 3.23.16, SET SQL_LOG_BIN = 0 will turn off replication (binary) logging on the master, and SET SQL_LOG_BIN = 1 will turn in back on - you must have the process privilege to do this.
The slave thread does not log updates to the binary log of the slave, so it is possible to couple two servers in a mutual master-slave relationship. You can actually set up a load balancing scheme and do queries safely on either of the servers. Just do not expect to do LOCK TABLES on one server, then connect to the other and still have that lock :-) .
Starting in 3.23.19 you can clean up stale replication leftovers when someting goes wrong and you want a clean start with FLUSH MASTER and FLUSH SLAVE commands
Starting in 3.23.21 you can use LOAD TABLE FROM MASTER for network backup and to set up replication initially.

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.