Shulou(Shulou.com)06/01 Report--

The following brings you about the actual combat demonstration MySQL 5.7.17 to achieve master-slave copy content, I believe you must have seen similar articles. What's the difference between what we bring to everyone? Let's take a look at the text. I believe you will gain something after watching the actual demonstration MySQL 5.7.17 to achieve master-slave replication.

The principle of master-slave replication:

It is divided into synchronous replication and asynchronous replication, and most of the actual replication architecture is asynchronous replication. The basic process of replication is as follows:

1) the IO process on Slave connects to Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2) after Master receives the request from the IO process of Slave, the IO process responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO process of Slave. In addition to the information contained in the log, the returned information also includes the name of the bin-log file that has been sent to Master and the location of the bin-log.

3) after receiving the information, the IO process of .Slave adds the received log content to the end of the relay-log file on the Slave side, and records the file name and location of the read bin-log on the Masterside into the master-info file, so that the next time it is read, it can clearly tell Master, "where do I need to start the log content in a certain bin-log, please send it to me"

4) as soon as the Sql process of Slave detects a new addition to the relay-log, it will immediately parse the content of the relay-log into the executable content that is actually executed on the Master side and execute it on its own.

I. Environmental preparation

Operating system version: centos 7.2

CVM architecture:

Master (primary) ip:192.168.2.70 host name: node01 server_id:1

Slave (slave) ip:192.168.2.71 host name: node02 server_id:2

Slave (slave) ip:192.168.2.5 host name: node03 server_id:3

Other preparations:

Each CVM is configured with the following hosts

# vim / etc/hosts

192.168.2.70 node01

192.168.2.71 node02

192.168.2.5 node03

System time synchronization:

# yum install chrony

Configure time synchronization source: # vi / etc/chrony.conf

# Please consider joining the pool (http://www.pool.ntp.org/join.html).

Server 0.rhel.pool.ntp.org iburst

Server 1.rhel.pool.ntp.org iburst

# systemctl start chronyd.service # launch

# chronyc sources-v # synchronize time sources

Close selinux

Turn off firewalld Firewall

Prepare the test database apps.sql

Solving dependencies: yum-y install gcc gcc-c++ ncurses ncurses-devel cmake bison

First remove: rpm-e-- nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64

Create a new directory / mydata/data (it is recommended to use lvm2 image for physical backup) to store database data

For mysql installation and deployment, please refer to http://daisywei.blog.51cto.com/7837970/1896614 (not discussed here)

2. Setting the login mode of SSH key

Operate on node01 node 192.168.2.70:

[root@node01 ~] # ssh-keygen-t rsa [root@node01 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.71 [root@node01 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.5

Operate on node02 node 192.168.2.71:

[root@node02 ~] # ssh-keygen-t rsa [root@node02 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.70 [root@node02 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.5

Operate on node03 node 192.168.2.5:

[root@node03 ~] # ssh-keygen-t rsa [root@node03 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.70 [root@node03 ~] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.2.71

Third, build a master-slave replication architecture

1. Node01, node02, node03 install mysql and initialize the database (omitted)

2. The default installation mysql has a randomly generated password. The administrator password (test password: 111111) is set on the database on node01, node02 and node3 nodes, and the account repl used for replication is created (password 111111).

# authorize 192.168.2.% IP address range host root users to access any library and local login password

Mysql > SET PASSWORD = PASSWORD ('111111'); mysql > UPDATE user SET host='192.168.2.%' where user = 'root';mysql > GRANT ALL PRIVILEGES ON *. * TO' root'@'localhsot' IDENTIFIED BY '111111 *. * TO' root'@'192.168.2.%' IDENTIFIED BY '111111 *. * TO' root'@'192.168.2.%' IDENTIFIED BY '111111

3. Node01 (192.168.2.70) master configuration mysql file my.cnf (it is recommended to back up this file before modification) modify the following contents, and restart the database after modifying the configuration:

