MySQL replication can help you to create an identical copy of a database located on the master database server on a slave server. Not just that, replicating the server will also mean that any updates applied to the database on master server will automatically be applied in a seamless manner to the database on the slave server as well
MySQL replication can help you to create an identical copy of a database located on the master database server on a slave server. Not just that, replicating the server will also mean that any updates applied to the database on master server will automatically be applied in a seamless manner to the database on the slave server as well. Also, the servers will be constantly synchronized, without requiring any user intervention. Be warned though, this is NOT equivalent to a backup procedure. Accidentally running the DELETE command on the master server will have the same effect on the slave server as well. This procedure is extremely useful as a countermeasure for hardware malfunctions or long-term power outages, especially if the servers are located at separate geographical locations.
Let us consider a sample database named exampledb . It is located on a master server with IP address 192.168.0.100 (say). Both the master and slave servers are running Debian Sarge. Rest assured that the configuration is usually independent of the Linux distribution versions being run on the servers.
MySQL is installed on both master and slave servers. But, the database exampledb is only present on master server, along with all of its tables and data.
N.B.: This setup may not work with all system versions in the same way. Be sure to have a Plan B handy, should something go awry.
How to set up the Master server:
1. Edit the file /etc/mysql/my.cnf and comment out the following lines (if present).
#skip-networking
#bind-address = 127.0.0.1
(this will enable networking for MySQL and will allow it to listen on all IP addresses).
2. Next, you have to instruct MySQL to maintain logs stating which modifications were made to the databases located on master server, and which log files it should use to store this data. The slave server will use this log to determine what changes were made to the database on the master.
So, once again edit the file mentioned in point 1, adding the following lines:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1
3. Now restart MySQL with the command: /etc/init.d/mysql restart
4. Now, log into the database as root user, and create a user with replication privileges.
mysql -u root -p
Enter password:
5. Now you are in the shell of MySQL. Put in the command:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY
'';
FLUSH PRIVILEGES;
(Remember to replace with a real password)
6. Now, while staying in the MySQL shell itself, type the following commands:
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
7. Note that the last command will show something like the following:
+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183 | exampledb | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
8. Make sure to jot down the above information with the names of the field. We will need it later while configuring the slave . Don't forget it.
9. Now leave the MySQL shell by typing the command: quit;
There are two ways of transferring a copy of the existing tables and data of exampledb from the master server to the slave. The first process involves creating a database dump, and is a very first process. The second one involves running the command
LOAD DATA FROM MASTER; on the slave server. In the second process, the database on the master server will be in locked condition throughout the transfer procedure. If you have a large database running a production system where high amounts of data transfer take place at any given moment, do not opt for this process. Try the first one instead. Both methods have been described below.
The first method:
Enter the following command in the master server:
mysqldump -u root -p --opt exampledb > exampledb.sql (Where password is the user root password for MySQL. Make sure there are no spaces between p and ).
This command will make a dump of the database exampledb in a file named exampledb.sql . Copy this file to the slave server.
Next, unlock the tables in exampledb by running the command:
mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;
That's it. We are done with Master server.
On to the slave server now:
Create a database exampledb by running the following command:
mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;
Now, we have to import the SQL dump of the database we copied from the master server into the database we created on the slave. Run the command:
mysql -u root -p exampledb < /path/to/exampledb.sql (The password would be the user root password of MySQL. Make sure, once again, that there are no spaces between p and password)
Now you will have to tell MySQL that this is the slave database, the master database is on the server with IP address 192.168.0.100, and that the slave needs to synchronize itself with the master. Do this by entering the following lines into the file /etc/mysql/my.cnf of the slave:
server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb
Done? Then restart the MySQL server by entering the command:
/etc/init.d/mysql restart
The second method :
First, create the database on the slave just like the first method. Then, you will have to get the data from the master database. Do that by running the following command:
mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;
The second method has the added advantage that you can check whether the Load Data command was successful by comparing the databases on Master and Slave servers using the utility phpMyAdmin.
Applicable to both methods:
Finally, run the following command on the slave:
mysql -u root -p
Enter password:
SLAVE STOP;
Now, run the following command in the MySQL shell:
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user',
MASTER_PASSWORD='', MASTER_LOG_FILE='mysql-bin.006',
MASTER_LOG_POS=183;
(Here MASTER_HOST is either the hostname or the IP address of the master server. MASTER_USER is the user account on master server, through which we performed the replication. MASTER_PASSWORD is the password of the MASTER_USER . MASTER_LOG_FILE is the name of the file that MySQL displayed when you ran the command SHOW MASTER STATUS; on the master server. MASTER_LOG_POS is the position that MySQL returned when you ran the command SHOW MASTER STATUS; earlier on the master server. This is why I told you to write the data down back then .
Now, all that's left to do is to start the slave.
Run the following command in the MySQL shell:
START SLAVE;
quit;
That's it. You're done. Test it out now to make sure everything went well.