Establish Replication

Background

Replication continuously copies changes from one server (the master) to another (the slave).

MySQL Replication is commonly used to:

Survive Failures - Replication gives you an up-to-the-moment copy of the master database, on a server that's ready to take over when the inevitable crash happens. It is typically much faster to restore service using a slave than restoring from a backup.

Scale Reads - Many modern applications have a write-once, read-many access pattern. For example, a blog post will be inserted once and read many times. You can use replication to spread the much higher SELECT load over many read-slaves. The smaller write load (INSERTs, UPDATEs, DELETES) still has to run on the master.

Distribute Data - A master can replicate to slaves anywhere on the globe. You can use slaves to improve network latency serving customers. You can also use a distant replicated slave as a Business Continuity Plan in case the entire location housing the master server is offline.

Run Intensive Reports - Some queries take a huge amount of processor, memory, or disk to execute. Dedicated read slaves can distribute the impact of heavy reports.

Steps

Connect to the Master Server

The master server is an existing database that we want to replicate to a new slave.

To get started, connect to master.example.com.

Configure a Replication Account

Add a service account named replicator on the master and grant it replication privileges. The slave will use this account to authenticate to the master.

master ~ $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'SilkInnFeatGear';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
After this step is completed:
  1. replicator can log into MySQL on the master from the slave with password SilkInnFeatGear
Configure MySQL on the Master

In order for this server to be a replication master, it needs log_bin enabled, and a server_id that is not the default (0).

Set those values by editing /etc/my.cnf:

mysql> exit;
Bye
master ~ $ sudoedit /etc/my.cnf

and add these two lines directly under [mysqld]

/etc/my.cnf
[mysqld] log_bin = mysql-bin server_id = 10 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown

Now restart MySQL:

master ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Log in to MySQL and check that those changes took effect.

master ~ $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW variables LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> SHOW variables LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. On the master, /etc/my.cnf sets server_id to 10
  2. On the master, /etc/my.cnf sets log_bin to mysql-bin
  3. On the master, the MySQL variable server_id has value 10
  4. On the master, the MySQL variable log_bin has value ON
Back Up the Master

On the master, make a complete backup that we can use to get the slave quickly up to speed.

mysql> exit
master ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
master ~ $ 
After this step is completed:
  1. The backup file exists on the master at /tmp/master_backup.sql
Copy the Backup to the Slave

Now we'll copy the backup file to the slave using SCP.

master ~ $ scp /tmp/master_backup.sql slave.example.com:/tmp/
The authenticity of host 'slave.example.com (10.242.58.189)' can't be established.
RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'slave.example.com,10.242.58.189' (RSA) to the list of known hosts.
you@slave.example.com's password: (input your password)
master_backup.sql                           100%  501KB 501.3KB/s   00:00
master ~ $ 

You can also see where the backup file includes replication information. When the slave applies this backup then starts replicating, it will know to look on the master in a specific binary log, after a specific position for changes newer than this backup.

master ~ $ egrep "^CHANGE MASTER" /tmp/master_backup.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
master ~ $ 
After this step is completed:
  1. The backup file exists on the slave at /tmp/master_backup.sql
Connect to the Slave Server

The slave server is a brand-new server with MySQL installed but no data. We'll set it up to replicate data from the master.

Connect to slave.example.com

Configure MySQL on the Slave

Now configure the slave to take part in replication, first by customizing /etc/my.cnf, similar to the master.

Set server_id to something different than the master, and not zero.

To make the slave easily promoted to a master, set a name for log_bin. Also turn on log_slave_updates and rename the relay_log.

We also want to make sure that no changes accidentally happen on the slave, since they wouldn't be returned to the master, and we'd end up with an inconsistent replication group. So we set this server to be read_only, for now.

slave ~ $ sudoedit /etc/my.cnf

Add these five lines to directly under [mysqld]

/etc/my.cnf
[mysqld] server_id = 20 log_bin = mysql-bin log_slave_updates = 1 relay_log = mysql-relay-bin read_only = 1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown

Now restart MySQL:

slave ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Now, in MySQL, check that the changes took effect:

slave ~ $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW variables WHERE Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates", "read_only");
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| log_bin           | ON              |
| log_slave_updates | ON              |
| read_only         | ON              |
| relay_log         | mysql-relay-bin |
| server_id         | 20              |
+-------------------+-----------------+
5 rows in set (0.00 sec)

mysql>
After this step is completed:
  1. On the slave, /etc/my.cnf sets server_id to 20
  2. On the slave, /etc/my.cnf sets log_bin to mysql-bin
  3. On the slave, /etc/my.cnf sets log_slave_updates to 1
  4. On the slave, /etc/my.cnf sets relay_log to mysql-relay-bin
  5. On the slave, /etc/my.cnf sets read_only to 1
  6. On the slave, the MySQL variable server_id has value 20
  7. On the slave, the MySQL variable log_bin has value ON
  8. On the slave, the MySQL variable log_slave_updates has value ON
  9. On the slave, the MySQL variable relay_log has value ON
  10. On the slave, the MySQL variable read_only has value ON
Import the Back Up on the Slave

Import the backup on the slave. You will get dozens of lines of status messages. Scan them briefly to make sure there are no errors.

mysql> source /tmp/master_backup.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 508 rows affected (0.02 sec)
Records: 508  Duplicates: 0  Warnings: 0
......some content not shown
After this step is completed:
  1. MySQL on the slave has the database important
  2. MySQL on the slave has the table important.stuff
Point the Slave at the Master

Configure the slave with the network name of the master and the username and password for the replicator account.

This information, combined with the log file and position in the backup file, are all the slave needs to establish replication with the master.

mysql> CHANGE MASTER TO MASTER_HOST='master.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='SilkInnFeatGear';
Query OK, 0 rows affected (0.05 sec)

mysql>
After this step is completed:
  1. MySQL on the slave shows the replication setting MASTER_HOST has value master.example.com
  2. MySQL on the slave shows the replication setting MASTER_USER has value replicator
Start Replication

Start replication on the slave, then ask MySQL for replication status.

mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 960
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1106
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 960
              Relay_Log_Space: 1262
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. SHOW SLAVE STATUS on the slave shows Slave_IO_Running is Yes
  2. SHOW SLAVE STATUS on the slave shows Slave_SQL_Running is Yes
Test Replication

Now, insert some new data on the master server.

master ~ $ mysql -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO important.stuff SET details='Replication is running';
Query OK, 1 row affected (0.04 sec)

mysql>

The row you inserted should show up on the slave server, almost immediately.

mysql> SELECT * FROM important.stuff;
+----+------------------------+---------------------+
| id | details                | happened            |
+----+------------------------+---------------------+
|  1 | Replication is running | 2013-03-12 19:29:33 |
+----+------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 
After this step is completed:
  1. INSERT a new record on the master in the table important.stuff
  2. Within 1 second, that record is replicated to the slave