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

1. Background

* MySQL Replication is asynchronous by default. When the main library executes some transactions, it will not control the progress of the library. If the slave database unfortunately lags behind, and even more unfortunately, the Crash occurs in the main database (for example, downtime), the data in the slave database is incomplete. In short, when the primary database fails, we can no longer use the standby library to continue to provide data-consistent services.

* Semi sync Replication (semi-synchronous replication) is submitted on master and then transferred to slave to wait for ack response. Only in certain cases, the transaction has been transferred to a slave, but it is not guaranteed that it has been completed on the slave database, which will cause inconsistency between master and slave in the last events.

* lossless replication (lossless copy) is transmitted to the slave to wait for an answer during the advance of the master. When at least one slave request bilog is written to relay-log and flush disk, ack is returned

2. Lossless replication transmission process

3. Environment

* master instance environment

Mysql > system cat / etc/redhat-releaseCentOS release 6.8 (Final) mysql > system ifconfig eth0 | sed-rn '2s# ^. * addr: (. *) Bca.*$#\ 1#gp'172.18.0.1mysql > show variables like' version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18-log | +-+-+ 1 row in set (0.00 sec)

* slave instance environment

Mysql > system cat / etc/redhat-releaseCentOS release 6.8 (Final) mysql > system ifconfig eth0 | sed-rn '2s# ^. * addr: (. *) Bca.*$#\ 1#gp'172.18.4.1mysql > show variables like' version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18-log | +-+-+ 1 row in set (0.00 sec)

* master instance my.cnf file

[mysqld] # basic settings# Master / Slave server-id must be set differently server-id = 110port = 3306user = mysqlbind_address = 0.0.0.0 character_set_server=utf8mb4skip_name_resolve = 1datadir = / data/mysql_datalog_error = error.log#replication settings#master_info_repository = TABLErelay_log_info_repository = TABLE# MySQL replication is based on binlog date Log_bin = bin.logsync_binlog = 1log_slave_updates# MySQL binlog format must be set to rowbinlog_format = rowrelay_log = relay.logrelay_log_recovery = 1slave_skip_errors = ddl_exist_errors#semi sync replication settings# set plug-in directory path plugin_dir=/usr/local/mysql/lib/plugin# load plug-in plugin_load = "rpl_semi_sync_master=semisync_master.so Rpl_semi_sync_slave=semisync_slave.so "# enable master semisync replicationrpl_semi_sync_master_enabled =" enable slave semisync replicationrpl_semi_sync_slave_enabled = "wait 5 seconds for no ack reply to automatically switch to asynchronous mode rpl_semi_sync_master_timeout = 500 turn on lossless replicationrpl_semi_sync_master_wait_point= AFTER_SYNC# at least 1 slave receives log RPL _ semi_sync_master_wait_for_slave_count = 1

* slave instance my.cnf file

[mysqld] # basic settings#server-id = 210port = 3306user = mysqlbind_address = 0.0.0.0character_set_server=utf8mb4skip_name_resolve = 1datadir = / data/mysql_datalog_error = error.log# slave enable read-only Avoid inconsistency of master and slave data caused by application miswriting read_only = onsuper_read_only = on#replication settings#master_info_repository = TABLErelay_log_info_repository = TABLElog_bin = bin.logsync_binlog = 1log_slave_updatesbinlog_format = rowrelay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors#semi sync replication settings#### # plugin_dir=/usr/local/mysql/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so" Rpl_semi_sync_slave=semisync_slave.so "loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000rpl_semi_sync_master_wait_point = AFTER_SYNCrpl_semi_sync_master_wait_for_slave_count = 1

4. Build numerous data based on lossless full replication master-slave [master originally has no data]

* the user used by Master to create replication [here ip is set to slave service IP or%]

Mysql > grant replication slave on *. * to 'rpl'@'172.18.4.1' identified by' 123 investors query OK, 0 rows affected, 1 warning (0.00 sec)

* View the binlog file name and log location on the master server

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + -+ | bin.000002 | 689 | +-+ -+ 1 row in set (0.00 sec)

* set master information on slave server

When the slave service is not enabled, the status of Slave_IO_Running and Slave_SQL_Running becomes No

Master_log_file settings start copying files, master_log_pos start file replication point

When mysql > show slave status; # does not enable replication, the slave status is empty Empty set (0.00 sec) mysql > change master to master_host='172.18.0.1',master_user='rpl',master_password='123',master_log_file='bin.000002',master_log_pos=689 Query OK, 0 rows affected 2 warnings (0.03 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 689 Relay_Log_File: relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: bin.000002 Slave_IO_Running: No Slave_SQL_Running: No 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: 689 Relay_Log_Space: 154 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: NULLMaster_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: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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)

* enable the slave service and check the status

After the slave service is enabled normally, the status of Slave_IO_Running and Slave_SQL_Running becomes Yes

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 689 Relay_Log_File: relay.000002 Relay_Log_Pos: 314 Relay_Master_Log_File: bin.000002 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: 689 Relay_Log_Space: 511 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: 110 Master_UUID: d7d5a01b-6ea0 -11e7-9773-00163e0432c5 Master_Info_File: mysql.slave_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)

