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
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
