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.
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.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.sync_binlog=, whereNNis a value other than 0 or 1: The binary log is synchronized to disk afterNbinary 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:
STATEMENTcauses logging to be statement based.ROWcauses logging to be row based.MIXEDcauses 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.
Comments are closed.