Detailed tutorial on MySQL5.7.18 master-slave replication (one master and one slave) and mysql5.7.18 slave

Detailed tutorial on MySQL5.7.18 master-slave replication (one master and one slave) and mysql5.7.18 slave

I. Replication Principle

The master server writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record the updates sent to the slave server. When an slave server connects to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/O thread is created from the server to connect to the master server and send statements recorded in its binary log. The master server creates a thread to send the binary log content to the slave server.

This thread is the Binlog Dump thread on the master server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread, which is created from the server to read relay logs and execute updates contained in logs.

Ii. 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 has been installed on both the master and slave servers.

Iii. Master-slave replication Implementation Details

1. The master server sets a connection account for the server and grants the replication slave permission.

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

2. Modify the master configuration file my. cnf

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

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

3. Back up the complete data on the master.

Mysqldump-uroot-p 'Password' -- master-data = 2 -- single-transaction-R -- triggers-A>/backup/all. SQL

Note:

-- Master-data = 2 indicates that the Binlog Position and Position of the master are recorded at the backup time.
-- Single-transaction indicates obtaining consistent snapshots.
-R indicates the backup stored procedures and functions.
-- Triggres indicates a backup trigger.
-A indicates backing up all databases

4. view the binlog name and location during master database backup

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

Or you can see in the database file you just backed up:vi all.sql

5. Modify the slave library configuration file my. cnf

Server-id = 247 (unique, not the same as the master database, usually set to the last three digits of the server IP address) log_bin =/data/mysql/logdir/3306/bin_log/binloginnodb_file_per_table = ONskip_name_resolve = ONrelay_log =/data/mysql/logdir/3306/relay_log/relay. logbinlog-format = rowlog-slave-updates = true

Read_only = ON (read-only mode)

After setting, restart MySQL.

6. Restore master backup on the slave server

Mysql-u root-p 'Password' <all. SQL

7. Stop the slave database, configure the master and slave parameters, and open the slave database.

Mysql> stop slave; # Pause slave database mysql> change master to MASTER_HOST = '2017. 16.115.245 ', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl @ 20170509', MASTER_LOG_FILE = 'binlog. 000004 ', MASTER_LOG_POS = 154; mysql> start slave; # Start copying mysql> show slave status \ G **************************** 1. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: login _ User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: Container _ Master_Log_Pos: container: Container _ Log_Pos: Container _ IO_Running: container: Replicate_Do_Table: container: last_Errno: 0Last_Error: Skip_Counter: counters: Until_Log_Pos: counters: Keys: Master_SSL_Cert: Keys: Master_SSL_Key: Keys: counters: replicate_Ignore_Server_Ids: Master_Server_Id: 245Master_UUID: 4f545573-3170-11e7-b903-000c29462d8cMaster_Info_File:/data/mysql/datadir/3306/data/master. infoSQL_Delay: login: Slave has read all relay log; waiting for more details: Usage: Master_SSL_Crl: Master_SSL_Crlpath: Usage: Auto_Position: Usage: Channel_Name: Master_TLS_Version:

8. view master and slave Processes

Master Binlog Dump thread:

mysql> SHOW PROCESSLIST \G*************************** 1. row ***************************Id: 13User: replHost: 172.16.115.247:44602db: NULLCommand: Binlog DumpTime: 76514State: Master has sent all binlog to slave; waiting for more updatesInfo: NULL

Slave IO/SQL thread:

mysql> SHOW PROCESSLIST \G*************************** 1. row ***************************Id: 10User: system userHost: db: NULLCommand: ConnectTime: 81148State: Waiting for master to send eventInfo: NULL*************************** 2. row ***************************Id: 12User: system userHost: db: NULLCommand: ConnectTime: 5State: Reading event from the relay logInfo: NULL

9. Now, the master-slave configuration has been completed. You can create databases, tables, and other operations on the master server to check whether the slave database is synchronized!

Summary

The above is a detailed tutorial on MySQL5.7.18 master-slave replication (one master and one slave). I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in time!