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:

  1. Root access to your MySQL servers. (note: this is different than root access to the server itself)
  2. Root access to both of the servers.
  3. 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:
  • 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 commented line –> #skip-networking

    my.cnf should look like this:

  • Next, we need to tell MySQL where to write the logs in /etc/my.cnf:
  • 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:

  • Restart MySQL daemon:
  • Login to MySQL shell as the root user:
  • (note: this command assumes you are not root on the system, but should work even if you are.)

  • 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:
  • Which should give us output similar to this:

    (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.

  • While still logged in to the MySQL shell, open another terminal on the system (not a MySQL shell):
  • 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

    Important: There is no space between -p and <password>)
    alternatively: If you leave out ‘-ppassword’ 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.

  • Now, go back to the open MySQL shell and issue the following MySQL commands:
  • This concludes the MySQL master setup.

  • Configuring the MySQL slave, login to the MySQL server as root on the slave system:
  • (note: this command assumes you are not root on the system, but should work even if you are.)

  • 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::
  • ]# mysql -u root -ppassword; mydb < /path/to/mydb.sql
    Important: There is no space between -p and password)
    alternatively: If you leave out ‘-ppassword’ mysqldump will prompt you for it before executing your command.

  • 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:
  • Add these lines in /etc/my.cnf on the slave server:

  • 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::
  • Login to the MySQL shell:

    Once logged in:

    (note: stay logged in to the MySQL shell)

  • Final steps (still logged in to MySQL shell):
  • (note: make sure to change the example values here appropriately.)

    * 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:
  • Login to the MySQL shell on both servers like we did above, and issue these commands:

    You should see a list of the last 200 queries to the database. Compare the output from both servers and it should match exactly.

  • Make sure MySQL is running on both systems:
  • 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

    Added Reading

    Last Modified: 25 Nov, 2015 at 03:26:39