MySQL/Replication

What is replication
Replication means that data written on a master MySQL will be sent to separate server and executed there.

Applications:
 * backups
 * spread read access on multiple servers for scalability
 * failover/HA

Replication types:
 * Asynchronous replication (basic master/slave)
 * Semi-asynchronous replication (asynchronous replication + enforce 1 slave replication before completing queries)

Replication configurations:
 * standard: master->slave
 * dual master: master<->master

In Master-Master replication both hosts are masters and slaves at the same time. ServerA replicates to serverB which replicates to serevrA. There are no consistency checks and even with auto_increment_increment/auto_increment_offset configured both servers should not be used for concurrent writes.

Asynchronous replication
That's the most simple replication. A master writes a binary log file, and slaves can read this log file (possibly selectively) to replay the query statements. It's asynchronous, which mean the master and slaves may have different states at a specific point of time; also this setup can survive a network disconnection.

Configuration on the master
In, in the   section: server-id = 1 log-bin
 * Define a server identifier (detects loops?); customarily we'll use  for the server, but it can be different:
 * Replication is based on binary logs, so enable them:
 * 1) or log-bin = /var/log/mysql/mysql-bin.log

Create a new user for the slave to connect with:

Verify your server identifier:

Configuration on each slave
In, in the   section: server-id = 2 report-host=slave1
 * Define a server identifier, different than the master (and different than the other slaves):
 * Verify with:
 * You can also declare the slave hostname to the master (cf.  below):

Declare the master:

If setting up replication from backup, specify start point (add to previous command): Start the replication: This will create a file named  in your data directory, typically  ; this file will contain the slave configuration and status.

TODO: Oct 15 21:11:19 builder mysqld[4266]: 101015 21:11:19 [Warning] Neither --relay-log nor --relay-log-index were used; so  replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.

On the slave
On a slave, type: Or more for a more readable (line-based) output: Example: *************************** 1. row *************************** Slave_IO_State: Master_Host: master_addr Master_User: myreplication Master_Port: 3306 ...

Check in particular: Slave_IO_Running: Yes Slave_SQL_Running: Yes

You can inspect the asynchronous nature of the replication: Seconds_Behind_Master: 0

See also: mysql> SHOW GLOBAL VARIABLES LIKE "%SLAVE%";

On the master
You can see a connection from the slave in the process list.

mysql> SHOW PROCESSLIST\G [...] *************************** 6. row *************************** Id: 14485 User: myreplication Host: 10.1.0.106:33744 db: NULL Command: Binlog Dump Time: 31272 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL

If you enabled, the slave is also visible in: mysql> SHOW SLAVE HOSTS; +---+-+--+---+---+ | Server_id | Host   | Port | Rpl_recovery_rank | Master_id | +---+-+--+---+---+ |        2 | myslave | 3306 |                 0 |         1 | +---+-+--+---+---+ 1 row in set (0.00 sec)

Consistency
Note that this replication is a simple replay, similar to feeding a  output to the   client. Consequently, to maintain the consistency:
 * Avoid writing critical data on the slave
 * Start the replication with identical initial data on both the master and the slave
 * To test: we suspect it would be best to use the same version of MySQL on the master and slaves

Fixing
By default, replicate will stop if it meets an error. This can happen if your master and slaves were not consistent in the beginning, or due to a network error causing a malformed query.

In this case, you'll get a trace in the system log (typically ): Oct 15 21:11:19 builder mysqld[4266]: 101015 21:11:19 [ERROR] Slave: Error 'Table 'mybase.form' doesn't exist' on query. Default database: 'mybase'. Query: 'INSERT INTO `form` (`form_id`,`timestamp`,`user_id`) VALUES ('abed',1287172429,0)', Error_code: 1146

The best way is to reset the replication entirely.

You can also fix the mistake manually, and then ask MySQL to skip  statement this way: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

You can set  to any number, e.g.  . Beware that in this case, it will skip both valid and invalid statements, not only errors.

Another way to fix broken replication is to use Maatkit tools.
 * mk-slave-restart (to restart replication on slave if there are more errors and  can't help)
 * mk-table-checksum (to perform checksumming of tables on master and slave)
 * mk-table-sync (to sync slave with master based on stats generated by mk-table-checksum)

Uninstalling
To erase the replication: mysql> RESET SLAVE;
 * Type:
 * Note: at this point, MySQL paused the slave and replaced the configuration with default values. The  file was also removed.
 * Restart MySQL to clear all configuration.

Warning:  will stop replication. It can be started manually again or (by default) it will automatically resume if you restart the MySQL server. To avoid auto start of replication during process of startup, add to your configuration file: slave-skip-start

If you want to stop the replication for good (and use the server for another purpose), you need to reset the configuration as explained above.

At this point your slave configuration should be completely empty: mysql> SHOW SLAVE STATUS; Empty set (0.00 sec)

SQL Hints
Some hints can be placed in comment before each request concerning the replication. For example via the PHP Mysqlnd plugin (for native driver).


 * MYSQLND_MS_MASTER_SWITCH: forces the request execution on the master.
 * MYSQLND_MS_SLAVE_SWITCH: forces the request execution on the slave.
 * MYSQLND_MS_LAST_USED_SWITCH: forces the request execution on the last used server.

Federated tables
As an alternative to the replication and clustering, the storage engine allows to create a table on a server which synchronizes with the same on another one.