[mysqld] datadir = / mydata/data # data storage directory socket = / tmp/mysql.sock # socketinnodb_file_per_table = ON # Open separate tablespace skip_name_resolve = ON # disable dns parsing log_bin = mysql-bin # enable log (log storage location should not be the same as data storage on the same disk as possible Relay_log = relay-bin # enable relay log (log storage location should not be the same as data storage on the same disk in the same directory, test is convenient not to repoint here) binlog-format = row # log format log-slave-updates = true # configure to write binary log sever _ id = 1 # server_id from the CVM must be unique [root@node01 ~] # systemctl restart mysqld.service # restart the service

Note: there are also three log formats for binlog: STATEMENT,ROW,MIXED.

Mysql > show variables like 'log_%'; # check whether the log is enabled

Binlog-do-db and replicate-ignore-db must be the same

Log_bin, relay_log, binary logs and relay logs should not be placed in the same disk directory as the data storage, so as to prevent the log from being lost when the hard disk is damaged.

4. Node01 (192.168.2.70) master imports the test database apps.sql

Mysql > CREATE DATABASE apps;Query OK, 1 row affected (0.00 sec) mysql > use apps;Database changedmysql > source / home/soft/apps.sql

5. Node02 (192.168.2.71) slave configuration mysql file my.cnf modifies the following contents, and restarts the database after modifying the configuration:

[mysqld] datadir = / mydata/data socket = / tmp/mysql.sock # socketinnodb_file_per_table = ON skip_name_resolve = ON log_bin = mysql-bin relay_log = relay-bin binlog-format = row log-slave-updates = true sever_id = 2 # relay_log_purge=0 # disable automatic deletion of relay logs (the following two entries are added to the slave configuration file) If MHA enables this [root@node02 ~] # systemctl restart mysqld.service # restart service [root@node02 ~] # mysql-uroot-p111111-e "set global read_only=1" # read-only from the library, it is not recommended to write it in the configuration file

6. Node03 (192.168.2.5) slave configuration mysql file my.cnf modifies the following contents, and restarts the database after modifying the configuration:

[mysqld] datadir = / mydata/data socket = / tmp/mysql.sock # socketinnodb_file_per_table = ON skip_name_resolve = ON log_bin = mysql-bin relay_log = relay-bin binlog-format = row log-slave-updates = true sever_id = 3 # relay_log_purge=0 # disable automatic deletion of relay logs (the following two entries are added to the slave configuration file) If MHA enables this [root@node03 ~] # systemctl restart mysqld.service # restart service [root@node03 ~] # mysql-uroot-p111111-e "set global read_only=1" # read-only from the library, it is not recommended to write it in the configuration file

7. Back up a complete data on node01 (192.168.2.70) Master:

[root@node01 /] # mysqldump-uroot-p111111-h292.168.2.70-- master-data=2-- single-transaction-R-- triggers-A > / home/soft/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

Check out more information mysqldump-help

Create the replication user repl (password 111111) on node01 (192.168.2.70) Master and authorize access to all hosts:

Mysql > grant replication slave on *. * to 'repl'@'%' identified by' 111111; mysql > flush privileges; # refresh cache

9. View the binlog name and location of node01 (192.168.2.70) Master main library backup, MASTER_LOG_FILE and MASTER_LOG_POS:

[root@node01 soft] # head-n 30 / home/soft/all.sql | grep 'CHANGE MASTER TO'-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154

10. Copy the node01 Master master library backup all.sql to the slave library node01 (192.168.2.71) and node3 (192.168.2.5) / home/soft/ directory, and import it into the slave library

[root@node01 /] # scp / home/soft/all.sql root@192.168.2.71:/home/soft/ [root@node01 /] # scp / home/soft/all.sql root@192.168.2.5:/home/soft/

11. Import all.sql from the library node02,node3 and set replication parameters

[root@node02 /] # mysql-uroot-p111111-h292.168.2.71

< /home/soft/all.sql [root@node03 /]# mysql -uroot -p111111 -h292.168.2.5 < /home/soft/all.sql mysql>

