Linux MySQL 5.7.23 Master-slave Replication (asynchronous replication)

Docker container Master node: 172.17.0.9Docker Container Sub-node: 172.17.0.10Asynchronous replication: First confirm that the main library and from the library is consistent, preferably a clean database that has just been initialized if the main library is in use cannot initialize, it is necessary to first copy the contents of the main library to the library to do synchronization 1, first to make a backup, the backup file to Slave[[email  protected]/]# mysqldump-uroot-p-B Test > Test.dumpenter password: [[email protected] data]# mysql- Uroot-penter Password: welcome to the MySQL monitor.  Commands end with; or \g.your MySQL connection ID is 4Server version:5.7.23 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> source/data/test.dumpquery OK, 0 rows Affected (0.00 sec) Query O K, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affe CTED (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.00 SEC) query OK, 0 rows affected, 1 Warning (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 1 row affected (0.00 sec ) Database changedquery OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.00 S EC) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) query OK, 0 Rows Affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected, 1 Warning (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Que Ry OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| database           |+--------------------+| Information_schema | | mysql              | | PErformance_schema | | sys                | | test               |+--------------------+5 rows in Set (0.00 sec) to slave data recovery Mys Ql> source/mysql/test3.sqlmysql> show databases;+--------------------+| database           |+--------------------+| Information_schema | | mysql              | | Performance_schema | | test1              | | test2              | | test3              |+--------------------+6 Rows in Set (0.00 sec) 2. Turn on the master node Bin-log log

Vi/etc/my.cnf

Add Bin-log Configuration

Server-id=1log-bin=mysql-binbinlog_format=row

Systemctl Restart Mysqld

3. Create a copy user, give user permissions mysql> grant replication Slave on * * to ' repl ' @ '% ' identified by ' MySQL ';mysql> flush Privileges;mys Ql> Show master status;to remember position+------------------+----------+--------------+--------------------------+-------------------+| file             | Position | binlog_do_db | binlog_ignore_db         | Executed_gtid_set |+------------------+----------+--------------+--------------------------+------------------- +| mysql-bin.000003 |     683 |               | Mysql,information_schema |                    |+------------------+----------+--------------+--------------------------+------------ -------+1 Row in Set (0.00 sec) 4. Then slavemysql>  change master to master_host= ' 172.17.0.9 ', master_port=3306, Master_user= ' Repl ',   ->  master_password= ' MySQL ', master_log_file= ' mysql-bin.000003 ', master_ log_pos=683; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql>  start slave; Query OK, 0 rows affected (0.09 sec) mysql> Show slave status\g;*************************** 1. Row ***************************               slave_io_state:waiting for master T o Send event                  master_host:172.17.0.9      &NB Sp           master_user:repl                  Master_ port:3306                connect_retry:60          &NBS P   master_log_file:mysql-bin.000003          read_master_log_pos:154      & nbsp        Relay_Log_File:72bd50b77c93-relay-bin.000002                relay_log_pos:320        relay_master_log_file:mysql-bin.000003    &NBSp        Slave_IO_Running:Yes            slave_sql_running:yes  &NB Sp           replicate_do_db:           replicate_ignore_db: & nbsp          Replicate_Do_Table:        Replicate_Ignore_Table:        replicate_wild_do_table:   replicate_wild_ignore_table:                    Last_Errno:0                &NBSP ;  Last_Error:                  Skip_Counter:0      & nbsp   exec_master_log_pos:154              relay_log_space:534      & nbsp       until_condition:none               UNTIL_LOG_FILE: &NB Sp               until_log_pos:0           Master_SSL_Allowed:No  & nbsp        Master_SSL_CA_File:            Master_SSL_CA_Path:                master_ssl_cert:             MASTER_SS l_cipher:                Master_SSL_Key:         Se conds_behind_master:0master_ssl_verify_server_cert:no                Last_io_ errno:0                last_io_error:           &NB Sp    Last_SQL_Errno:0               Last_SQL_Error:   replicate_ignore_server_ids:              Master_Server_Id:1              &nbsp   master_uuid:8ce1a2b2-b01c-11e8-90a0-0242ac11000a             master_info_ File:/usr/local/mysql/data/master.info                    sql_delay:0& nbsp         sql_remaining_delay:null      Slave_sql_running_state:slave have read all RelA Y log; Waiting for more updates           Master_Retry_Count:86400        &NBS P         master_bind:       last_io_error_timestamp:       last_sql_error_timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            retrieved_gtid_set:              executed_gtid_set:               and nbsp Auto_position:0&nBsp        Replicate_Rewrite_DB:                  chann el_name:            master_tls_version: 1 row in Set (0.00 sec) See two Yes basic success can be in the main The library to do something to test

5. Test results:

Main Library Insert Data

[Email protected] mysql]# Mysql-urootenter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.7.23-log mysql Community Server (GPL) Copyright (c) +, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> use test; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> SELECT * FROM t;+------+------+------+| ID | name |    Age |+------+------+------+| 1 |   DD | |+------+------+------+1 row in Set (0.00 sec) mysql> insert into t values (2, ' 2222 ', 11); Query OK, 1 row Affected (0.00 sec) mysql>

Log in to the slave database

mysql> use test; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> SELECT * FROM t;+------+------+------+| ID | name |    Age |+------+------+------+| 1 |   DD |    10 | | 2 |   2222 | |+------+------+------+2 rows in Set (0.00 sec) mysql> mysql> mysql>

Linux MySQL 5.7.23 Master-slave Replication (asynchronous replication)