Setup MySQL Master-Slave Replication on Ubuntu with Existing Data

MySQL Master-Slave Replication is a procedure that is replicating or in other words copying the databases from one (Master) server to other (Slave) servers in the network. It can be useful for having multiple read replicas (that I plan to use in the future as my server grows) or have it as a place to do backups.

As I have my Master server in place, it’s a production server with data on it, it’s a Virtual Server running Ubuntu 16.04 LTS and MySQL 5.7 with sufficient RAM memory and vCPU cores running on SSD drives. The server is starting to be used all day long and I can’t find a suitable time when I can do my backups, so from now on, I can do my backup jobs on my Slave server anytime I want.

ADVERTISEMENT

In my current setup I have my Master MySQL server mysql01 and my Slave MySQL server mysql02 which is in another datacenter

mysql01 IP: 10.90.21.16
mysql02 IP: 10.91.21.21

Master server setup

You will need to plan for downtime because we will need to take a consistent snapshot of the databases for which we will need to put the server into a read-only mode.

1. Update the configuration file

Edit: /etc/mysql/mysql.conf.d/mysqld.cnf and under [mysqld] uncomment or add the following lines

[mysqld]
bind-address = 10.90.21.16
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

This will make the database server listen on private IP, set server-id which should be unique among all Master and Slave nodes and enable binary logging. Some of this you should already have but let’s check it once again.

2. Restart for services to be effective

systemctl restart mysql

3. Create Replication User

root@mysql01:~# mysql -u root -p
Enter password: 

mysql> CREATE USER 'repl'@'10.91.21.21' IDENTIFIED BY 'Sl@v3p@$$';
Query OK, 0 rows affected (0.00 sec)

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

This will create a dedicated repl user for the Slave server IP address 10.91.21.21 and we will give privileges for REPLICATION SLAVE. Remember to change 10.91.21.21 with your Slave server IP address.

4. Lock the Master Server Databases

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.22 sec)

This will lock ALL tables with READ-ONLY flag and during the lock we need to create a consistent snapshot of the database so we can bootstrap the Slave server later on. Remember this will lock all INSERT and UPDATE queries.

Lock will be released when you exit the MySQL CLI or issue UNLOCK TABLES. Keep the lock until you finish the mysqldump.

5. Master Replication log position

mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.003577 | 141378624 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Write the File and the Position values, you will need them later when you initialize the Slave replication.

6. DUMP the Master server databases

mysqldump -uroot -p --all-databases --single-transaction --triggers --routines > dump.sql

This will dump all the databases into a single dump.sql file. You can check my MySQL Backup and Restore post regarding the backup and restore commands.

7. Unlock the databases on the Master server

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

WAIT until your backup from step 6 has finished before issuing this command. After this, your Master server will be fully operational.

8. Transfer the data to the Slave server

root@mysql01:~# $ scp dump.sql 10.91.21.21:/tmp

This will transfer the data from the Master server to the Slave server and we can jump and configure the Slave server.

You can even have a break at this point.


Slave Server Setup

As with the Master server we need to make few adjustments with the Slave server, restore the databases and start the Replication process.

1. Update the configuration file

Edit: /etc/mysql/mysql.conf.d/mysqld.cnf and under [mysqld] uncomment or add the following lines

[mysqld]
bind-address = 10.91.21.21
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

Like the Master server above we need to make the server listen on it’s private IP, set server-id to a unique value (in our case 2) and enable binary logging.

2. Restart for services to be effective

systemctl restart mysql

3. Import the dump file

root@mysql02:~# mysql -u root -p < /tmp/dump.sql

4. Setup the slave to communicate with the Master

root@mysql02:~# mysql -u root -p

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.90.21.16',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Sl@v3p@$$',
    -> MASTER_LOG_FILE='mysql-bin.003577',
    -> MASTER_LOG_POS=141378624;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Change the Master Host, Password, Log file and Log Position with YOUR VALUES.


Check if replication is working

To see if your configuration is working you need to connect on your Slave server and issue SHOW SLAVE STATUS\G

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.90.21.16
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003577
          Read_Master_Log_Pos: 229958895
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 11083560
        Relay_Master_Log_File: mysql-bin.003577
             Slave_IO_Running: Yes <-- Imporant
            Slave_SQL_Running: Yes <-- Imporant
              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: 152461864
              Relay_Log_Space: 88580800
              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: 1575 <-- Imporant
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: 1
                  Master_UUID: ee9774f5-8552-11e8-b6cb-00505684dbd9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

From all this data there are few parameters you need to check, Slave_IO_Running and Slave_SQL_Running should be Yes, and since I had a break before setting up the Slave I have Seconds_Behind_Master: 1575 Wait for this parameter to be 0 which indicates that the Slave is not behind the Master with the data.

MySQL Replication Chapter: https://dev.mysql.com/doc/refman/5.7/en/replication.html

ADVERTISEMENT