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 ensures to some extent that the committed transaction has been passed to at least one repository.

* Semi sync Replication only guarantees that the transaction has been passed to the slave, but it does not guarantee that the execution has been completed on the slave.

* after the Semi sync Replication main library waits for a timeout (rpl_semi_sync_master_timeout), it is automatically downgraded to the default asynchronous.

* Semi sync Replication is supported in MySQL version 5.5

two。 Environment

* master server 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.6.36-log | +-+-+ 1 row in set (0.00 sec)

* master server 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.6.36-log | +-+-+ 1 row in set (0.00 sec)

* master server my.cnf configuration 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 replicationloose_rpl_semi_sync_master_enabled = enable slave semisync replicationloose_rpl_semi_sync_slave_enabled = wait 5 seconds without ack reply automatically switch to asynchronous mode loose_rpl_semi_sync_master_timeout = 5000

* slave server my.cnf configuration 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 = 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 = 5000

3. Build based on semi-synchronous master-slave

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

Grant replication slave on *. * to 'rpl'@'172.18.4.1' identified by' 123'

* 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.000003 | 1187 | +-- + -+ 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

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.000003',master_log_pos=1187 Query OK, 0 rows affected 2 warnings (0.01 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.000003 Read_Master_Log_Pos: 1187 Relay_Log_File: relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: bin.000003 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: 1187 Relay_Log_Space: 120 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: 01 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 > 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.000003 Read_Master_Log_Pos: 1187 Relay_Log_File: relay.000002 Relay_Log_Pos: 277 Relay_Master_Log_File: bin.000003 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: 1187 Relay_Log_Space: 440 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: 08ea3ca7-6dd4-11e7-a45c-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 the slave Master_Bind O thread to update it 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: 01 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 | d6ceba69-6dd4-11e7-a462-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;mysql > 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.03 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 | | test | +-+ 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)

4. Master/Slave transmission process

5. 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.