CREATE DATABASE apps;mysql > use apps;Database changedmysql > source / home/soft/all.sql

Node02 and node03, connect to mysql, and execute the following command:

[root@node02 /] # mysql-uroot-p111111-h292.168.2.71

Mysql > stop slave; # pause slave library Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > CHANGE MASTER TO MASTER_HOST='192.168.2.70',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.04 sec)

Description: MASTER_HOST # main library, MASTER_USER and MASTER_PASSWORD # copy account passwords, CHANGE MASTER TO # restore synchronous files and logs

Mysql > start slave # start replication of Query OK 0 rows affected (0.00 sec) mysql > show slave status\ G # View replication status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 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: 154 Relay_Log_Space: 521 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: 0Master_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 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: / mydata/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: 1 row in set (0.00 sec)

=

[root@node03 /] # mysql-uroot-p111111-h292.168.2.5

Mysql > stop slave; # pause slave library Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > CHANGE MASTER TO MASTER_HOST='192.168.2.70',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154 Query OK, 0 rows affected, 2 warnings (0.04 sec) description: MASTER_HOST # main library, MASTER_USER and MASTER_PASSWORD # copy account passwords, CHANGE MASTER TO # restore synchronous files and logs mysql > start slave # start replication of Query OK 0 rows affected (0.00 sec) mysql > show slave status\ G # View replication status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 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: 154 Relay_Log_Space: 521 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: 0Master_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 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: / mydata/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: 1 row in set (0.01sec)

12. Verify that the replication is normal!

# insert a row of data in the main library node01 Master and check the Position number

Mysql > INSERT INTO apps (app_name,url,country) VALUES ('BAIDU',' http://www.baidu.com','CN'); Query OK, 1 row affected (0.00 sec) mysql > show master status\ gateway * 1. Row * * File: mysql-bin.000001 Position: 449 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

# check whether the synchronization exists from the library node02 slave, and whether the Position number is the same as the main library master

Mysql > SELECT * from apps +-+ | id | app_name | url | country | +-+ -+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | Weibo APP | http://weibo.com/ | CN | | 3 | Taobao APP | https://www.taobao.com/ | CN | | 4 | BAIDU | http://www.baidu.com | CN | +-+-- -+-+ mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 449 # consistent with the main library Position Relay_Log_File: relay-bin.000002 Relay_Log _ Pos: 615 Relay_Master_Log_File: mysql-bin.000001 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: 449 Relay_Log_Space: 816 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: 0Master_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 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: / mydata/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: 1 row in set (0.00 sec)

# check whether the synchronization exists from the library node03 slave, and whether the Position number is the same as the main library master

Mysql > SELECT * from apps +-+ | id | app_name | url | country | +-+ -+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | Weibo APP | http://weibo.com/ | CN | | 3 | Taobao APP | https://www.taobao.com/ | CN | | 4 | BAIDU | http://www.baidu.com | CN | +-+-- -+-+ mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 449 # consistent with the main library Position Relay_Log_File: relay-bin.000002 Relay_ Log_Pos: 615 Relay_Master_Log_File: mysql-bin.000001 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: 449 Relay_Log_Space: 816 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: 0Master_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 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: / mydata/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: 1 row in set (0.00 sec)

IV. Master-slave failover

1. Make sure that all master and slave databases have binary logs open.

Mysql > show variables like 'log_bin';+-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.01 sec)

2. Make sure that the slave database is up-to-date when switching data. First, set the main library node01 to read-only: set global read_only=1; and refresh the master library log-bin log.

Mysql > show variables like 'read_only';+-+-+ | Variable_name | Value | +-+-+ | read_only | ON | +-+-+ 1 row in set (0.00 sec) mysql > flush logs;Query OK, 0 rows affected (0.02 sec) mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000002 | 154 | +- -+ 1 row in set (0.00 sec)

Note: the file here is already mysql-bin.000002, not mysql-bin.000001, the Position number is 154, not the 449 above!

