Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  Replication

Chapter 17 Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default, therefore slaves do not need to be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

For answers to some questions often asked by those who are new to MySQL Replication, see Section A.13, “MySQL 5.6 FAQ: Replication”.

Advantages of replication in MySQL include:

  • Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.

  • Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.

  • Analytics - live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.

  • Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.

Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 18, MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4). In MySQL 5.6, an interface to semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. See Section 17.3.8, “Semisynchronous Replication” MySQL 5.6 also supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. See Section 17.3.9, “Delayed Replication”.

There are a number of solutions available for setting up replication between two servers, but the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 17.1.1, “How to Set Up Replication”.

There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You may also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 17.1.2, “Replication Formats”. In MySQL 5.6, statement-based format is the default.

MySQL 5.6.5 and later supports transactional replication based on global transaction identifiers (GTIDs). When using this type of replication, it is not necessary to work directly with log files or positions within these files, which greatly simplifies many common replication tasks. Because replication using GTIDs is entirely transactional, consistency between master and slave is guaranteed as long as all transactions committed on the master have also been applied on the slave. For more information about GTIDs and GTID-based replication, see Section 17.1.3, “Replication with Global Transaction Identifiers”.

Replication is controlled through a number of different options and variables. These control the core operation of the replication, timeouts, and the databases and filters that can be applied on databases and tables. For more information on the available options, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.

You can use replication to solve a number of different problems, including problems with performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 17.3, “Replication Solutions”.

For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and problems and their resolution, including an FAQ, see Section 17.4, “Replication Notes and Tips”.

For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replication, see Section 17.2, “Replication Implementation”.

Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
User Comments
  Posted by on December 18, 2002
Handy mysql log rotation script. For those not
using any chroot environements, comment out both
lines in #chroot section. Set MYSQL_HOME QUERYLOG
SLOWLOG ERRLOG appropiately.

# MySQL log rotation

# chroot

# mysql

# most universal method for calculating
yesterday's date in YYYYMMDD format
DATE=`/usr/bin/perl -e


if ! [ -s ${PID_FILE} ]; then
echo " Error: pid file not found."
exit 1;


echo -n "Rotating logs: "

if [ -e ${QUERYLOG} ]; then
echo -n "querylog "
if [ -e ${SLOWLOG} ]; then
echo -n "slowlog "
/bin/mv ${SLOWLOG} ${SLOWLOG}.${DATE}
if [ -e ${ERRLOG} ]; then
echo -n "errlog "
/bin/mv ${ERRLOG} ${ERRLOG}.${DATE}

/bin/kill -1 $PID
Run from cron at midnight.
  Posted by S Harper on June 19, 2008
To answer the above question, in the current version, replication supports parallel processing for reads, but you have to be extremely careful for writes.

There is, however, a way around the write limitations in most application situations.

Let's say you have two websites: Each site _could_ be (and in many default situations is) hosted on its own server with its own cpu and MySQL database.

The problems inherent in that situation are reliability (uptime), waste of system resources and lack of flexibility with system resources.

So instead you setup both websites to run on both servers. (Details of IP level load balancing are beyond the scope of this post, but there are lots of options available.) Now, if you've limited your application appropriately, you could setup both servers to write to each other, but I personally wouldn't recommend that you accept that limitation, as it can be fraught with hidden dangers.

Besides, in most applications, the vast majority of the load is reads, not writes.

The language your application is written in is probably implemented with pools of database connectors to service application threads. Using that model, you would setup a pool of read threads on each server to balance their reads from their local MySQL database and the replicated one on the other server.

For writes, you would setup your connectors on both servers to use the master MySQL database for writes, then setup a different pool of connectors for writes to use the slave database. You'll have to handle the error at your application level, but when the master is unavailable, then you switch your application to start writing to the slave instead.

You'll need to write some explicit error handling to tell the slave it's now the master and prevent the original master from being used for reads or writes until it has become the slave in turn and refreshed itself from the slave.

Writing a record to the database immediately after a transition from slave to master to use as a locking mechanism can help ensure that your application always knows what state the two (or more) MySQL databases are in with regards to which pool of connectors writes should be sent to.

A cleaner solution, but much more expensive option in terms of hardware, would be to use two database servers with a hardware IP level load balancer between them and the application servers. In that case, use the same method of using different connection pools for reads and writes, but configure them to hit one IP for each, then configure the load balancer to send the read IP address to both database servers, while only sending the write IP address to one database server at a time. The other database server(s) would be configured to only have traffic sent to them for the write IP address if the original has failed.

You could then safely chain multiple databases to each other for circular writing, but still ensure that all writes only originate in the correct sequence because unless there is a database failure, they are only performed on one database server.

Of course, before you try any replication scheme, be sure to read and the rest of this section of the manual looking for gotchas!
  Posted by Achilleterzo on May 7, 2005
Im testing replication structured master to cascade of slave servers but i got sync problem if a client use a slave to insert/update data.
Waiting for a master-2-master replication i solved this issue by a little cron in php:

$db = mysql_connect("master-host","master-user","master-pwd") or $db = false;
if ($db!=false) {
$db = mysql_connect("localhost","local-user","local-pwd") or die ("Could not connect to MySQL");
mysql_query("stop slave; DROP DATABASE my_database; load data from master; start slave;");
  Posted by Jerome Davies on May 19, 2005
If you are using InnoDB tables, remember that nothing is replicated until the transaction is completed, so if you need to read information to complete the transaction that is dependent upon what has been written eg the value of an auto_increment field, you need to read it from the master.
  Posted by Nick Doyle on April 10, 2012
For anyone looking to add databases (not just slaves) to running replication, I wrote the following script which works pretty well:

It's useful in a number of scenarios, but for me mainly if
1. Your server has many DBs, and you want to bring slave DBs online one-by-one while having replication continue to run
2. One database on the slave gets out of sync with the master and it is faster to recreate from backups
  Posted by Lasantha Aberathna on September 13, 2012
If you are looking for step by step explanation about replication setup. Please follow
Sign Up Login You must be logged in to post a comment.