[MySQL] MySQL replication

MySQL allows to set up database replication to have bases redundance.
There are many advantages to have replication :

to carry out server redundance : if a database server crashs, the server in replication can take its place,
to set up a loadbalancing on requests, for example with ipvsadm.

Replication is not magic, albeit it is easy to do, it brings constraints and complications if there is an important delay. Of course, it's needed to don't write data on a slave to preserve database integrity...
In this article, we will see how to set up replication and how to manage it...

Vocabulary
How does replication work ?
Setting up
Maintenance
How to change the master ?
Synchronize a slave from another

Vocabulary

First, a little of vocabulary :

The master is the reference database.
The slave is the server which have the replication.
Bnlogs are binary files which can be activated on all MySQL servers. These files log update queries (INSERT, UPDATE and other CREATE TABLE for example) to secure data consistency if a server crash (binlogs are replay during boot) but there are also used by replication.

How does replication work ?

To have a functional replication, the master must have its binlogs activated. As said previously, the master writes in its binlogs writing queries.
An activated slave has 2 threads :

IO thread which reads his master's binlogs and write them in its hard disk renaming them relay-logs
SQL thread reads the relay-logs and executes requests.

To summarize, replication has 2 steps : getting requests and executing them.

Setting up

As all network application, there is 2 parts : server and client.
Here, the server is the master and the client is the host which executes replication.

Setting up the master :

In the my.cnf configuration file, just add the folowing lines :

[mysqld]
log-bin=mysql-bin
server-id=1

Some explanations :

The first line means the my.cnf part to modify.
The seconde one activates binlogs. The value is the path to binlogs. If no path is given, the MySQL data folder will be used.
La troisième ligne est "l'authentification" du serveur. Il s'agit d'un entier et doit impérativement être unique au sein du réseau pour que tout fonctionne correctement.

These modifications done, reboot the MySQL server.

Warning :

Binlogs are files which can be numerous and big. So, plan a big partition.

Then, create a user account which will be used by the slave :

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'replication';
FLUSH PRIVILEGES;

Master is now set up.
Now, a database dump must be made to initialize the slave. Of course, it's optionnal if the database is empty...

Updating the slave :

To update a slave, there are many ways :

The simplest : La plus simple : shutdown the server, copy the data folder and ibdata files. This method is simple and fast but files must be copied in the same tree folder than on the master, have the same rights and owner.
Using the LOAD DATA INFILE MySQL command but this can take a long time and this method hasn't be tested.
Doing a database backup with mysqldump.

If the third method is chosen, writing actions must be blocked :

FLUSH TABLES WITH READ LOCK;

When the dump is done and before to reactivate writing actions on the master, check master position in its binlogs :

SHOW MASTER STATUS;

In the result, note the File and Position fields.

On the slave, modify the my.cnf file to add a different server-id than the master's one, relaunch the MySQL service and inject the dump.
Once the dump integrated, set up the slave in order to it knows its master and from which position it must start replication :

CHANGE MASTER TO
MASTER_HOST='master IP address',
MASTER_USER='slave',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='binlog filename',
MASTER_LOG_POS='binlog position';

Some explanation :

MASTER_HOST is the master definition;
MASTER_USER is the replication user defined on the master;
MASTER_PASSWORD is the replication password defined on the master;
MASTER_LOG_FILE is the filename given by the FILE field from the previous 'SHOW MASTER STATUS' query;
MASTER_LOG_POS is the position in the binlog given by the Position field from the previous 'SHOW MASTER STATUS' query.

Then start replication :

START SLAVE;

Finally, allow writing actions on the master :

UNLOCK TABLES;

Maintenance

Replication monitoring

To monitor the replication, it's possible to query the slave as following :

SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event       
Master_Host: prod                    
Master_User: slave                   
Master_Port: 3306                    
Connect_Retry: 60    
Master_Log_File: preprod-bin.000036       
Read_Master_Log_Pos: 501664152                
Relay_Log_File: dev3-relay-bin.000035    
Relay_Log_Pos: 501664573                
Relay_Master_Log_File: preprod-bin.000036
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:                         
Replicate_Ignore_DB:                         
Replicate_Do_Table:                         
Replicate_Ignore_Table:                         
Replicate_Wild_Do_Table:                         
Replicate_Wild_Ignore_Table:
Last_Errno: 0                       
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 501664152
Relay_Log_Space: 501664573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0

