Mysql 主从复制GTID

——————————————–安装准备配置/etc/my.cnf主mastergrant分配复制帐号从slavechange；开启slave；查看状态；验证一致性——————————————–当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。binlog传输到slave，并存储到slave的relaylog后，读取这个GTID值设置gtid_next变量，告诉slave，下一个要执行的GTID值。sql线程从relaylog中获取GTID，然后对比slave端的binlog是否有该GTID。如果有记录，说明该GTID的事务已经执行过，slave就会忽略。如果没有记录，slave就会执行该GTID事务，记录该GTID到自身的binlog，再读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫面。——————————————–mysql-5.7.17-1.el6.x86_64.rpm-bundle.tartarxfmysql-5.7.17-1.el6.x86_64.rpm-bundle.taryuminstall-ymysql-community-client-5.7.17-1.el6.x86_64.rpmmysql-community-common-5.7.17-1.el6.x86_64.rpmmysql-community-libs-5.7.17-1.el6.x86_64.rpmmysql-community-libs-compat-5.7.17-1.el6.x86_64.rpmmysql-community-server-5.7.17-1.el6.x86_64.rpm/etc/init.d/mysqldstart##开启服务，初始化，得到一个uuid和临时秘密2017-03-28T00:46:21.597691Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:f6701f68-134f-11e7-94e7-52540022095a.2017-03-28T00:46:21.664060Z1[Note]Atemporarypasswordisgeneratedforroot@localhost:I-S>yshos7JT[root@server3~]#mysql-uroot-p##修改密码Enterpassword:mysql>alteruserroot@localhostidentifiedby’Redhat-1024′;QueryOK,0rowsaffected(0.09sec)vim/etc/my.cnf##编辑文件，进行配置server-id=3gtid-mode=ONenforce-gtid-consistency=1##开启gtid的一些安全限制log-bin=mysql-binbinlog-do-db=testbinlog-ignore-db=mysqllog-slave-updates/etc/init.d/mysqldrestart##更改配置，重启服务mysql>showmasterstatus;##查看主节点状态+——————+———-+————–+——————+——————-+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+——————+———-+————–+——————+——————-+|mysql-bin.000004|154|test|mysql||+——mysql>grantreplicationslaveon*.*toly@’172.25.38.%’identifiedby’Redhat-1024′;##分配复制帐号QueryOK,0rowsaffected,1warning(0.10sec)——————————————–[root@server4~]#/etc/init.d/mysqldstart2017-03-28T00:47:40.538466Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:257d874a-1350-11e7-b4ab-525400e717cb.2017-03-28T00:47:40.661188Z1[Note]Atemporarypasswordisgeneratedforroot@localhost:Aozj4uC:4iff[root@server4~]#mysql-uroot-pEnterpassword:mysql>alteruserroot@localhostidentifiedby’Redhat-1024′;QueryOK,0rowsaffected(0.40sec)[root@server4~]#/etc/init.d/mysqldrestart[root@server4~]#mysql-uroot-pEnterpassword:mysql>changemastertomaster_host=’172.25.38.3′,master_user=’ly’,master_password=’Redhat-1024′,master_auto_position=1;##mysql>showslavestatusG;Slave_IO_Running:YesSlave_SQL_Running:YesRetrieved_Gtid_Set:f6701f68-134f-11e7-94e7-52540022095a:1Executed_Gtid_Set:f6701f68-134f-11e7-94e7-52540022095a:1[root@server3mysql]#mysql-pEnterpassword:[root@server3mysql]#catauto.cnf[auto]server-uuid=f6701f68-134f-11e7-94e7-52540022095amysql>showglobalvariableslike’%gtid%’;+———————————-+——————————————+|Variable_name|Value|+———————————-+——————————————+|binlog_gtid_simple_recovery|ON||enforce_gtid_consistency|ON||gtid_executed|f6701f68-134f-11e7-94e7-52540022095a:1-7||gtid_executed_compression_period|1000||gtid_mode|ON||gtid_owned|||gtid_purged|||session_track_gtids|OFF|+———————————-+—————————————-开发云主机域名–+[root@server4mysql]#catauto.cnf[auto]server-uuid=257d874a-1350-11e7-b4ab-525400e717cbmysql>showglobalvariableslike’%gtid%’;+———————————-+——————————————+|Variable_name|Value|+———————————-+——————————————+|binlog_gtid_simple_recovery|ON||enforce_gtid_consistency|ON||gtid_executed|f6701f68-134f-11e7-94e7-52540022095a:1-7||gtid_executed_compression_period|1000||gtid_mode|ON||gtid_owned|||gtid_purged|f6701f68-134f-11e7-94e7-52540022095a:1-6||session_track_gtids|OFF|+———————————-+——————————————+mysql>grantreplicationslaveon*.*toly@’172.25.38.%’identifiedby’Redhat-1024′;mysql>changemastertomaster_host=’172.25.38.3′,master_user=’ly’,master_password=’Redhat-1024′,master_auto_position=1;mysql>startslave;mysql>showslavestatusG;***************************1.row***************************Slave_IO_State:Waitingformastertosendeventmysql>insertintousertbvalues(0004,’xiao’);QueryOK,1rowaffected(0.13sec)Master_Host:172.25.38.3Master_User:lyMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000004Read_Master_Log_Pos:445Relay_Log_File:server4-relay-bin.000002Relay_Log_Pos:658Relay_Master_Log_File:mysql-bin.000004Slave_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:445Relay_Log_Space:867Until_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:3Master_UUID:f6701f68-134f-11e7-94e7-52540022095aMaster_Info_File:/var/lib/mysql/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:f6701f68-134f-11e7-94e7-52540022095a:1Executed_Gtid_Set:f6701f68-134f-11e7-94e7-52540022095a:1Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)mysql>select*fromtest.usertb->;+—-+———-+|id|name|+—-+———-+|1|zhangsan||2|wang||3|lisi|+—-+———-+mysql>insertintousertbvalues(0004,’xiao’);QueryOK,1rowaffected(0.13sec)mysql>select*fromtest.usertb;+—-+———-+|id|name|+—-+———-+|1|zhangsan||2|wang||3|lisi||4|xiao|+—-+———-+

相关推荐: 基于逻辑卷LVM的MySQL、mariadb数据库备份还原详细实现

创建分区：创建PV，vg，lv：提供数据目录，挂载：提供数据：刷新日志，记录二开发云主机域名进制日志的位置：使用lv创建数据库快照：相关推荐: 第二章 身份验证——跟我学习springmvc shiro mybatis身份验证，即在应用中谁能证明他就是他本人。…