Site: US UK AU |
Nexcess Blog

Configuring MySQL Replication

June 4, 2011 2 Comments RSS Feed

Configuring MySQL Replication
The native replication built into MySQL is a powerful tool to keep an identical copy or copies of a database across multiple servers. MySQL replication is useful if you want have an exact copy of a live database for backup or application purposes. In the event of a total failure of the primary master database server, the application utilizing the database can be manually pointed to the backup slave database. This could save a significant amount of downtime as repairing / rebuilding a failed database server and re-importing databases from backup can take some time. Some applications may have code to detect a connection failure to the primary database and automatically fail-over to the slave server. Replication is also useful to high traffic sites where splitting the database reads across many servers is beneficial for performance.

MySQL replication is not a backup policy to protect against accidentally deleting data or from data corruption. If you delete data or DROP a table on the master, it will be propagated to the slave server and be deleted / dropped there too. Because of this you will still need a backup policy to provide snapshots in time of your databases data.


You have two servers configured running the same version of MySQL. Replication between two different versions of MySQL is possible depending on versions but not recommended.

The two servers are connected together via a private internal network or across a secure connection such as a VPN if the servers are geographically located apart. Please note that default communication between two MySQL servers is not encrypted.

You have at least one database or table on the master database server that you wish to replicate to the slave.

Based on the above criteria, there are four steps you will need to perform to setup replication.

Step 1. Configure the Master Server for Replication:

Begin by editing your my.cnf on the master database server. You will want to add or modify the following lines:

log-bin = /var/lib/mysql/mysql-bin.log<br />
binlog-do-db=database_name<br />
server-id=1<br />

log-bin point to a path where MySQL will write it’s binary log. The binary log is a log file containing all statements on the master server that the slave server uses to see what has happened to stay in sync.

binlog-do-db will be assigned the database name that you wish to replicate to the slave.

server-id is a unique id that must be a different from id’s on the other MySQL server(s).

expire_logs_days is important. By default MySQL will not delete or rotate out its binary log files. Because of this your partition where MySQL logs live will fill up over time. Setting this value to sever will allow you to recover from up to 7 days in the past if replication stopped running for some reason. This variable can be adjusted as deemed necessary.

Once these lines have been modified, you will need to go ahead and restart MySQL to apply the changes:

$service mysqld restart

Now we have to create a user with replication privileges on the master. Begin by logging into MySQL and run the following commands:

mysql&gt;GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';<br />
flush privileges;

The user we created is ‘slave_user’ with password ‘password’. Your password will actually be secure of course. At this point stay logged into the master server as we are not done here yet, but will move onto the next step.

Step 2. Configure the Slave Server for Replication.

Now in the slave servers my.cnf, add or modify the following lines:

server-id=2<br />
master-host=ip_of_master<br />
master-user=slave_user<br />
master-password=password<br />
master-connect-retry=60<br />

server-id will be a different unique id for the slave.
master-host is the ip address of the master server.
master-user is the user ‘slave_user’ we created on the master.
master-password is the secure password we create for the slave_user.
replicate-do-db is the same database name we had set on the masters binlog-do-db.

Do not restart MySQL at this point.

Step 3. Create Database Dump on Master.

It is now time to export the database on the master so we can import into the slave. We begin by running the following commands which clear the binary log, lock the tables, and obtain the current binary log position and offset on the master:

mysql&gt;USE database_name;<br />

The output will look like this:

+---------------+----------+---------------+------------------+<br />
| File          | Position | Binlog_do_db  | Binlog_ignore_db |<br />
+---------------+----------+---------------+------------------+<br />
| mysql-bin.002 | 8383     | database_name |                  |<br />

Please keep this information as we will need it on the slave.

We can now create mysqldump of the database. This dump will be imported on the slave server:

 --opt database_name &gt; database_name.sql</p>
<p>This dump will need to be copied over the the slave server to import.</p>
<p>Now unlock the tables on the master:</p>
<p>mysql&gt;UNLOCK TABLES;

Step 4. Import Database to Slave and Start Replication

Begin by importing the sql file you dumped on the master into the slave. First in MySQL, create the database:

mysql&gt;CREATE DATABASE databse_name;

Then go ahead and import the db:

$mysql -u root -p[password] database_name &lt; databse_name.sql

Now that the database is imported, go ahead and restart MySQL. Once restarted, we will run the following commands within MySQL:

mysql&gt;SLAVE STOP;

mysql&gt;CHANGE MASTER TO MASTER_HOST='ip_of_master', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.002', MASTER_LOG_POS=8383;

Note that we are using the filename and position we saw during the SHOW MASTER STATUS command on the master. This tells the slave server exactly where the dump occurred so when we start replication it knows where to start reading from the binary log files.

Finally start replication on the slave by issuing the following command:

mysql&gt;START SLAVE;

At this point the slave will read from the master servers binary logs and catch up to the master as other queries may have occurred since we made the MySQL dump. We can verify replication on the slave by running:


We will see output similar to:

mysql&gt; SHOW SLAVE STATUS\G<br />
*************************** 1. row ***************************<br />
             Slave_IO_State: Waiting for master to send event<br />
                Master_Host: ip<br />
                Master_User: root<br />
                Master_Port: 3306<br />
              Connect_Retry:<br />
            Master_Log_File: log-bin.005<br />
        Read_Master_Log_Pos: 79<br />
             Relay_Log_File: relay-bin.005<br />
              Relay_Log_Pos: 8893<br />
      Relay_Master_Log_File: log-bin.005<br />
           Slave_IO_Running: Yes<br />
          Slave_SQL_Running: Yes<br />
            Replicate_Do_DB:<br />
        Replicate_Ignore_DB:<br />
         Replicate_Do_Table:<br />
     Replicate_Ignore_Table:<br />
    Replicate_Wild_Do_Table:<br />
Replicate_Wild_Ignore_Table:<br />
                 Last_Errno: 0<br />
                 Last_Error:<br />
               Skip_Counter: 0<br />
        Exec_Master_Log_Pos: 23<br />
            Relay_Log_Space: 342<br />
            Until_Condition: None<br />
             Until_Log_File:<br />
              Until_Log_Pos: 0<br />
         Master_SSL_Allowed: No<br />
         Master_SSL_CA_File:<br />
         Master_SSL_CA_Path:<br />
            Master_SSL_Cert:<br />
          Master_SSL_Cipher:<br />
             Master_SSL_Key:<br />
      Seconds_Behind_Master: 0

A few things to look at in this output. If Slave_IO_Running and Slave_SQL_Running are both ‘Yes’, replication is running. You can compare the master and slave server by looking at the Log position on each. If you run both commands at the same time (SHOW MASTER STATUS on master and SHOW SLAVE STATUS on slave) the log positions will be about the same. Seconds_Behind_Master is useful as well. It can show you how much longer the slave is behind the master if it is used on a busy server or on a high latency connection.

Posted in: General