MySQL Database Replication

This Howto will show you how to setup MySQL database replication in case of hardware failure. This is not a permanent backup solution because if the DELETE command was given accidentally, it would also be replicated to the slave. This howto assumes you will not be making mistakes or giving DBA access to people who are not trustworthy. When finished, you’ll have 2 identical MySQL databases on 2 different computers. This process can be adjusted to include multiple databases as well so you can backup all of your MySQL databases. Let’s begin…

Applicable to Centos Versions:

Centos 5.x
Centos 6.x

Requirements

Explanation of requirements:

Root access to your MySQL servers. (note: this is different than root access to the server itself)
Root access to both of the servers.
2 Separate computers you want to use for this purpose.

Doing the Work

This assumes you have MySQL currently installed on both machines and have at least one database you wish to replicate. We’ll be using the database “mydb” as an example, you’ll need to change this to your actual database. We’ll assume the IP of the master MySQL server is 192.168.0.6

Configuring the Master:

First we have to edit /etc/my.cnf. So we have to enable networking for MySQL, and MySQL should listen on all IP addresses, uncomment or add these lines
(note: # is a comment):

This is a commented line –> #skip-networking

my.cnf should look like this:

skip-networking
bind-address = 127.0.0.1

1 2	skip-networking bind-address = 127.0.0.1

Next, we need to tell MySQL where to write the logs in /etc/my.cnf:

These logs are used by the slave to see what has changed on the master, which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database mydb, so we put the following lines into /etc/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=mydb
server-id=1

log-bin = /var/log/mysql/mysql-bin.log

binlog-do-db=mydb

server-id=1

Restart MySQL daemon:

]# service mysqld restart

1	]# service mysqld restart

]$ mysql -u root -p

1	]$ mysql -u root -p

(note: this command assumes you are not root on the system, but should work even if you are.)

Now that we’re logged in as the root user we need to create a user with database replication privileges:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'some_password';
mysql> FLUSH PRIVILEGES;

1 2	mysql> GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'some_password'; mysql> FLUSH PRIVILEGES;

Next, while still logged into the MySQL shell:

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

mysql> USE exampledb;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

Which should give us output similar to this:

+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 167 | mydb | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

(note: copy this information, we’ll need it for the slave setup later.) Also, DO NOT logout of the MySQL shell after “FLUSH TABLES WITH READ LOCK;” or you will
lose the lock.

While still logged in to the MySQL shell, open another terminal on the system (not a MySQL shell):

There are two possibilities to get the existing tables and data from mydb from the master to the slave. The first one is to make a database dump, the second one is to use the: LOAD DATA FROM MASTER; command on the slave.

The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and recommend to follow the first method in this case. However, the latter method is very fast, so both will be described here.

Method #1

]# mysqldump -u root -ppassword; --opt mydb > mydb.sql

1	]# mysqldump -u root -ppassword; --opt mydb > mydb.sql

Important: There is no space between -p and <password>)
alternatively: If you leave out ‘-ppassword’ mysqldump will prompt you for it before executing your command.

This will create an SQL dump of mydb in the current working directory with the filename:
mydb.sql. Transfer this file to your slave server.

Method #2
If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do at this moment.

Now, go back to the open MySQL shell and issue the following MySQL commands:

mysql> UNLOCK TABLES;
mysql> FLUSH PRIVILEGES;
mysql> exit;

mysql> UNLOCK TABLES;

mysql> FLUSH PRIVILEGES;

mysql> exit;

This concludes the MySQL master setup.

Configuring the MySQL slave, login to the MySQL server as root on the slave system:

]$ mysql -u root -p

1	]$ mysql -u root -p

(note: this command assumes you are not root on the system, but should work even if you are.)

Create the database “mydb” on the slave MySQL server:

mysql> CREATE DATABASE mydb;
mysql> exit;

1 2	mysql> CREATE DATABASE mydb; mysql> exit;

If you have made an SQL dump of mydb on the master and have transferred it to the slave, then it is now time to import the SQL dump into our newly created mydb on the slave::

]# mysql -u root -ppassword; mydb < /path/to/mydb.sql
Important: There is no space between -p and password)
alternatively: If you leave out ‘-ppassword’ mysqldump will prompt you for it before executing your command.

Next we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.6, and that the master database to watch is mydb. Therefore we add the following lines to /etc/my.cnf:

Add these lines in /etc/my.cnf on the slave server:

server-id=2
master-host=192.168.0.6
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=mydb

server-id=2

master-host=192.168.0.6

master-user=slave_user

master-password=secret

master-connect-retry=60

replicate-do-db=mydb

Restart the MySQL server on the slave:

]# service mysqld restart

1	]# service mysqld restart

If you have not imported the master mydb by using an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is now time to get the data from the master mydb::

Login to the MySQL shell:

]$ mysql -u root -p

1	]$ mysql -u root -p

Once logged in:

mysql> LOAD DATA FROM MASTER;
mysql> FLUSH PRIVILEGES;

1 2	mysql> LOAD DATA FROM MASTER; mysql> FLUSH PRIVILEGES;

(note: stay logged in to the MySQL shell)

Final steps (still logged in to MySQL shell):

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.6', MASTER_USER='slave_user', MASTER_PASSWORD='&lt;some_password&gt;', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=167;
mysql> START SLAVE;
mysql> FLUSH PRIVILEGES;
mysql> exit;

mysql> SLAVE STOP;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.6', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=167;

mysql> START SLAVE;

mysql> FLUSH PRIVILEGES;

mysql> exit;

(note: make sure to change the example values here appropriately.)

* MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.6).
* MASTER_USER is the user we granted replication privileges on the master.
* MASTER_PASSWORD is the password of MASTER_USER on the master.
* MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Troubleshooting

How to test.

Now query your database and compare the databases on both systems and make sure replication is taking place:

mysql> use mydb;
mysql> select * from queries order by querie_id desc limit 200;

1 2	mysql> use mydb; mysql> select * from queries order by querie_id desc limit 200;

You should see a list of the last 200 queries to the database. Compare the output from both servers and it should match exactly.

Make sure MySQL is running on both systems:

]# chkconfig --list | grep mysql; service mysqld status

1	]# chkconfig --list \| grep mysql; service mysqld status

Common problems and fixes

Official MySQL documentation can be found here:

http://dev.mysql.com/doc/

More Information

None at this time.

Disclaimer

We test this stuff on our own machines, really we do. But you may run into problems, if you do, come to #centoshelp on irc.freenode.net

This was tested on Centos 5.x and 6.x

MySQL Database Replication

Applicable to Centos Versions:

Requirements

Doing the Work

Troubleshooting

How to test.

Common problems and fixes

More Information

Disclaimer

Added Reading