You can back up your MySQL database using the built-in dump tool, but the second you do that backup is out of date. Since so many companies and web-based tools rely heavily on databases, it’s crucial that those databases are as up-to-date as possible. One very simple solution is database replication, which keeps a real-time copy of the database on a remote server. With this in place, if something happens to the primary database, it will be much easier to get your database back up and running with current information.
This is what you’ll need to set up MySQL database replication:
- Two correctly configured MySQL servers
- Root access and access to the database administrator on both servers
- Ability to work from the command line
The setup will use two machines: the Master and the Slave. We’ll start with the setup of the Master.
Configure the Master
The first step is to enable MySQL for networking. To do this, open the file /etc/mysql/my.conf, look for the following lines, and uncomment them if they exist:
#skip-networking #bind-address = 127.0.0.1
If the lines do not exist, you should add them (minus the “#”). Within the same file, we have to make MySQL aware of the database that will be replicated. You can do this with these lines:
log-bin = /var/log/mysql/mysql-bin.log
DATABASE_TO_BE_REPLICATED is the actual name of the database you want to replicate.
The above lines tell MySQL the following:
Line 1: Configures the log file to be used
Line 2: Configures the database to be replicated
Line 3: Configures the machine as the Master
After you add these lines, save the my.conf file and restart MySQL with the command /etc/inid.t/mysqld restart.
You might need to use sudo to issue the above command. Another option for the restarting of the MySQL daemon is using the service command like service mysqld start.
With this configuration complete, it’s time to set up a user for replication privileges. Log on to the Master and issue the command mysql -u root -p. Enter the password for the MySQL admin password. Upon successful authentication, you will be at the MySQL prompt, where you will enter the command GRANT REPLICATION SLAVE ON *.* TO ‘USER’@’%’ IDENTIFIED BY ‘PASSWORD’;. USER is the user who will have replication privileges, and the PASSWORD is that user’s MySQL password.
With that command issued, you must flush the database privileges with the command FLUSH PRIVILEGES;.
Let’s make sure MySQL can see the Master with the command SHOW MASTER STATUS;. This command should list information (including the all-important position number, which will be required for the Slave setup) for the database to be replicated. You need to write down that information, which will look something like this:
1 row in set (0.00 sec)
The next step is to retrieve the tables and data from the database to be replicated; in order to do this, the database must be temporarily locked. When the database is locked, it cannot be used, so do this at a time when the database won’t be needed. To lock the database, issue the command FLUSH TABLES WITH READ LOCK;.
You must dump the database that will be copied to the Slave. There used to be a command LOAD DATA FROM MASTER, but that command has been deprecated. So, to get the data, you must do a dump with the command mysqldump -u root -p DATABASE_TO_BE_REPLICATED > DATABASE_TO_BE_REPLICATED.sql where DATABASE_TO_BE_REPLICATED is the name of the actual database being replicated. You’ll be prompted for the MySQL admin password; enter that password, and the dump will process.
Now unlock the tables with the command UNLOCK TABLES; and then quit the MySQL prompt with the command quit.
Copy that database dump file to the Slave (using a USB drive or the scp command) and then restore the database (on the SLAVE) with the command mysql -u root -p DATABASE_TO_BE_REPLICATED < DATABASE_TO_BE_REPLICATED.sql where DATABASE_TO_BE_REPLICATED is the name of the database being replicated.
Configuring the Slave
In order to configure the Slave, open the /etc/mysql/my.conf file, add the following, and save and close that file:
- IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
- USER is the database administrator with replication privileges.
- USER_PASSWORD is the password associated with the USER.
- DATABASE is the name to be replicated.
This next step requires information returned from the SHOW MASTER STATUS; command that was run. Stop the Slave by issuing these commands:
mysql -u root -p (enter the MySQL admin password)
You must run the following command:
CHANGE MASTER TO MASTER_HOST=’IP_ADDRESS_OF_MASTER’, MASTER_USER=’USER’, MASTER_PASSWORD=’USER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.007′, MASTER_LOG_POS=NUMBER;
Where the following applies:
- IP_ADDRESS_OF_MASTER is the actual IP address of the Master
- USER is the MySQL admin user
- USER_PASSWORD is the password for the USER
- NUMBER is the Position Number reported from the SHOW MASTER STATUS command.
Restart the Slave by issuing the command SLAVE START; and then exit the MySQL prompt with the command quit. Now you can follow these steps to make sure everything is working:
1. Issue the command mysql -u root -p (enter the database admin password).
2. Issue the command SHOW SLAVE STATUS;.
You should see something like this:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
If all is set to Yes, you now have a working, replicated MySQL database. Congratulations!