Netexpertise

Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I’m not even talking about the potential financial loss due to the downtime. A better reliability can be achieved with Mysql either with a cluster or the replication feature. We will focus on replication in this article. However, this is a quick tutorial on how to setup replication; Check Mysql documentation to get more information about it.
 

 
The master can replicate to many slaves, to which different web clients can send their requests. Writes always have to be sent to the master. If they were sent to a slave, they wouldn’t be replicated on other servers.
 
Note Replication is asynchrone as the slave needs a small delay to be up to date. This is particularly suited to applications such as datawarehouses.
 

Fail-over

Replication doesn’t provide auto fail-over as it requires a manual intervention. Another article has been written about implementing failover with Heartbeat. It provides the ability to switch automatically to the slave server within seconds.
 

Getting started

We’ll assume a standalone server is already running with the precious data, and redundancy needs to be implemented. Proceed to the following steps to get your server ready fr replication:

• Activate binary log on the master server. This is absolutely needed, the slave reads the binary logs to synchronize. my.cnf contains log-bin=mysql-bin in the [mysqld] section in my case
• Add server-id=1 in my.cnf.
  This id should be unique for each server (The slave will be 2). Restart the server if those options were not activated.
• Create an account for the slave on the master that’s only allowed to replicate:

  GRANT REPLICATION SLAVE ON *.*
  TO 'slave'@'192.168.0.3' IDENTIFIED BY 'mypassword';

  where ‘slave’ is the username the slave server is going to connect with, 192.168.0.3 the IP address of the slave.

• Setup a new machine for the slave server and install Mysql. my.cnf should contain the following:
  [mysqld]
  server-id=2. Don’t start the service yet!

Transfer data accross to the slave

• First block write operations on the master and record the last values from the binary log:

  mysql> FLUSH TABLES WITH READ LOCK;
  mysql> SHOW MASTER STATUS;

   
  You should get a result similar to the following:

  +-------------------+----------+--------------+------------------+
  | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +-------------------+----------+--------------+------------------+
  | my-db1-bin.000002 |      239 |              |                  |
  +-------------------+----------+--------------+------------------+

   
  Make sure to record those values, they will be needed after starting the slave.

• Shutdown Mysql process on the master and make a copy of the data directory with tar for example. After the tar was done, restart the process. This is the only time the master will be turned off.
• Copy the tar file accross to the slave into the temp folder, uncompress it and copy the files to the data directory. Don’t copy the log files.
  Make sure the rights are correct (They should be identical to the original files). You now have the master data on the slave.

Activate Replication on the Slave

Start the slave with the following option:

--skip-slave-start

 
You can also log warnings in the error log to have a better idea of what is going on

--log-warnings

 
Now that the server is started, connect on to it with Mysql client, and add the master’s details:

mysql> CHANGE MASTER TO
mysql> MASTER_HOST='192.168.0.2',
mysql> MASTER_USER='slave',
mysql> MASTER_PASSWORD='mypassword',
mysql> MASTER_LOG_FILE='my-db1-bin.000002',
mysql> MASTER_LOG_POS=239;

 
The last 2 lines contain of course the values that were collected on the master.

mysql> START SLAVE;

 
Replication can start!
The slave status can be checked via the following command:

mysql> show slave status;

+----------------------------+-------------+-------------+-///-+---------------+-------------------+
| Slave_IO_State             | Master_Host | Master_User |     | Connect_Retry | Master_Log_File   |
+----------------------------+-------------+-------------+-///-+---------------+-------------------+
| Waiting for master to s... | 192.168.0.2 | slave       |     |            60 | my-db1-bin.000006 |
+----------------------------+-------------+-------------+-///-+---------------+-------------------+

+---------------------+----------------------+---------------+-----------------------+------------------+
| Read_Master_Log_Pos | Relay_Log_File       | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |
+---------------------+----------------------+---------------+-----------------------+------------------+
|           514457737 | bak-relay-bin.000007 |      26082745 | my-db1-bin.000006     | Yes              |
+---------------------+----------------------+---------------+-----------------------+------------------+

+-------------------+-///-+---------------------+-----------------+-///-+-----------------------+
| Slave_SQL_Running |     | Exec_Master_Log_Pos | Relay_Log_Space |     | Seconds_Behind_Master |
+-------------------+-///-+---------------------+-----------------+-///-+-----------------------+
| Yes               |     |           514457737 |        26082745 |     |                     0 |
+-------------------+-///-+---------------------+-----------------+-///-+-----------------------+

If replication stops working for some reasons, the last error will be displayed here. This can also find them in mysql error log file.
Disable the –skip-slave-start option from the startup script so replication is activated after a server reboot.

Tags: cluster, Database, Mysql

Comments RSS