Some explanation :

Slave_IO_State : IO thread state, here wainting for master
Master_Host : the master
Master_User : replication user used
Master_Log_File : binlog master file get by the IO thread
Read_Master_Log_Pos : position in the binlog reads by the thread IO
Relay_Log_File : relay-log file written by the IO thead to store queries to execute locally
Relay_Log_Pos : current position in the relay-log file
Slave_IO_Running : IO thread state
Slave_SQL_Running : SQL thread state
Replicate_Do_DB : databases list to replicate
Replicate_Ignore_DB : databases list to not replicate
Replicate_Do_Table and Replicate_Wild_Do_Table fields list tables to replicate. The wild version allows to use a regular expression
Replicate_Ignore_Table and Replicate_Wild_Ignore_Table : tables list to not replicate. The wild version allows to use a regular expression
Exec_Master_Log_Pos : position of the current replicated query in the master binlog
Seconds_Behind_Master : delay with the master.

Note that replication is done by a single thread and that's why the delay with the master is in fact the delay between time of execution in the master and time on the slave. So, longer is the current query, longer is the delay and more there are queries to execute.

Replication errors

It's possible that the replication breaks and has troubles.
Many types can be met :
- the slave can't contact its master and the IO thread is broken
- the slave couldn't execute a query.

To know which is the error cause, use the SHOW SLAVE STATUSG command which gives the MySQL reference id of the error and its cause.
Many errors can be ignored (error 1062 : Duplicate key). To ignore these errors, just do :

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE SQL_THREAD;

Some explanations :

The first line forces the slave to get through the query on which it failed
The second one relaunch the SQL thread. If it was the IO thread which was stoppped, just do START SLAVE IO_THREAD. (START SLAVE; only works too and relaunch both).

Binlogs management

Relay-logs files are consumed when all queries were executed. So, no action is needed for them.
It's not true for binlogs files because it doesn't know its slaves and it doesn't when delete these files.

To delete the binlogs files before they become too numerous, just execute the following query :

SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'mysql-bin.000047';

Some explanations :

On the first line, result gives the current binlog. DO NOT DELETE IT.
The second one deletes all binlogs to the given one excluding it.

An other way is to delete these files on the system (rm) but it's strongly inadvisable because MySQL can to have trouble to generate its binlogs and the PURGE MASTER won't work.

How to change the master ?

Sometimes it's necessary to change the MySQL master server (for example because it's down and a slave must be promoted as the new master and others slaves must referencing it as their new master).

To do this, just :

Disconnected ll clients
Wait for the end of queries execution on the master
RESET MASTER; (on the master)
Wait for the end of queries execution on slaves
Shutdown the master
Find the new master among slaves (the most up-to-date)
Connect clients on the new master
On each slave do CHANGE MASTER TO ...

Synchronize a slave from another

Sometimes it's useful to add a new slave.
As previoulsy seen, setting up a slave involves to stop the service on the master. If a slave already exists, it's possible to use it to set up a new one.
To do this, just make a dump on the slave after stopping writings (here), inject this dump in the future slave and set up the replication.
To initialize replication in the new slave, just do a SHOW SLAVE STATUS on the original slave before removing locks and in the CHANGE MASTER query, put the right values to the Relay_Master_Log_File and Exec_Master_Log_Pos fields from the MASTER_LOG_FILE and MASTER_LOG_POS fields.

To summarize :

Stop replication on the source slave
Lock writing queries on the source slave
Do a dump
Execute SHOW SLAVE STATUS and note the Relay_Master_Log_File et Exec_Master_Log_Pos fields
Remove locks
Relaunch replication
Inject the dump in the new slave
Execute CHANGE MASTER STATUS TO MASTER_HOST='192.168.0.1', MASTER_USER='slave', MASTER_PASSWORD='replication', MASTER_LOG_FILE='File previously noted',MASTER_LOG_POS='Position previously noted';
START SLAVE;

Some issues

If the database has BLOB fields, replicated tables with these fields can have problems (1064 MySQL error).
Stopping a query on the master can stop the replication.