MySQL

How to configure MySQL replication (Master Slave) on CentOS

Pinterest LinkedIn Tumblr

MySQL replication process enables you to reduce load MySQL and increase high availability. The benefit of MySQL replication also includes disaster recovery and reporting. In this tutorial, we will set up MySQL replication on 2 servers: one for the master database and another for the slave database. You can use virtual machines or VPS for this lab.

  • Master database: 192.168.100.101
  • Slave database: 192.168.100.102

What is MySQL replication?

Install MySQL 8 on CentOS

View guide installs MySQL on CentOS. If your machine has installed MySQL you can skip this step.

Configure Master database

Open MySQL configuration file and add the following lines to bottom:

[root@learncode24h ~]# nano /etc/my.cnf
server-id = 1
log-bin=mysql-bin.log
binlog_do_db=replicadb
sync_binlog=1
user=mysql
symbolic-links=0
binlog_format = ROW
max_binlog_size = 500
expire-logs-days = 7
slow_query_log
  • server-id:  is used to identify individual servers within the replication topology, and must be a positive integer between 1 and (232)−1. Server ID must be different from 0. With the default server ID of 0, a source refuses any connections from replicas, and a replica refuses to connect to a source, so this value cannot be used in a replication topology. 
  • log-bin=base_name: specifies the base name to use for binary log files. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. 
  • binlog_do_db=db_name: allows you to configure a replication master to write statements and transactions affecting databases that match a specified name into its binary log.  This option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:
[mariadb]
...
binlog_do_db=db1
binlog_do_db=db2
  • sync_binlog: controls how often the MySQL server synchronizes the binary log to disk.
    1. sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.
    2. sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.
    3. sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.
  • symbolic-links=0: disable symbolic links on MySQL
  • binlog_format = type: specify the binary logging format. This option supports 3 values are:
    1. STATEMENT causes logging to be statement based.
    2. ROW causes logging to be row based.
    3. MIXED causes logging to use mixed format.
  • max_binlog_size: If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.
  • expire-logs-days: specify limit on the lifespan of binary logs. 90 days is a fairly safe minimum limit (removal happens at startup and when the binary log is flushed)
  • slow_query_log: by identifying queries that are particularly slow in their execution, you can address them by restructuring the application that triggers your queries. You can also rebuild the queries themselves to ensure that they are constructed as efficiently as possible.

Restart MySQL service for changes to take effect.

[root@learncode24h ~]# systemctl restart mysqld

 Create replication user on Master db server, slave will use this user to connect to master db.

mysql> create user 'replica'@'192.168.100.112' IDENTIFIED WITH mysql_native_password BY 'your-password';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'replica'@'192.168.0.192';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'replica'@'192.168.100.112';
+-------------------------------------------------------------+
| Grants for replica@192.168.100.112                            |
+-------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replica`@`192.168.100.112` |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Create replicated database for testing and grant all privileges for this database.

mysql> create database replicadb;
Query OK, 1 row affected (0.01 sec)
mysql> use replicadb;
Database changed
mysql> CREATE TABLE replicatb ( id int unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT ALL PRIVILEGES ON replicadb.* to 'replica'@'192.168.100.112';
Query OK, 0 rows affected (0.01 sec)

Lock database tables then backup database and transfer it to slave for restoring later.

[root@lampblogs ~]# mysqldump -u root -p replicadb > replicadb.sql
Enter password: 
[root@lampblogs ~]# rsync replicadb.sql 192.168.100.112:/tmp/
root@192.168.100.112's password: 

Unlock tables in MySQL shell

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

Configure Slave database

Open my.conf file and add the following lines to bottom of file

[mysqld]
server-id=2
relay-log=mysql-relay-bin.log
log_bin=mysql-bin.log
binlog_do_db=replicadb
user=mysql
symbolic-links=0
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500
expire-logs-days = 7
slow_query_log

read_only = 1: This sets the slave to read-only mode. Only users with super privilege and the replication slave thread will be able to modify data on it. This means there are no applications that can accidentally modify data on the slave instead of master. When replication is working you shouldn’t modify data at slave because it can lead to stopping synchronization between master and slave.

Now restart mysql server as below in slave

systemctl restart mysqld

Restore database in slave

Create the database before importing the data

mysql> create database replicadb;
Query OK, 1 row affected (0.03 sec)
Importing data to created database replicadb
cat replicadb.sql | mysql -u root -p replicadb

Initialize Replication process

On master database you run the command “show master status\G” to get binlog file name and binlog position

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 156
     Binlog_Do_DB: replicadb
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Now we start the replication process in slave server by login into MySQL server at slave with root and stop slave.

mysql>STOP SLAVE;
Query OK, 0 rows affected (0.04 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.101', MASTER_USER='replica', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;

Now start replication on the slave and check replication status

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.10
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 937
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000002
             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: 937
              Relay_Log_Space: 530
              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: 1
                  Master_UUID: 82235676-f96f-11e9-b214-08f252523e12a
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

If you can see Slave_IO_Running: Yes and Slave_SQL_Running: Yes, replication is working.

Test Replication

In Master database

mysql> use replicadb;
mysql> INSERT INTO replicatb ( name ) VALUES ( 'row1' );
mysql> select * from replicatb;
+----+------+
| id | name |
+----+------+
|  1 | row1|
+----+------+
1 row in set (0.00 sec)

Login into slave and check data is synchronized to replicadb.

mysql> use replicadb;
Database changed
mysql> select * from replicatb;
+----+------+
| id | name |
+----+------+
|  1 | row1|
+----+------+
1 row in set (0.00 sec)

On master database run “SHOW SLAVE HOSTS” to view slaves are connected to master

SHOW SLAVE HOSTS;
+------------+-----------+------+-----------+
| Server_id  | Host      | Port | Master_id |
+------------+-----------+------+-----------+
|  192168100112 | 2| 3306 | 192168100111|
+------------+-----------+------+-----------+

If you see the above result, congratulation! You have configured MySQL replication successfully.

I'm a full stack developer. I have experiences with Java, Android, PHP, Python, C#, Web development...I hope website https://learncode24h.com will help everyone can learn code within 24h and apply it in working easily.

Comments are closed.

%d bloggers like this: