MySQL Replication

Published on Jan. 22, 2006

Whether you want to have a live copy to help distribute workload, or have a copy as a pseudo-hot-swapable backup like me, replicating your MySQL server is a must. While this isn't a foolproof solution (a DELETE/DROP command issues will replicate as well), it does help protect against data loss due to hardware failure. Here's how I did it.

You need two+ servers with MySQL already installed. I have one one with MySQL 5.0.16 and the other with 5.0.18.
Sample DB used: testdb
Username: replicator
Password: password
IP Master: 192.168.1.5
IP Slave: 192.168.1.6

Master Configuration

The first step is to configure the master server. We will first edit /etc/my.cnf to allow for networking. While mine was already setup to allow non-localhost connections, maybe your's isn't. On my master server I had to copy /etc/my-small to /etc/my.cnf. So...

 kelvin@192.168.1.5:/etc$ sudo cp my-small.cnf my.cnf
 kelvin@192.168.1.5:/etc$ sudo vi /etc/my.cnf 

Now look for, and uncomment, the following lines:

 #skip-networking
 #bind-address = 127.0.0.1 

Peachie, now was can do networking. Next we need to setup binary logging. What this form of logging does is allow MySQL to complete much more complete restores than traditional logging. We will specify where the log should be stored, which database to use and that this server is a master server.

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

Restart MySQL:

 kelvin@192.168.1.5:/etc/init.d$ sudo ./mysql restart
 -OR-
 kelvin@192.168.1.5:/etc/rc.d$ sudo ./rc.mysqld restart
 

 STOPPING server from pid file /var/run/mysql/mysql.pid
 060122 00:52:05  mysqld ended
 Starting mysqld daemon with databases from /var/lib/mysql
 

If you had a problem with it you can start mysql with logging enabled. I would first check that /var/log/mysql exists and is writable by the mysql users. If not...

 kelvin@192.168.1.5:/var/log$ sudo mkdir mysql
 kelvin@192.168.1.5:/var/log$ sudo chown mysql.mysql mysql 

Next we enter the mysql cli.

 kelvin@192.168.1.5:/var/log$ mysql -u root -p Enter password: [enter password] 

Now to actually create the user and .

 mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'passowrd';
 mysql> FLUSH PRIVILEGES;
 mysql> USE testdb;
 mysql> FLUSH TABLES WITH READ LOCK;
 mysql> SHOW MASTER STATUS;

 +------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+-----+--------------+------------+
 | mysql-bin.000005 |       98 | testdb       |                  |
 +------------------+----------+----------+-----------+
 1 row in set (0.00 sec)
 

Do not quit out of this sql session; leaving unlocks the database. Write down that information, take a screenshot, send it as an instant message to somebody. You will need it shortly.

We now need to do a dump of the current database and sent it to the slave server. There are several ways to do this. Some peole do a simple dump via "mysqldump" -- I've decided just to tar the data directory.

 kelvin@192.168.1.5:~$ cd /var/lib/mysql
 kelvin@192.168.1.5:/var/lib/mysql$ tar -cvf /tmp/mysql.tar . //include this period!
 kelvin@192.168.1.5:/var/lib/mysql$ cd /tmp
 kelvin@192.168.1.5:/var/lib/mysql$ ls
 kelvin@192.168.1.5:/var/lib/mysql$ mysql.tar
 kelvin@192.168.1.5:/var/lib/mysql$ sftp kelvin@192.168.1.6
 Connecting to 192.168.1.5...
 kelvin@192.168.1.6's password: [enter password]
 Enter password: [Enter it]
 kelvin@192.168.1.6:~$ put mysql.tar
 kelvin@192.168.1.6:~$ exit
 

Now, since we locked the tables on the master before, we need to unlock them.

 kelvin@192.168.1.5:~$ mysql -u root -p
 Enter password: [enter password]
 mysql>  UNLOCK TABLES;
 mysql> quit;
 

So you now have the databases exported as mysql.tar on the slave server. We will untar them and move them to the slave server's data directory. I decided not to move the mysql directory.

 kelvin@192.168.1.6:~$ cd /tmp
 kelvin@192.168.1.6:~$ mkdir mysqlDump
 kelvin@192.168.1.6:~$ mv mysql.tar mysqlDump
 kelvin@192.168.1.6:~$ cd mysqlDump
 kelvin@192.168.1.6:~$ tar -xvf mysql.tar
 kelvin@192.168.1.6:~$ rm -fr mysql
 kelvin@192.168.1.6:~$ sudo mv -f * /var/lib/mysql
 

Congrats, the master server is now configured!!

Slave Configuration

First we create the testdb database on the master server and import the dump we did from the master server.

 mysql> CREATE DATABASE testdb;
 mysql> quit;
 kelvin@192.168.1.6:~$ ls
 testdb.sql
 kelvin@192.168.1.6:~$ mysql -u root -p testdb < testdb.sql
 Enter password: [enter password]
 

Time to configure the slave server to communicate with the master server. We want to tell the slave server that it is a slave, where the master is, to use the replicator user, the replicator user password, retry rate, and what database to use.

 kelvin@192.168.1.6:~$ cd /etc
 kelvin@192.168.1.6:/etc$ vi my.cnf 

Now add these lines. Mine are added on line 43, just make sure your's are inserted into the [mysqld] section.

 server-id=2
 master-host=192.168.1.5
 master-user=replicator
 master-password=password
 master-connect-retry=60
 replicate-do-db=testdb
 

Restart MySQL.

 kelvin@192.168.1.6:/etc/init.d$ sudo ./mysql restart
 -OR-
 kelvin@192.168.1.6:/etc/rc.d$ sudo ./rc.mysqld restart
 

Lastly, we turn on the slave server and say "replicate please!" Get out the information you wrote down before. mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.5', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=98;
mysql> START SLAVE; mysql> quit;

That's it! Not too bad, eh? Now enter in a little information into the master server and do a select * from testdb to see the magic happen.

Additional Info

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://www.howtoforge.com/mysql_database_replication

Comment

Do you notice any errors? If so, let me know! I tried to make this as close to the process as I have used, but sometimes I forget to cross my Ts and dot my Is.

Comments are currently closed for this entry.