Steps for configuring Master-Slave Replication for MySQL in CentOS 7

MySQL master-slave replication is widely used in database backup, failover, data analysis, and other scenarios.

MySQL master-slave replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ). Therefore, binary logs must be enabled on the master server for replication. The slave server receives updates to its binary logs from the master server. When a slave server connects to the master server, the master server reads the last successful update from the log, the slave server receives updates from that time, executes the same updates on the local server, and waits for the master server to notify the new updates. Backup on the slave server does not affect the master server. During the backup process, the master server can continue to process updates.

Test Environment

Master: 192.168.10.201
Slave: 192.168.10.202
Port 3306
Database: test2

Install MySQL

Yum install mariadb-server
Systemctl enable mariadb
Service mariadb start

# Reset root password
Mysqladmin-u root password abc @ DEF

Master server configuration

Master server configuration file/etc/my. cnf

[Mysqld]
Server-id = 1
Binlog-do-db = test2
Relay-log =/var/lib/mysql-relay-bin
Relay-log-index =/var/lib/mysql/mysql-relay-bin.index.
Log-error =/var/lib/mysql. err
Master-info-file =/var/lib/mysql/mysql-master.info.
Relay-log-info-file =/var/lib/mysql/mysql-relay-log.info.
Log-bin =/var/lib/mysql-bin

Restart MySQL

Service mariadb restart
Grant replication slave permissions
Mysql-uroot-p
Grant replication slave on *. * TO 'slave _ user' @ '% 'identified BY 'password ';
Flush privileges;
Flush tables with read lock;
Show master status;

+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 469 | test2 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Note: write down the red part and use it later.
Back up Database
To back up the database, you need to call "Read-only Lock" for all tables in the database, and then perform dump backup:

Mysqldump-u root-p -- all-databases -- master-data>/root/dbdump. db

After the backup is complete, run the following command to unlock it:

Mysql-uroot-p
Unlock tables;

Slave server configuration
Restore database
Mysql-u root-p </root/dbdump. db
Slave server configuration file/etc/my. cnf
[Mysqld]
Server-id = 2
Replicate-do-db = test2
Relay-log =/var/lib/mysql-relay-bin
Relay-log-index =/var/lib/mysql/mysql-relay-bin.index.
Log-error =/var/lib/mysql. err
Master-info-file =/var/lib/mysql/mysql-master.info.
Relay-log-info-file =/var/lib/mysql/mysql-relay-log.info.
Log-bin =/var/lib/mysql-bin

Restart MySQL

Service mariadb restart

Connect to the master server

Mysql-uroot-p
Stop slave;
Change master to MASTER_HOST = '192. 168.10.201 ', MASTER_USER = 'slave _ user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'MySQL-bin.000002', MASTER_LOG_POS = 192;
Start slave;
Show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.201
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 469
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: test2
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: 469
Relay_Log_Space: 1107
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

Verify

Master server

Mysql-uroot-p
Drop test2;
Create database test2;
Use test2;
Create table emp (c int );
Insert into emp (c) values (10 );
Slave server
On the slave server, you should be able to see the same changes.