Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication

This article mainly introduces the detailed tutorial of MySQL5.7.18 master-slave replication setup (one master and one slave). Friends who need it can refer to it. I hope it can help everyone. MySQL uses 3 threads to perform the replication function (one of which is on the master server and the other two on the slave server. Let’s introduce the content of this article in detail.

1. Replication Principle

The master writes updates to binary log files and maintains an index of the files to keep track of the log rotation. These logs record updates sent to the slave. When a slave connects to the master, it notifies the master that the slave is in the log. The location of the last successful update is read. The slave server receives any updates that have occurred since then, and then blocks and waits for the master server to notify of new updates.

MySQL uses 3 threads to perform the replication function (where 1 on the master server and the other two on the slave server. When START SLAVE is issued, the slave server creates an I/O thread to connect to the master server and let it send the statements recorded in its binary log. One thread sends the content in the binary log to the slave server.

This thread reads the content sent by the main server Binlog Dump thread and transfers the data to the Binlog Dump thread on the master server. Copy to the local file in the data directory of the slave server, that is, the relay log. The third thread is the SQL thread, which is created by the slave server to read the relay log and execute the updates contained in the log

. 2. Server preparation

Operating system version: Red Hat Enterprise Linux Server release 6.7 (Santiago)

Master (master) ip: 172.16.115.245 Host name: mysql2 server_id:245

Slave (slave) ip: 172.16.115.247 Host name: mysql3 server_id:247

MySQL5.7.18 is installed on both the master and slave servers

3. Master-slave replication implementation details

1. Set a connection account for the server on the master server and grant REPLICATION SLAVE permission

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl@20170509';

2. Modify the master configuration file my.cnf

server-id = 245
log_bin = /data/mysqllog/3306/bin_log/binlog

These two values ​​must be set. After setting up, restart MySQL

3. Back up the complete data of the master

mysqldump -uroot -p'密码' --master-data=2 --single-transaction -R --triggers -A > /backup/all.sql

Description:

--master-data=2 means recording the master at the backup time. Binlog location and Position
--single-transaction means getting a consistent snapshot
-R means backing up stored procedures and functions
--triggres means backup triggers
-A means backup All libraries

4. Check the binlog name and location when backing up the main library

SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 79394496 |       |         |          |
+---------------+----------+--------------+------------------+-------------------+

Or go to the database file just backed up: vi all.sql

5. Modify the slave library configuration file my.cnf

server-id = 247 (唯一，不能与主库一样，一般设为服务器IP后3位)
log_bin = /data/mysql/logdir/3306/bin_log/binlog
innodb_file_per_table = ON
skip_name_resolve = ON
relay_log = /data/mysql/logdir/3306/relay_log/relay.log
binlog-format = row
log-slave-updates = true

read_only=ON (read-only mode)

After setting, restart MySQL.

6. Restore the master backup on the slave server

mysql -u root -p'密码' < all.sql

7. Stop the slave library, configure the master-slave parameters, and open the slave library.

mysql> stop slave; #暂停从库
mysql>CHANGE MASTER TO MASTER_HOST='172.16.115.245',MASTER_USER='repl', MASTER_PASSWORD='repl@20170509',MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=154;
mysql> start slave; #启动复制
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.115.245
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 104634190
Relay_Log_File: relay.000003
Relay_Log_Pos: 104632819
Relay_Master_Log_File: binlog.000004
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: 104634190
Relay_Log_Space: 104634713
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: 245
Master_UUID: 4f545573-3170-11e7-b903-000c29462d8c
Master_Info_File: /data/mysql/datadir/3306/data/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:

8. View master and slave related processes

master Binlog Dump thread:

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 13
User: repl
Host: 172.16.115.247:44602
db: NULL
Command: Binlog Dump
Time: 76514
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL

slave IO/SQL thread:

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 10
User: system user
Host: 
db: NULL
Command: Connect
Time: 81148
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 12
User: system user
Host: 
db: NULL
Command: Connect
Time: 5
State: Reading event from the relay log
Info: NULL

9. At this point, The master-slave configuration has been completed. You can create databases, tables and other operations on the master server to see if the slave database is synchronized!

Related recommendations:

Mysql master-slave replication setup_MySQL

How to implement the master-slave replication process in MySQL Detailed example explanation (picture)

Analysis of the principle and configuration of MySQL master-slave replication

The above is the detailed content of Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication. For more information, please follow other related articles on the PHP Chinese website!