让我们继续MySQL集群搭建之旅。大多数MySQL集群采用经典的主从模式，这也是部署高可用集群的基础。通过以上，我们已经知道如何搭建MySQL的主备模式了。如果你还没有看到它，你可以了解它。MySQL安装（二进制版）MySQL集群搭建（一）-master/standbysetup1环境准备上次我们搭建了master/standby架构，如下图，这次我们的目标是配置具体信息IP系统端口MySQL版本节点读取和写入指令192.168.41.83Centos6.833065.7.20Master读写主节点192.168.41.72Centos6.833065.7.20Standby只读，可以切换为读写备节点，允许升级到主节点192.168.41.83Centos6.833075.7。20Slave只读从节点环境I已经准备好了，如下图，如果不知道如何安装MySQL和搭建主备，可以参考之前的文章参考配置Master[client]port=3306default-character-set=utf8mb4socket=/data/mysql_db/mysql_seg_3306/mysql.sock[mysqld]datadir=/data/mysql_db/mysql_seg_3306basedir=/usr/local/mysql57tmpdir=/tmpsocket=/data/mysql_db/mysql_seg_3306/mysql.sockpid-file=/data/mysql_db/mysql_seg_3306/mysql.pidskip-external-locking=1skip-name-resolve=1port=3306server_id=833306default-storage-engine=InnoDBcharacter-set-server=utf8mb4default_password_lifetime=0auto_increment_offset=1auto_increment_increment=2####log####log_timestamps=mysql_bin=/data/mysql_logmysql-binlog_bin_index=/data/mysql_log/mysql_seg_3306/mysql-bin.indexbinlog_format=rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-binrelay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.indexlog_error=/data/mysql_log/mysql_seg_3306/mysql-error.log####复制####log_slave_updates=1replicate_wild_ignore_table=information_schema.%,performance_schema.%,sys.%####半同步复制设置#####plugin_dir=/usr/local/mysql57/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=5000Standby[client]port=3306default-character-set=utf8mb4socket=/data/mysql_db/mysql_seg_3306/mysql.sock[mysqld]datadir=/data/mysql_db/mysql_seg_3306basedir=/usr/local/mysql57tmpdir=/tmpsocket=/data/mysql_db/mysql_seg_3306/mysql.sockpid-file=/data/mysql_db/mysql_seg_3306/mysql.pidskip-external-locking=1skip-name-resolve=1port=3306server_id=723306default-storage-engine=InnoDBcharacter-set-server=utf8mb4default_password_lifetime=0auto_increment_offset=2auto_increment_increment=2####log####log_timestamps=systemlog_bin=/data/mysql_log/mysql_seg_3306/mysql-binlog_bin_index=/data/mysql_log/mysql_seg_3306/mysql-bin.indexbinlog_format=rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-binrelay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.indexlog_error=/data/mysql_log/mysql_seg_3306/mysql-错误.log####复制####log_slave_updates=1replicate_wild_ignore_table=information_schema.%,performance_schema.%,sys.%####半同步复制设置#####plugin_dir=/usr/local/mysql57/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=5000Slave[client]port=3307default-character-set=utf8mb4socket=/data/mysql_db/mysql_seg_3307/mysql.sock[mysqld]datadir=/data/mysql_db/mysql_seg_3307basedir=/usr/local/mysql57tmpdir=/tmpsocket=/data/mysql_db/mysql_seg_3307/mysql.sockpid-file=/data/mysql_db/mysql_seg_3307/mysql.pidskip-external-locking=1skip-name-resolve=1port=3307server_id=833307read_only=1default-storage-engine=InnoDBcharacter-set-server=utf8mb4default_password_lifetime=0####日志####log_timestamps=systemlog_bin=/data/mysql_log/mysql_seg_3307/mysql-binlog_bin_index=/data/mysql_log/mysql_seg_3307/mysql-bin.indexbinlog_format=rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3307/mysql-relay-binrelay_log_index=/数据/mysql_log/mysql_seg_3307/mysql-relay-bin.indexlog_error=/data/mysql_log/mysql_seg_3307/mysql-error.log####复制####replicate_wild_ignore_table=information_schema.%,performance_schema.%,sys.%####半同步复制设置#####plugin_dir=/usr/local/mysql57/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配置解释auto_increment_offset自增字段起始值，设置为2表示自增字段从2开始算auto_increment_incrementauto-increment字段的值是一步步递增的，设置为2表示每次递增2。根据主节点的设置，自增字段的值可能如下：1,3,5,7...log_slave_updates将复制事件写入binlog，如果server既是主库又是从库。在这里，让我们为Master和Standby打开它。设置auto_increment_offset和auto_increment_increment的目的是防止两个节点双写时主键冲突，当前部署状态为，Standby正在与Master同步，Slave空运行。现在开始搭建双主架构2.1记录Standy节点的二进制位置，实现Master对Slave的同步。简单点说就是逆向搭建主备同步我们先到备节点记录当前二进制日志状态db72-3306>>showmasterstatus;+----------------+-----------+--------------+----------------+-------------------+|文件|当前位置|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------------+--------+-------------+-------------------+--------------------+|mysql-bin.000005|154||||+----------------+--------+--------------+-----------------+--------------------+1rowinset(0.00sec)一般情况下，masternode一直在写数据，而我们设置了log_slave_updates，有同学会疑惑，我们记录当前二进制位置后，如果有数据同步，二进制位置发生变化，我们从旧数据开始同步，会不会有任何数据冲突。其实不是的，在双主模式下，数据库会帮我们处理这种情况。现在我们模拟这种情况，在master节点上插入一些数据db83-3306>>insertintotest1values(0,'echoQMC',24);查询正常，1行受影响（0.01秒）db83-3306>>select*fromtest1;+----+--------+-----+|编号|姓名|年龄|+-----+--------+-----+|1|a|16||2|b|17||3|c|18||4|d|19||5|程清晨|24||7|回声QMC|24|+----+--------+-----+6rowsinset(0.00sec)插入数据后，让我们看看Standbybinarylocationdb72-3306>>showmasterstatus;+----------------+---------+--------------+------------------+----------------+|文件|当前位置|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------------+--------+---------------+----------------+--------------------+|mysql-bin.000005|424||||+----------------+----------+------------+-------------+--------------------+1rowinset(0.00sec)2.2in主节点根据二进制日志开始同步我们首先获得的位配置，在Master节点执行如下命令开启同步--resetreplication--resetslave；--同步配置CHANGEMASTERTOMASTER_HOST='192.168.41.72',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;--具体执行过程启动同步启动slave如下db83-3306>>CHANGEMASTERTO->MASTER_HOST='192.168.41.72',->MASTER_PORT=3306,->MASTER_USER='repl',->MASTER_PASSWORD='repl',->MASTER_LOG_FILE='mysql-bin.000005',->MASTER_LOG_POS=154;QueryOK,0rowsaffected,2warnings(0.00sec)db83-3306>>startslave;QueryOK,0rowsaffected(0.00sec)查看同步statusdb83-3306>>showslavestatus\G;*****************************1.行***************************Slave_IO_State：等待master发送事件Master_Host：192.168.41.72Master_User：replMaster_Port：3306Connect_Retry：60Master_Log_File：mysql-bin.000005关于ad_Master_Log_Pos:424Relay_Log_File:mysql-relay-bin.000002Relay_Log_Pos:320Relay_Master_Log_File:mysql-bin.000005Slave_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:information_schema.%,performance_schema.%,sys.%Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:424Relay_Log_Space:527Until_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_Server_MasterVKey:Secondser_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:723306Master_UUID:f9610603-e99f-11e8-b980-90b11c1a653aMaster_Info_File:/data/mysql_db/mysql_seg_3306/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slave已读取所有中继日志；等待更多更新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)ItisfoundthatspecifiedthequeryisNoqueryingthe可错误正常，没有数据冲突。现在在Standby中测试写入数据会同步到Masterdb72-3306>>insertintotest1values(0,'Keeming',24);QueryOK,1rowaffected(0.04sec)在Master节点db83-3306上查看数据>>select*fromtest1;+-----+--------+-----+|编号|姓名|年龄|+----+--------+-----+1|一个|16||2|乙|17||3|c|18||4|d|19||5|----+--------+-----+7rowsinset(0.00sec)数据同步完成，双主架构搭建成功。目前的架构如下仔细观察主键的增量，我们发现在Master节点插入数据echoQMC时，主键从5跳到7，而在Standby节点插入Keeming数据时，主键从7跳到8，说明主键的增量不同。在Master中插入数据只会有Singularprimarykey，在Standby中插入数据只会有dualprimarykey，所以即使两个节点同时写入数据，也不会冲突Master同步，这个过程是一个简单的master-slave搭建，具体过程不再赘述。在使用master-master-slave架构下，为了数据的一致性，我们一般只允许一个节点有写操作，其他节点设置为只读，所以在Slave的配置文件中添加read_only，并手动设置Standby节点为只读（易于切换）。所有操作完成后，架构如下，主-主-备架构完成。