3. Confirm whether the file and pos of the slave library node02,node03 are the same as the master library.

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 # Note whether this is consistent with the main library Read_Master_Log_Pos: 154Note whether it is consistent with the main library Relay_Log_File: relay-bin.000004 Relay_Log_Pos: 367For File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes

4. After confirming that there is no data update in the master database node01, promote node02 (192.168.2.71) as the master database and node01 as the slave database.

1) node02 pauses the slave library (note here you can also pause the node03 slave library SLAVE so that it can still synchronize node01)

Mysql > STOP SLAVE;Query OK, 0 rows affected (0.01 sec)

2) change node02 to the main library master

Mysql > RESET MASTER;Query OK, 0 rows affected (0.01sec) mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000001 | 154 | +- -+ 1 row in set (0.00 sec)

3) close the read-only property of the new master library node02 and create a test01 database (whether the newly linked slave libraries node01 and node03 are synchronized properly)

Mysql > SET GLOBAL read_only=0;Query OK, 0 rows affected (0.00 sec) mysql > CREATE DATABASE test01;Query OK, 1 row affected (0.00 sec)

The Position of the new master library is as follows:

Mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000001 | 319 | +- -+ 1 row in set (0.00 sec)

4) set the identity of node01 (original master library) to slave, and change the node02 information of the linked new master library (Note: MASTER_LOG_POS should be the old 154at this time, not 319 after the creation of the new test01 library), and start SLAVE

Mysql > RESET SLAVE;Query OK, 0 rows affected (0.00 sec) mysql > CHANGE MASTER TO MASTER_HOST='192.168.2.71',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql > START SLAVE;mysql > show slave status\ G # View slave database status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 485 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes. Mysql > show databases +-+ | Database | +-+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | # test01 data has also come +- -+ 6 rows in set (0.00 sec)

Note: when you check the node03 slave library, Master_Host is still node01 (192.168.2.70), but the database test01 verified by the test is still synchronized. If you need to change the link to the new master library node02, see below for details!

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 # is also consistent with the Position number of the new main library node02 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes. Mysql > SHOW DATABASES +-+ | Database | +-+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | # the newly created test01 library is also synchronized with +- -+ 6 rows in set (0.00 sec)

* Note: modify the SLAVE identity of the node03 slave library, link the information of the new master library node02 (Note: MASTER_LOG_POS is still the old one), modify and restart SLAVE*

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.2.71',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql > START SLAVE;Query OK, 0 rows affected (0.00 sec) mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 # is also consistent with the Position number of the new main library node02 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes. Mysql > SHOW DATABASES +-+ | Database | +-+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | # the newly created test01 library is also synchronized with +- -+ 6 rows in set (0.00 sec)

5. Verify the data again, and insert a new record in the apps library apps table of the new node02 (192.168.2.71) master library to see if it is synchronized to other slave libraries.

Execute the following command on the node02 main library:

Mysql > USE apps;Database changedmysql > INSERT INTO apps (app_name,url,country) VALUES ('XINLANG',' http://www.sina.com.cn','CN');Query OK, 1 row affected (0.00 sec) mysql > SELECT * FROM apps +-+ | id | app_name | url | country | +-+ -+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | Weibo APP | http://weibo.com/ | CN | | 3 | Taobao APP | https://www.taobao.com/ | CN | 4 | BAIDU | http://www.baidu.com | CN | 5 | XINLANG | http://www.sina.com.cn | CN | | +-+ 5 rows in set (0.00 sec) |

Check whether the apps table from the node01 and node03 nodes of the library has also been successfully inserted, and the result is as follows:

Mysql > SELECT * FROM apps +-+ | id | app_name | url | country | +-+ -+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | Weibo APP | http://weibo.com/ | CN | | 3 | Taobao APP | https://www.taobao.com/ | CN | 4 | BAIDU | http://www.baidu.com | CN | 5 | XINLANG | http://www.sina.com.cn | CN | | +-+ 5 rows in set (0.00 sec) |

For the above demonstration MySQL 5.7.17 to achieve master-slave replication, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

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.