1. 背景

* MySQL Replication默认都是异步（asynchronous），当主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

* Semi sync Replication(半同步复制)是在master上提交完成后，再传送到slave等待ack应答，仅仅在一定情况下事务的已经传递到一个slave上，但是并不确保已经在备库上执行完成，会造成最后一次events的主备不一致。

*lossless replication(无损复制)是在master提前过程中，传送到slave中等待应答。当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack

2. lossless replication传输过程

3. 环境

* master 实例环境

mysql>systemcat/etc/redhat-releaseCentOSrelease6.8(Final)mysql>systemifconfigeth0|sed-rn'2s#^.*addr:(.*)Bca.*$#\1#gp'172.18.0.1mysql>showvariableslike'version';+---------------+------------+|Variable_name|Value|+---------------+------------+|version|5.7.18-log|+---------------+------------+1rowinset(0.00sec)

* slave 实例环境

mysql>systemcat/etc/redhat-releaseCentOSrelease6.8(Final)mysql>systemifconfigeth0|sed-rn'2s#^.*addr:(.*)Bca.*$#\1#gp'172.18.4.1mysql>showvariableslike'version';+---------------+------------+|Variable_name|Value|+---------------+------------+|version|5.7.18-log|+---------------+------------+1rowinset(0.00sec)

* master 实例my.cnf文件

[mysqld]########basicsettings#########主从server-id一定要设置不同server-id=110port=3306user=mysqlbind_address=0.0.0.0character_set_server=utf8mb4skip_name_resolve=1datadir=/data/mysql_datalog_error=error.log#######replicationsettings########master_info_repository=TABLErelay_log_info_repository=TABLE#MySQL复制是基于binlog日志的log_bin=bin.logsync_binlog=1log_slave_updates#MySQLbinlog格式搭建主从时必须设置为rowbinlog_format=rowrelay_log=relay.logrelay_log_recovery=1slave_skip_errors=ddl_exist_errors######semisyncreplicationsettings#########设置插件目录路径plugin_dir=/usr/local/mysql/lib/plugin#加载插件plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#开启mastersemisyncreplicationrpl_semi_sync_master_enabled=1#开启slavesemisyncreplicationrpl_semi_sync_slave_enabled=1#等待5秒无ack应答自动切换为异步模式rpl_semi_sync_master_timeout=5000#开启losslessreplicationrpl_semi_sync_master_wait_point=AFTER_SYNC#至少有1个slave接收到日志rpl_semi_sync_master_wait_for_slave_count=1

* slave 实例my.cnf文件

[mysqld]########basicsettings########server-id=210port=3306user=mysqlbind_address=0.0.0.0character_set_server=utf8mb4skip_name_resolve=1datadir=/data/mysql_datalog_error=error.log#slave上开启只读，避免应用误写导致主从数据不一致read_only=onsuper_read_only=on#######replicationsettings########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######semisyncreplicationsettings########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. 搭建无数据基于无损全复制主从 [ master原来无数据 ]

* Master创建复制所使用的用户 [ 此处ip设置为slave服务IP或者% ]

mysql>grantreplicationslaveon*.*to'rpl'@'172.18.4.1'identifiedby'123';QueryOK,0rowsaffected,1warning(0.00sec)

*master服务器上查看binlog文件名和日志位置

mysql>showmasterstatus;+------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------+----------+--------------+------------------+-------------------+|bin.000002|689||||+------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

* slave服务器上设置master信息

未开启slave服务时，Slave_IO_Running与Slave_SQL_Running状态成No

master_log_file 设置开始复制文件, master_log_pos 开始文件复制点

mysql>showslavestatus;#未开启复制功能时，slave状态是空的Emptyset(0.00sec)mysql>changemastertomaster_host='172.18.0.1',master_user='rpl',master_password='123',master_log_file='bin.000002',master_log_pos=689;QueryOK,0rowsaffected,2warnings(0.03sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:Master_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:689Relay_Log_File:relay.000001Relay_Log_Pos:4Relay_Master_Log_File:bin.000002Slave_IO_Running:NoSlave_SQL_Running:NoReplicate_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:689Relay_Log_Space:154Until_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:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:0Master_UUID:Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_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:1rowinset(0.00sec)

* 开启slave服务，并查看状态

正常开启slave服务后，Slave_IO_Running与Slave_SQL_Running状态成Yes

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:689Relay_Log_File:relay.000002Relay_Log_Pos:314Relay_Master_Log_File:bin.000002Slave_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:689Relay_Log_Space:511Until_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:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:110Master_UUID:d7d5a01b-6ea0-11e7-9773-00163e0432c5Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_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:1rowinset(0.00sec)

* Master上查看Slave连接信息

mysql>showslavehosts;+-----------+------+------+-----------+--------------------------------------+|Server_id|Host|Port|Master_id|Slave_UUID|+-----------+------+------+-----------+--------------------------------------+|210||3306|110|499ecfb3-6ea2-11e7-aec1-00163e028c02|+-----------+------+------+-----------+--------------------------------------+1rowinset(0.00sec)

* Master上操作创建数据库与表，并插入数据

