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:
- Next, we need to tell MySQL where to write the logs in /etc/my.cnf:
- Restart MySQL daemon:
- Login to MySQL shell as the root user:
- Now that we’re logged in as the root user we need to create a user with database replication privileges:
- Next, while still logged into the MySQL shell:
- While still logged in to the MySQL shell, open another terminal on the system (not a MySQL shell):
- Now, go back to the open MySQL shell and issue the following MySQL commands:
- Configuring the MySQL slave, login to the MySQL server as root on the slave system:
- Create the database “mydb” on the slave MySQL server:
- 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::
- 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:
- Restart the MySQL server on the slave:
- 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::
- Final steps (still logged in to MySQL shell):
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 comment --> #skip-networking
should look like this:
skip-networking
bind-address = 127.0.0.1
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
]# service mysqld restart
]$ mysql -u root -p
(note: this command assumes you are not root on the system, but should work even if you are.)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';
mysql> FLUSH PRIVILEGES;
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)
(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.
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 -p<password> --opt mydb > mydb.sql
Important: There is no space between -p and <password>)
alternatively: If you leave out '-p<password>' 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.
mysql> UNLOCK TABLES;
mysql> FLUSH PRIVILEGES;
mysql> exit;
This concludes the MySQL master setup.
]$ mysql -u root -p
(note: this command assumes you are not root on the system, but should work even if you are.)
mysql> CREATE DATABASE mydb;
mysql> exit;
]# mysql -u root -p<password> mydb < /path/to/mydb.sql
Important: There is no space between -p and <password>)
alternatively: If you leave out '-p<password>' mysqldump will prompt you for it before
executing your command.
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
]# service mysqld restart
login to the MySQL shell:
]$ mysql -u root -p
(note: this command assumes you are not root on the system, but should work even if you are.)
once logged in:
mysql> LOAD DATA FROM MASTER;
mysql> FLUSH PRIVILEGES;
(note: stay logged in to the MySQL shell)
mysql> SLAVE STOP;
(note: make sure to change the example values here appropriately.)
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;
* 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:
- Make sure MySQL is running on both systems:
Login to the MySQL shell on both servers like we did above, and issue these commands:
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.
]# chkconfig --list | grep mysql; service mysqld status
Common problems and fixes
Official MySQL documentation can be found here:
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
