Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
Advantages of replication in MySQL include:
Using replication for backups
Using Replication with Different Master and Slave Storage Engines
It does not matter for the replication process whether the source table on the master and the replicated table on the slave use different engine types. In fact, the
storage_engine system variables are not replicated.
This provides a number of benefits in the replication process in that you can take advantage of different engine types for different replication scenarios. For example, in a typical scale-out scenario you want to use
InnoDB tables on the master to take advantage of the transactional functionality, but use
MyISAM on the slaves where transaction support is not required because the data is only read. When using replication in a data-logging environment you may want to use the
Archive storage engine on the slave.
Configuring different engines on the master and slave depends on how you set up the initial replication process:
If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:
mysql> STOP SLAVE;
ENGINE='engine_type' for each table to be changed.
mysql> START SLAVE;
default_storage_engine variable is not replicated, be aware that
CREATE TABLE and
ALTER TABLE statements that include the engine specification will be correctly replicated to the slave. For example, if you have a CSV table and you execute:
mysql> ALTER TABLE csvtable Engine='MyISAM';
The above statement will be replicated to the slave and the engine type on the slave will be converted to
MyISAM, even if you have previously changed the table type on the slave to an engine other than CSV. If you want to retain engine differences on the master and slave, you should be careful to use the
default_storage_engine variable on the master when creating a new table. For example, instead of:
mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;
Use this format:
mysql> SET default_storage_engine=MyISAM;
mysql> CREATE TABLE tablea (columna int);
When replicated, the
default_storage_engine variable will be ignored, and the
CREATE TABLE statement will execute on the slave using the slave's default engine.
Using Replication for Scale-Out
You can use replication as a scale-out solution; that is, where you want to split up the load of database queries across multiple database servers, within some reasonable limitations.
Because replication works from the distribution of one master to one or more slaves, using replication for scale-out works best in an environment where you have a high number of reads and low number of writes/updates. Most Web sites fit into this category, where users are browsing the Web site, reading articles, posts, or viewing products. Updates only occur during session management, or when making a purchase or adding a comment/message to a forum.
Replication in this situation enables you to distribute the reads over the replication slaves, while still enabling your web servers to communicate with the replication master when a write is required
If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to clean it up. Start by creating a wrapper library or module that implements the following functions:
safe_ in each function name means that the function takes care of handling all error conditions. You can use different names for the functions. The important thing is to have a unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.
Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a master/slave configuration, even one involving multiple slaves. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions; for example, to log how long each statement took, or which statement among those issued gave you an error.
If you have written a lot of code, you may want to automate the conversion task by using the replace utility that comes with standard MySQL distributions, or write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.
Replicating Different Databases to Different Slaves
There may be situations where you have a single master and want to replicate different databases to different slaves. For example, you may want to distribute different sales data to different departments to help spread the load during data analysis.
Using Replication to Replicate Databases to Separate Replication Slaves
For example, to support the separation as shown in picture you should configure each replication slave as follows, before executing
Each slave in this configuration receives the entire binary log from the master, but executes only those events from the binary log that apply to the databases and tables included by the
--replicate-wild-do-table option in effect on that slave.
If you have data that must be synchronized to the slaves before replication starts, you have a number of choices:
Improving Replication Performance
As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase and create a bottleneck.
If you are using a large number of slaves connected to one master, and that master is also busy processing requests (for example, as part of a scale-out solution), then you may want to improve the performance of the replication process.
One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements. A sample of this structure is shown in
For this to work, you must configure the MySQL instances as follows:
The above solution reduces the client load and the network interface load on the primary master, which should improve the overall performance of the primary master when used as a direct database solution.
If your slaves are having trouble keeping up with the replication process on the master, there are a number of options available:
--log-slave-updates. This prevents “dumb” slaves from also logging events they have executed into their own binary log.
Switching Masters During Failover
When using replication with GTIDs (see Section 16.1.3, “Replication with Global Transaction Identifiers”), you can provide failover between master and slaves in the event of a failure using mysqlfailover, which is provided by the MySQL Utilities; see mysqlfailover — Automatic replication health monitoring and failover, for more information. If you are not using GTIDs and therefore cannot use mysqlfailover, you must set up a master and one or more slaves; then, you need to write an application or script that monitors the master to check whether it is up, and instructs the slaves and applications to change to another master in case of failure. This section discusses some of the issues encountered when setting up failover in this way.
You can tell a slave to change to a new master using the
CHANGE statement. The slave does not check whether the databases on the master are compatible with those on the slave; it simply begins reading and executing events from the specified coordinates in the new master's binary log. In a failover situation, all the servers in the group are typically executing the same events from the same binary log file, so changing the source of the events should not affect the structure or integrity of the database, provided that you exercise care in making the change.
Slaves should be run with the
--log-bin option, and if not using GTIDs then they should also be run without
--log-slave-updates. In this way, the slave is ready to become a master without restarting the slave mysqld. Assume that you have the structure shown in Figure 16.4, “Redundancy Using Replication, Initial Structure”.
Redundancy Using Replication, Initial Structure
MySQL Master holds the master database, the
MySQL hosts are replication slaves, and the
Web machines are issuing database reads and writes. Web clients that issue only reads (and would normally be connected to the slaves) are not shown, as they do not need to switch to a new server in the event of failure. For a more detailed example of a read/write scale-out replication structure, see Section 16.3.4, “Using Replication for Scale-Out”.
Each MySQL Slave (
Slave 3) is a slave running with
--log-bin and without
--log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless
--log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason
MySQL Master becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick
Slave 1, all
Web should be redirected to
Slave 1, which writes the updates to its binary log.
Slave 3 should then replicate from
The reason for running the slave without
--log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. If
--log-slave-updates enabled, it writes any updates that it receives from
Master in its own binary log. This means that, when
Slave changes from
Slave as its master, it may receive updates from
Slave that it has already received from
Make sure that all slaves have processed any statements in their relay log. On each slave, issue
STOP SLAVE, then check the output of
SHOW until you see
Has read all. When this is true for all slaves, they can be reconfigured to the new setup. On the slave
Slave being promoted to become the master, issue
On the other slaves
Slave 2 and
STOP SLAVE and
MASTER TO MASTER_HOST='Slave1'
'Slave1' represents the real host name of
Slave 1). To use
CHANGE MASTER TO, add all information about how to connect to
Slave 1 from
Slave 2 or
port). When issuing the
CHANGE statement in this, there is no need to specify the name of the
Slave 1 binary log file or log position to read from, since the first binary log file and position 4, are the defaults. Finally, execute
Slave 2 and
Once the new replication setup is in place, you need to tell each
Web Client to direct its statements to
Slave 1. From that point on, all updates statements sent by
Web Client to
Slave are written to the binary log of
Slave, which then contains every update statement sent to
The resulting server structure is shown in Figure “Redundancy Using Replication, After Master Failure”.
Redundancy Using Replication, After Master Failure
Master becomes available again, you should make it a slave of
Slave. To do this, issue on
Master the same
CHANGE statement as that issued on
Master then becomes a slave of
S1ave and picks up the
Web writes that it missed while it was offline.
Master a master again, use the preceding procedure as if
Slave 1 was unavailable and
Master was to be the new master. During this procedure, do not forget to run
Master before making
Slave 2, and
Slave 3 slaves of
Master. If you fail to do this, the slaves may pick up stale writes from the
Web Client applications dating from before the point at which
Master became unavailable.
You should be aware that there is no synchronization between slaves, even when they share the same master, and thus some slaves might be considerably ahead of others. This means that in some cases the procedure outlined in the previous example might not work as expected. In practice, however, relay logs on all slaves should be relatively close together.
One way to keep applications informed about the location of the master is to have a dynamic DNS entry for the master. With
bind you can use
nsupdate to update the DNS dynamically.
Setting Up Replication to Use Secure Connections
To use a secure connection for encrypting the transfer of the binary log required during replication, both the master and the slave servers must support encrypted network connections. If either server does not support secure connections (because it has not been compiled or configured for them), replication through an encrypted connection is not possible.
Setting up secure connections for replication is similar to doing so for client/server connections. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave. You must also obtain suitable key files.
To enable secure connections on the master, you must create or obtain suitable certificate and key files, and then add the following configuration options to the master's configuration within the
[mysqld] section of the master's
my.cnf file, changing the file names as necessary:
The paths to the files may be relative or absolute; we recommend that you always use complete paths for this purpose.
The options are as follows:
ssl-caidentifies the Certificate Authority (CA) certificate.
ssl-certidentifies the server public key certificate. This can be sent to the client and authenticated against the CA certificate that it has.
ssl-keyidentifies the server private key.
On the slave, there are two ways to specify the information required for connecting securely to the master. You can either name the slave certificate and key files in the
[client] section of the slave's
my.cnf file, or you can explicitly specify that information using the
[client]section of the slave's
my.cnffile, changing the file names as necessary:
Restart the slave server, using the
--skip-slave-start option to prevent the slave from connecting to the master. Use
CHANGE to specify the master configuration, using the
MASTER_SSL option to connect securely:
mysql> CHANGE MASTER TO
To specify the certificate and key names using the
CHANGE statement, append the appropriate
mysql> CHANGE MASTER TO
-> MASTER_SSL_CA = 'ca_file_name',
-> MASTER_SSL_CAPATH = 'ca_directory_name',
-> MASTER_SSL_CERT = 'cert_file_name',
-> MASTER_SSL_KEY = 'key_file_name';
After the master information has been updated, start the slave replication process:
mysql> START SLAVE;
You can use the
SHOW SLAVE STATUS statement to confirm that a secure connection was established successfully.
For more information on the
CHANGE MASTER TO statement, If you want to enforce the use of secure connections during replication, create a user and use the
REQUIRE option, then grant that user the
REPLICATION privilege. For example:
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'
-> REQUIRE SSL;
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com';
If the account already exists, you can add
REQUIRE to it with this statement:
mysql> ALTER USER 'repl'@'%.mydomain.com'