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:

• 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 - you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.

Replication Solutions

Using replication for backups

• To use replication as a backup solution, replicate data from the master to a slave, and then back up the data slave. The slave can be paused and shut down without affecting the running operation of the master, so you can produce an effective snapshot of “live” data that would otherwise require the master to be shut down.
• Another backup strategy, which can be used for either master or slave servers, is to put the server in a read-only state. The backup is performed against the read-only server, which then is changed back to its usual read/write operational status.

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 default_storage_engine and 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 used mysqldump to create the database snapshot on your master, you could edit the dump file text to change the engine type used on each table.Another alternative for mysqldump is to disable engine types that you do not want to use on the slave before using the dump to build the data on the slave. For example, you can add the --skip-federated option on your slave to disable the FEDERATED engine. If a specific engine does not exist for a table to be created, MySQL will use the default engine type, usually MyISAM. (This requires that the NO_ENGINE_SUBSTITUTION SQL mode is not enabled.) If you want to disable additional engines in this way, you may want to consider building a special binary to be used on the slave that only supports the engines you want.
• If you are using raw data files (a binary backup) to set up the slave, you will be unable to change the initial table format. Instead, use ALTER TABLE to change the table types after the slave has been started.
• For new master/slave replication setups where there are currently no tables on the master, avoid specifying the engine type when creating new tables.

If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:

1. Stop the slave from running replication updates:

   mysql> STOP SLAVE;

• This will enable you to change engine types without interruptions.
• Execute anALTER TABLE ...
  ENGINE='engine_type' for each table to be changed.
• Start the slave replication process again:

mysql> START SLAVE;

Although the 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_writer_connect()
• safe_reader_connect()
• safe_reader_statement()
• safe_writer_statement()

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 START

SLAVE:

• Replication slave 1 should use --replicate-wild-do-table=databaseA.%.

• Replication slave 2 should use --replicate-wild-do-table=databaseB.%.

• Replication slave 3 should use --replicate-wild-do-table=databaseC.%.

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:

• Synchronize all the data to each slave, and delete the databases, tables, or both that you do not want to keep.
• Use mysqldump to create a separate dump file for each database and load the appropriate dump file on each slave.
• Use a raw data file dump and include only the specific files and databases that you need for each slave.

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:

• Master 1 is the primary master where all changes and updates are written to the database. Binary logging should be enabled on this machine.
• Master 2 is the slave to the Master 1 that provides the replication functionality to the remainder of the slaves in the replication structure. Master 2 is the only machine permitted to connect to Master 1. Master 2 also has binary logging enabled, and the --log-slave-updates option so that replication instructions from Master 1 are also written to Master 2's binary log so that they can then be replicated to the true slaves.
• Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and replicate the information from Master 2, which actually consists of the upgrades logged on Master 1.

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:

• If possible, put the relay logs and the data files on different physical drives. To do this, use the --relay-log option to specify the location of the relay log.
• If the slaves are significantly slower than the master, you may want to divide up the responsibility for replicating different databases to different slaves.
• If your master makes use of transactions and you are not concerned about transaction support on your slaves, use MyISAM or another nontransactional engine on the slaves.
• If your slaves are not acting as masters, and you have a potential solution in place to ensure that you can bring up a master in the event of failure, then you can switch off --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
MASTER TO 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

The MySQL Master holds the master database, the MySQL
Slave hosts are replication slaves, and the Web
Client 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 1, Slave
2, and 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
Clients should be redirected to Slave 1, which writes the updates to its binary log. Slave
2 and Slave 3 should then replicate from Slave 1.

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 Slave
1 has --log-slave-updates enabled, it writes any updates that it receives from Master in its own binary log. This means that, when Slave
2 changes from Master to Slave
1 as its master, it may receive updates from Slave
1 that it has already received from Master.

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE
IO_THREAD, then check the output of SHOW
PROCESSLIST until you see Has read all
relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave Slave
1 being promoted to become the master, issue STOP
SLAVE and RESET MASTER.

On the other slaves Slave 2 and Slave
3, use STOP SLAVE and CHANGE
MASTER TO MASTER_HOST='Slave1' (where '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 Slave
3 (user, password, port). When issuing the CHANGE
MASTER TO 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 START
SLAVE on Slave 2 and Slave
3.

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
1 are written to the binary log of Slave
1, which then contains every update statement sent to Slave
1 since Master died.

The resulting server structure is shown in Figure “Redundancy Using Replication, After Master Failure”.

Redundancy Using Replication, After Master Failure

When Master becomes available again, you should make it a slave of Slave
1. To do this, issue on Master the same CHANGE
MASTER TO statement as that issued on Slave
2 and Slave
3 previously. Master then becomes a slave of S1ave
1 and picks up the Web
Client writes that it missed while it was offline.

To make 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 RESET
MASTER on Master before making Slave 1, 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:

[mysqld] ssl-ca=cacert.pem ssl-cert=server-cert.pem ssl-key=server-key.pem

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-ca identifies the Certificate Authority (CA) certificate.
• ssl-cert identifies the server public key certificate. This can be sent to the client and authenticated against the CA certificate that it has.
• ssl-key identifies 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 CHANGE
MASTER TO statement:

• To name the slave certificate and key files using an option file, add the following lines to the [client] section of the slave's my.cnf file, changing the file names as necessary:

  [client] ssl-ca=cacert.pem ssl-cert=client-cert.pem ssl-key=client-key.pem

Restart the slave server, using the --skip-slave-start option to prevent the slave from connecting to the master. Use CHANGE
MASTER TO to specify the master configuration, using the MASTER_SSL option to connect securely:

mysql> CHANGE MASTER TO     -> MASTER_HOST='master_hostname',     -> MASTER_USER='replicate',     -> MASTER_PASSWORD='password',     -> MASTER_SSL=1;

To specify the certificate and key names using the CHANGE
MASTER TO statement, append the appropriate MASTER_SSL_xxx options:

mysql> CHANGE MASTER TO     -> MASTER_HOST='master_hostname',     -> MASTER_USER='replicate',     -> MASTER_PASSWORD='password',     -> MASTER_SSL=1,     -> 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
SSL option, then grant that user the REPLICATION
SLAVE 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
SSL to it with this statement:

mysql> ALTER USER 'repl'@'%.mydomain.com'
REQUIRE SSL;