Create a replicated MySQL database

Whoops, server crashed. My data is flying.

When did you save your database for the last time?
With replicated MySQL databases, the answer is a few seconds before the crash.
Replication brings you security, performance and distribution of your valuable data.

This tutorial introduces you step-by-step to creating a replicated master database with a slave database with MySQL.

Why replicate a MySQL database

Using a replicate database provides several advantages over a simple installation:

  • Security: the backup of the slave database avoids locks on the master database. There is no unavailability. Replication resumes as soon as the backup is complete.
  • Performance:
    • utiliser différents moteurs de bases de données sur les 2 serveurs; le moteur InnoDB sur le serveur maître pour profiter des écritures de sécurisation des transactions et du moteur MyISAM sur l’esclave pour des performances de lecture.
    • using the master server for update operations and slave servers for read operations. Master servers can be cascaded to further increase performance. This system was presented by the architects of the Flickr site.
    • by performing asynchronous data extractions on the slave.
  • Geographically distribute databases on slave servers to optimize bandwidth and minimize data privacy issues, with each database having users with specific rights.

Differences Between Replicated Databases and a MySQL Cluster

Replicated databases do not provide the same services as a database cluster.
A MySQL database cluster automatically manages load balancing, integration of new servers, high availability without service interruption.
These services require human intervention for the replicated databases. In return, installing replicate databases is easier.

Set up the master data server

In this tutorial, the IP address of the master server is 192.168.0.201.
Initialize the /etc/mysql/my.cnf file settings of the following master database:

mysql-master:/# vi /etc/mysql/my.cnf

server-id = 201
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 3
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = mysql
#bind-address = 127.0.0.1
skip-innodb

and restart the MySQL server:

mysql-master:/# /etc/init.d/mysql restart

Create a dedicated user for replication:

mysql-master:/# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@'%' IDENTIFIED BY 'replication';

Get the status of the master database to initialize the slave:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> exit

Configure the slave data server

The IP address of the slave server is 192.168.0.202.

Edit the /etc/mysql/my.cnf file of the slave database:

mysql-slave:/# vi /etc/mysql/my.cnf

and initialize the following parameters:

server-id = 202
#bind-address = 127.0.0.1
skip-innodb

Restart the MySQL server:

mysql-slave:/# /etc/init.d/mysql restart

Initialize the slave database from the master database:

mysql-slave:/# mysqldump -uroot -pPassword -hmysql-master --all-databases | mysql -uroot -pPassword

Define the master server on the slave server from the previously retrieved status information and start replication:

mysql-slave:/# mysql -u root -p
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.201',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='replication',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=106;

mysql> START SLAVE;

Check the status of the slave server:

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.201
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
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: 106
Relay_Log_Space: 552
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

The Slave_IO_Running and Slave_SQL_Running flags are set to Yes. Everything is fine.

Note 1: You can create as many slaves as you want by repeating the steps that concern them and using a single (unique) server-id.

Note 2: You can define a slave server as the master of another slave. The second-level slave then uses the first-level slave as a source of replication. This is useful for very busy configurations.
Flickr example: the master is used for the updates and is solicited only by 2 slaves, these 2 slaves are used as sources of replication by 2 slave farms of second level. One of the slave farms is used for data readings, the other for longer search operations.
SkyRock example: a similar architecture to Flickr with clusters of slaves dedicated to targeted functions (profile management, comment, rating, content …) can deliver 5 billion pages per month.

Test the result

To verify that the replication is operational, we will create a database on the master server. It should then be replicated to the slave server.

mysql-slave:/# mysql -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

mysql-master:/# mysqladmin -uroot -p create test
mysql-master:/# mysqlshow -uroot -p
+--------------------+
| Databases |
+--------------------+
| information_schema |
| database |
| mysql |
| test |
+--------------------+

mysql-slave:/# mysqlshow -uroot -p
+--------------------+
| Databases |
+--------------------+
| information_schema |
| database |
| mysql |
| test |
+--------------------+

The test database has been successfully replicated from the master database to the slave database.

What to do in case of error

Errors always occur on the slave machine side. For various reasons, replication stops.
Study the slave logs, fix the problem on the master or slave, and restart the replication:

mysql> START SLAVE;

Check the status of the slave:

mysql> SHOW SLAVE STATUSG

If Slave_IO_Running and Slave_SQL_Running are not Yes and you can not fix the problem, you can completely reset the slave database from a backup as described in the slave data server configuration section.

What to do in case of hardware failure

Server failures can occur on the master and the slave.

If the slave fails, replace any connections from your applications to this base with a connection to another slave or to the master. Reconfigure a slave by scrolling the procedure.

If the master fails, plug your applications into a slave server. The slave becomes the new master server. If you have multiple slaves, change their configurations to use this master.
Reconfigure the failed server and initialize it as a slave to the new master.

Source : MySQL