基于GTID的 故障切换
来源:互联网 发布:php ajax传值中文乱码 编辑:程序博客网 时间:2024/05/18 16:16
数据库状态
主库1:192.168.1.246:3306 从库1:192.168.1.246:3307 从库2:192.168.1.246:3308
主库1(root:127.0.0.1:3306) [db3306]> show master status;+------------------+----------+--------------+------------------+-------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------------------------------+| mysql-bin.000002 | 2464 | | | d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 |+------------------+----------+--------------+------------------+-------------------------------------------+从库1(root:127.0.0.1:3307) [test]> show master status;+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+| mysql-bin.000008 | 2449 | | | 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4,d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 |+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+从库2(root:127.0.0.1:3308) [test]> show master status;+------------------+----------+--------------+------------------+-----------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-----------------------------------------+| mysql-bin.000008 | 426 | | | d023f67f-4a90-11e7-9eff-0800271d97b5:11 |+------------------+----------+--------------+------------------+-----------------------------------------+此时从库2与主库落后一个事物,并且3307本身做过一些操作产生了自己的GTID--6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4
假设此时主库故障,从库1选为新主库,从库2复制,并不需要从库1产生的GTID--6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4的事物,
操作如下:
(root:127.0.0.1:3308) [test]> reset master ;Query OK, 0 rows affected (0.04 sec)(root:127.0.0.1:3308) [test]> set global gtid_purged='6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4,d023f67f-4a90-11e7-9eff-0800271d97b5:1-11';Query OK, 0 rows affected (0.02 sec)(root:127.0.0.1:3308) [test]> show master status;+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+| mysql-bin.000008 | 154 | | | 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4,d023f67f-4a90-11e7-9eff-0800271d97b5:1-11 |+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+(root:127.0.0.1:3308) [test]> CHANGE MASTER TO -> MASTER_HOST = '192.168.1.246', -> MASTER_PORT = 3307, -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'repl', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0.02 sec)(root:127.0.0.1:3308) [test]> start slave;Query OK, 0 rows affected (0.01 sec)(root:127.0.0.1:3308) [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.246 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 2449 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 686 Relay_Master_Log_File: mysql-bin.000008 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: 2449 Relay_Log_Space: 927 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: 10153307 Master_UUID: 6e71bd59-4a97-11e7-ad35-0800271d97b5 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: d023f67f-4a90-11e7-9eff-0800271d97b5:12 Executed_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4,d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
阅读全文
0 0
- 基于GTID的 故障切换
- 基于GTID的复制
- 搭建基于GTID的复制方式
- mysql基于gtid的读写分离
- Mysql基于GTID的从库创建
- MySQL5.6基于GTID的主从复制
- MySql基于GTID主从复制的搭建
- mysql 5.7传统复制到gtid复制的在线切换
- 基于GTID搭建主从
- MySQL基于GTID复制
- GTID:基于GTID搭建从库
- MySQL 5.6 基于GTID及多线程的复制详解
- MySQL 5.6 基于GTID及多线程的复制详解
- 基于gtid复制模式下的主从同步搭建测试
- MySQL基于gtid特性与xtrabackup的数据恢复
- MySQL优化之七--Mysql基于GTID的主从复制
- MySQL study之--MySQL基于GTID的主从复制
- MySQL基于GTID模式的主从复制设置
- 美团外卖订单系统演进
- phpfmt自动删除代码解决方案
- 金蝶EAS,消息中心,即时消息,发送通知
- iOS应用架构谈 网络层设计方案
- 第六章 流程控制
- 基于GTID的 故障切换
- python小工具___读取指定文件中特定字符串存入Excel表格
- 微信自定义内容 分享到朋友圈成功后朋友看不到问题
- 【mysql 存储过程】应用存储过程的优点与缺点
- Android实现chrome浏览器的Tab样式
- Binary Tree Inorder Traversal
- 怎样将Java项目打包成.Jar包
- MySQL慢查询分析mysqldumpslow
- Unity3d 破解 网址