* View Slave connection information on Master

Mysql > show slave hosts +-+ | Server_id | Host | Port | Master_id | Slave_UUID | +-+- -+-- + | 10 | 3306 | 3306 | 499ecfb3-6ea2-11e7-aec1-00163e028c02 | +- -+-+ 1 row in set (0.00 sec)

* create databases and tables and insert data on Master

Mysql > create database mytest character set utf8mb4;Query OK, 1 row affected (0.01 sec) mysql > use mytest;Database changedmysql > create table users (- > id BIGINT NOT NULL AUTO_INCREMENT,-> name VARCHAR (255) NOT NULL,-> sex ENUM ('sex ENUM,' F') NOT NULL DEFAULT 'NOT NULL,-> age INT SIGNED NOT NULL DEFAULT' 0),-> PRIMARY KEY (id)->) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 Query OK, 0 rows affected (0.02 sec) mysql > insert into users values (null, 'tom',' Manners, 24), (null, 'jak',' Fathers, 32), (null, 'sea',' Mises, 35), (null, 'lisea',' sec, 29); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > select * from users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | sea | M | 35 | 4 | lisea | M | 29 | +-+ 4 rows in set (0.00 sec)

* check on Slave

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | mytest | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec) mysql > use mytest Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables;+-+ | Tables_in_mytest | +-+ | users | +-+ 1 row in set (0.00 sec) mysql > select * from users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | sea | M | 35 | 4 | lisea | M | 29 | +-+ 4 rows in set (0.00 sec)

5. Build data based on lossless full replication master-slave [master originally has data]

* View the contents of the mytest library

Database changedmysql > show tables;+-+ | Tables_in_mytest | +-+ | users | +-+ 1 row in set (0.00 sec) mysql > select * from users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | sea | M | 35 | 4 | lisea | M | 29 | +-+ 4 rows in set (0.00 sec)

* use mysqldump atom to export master library data and record binlog [test only mytest library]

If there are multiple libraries, the-B parameter is separated by a comma.

[root@master] # mysqldump-- single-transaction-- master-data-B mytest-uroot-p > mytest.sqlEnter password:

* transfer the exported backup file mytest.sql to slave

[root@master ~] # scp. / mytest.sql root@172.18.4.1:/root

* slave creates the same database and imports the backup

Mysql > create database mytest character set utf8mb4;Query OK, 1 row affected (0.01sec) [root@slave] # mysql-uroot-p mytest

Grant replication slave on *. * to 'rpl'@'172.18.4.1' identified by' 123 investors query OK, 0 rows affected, 1 warning (5.01 sec)

* View backup file mytest.sql to view binlog file name and log location

[root@slave ~] # grep 'CHANGE MASTER TO' mytest.sql CHANGE MASTER TO MASTER_LOG_FILE='bin.000002', MASTER_LOG_POS=1575

* set master information on slave server

When the slave service is not enabled, the status of Slave_IO_Running and Slave_SQL_Running becomes No

When mysql > show slave status; # does not enable replication, the slave status is empty Empty set (0.00 sec) mysql > change master to master_host='172.18.0.1',master_user='rpl',master_password='123',master_log_file='bin.000002',master_log_pos=1575 Query OK, 0 rows affected 2 warnings (0.02 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Master_Host: 172.18.0.1 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 1575 Relay_Log_File: relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: bin.000002 Slave_IO_Running: No Slave_SQL_Running: No 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: 1575 Relay_Log_Space: 154 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: NULLMaster_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: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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)

* enable the slave service and check the status

Mysql > start slave Query OK 0 rows affected (0.01 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.1 Master_ User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 1872 Relay_Log_File: relay.000002 Relay_Log_Pos: 611 Relay_Master_Log_File: bin.000002 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: 1872 Relay_Log_Space: 808 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: 110 Master_UUID: d7d5a01b-6ea0-11e7-9773-00163e0432c5 Master_Info_File: mysql.slave_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)

* data manipulation of mytest library on master

Mysql > select * from mytest.users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | sea | M | 35 | 4 | lisea | M | 29 | + -- + 4 rows in set (0.00 sec) mysql > insert into mytest.users select null 'test', 'Manners, 42 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > update mytest.users set name='seasea' where id = 3 row affected query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from mytest.users +-+ | id | name | sex | age | +-- + | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | seasea | M | 35 | 4 | lisea | M | 29 | 5 | test | M | 42 | + -+ 5 rows in set (0.00 sec)

* check on slave

Mysql > select * from mytest.users +-+ | id | name | sex | age | +-- + | 1 | tom | M | 24 | 2 | jak | F | 32 | 3 | seasea | M | 35 | 4 | lisea | M | 29 | 5 | test | M | 42 | + -+ 5 rows in set (0.00 sec)

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

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.