Home > Web Development > MySQL Replication Guide for Fedora Linux

MySQL Replication Guide for Fedora Linux

March 4th, 2011

This guide provides detailed instructions for configuring MySQL replication on Fedora Linux.

What is MySQL replication?

Replication allows MySQL to make a continuos copy of your databases on one or more servers. In a replication environment, there is a master server and one or more slave servers. All INSERTs, UPDATEs, and DELETEs happen on the master server. Every time a database on the master server is changed, the slave servers are updated to reflect the change. For instance, if you add a record on the master server, that record will be added to each slave server, as well. Most of this happens in near-real-time. For quick-executing SQL statements, you may notice no lag between the master server and the slaves.

What is the binary log?

The master server logs all of the SQL statements it executes to the binary log. Each of the slave servers read the binary log and execute the same SQL statements. This is called statement-based replication.

Why should I use replication?

Replication is a great way to improve performance. You can use your master server for all data modification (INSERT, UPDATE, and DELETE) and for those SELECT statements that need real-time data. You can then use your slave servers for more resource intensive read-only reporting tasks. This allows you to spread your database demand across multiple servers.

Installing MySQL

What follows is a quick set of instructions for installing MySQL on Fedora Linux. Remember, you’ll need at least two MySQL installations, one on the master and one on the slave. If you already have MySQL installed, you can skip this part. We’ll describe configuration of the master and slave in the next section.

  1. Install MySQL and any related components you wish.
    sudo yum install mysql mysql-server
    
  2. Make MySQL run at system startup.
    sudo chkconfig mysqld on
    
  3. Start the MySQL service.
    sudo service mysqld start
    

Securing the MySQL User Accounts

MySQL is installed with several default user accounts, and none of them require a password to login. Some of the default user accounts provide anonymous access, allowing you to login without a username. We’re going to login to MySQL and view a list of all the default user accounts. I recommend setting a strong password for the root accounts, and removing the anonymous accounts. All of the following steps should be completed on both the master and the slave.

  1. Login to MySQL
    mysql --user=root mysql
    
  2. Look at the list of existing user accounts.
    SELECT User, Host, Password FROM user;
    
    +--------------+--------------------+----------------+
    | User         | Host               | Password       |
    +--------------+--------------------+----------------+
    | root         | localhost          |                |
    | root         | db1.example.com    |                |
    | root         | 127.0.0.1          |                |
    |              | localhost          |                |
    |              | db1.example.com    |                |
    +--------------+--------------------+----------------+
    

    Notice that none of these accounts are secured by passwords. We’ll want to assign passwords to the root accounts. Also, notice the two anonymous accounts. We’re going to remove these.

  3. Assign passwords to the root user accounts.
    SET PASSWORD FOR 'root'@'localhost'=PASSWORD('S0meStr0ngP4ssw0rd');
    SET PASSWORD FOR 'root'@'db1.example.com'=PASSWORD('S0meStr0ngP4ssw0rd');
    SET PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('S0meStr0ngP4ssw0rd');
    
  4. Remove the anonymous user accounts.
    DROP USER ''@'localhost';
    DROP USER ''@'db1.example.com';
    
  5. While we’re at it, we will need to create a user account that will handle replication. You’ll want to make sure that MySQL only allows this replication user to connect from a known network, preferrably one behind your firewall. In my example, I’m only accepting connections from 192.168.1.x.
    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repuser'@'192.168.1.%' IDENTIFIED BY 'An0th3rStr0ngP4ssw0rd'
    

Identifying Databases for Replication

If you’re reading this guide, you probably already have one or more databases that you would like to replicate. If so, you should import those databases into the master now. You won’t import them into the slave yet. We’ll do that later. It’s also possible that you haven’t yet created the database that you wish to replicate. We’ll cover both scenarios below.

  1. If you already have a database on another server, you can import it into your master server using the source command.
    SOURCE /home/someuser/exampleDatabaseDump.sql;
    
  2. If you want to create a new database, use the following command.
    CREATE DATABASE exampleDatabase;
    

Configuring my.cnf for Replication

The MySQL installation includes a simple configuration file called my.cnf. On Fedora Linux, that file is found in /etc/my.cnf. We only need to add three lines to this file on the master, and three lines on the slave. In my.cnf, we’re going to tell MySQL to log SQL commands to the binary log, and we’re going to give each server a unique numeric indentifier. On the master, we’re going to specify the databases for which to log binary events. On the slave, we’re going to specify the databases we wish to replicate. For this guide, we’re going to replicate one database called exampleDatabase. We do not use my.cnf to tell the slave how to connect to the master, or vice-versa. We’ll do that later.

On the master:

  1. Open my.cnf.
    sudo nano /etc/my.cnf
    
  2. Add the highlighted lines to my.cnf. Line 5 tells MySQL to log SQL commands to the binary log. Line 6 gives the MySQL server a unique identifier. Line 7 tells MySQL to enable the binary log for exampleDatabase.
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    log-bin=mysql-bin
    server-id=1
    binlog-do-db=exampleDatabase
    # If you need to enable the binary log for multiple databases, just set the binlog-do-db variable multiple times.
    # binlog-do-db=secondDatabase
    # binlog-do-db=thirdDatabase
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    

On the slave:

  1. Open my.cnf.
    sudo nano /etc/my.cnf
    
  2. Add the highlighted lines to my.cnf. Notice that the server-id is different on the slave. Line 7 tells the slave to replicate any changes made to exampleDatabase on the master.
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    log-bin=mysql-bin
    server-id=2
    replicate-do-db=exampleDatabase
    # If you want the slave to replicate multiple databases, set the replicate-do-db variable multiple times.
    # replicate-do-db=secondDatabase
    # replicate-do-db=thirdDatabase
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    

