Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL5.7.18 master-slave replication to build a master-slave example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

I. the principle of replication

The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update.

MySQL uses three threads to perform the replication function (one on the master server and the other two on the slave server. When the START SLAVE is issued, create an Imax O thread from the server to connect to the master server and have it send statements recorded in its binary log. The master server creates a thread to send the contents of the binary log to the slave server.

This thread is a Binlog Dump thread on the primary server. The content sent by the master server Binlog Dump thread is read from the slave server Icano thread and copied to the local file in the slave server data directory, that is, the relay log. The third thread, the SQL thread, is created from the server to read the relay log and perform the updates contained in the log.

Second, server preparation

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

Master (primary) 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 master and slave servers

III. Implementation details of master-slave replication

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

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

two。 Modify master configuration file my.cnf

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

These two values must be set, and once set, restart MySQL.

3. Back up a complete piece of data on master

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

Description:

-- master-data=2 represents the backup time to record the Binlog location and Position of master.

-- single-transaction means to take a consistent snapshot

-R means to back up stored procedures and functions

-- triggres means backup trigger

-A stands for backing up all libraries

4. View 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 look in the database file you just backed up: vi all.sql

5. Modify the slave library configuration file my.cnf

Server-id = 247 (unique, cannot be the same as the main library, generally set to 3 digits after the server IP) 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 up, restart MySQL.

6. Restore a master backup on a slave server

Mysql-u root-p 'password'

Stop slave; # pauses from library 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 # start replication mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.115.245Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000004Read_Master_Log_Pos: 104634190 Relay _ Log_File: relay.000003Relay_Log_Pos: 104632819Relay_Master_Log_File: binlog.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 104634190Relay_Log_Space: 104634713Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_ Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 245Master_UUID: 4f545573-3170-11e7-b903-000c29462d8cMaster_Info_File: / data/mysql/datadir/3306/data/ Master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log Waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:

8. View master and slave related processes

Master Binlog Dump thread:

Mysql > SHOW PROCESSLIST\ gateway * 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\ gateway * 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. At this point, the master-slave configuration has been completed, you can go to the master server to create databases, tables and other operations to see whether the slave database is synchronized!

Thank you for reading this article carefully. I hope the article "MySQL5.7.18 Master-Slave replication and one-Master-Slave sample Analysis" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.