How to replicate your MySQL database in Fedora, Ubuntu and Opensuse

This tutorial will guide you on how to create MySQL database replication over different servers. All updates on the Master server will be replicated on the slave server. This method may not save you in case of accidental delete operation as that change would have been automatically performed on the slave server as well. This method is best to protect against hardware failures or accidental crashes.

This is not a backup mechanism, its live replication. If you want to create regular backups of your databases you should read, Backup your MySQL databases automatically using Automysqlbackup.

The biggest benefit of this method is that in case your master server fails, you can switch onto the slave mysql server and the updated data be there, because the databases were in sync.

I am assuming mysql is already installed on both the server, if not follow this how to - How to install mysql server.

Note: I have successfully tested it on Fedora 12 (as Master) and Ubuntu 9.10(as Slave) server. I do not issue any guarantee that it will work for you.

Basic Principle

We keep a log of every action/query that is done on the master server. The master stores all that in a log file. The slave read all those binary logs, which stores all the statements that changes the state of the database and repeat those on its Database, i.e. replicates the original database.

This will not always guarantee that the slave database server will be in sync with the master database server. For example - a busy slave might not have caught with the master server and a SELECT query can't be exchanged between a slave and a master server.

Its not an answer to all the performance problems. If you use MyISAM engine, table locking will still occur and a database under high stress can still struggle.

In order to enable this replication mechanism log into Master and slave at the same time in two different shell console.

Master IP: 10.42.43.1
Database Name: test_db
Slave user: user_slave
Password for Slave user: password_slave

Configuring the Master DB server

Login in master as a root, now we will create a new user say "user_slave" on Master server that the Slave user will connect as and will give slave user the required replication privileges.

[root]# mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'user_slave'@'%' IDENTIFIED BY 'password_slave';

mysql>FLUSH PRIVILEGES;

Create a directory for the mysql-bin logs

[root]# mkdir /var/log/mysql

[root]# chown mysql:mysql /var/log/mysql

Now edit the /etc/my.cnf to tell the mysql which database it should write logs and specify that this mysql is the Master server. In this case we will replicate our database test_db  write these lines under [mysqld]

[root]# vim /etc/my.cnf     // for RedHat or Fedora or OpenSuse systems

[root]# vim /etc/mysql/my.cnf   // for Debian or Ubuntu systems.

[mysqld]

[ . . . ]

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

binlog-do-db = test_db

server-id = 1

[ . . . ]

Now restart the mysql server

[root]# service mysqld restart

or

[root]# service mysql restart     // In case of Debain or Ubuntu or Opensuse

Now, we will put a READ LOCK on the database so that no script can alter the state of the database while we take the backup.

mysql> USE test_db

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

+--------------------+----------+----------------------+------------------+
| File               | Position | Binlog_Do_DB         | Binlog_Ignore_DB |
+--------------------+----------+----------------------+------------------+
| test_db-bin.000001 | 106      | test_db              |                  |
+--------------------+----------+----------------------+------------------+

FLUSH TABLES WITH READ LOCK closes all tables for all databases  with a read lock until you explicitely release the lock with UNLOCK TABLES. FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

This is what exactly it does: 

1. set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run
2. close open tables - this step will block until all statements started previously have stopped
3. set a flag to block commits

Do not close the Mysql shell, because if you do then the database lock will be removed, and we don't want to do that right now because we want to do a Mysqldump of the current state of the database.

Now create the dump of this database and copy it to slave server:

[root]# mysqldump -u root -p test_db > test_db.sql

# Scp this sql file to the slave server

[root]# scp text_db.sql root@slave-server:

You can now close down this shell. On the first mysql shell now run the following:

mysql> UNLOCK TABLES;

mysql>  QUIT;

Configuring the Slave DB server

Now we will configure the Mysql on the slave server and let MySQL know that this is a slave server. Edit /etc/my.cnf ( or /etc/mysql/my.cnf for Ubuntu and Debian) and add these lines under [mysqld]

[mysqld]
[ . . . ]
server-id = 2
master-host = 10.42.43.1
master-user = user_slave
master-password = password_slave
replicate-do-db = test_db
master-connect-retry = 60

Now restart the slave server,

[slave-server]$ service mysqld restart

or

[slave-server]$ service mysql restart // In case of Ubuntu or Debian or OpenSuse

Ok now we will create the database and make stop all the slave processes if it exists

mysql> STOP SLAVE;

mysql> CREATE DATABASE test_db;

mysql> quit;

# Now import the original snapshot of the sql on the slave server

[slave-server]$ mysql -u root -p test_db < test_db.sql

Now run the following command to configure that the server2 is a slave of server 1

mysql> CHANGE MASTER TO MASTER_HOST='10.42.43.1', MASTER_USER='user_slave', MASTER_PASSWORD='password_slave', MASTER_LOG_FILE='test_db-bin.000001', MASTER_LOG_POS=106;

MASTER_LOG_FILE and MASTER_LOG_POS is taken from the table that we talked about earlier.

Now start the slave server

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

If you see output similar as above, then its configured properly. You can try testing it out.