Making an Exact Copy of the Master Databases on the Slave

Before we can tell the slave to start replicating from the master, we need both servers to have an exact copy of our database, exampleDatabase. We’ll use mysqldump to backup exampleDatabase on the master. Then, we’ll copy the mysqldump backup file to our slave. Finally, we’ll import the backup and start replication.

On the master:

  1. Use mysqldump to create a backup of our database, exampleDatabase. Here are some things to notice about the following mysqldump command:
    • We’re supplying a specific username, root, with the corresponding password.
    • We’re explicitly telling mysqldump to backup routines (stored procedures) using the –routines switch.
    • We’re using the –master-data switch so that mysqldump includes a CHANGE MASTER statement in the backup file. The CHANGE MASTER statement will tell the slave which binary log file to use, and at which point in that file to resume replication.
    • We’re telling mysqldump exactly which databases to backup with the –databases switch.
    mysqldump --user=root --password=S0meStr0ngP4ssw0rd --opt --routines --master-data --databases exampleDatabase > /home/someuser/exampleDatabase.sql
    
  2. Use scp to copy the backup file (exampleDatabase.sql) created by mysqldump to our slave server.
    scp -rpC /home/someuser/exampleDatabase.sql someotheruser@db2.example.com:/home/someotheruser/exampleDatabase.sql
    

On the slave:

  1. Launch the MySQL client as root.
    mysql --user=root --password=S0meStr0ngP4ssw0rd
    
  2. Use the CHANGE MASTER command to tell the slave which master use. This command has three components:
    • MASTER_HOST: The IP address or FQDN of the master server.
    • MASTER_USER: A MySQL user on the master who has replication priveledges.
    • MASTER_PASSWORD: The password for the user specified by MASTER_USER.
    CHANGE MASTER TO MASTER_HOST='db1.example.com', MASTER_USER='repuser', MASTER_PASSWORD='An0th3rStr0ngP4ssw0rd';
    
  3. Import the backup we made using mysqldump. Remember that this backup includes its own CHANGE MASTER statement that will tell the slave where in the binary log to resume replication.
    SOURCE /home/someotheruser/exampleDatabase.sql;
    
  4. Tell the slave to start replication.
    START SLAVE;
    

Monitoring Replication Status

MySQL replication is very reliable, but you should monitor it to ensure replication is running without error. You monitor replication on the slave. Replication can stop when the slave encounters a SQL error that the master does not, or vice-versa. It’s okay if the master and slave both encounter the same SQL error. If there is an error that stops replication, we’ll describe how to get replication started again later in this guide.

  1. On the slave, launch the MySQL client as root.
    mysql --user=root --password=S0meStr0ngP4ssw0rd
    
  2. Issue the SHOW SLAVE STATUS COMMAND. We’re using the \G delimiter to output our result as a series of name:value pairs.
    SHOW SLAVE STATUS\G
    
  3. Let’s look at the output of SHOW SLAVE STATUS. There are two important variables to notice, Slave_IO_Running and Slave_SQL_Running. If either of these is set to No, then the slave or master has encountered an error, and replication has stopped. We’ll discuss how to fix this later.
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: db1.example.com
                      Master_User: repuser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000058
              Read_Master_Log_Pos: 494986526
                   Relay_Log_File: mysqld-relay-bin.000495
                    Relay_Log_Pos: 274004705
            Relay_Master_Log_File: mysql-bin.000058
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: exampleDatabase
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 494986526
                  Relay_Log_Space: 274005650
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:
    

What To Do If Replication Stops

Under normal operation, the values of the Slave_IO_Running and Slave_SQL_Running variables should both be set to Yes. If either one is set to No, then either the slave or the master has encountered an error and replication has stopped. The output of SHOW SLAVE STATUS will also include the offending SQL statement or system error.

When replication stops, the best solution is to stop the slave, make a backup of the master using mysqldump, import the backup on the slave, and finally start the slave again. This may seem like a lot of work, but it will ensure that your master and slave are properly synchronized.

On the slave:

  1. On the slave, launch the MySQL client as root.
    mysql --user=root --password=S0meStr0ngP4ssw0rd
    
  2. Issue the SHOW SLAVE STATUS COMMAND. Let’s assume that either Slave_IO_Running or Slave_SQL_Running is set to No, alerting us to a replication error.
    SHOW SLAVE STATUS\G
    
  3. Stop the slave from continuing the replication process.
    STOP SLAVE;
    

On the master:

  1. Use mysqldump to create a backup of our database, exampleDatabase. Notice the –master-data switch. This will record the current position of the master’s binary log.
    mysqldump --user=root --password=S0meStr0ngP4ssw0rd --opt --routines --master-data --databases exampleDatabase > /home/someuser/exampleDatabase.sql
    
  2. Use scp to copy the backup file (exampleDatabase.sql) created by mysqldump to our slave server.
    scp -rpC /home/someuser/exampleDatabase.sql someotheruser@db2.example.com:/home/someotheruser/exampleDatabase.sql
    

On the slave:

  1. Import the backup we made using mysqldump.
    SOURCE /home/someotheruser/exampleDatabase.sql;
    
  2. Tell the slave to start replication.
    START SLAVE;
    

Conclusion

I hope this guide is helpful. MySQL replication can be incredibly useful, and it’s fairly simple to setup. Please feel free to ask questions or give feedback in the comments.

Nate Smith Web Development , ,

  1. No comments yet.
  1. No trackbacks yet.