mysql>createdatabasemytestcharactersetutf8mb4;QueryOK,1rowaffected(0.01sec)mysql>usemytest;Databasechangedmysql>createtableusers(->idBIGINTNOTNULLAUTO_INCREMENT,->nameVARCHAR(255)NOTNULL,->sexENUM('M','F')NOTNULLDEFAULT'M',->ageINTSIGNEDNOTNULLDEFAULT'0',->PRIMARYKEY(id)->)ENGINE=INNODBDEFAULTCHARSET=utf8mb4;QueryOK,0rowsaffected(0.02sec)mysql>insertintousersvalues(null,'tom','M',24),(null,'jak','F',32),(null,'sea','M',35),(null,'lisea','M',29);QueryOK,4rowsaffected(0.01sec)Records:4Duplicates:0Warnings:0mysql>select*fromusers;+----+-------+-----+-----+|id|name|sex|age|+----+-------+-----+-----+|1|tom|M|24||2|jak|F|32||3|sea|M|35||4|lisea|M|29|+----+-------+-----+-----+4rowsinset(0.00sec)

*Slave上查看

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||mytest||performance_schema||sys|+--------------------+5rowsinset(0.00sec)mysql>usemytest;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+------------------+|Tables_in_mytest|+------------------+|users|+------------------+1rowinset(0.00sec)mysql>select*fromusers;+----+-------+-----+-----+|id|name|sex|age|+----+-------+-----+-----+|1|tom|M|24||2|jak|F|32||3|sea|M|35||4|lisea|M|29|+----+-------+-----+-----+4rowsinset(0.00sec)

5. 搭建有数据基于无损全复制主从 [ master原来有数据 ]

* 查看mytest库内容

Databasechangedmysql>showtables;+------------------+|Tables_in_mytest|+------------------+|users|+------------------+1rowinset(0.00sec)mysql>select*fromusers;+----+-------+-----+-----+|id|name|sex|age|+----+-------+-----+-----+|1|tom|M|24||2|jak|F|32||3|sea|M|35||4|lisea|M|29|+----+-------+-----+-----+4rowsinset(0.00sec)

* 使用mysqldump原子导出master库数据，并记录binlog [ 测试只有mytest库 ]

如果有多个库，-B参数后逗号分隔。

[root@master~]#mysqldump--single-transaction--master-data-Bmytest-uroot-p>mytest.sqlEnterpassword:

* 将导出的备份文件mytest.sql传输到slave

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

* slave创建相同的数据库，并将备份导入

mysql>createdatabasemytestcharactersetutf8mb4;QueryOK,1rowaffected(0.01sec)[root@slave~]#mysql-uroot-pmytest<mytest.sqlEnterpassword:

* Master创建复制所使用的用户 [ 此处ip设置为slave服务IP或者% ]

mysql>grantreplicationslaveon*.*to'rpl'@'172.18.4.1'identifiedby'123';QueryOK,0rowsaffected,1warning(5.01sec)

* 查看备份文件mytest.sql查看binlog文件名和日志位置

[root@slave~]#grep'CHANGEMASTERTO'mytest.sqlCHANGEMASTERTOMASTER_LOG_FILE='bin.000002',MASTER_LOG_POS=1575;

* slave服务器上设置master信息

未开启slave服务时，Slave_IO_Running与Slave_SQL_Running状态成No

mysql>showslavestatus;#未开启复制功能时，slave状态是空的Emptyset(0.00sec)mysql>changemastertomaster_host='172.18.0.1',master_user='rpl',master_password='123',master_log_file='bin.000002',master_log_pos=1575;QueryOK,0rowsaffected,2warnings(0.02sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:Master_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:1575Relay_Log_File:relay.000001Relay_Log_Pos:4Relay_Master_Log_File:bin.000002Slave_IO_Running:NoSlave_SQL_Running:NoReplicate_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:1575Relay_Log_Space:154Until_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:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:0Master_UUID:Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_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:1rowinset(0.00sec)

*开启slave服务，并查看状态

mysql>startslave;QueryOK,0rowsaffected(0.01sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:172.18.0.1Master_User:rplMaster_Port:3306Connect_Retry:60Master_Log_File:bin.000002Read_Master_Log_Pos:1872Relay_Log_File:relay.000002Relay_Log_Pos:611Relay_Master_Log_File:bin.000002Slave_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:1872Relay_Log_Space:808Until_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:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:110Master_UUID:d7d5a01b-6ea0-11e7-9773-00163e0432c5Master_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_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:1rowinset(0.00sec)

* master上mytest库数据操作

mysql>select*frommytest.users;+----+-------+-----+-----+|id|name|sex|age|+----+-------+-----+-----+|1|tom|M|24||2|jak|F|32||3|sea|M|35||4|lisea|M|29|+----+-------+-----+-----+4rowsinset(0.00sec)mysql>insertintomytest.usersselectnull,'test','M',42;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>updatemytest.userssetname='seasea'whereid=3;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>select*frommytest.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|+----+--------+-----+-----+5rowsinset(0.00sec)

* slave上查看

mysql>select*frommytest.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|+----+--------+-----+-----+5rowsinset(0.00sec)

6. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。