9. ClustrixDB主从复制
一、在线添加从库
主集群: 10.1.1.23:5306
从集群: 10.1.3.88:5306
主库开启binlog
MySQL [(none)]> CREATE BINLOG 'clustrix-bin' FORMAT='ROW'
主库创建同步账号
MySQL [(none)]> create user 'clx_repl'@'%' identified by '123123';
MySQL [(none)]> Grant REPLICATION SLAVE ON *.* to 'clx_repl'@'%';
主库模拟创建数据
scloudusr@p2cn1uclx101m_10.248.100.241 ~]$ sysbench --mysql-host=localhost --mysql-port=5306 --mysql-user=root --mysql-password= --mysql-db=scloud --table_size=1000000 oltp_insert prepare
主库备份数据
MySQL [(none)]> backup scloud.* to "sftp://scloudusr:Scloud201!@10.1.3.88:2022/tmp/backup" ;
从库恢复数据
RESTORE * FROM "sftp://scloudusr:Scloud201!@10.1.3.88:2022/tmp/backup" REPLICAS = 1; (快速恢复使用1个副本)
从库备份文件找到主库file和pos信息
cat /tmp/backup/metadata/clustrix-bin.000002:103179247
从库配置同步信息
MySQL [(none)]> stop slave;
MySQL [(none)]> CHANGE MASTER TO MASTER_LOG_FILE = 'clustrix-bin.000002', MASTER_LOG_POS = 103179247, MASTER_HOST = '10.1.1.23', MASTER_USER = 'clx_repl', MASTER_PASSWORD = '123123', MASTER_PORT = 5306;
MySQL [(none)]> start slave;
MySQL [(none)]> show slave status\GMySQL [scloud]> show slave status\G
*************************** 1. row ***************************
Slave_Name: default
Slave_Status: Running
Master_Host: 10.1.1.23
Master_Port: 5306
Master_User: clx_repl
Master_Log_File: clustrix-bin
Slave_Enabled: Enabled
Log_File_Seq: 3
Log_File_Pos: 26232091
Last_Error: no error
Connection_Status: Connected
Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 12483
Seconds_Behind_Master: 225
1 row in set (0.00 sec)
二、主从同步异常处理
通过stop slave; start slave skip 1; 来跳过错误事务
MySQL [test]> show slave status\G
*************************** 1. row ***************************
Slave_Name: default
Slave_Status: Errored
Master_Host: 10.1.3.151
Master_Port: 5306
Master_User: clx_repl
Master_Log_File: clustrix-bin
Slave_Enabled: Enabled
Log_File_Seq: 2
Log_File_Pos: 102189775
Last_Error: Row Not Found: for delete on test.tt at clustrix-bin2.102189909 where tt.__idx_tt__PRIMARY(id)=(3). Statement: COMMIT, Database:
Connection_Status: Disconnected
Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)MySQL [test]> stop slave;
Query OK, 0 rows affected (0.01 sec)MySQL [test]> start slave skip 1;
Query OK, 0 rows affected (0.01 sec)MySQL [test]> show slave status\G
*************************** 1. row ***************************
Slave_Name: default
Slave_Status: Running
Master_Host: 10.1.3.151
Master_Port: 5306
Master_User: clx_repl
Master_Log_File: clustrix-bin
Slave_Enabled: Enabled
Log_File_Seq: 2
Log_File_Pos: 102189969
Last_Error: no error
Connection_Status: Connected
Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
最新文章
- JavaScript语言精粹--Function,类,this,对象
- 课堂Java小程序(加减乘除与验证码)
- mongodb架构篇
- VC++/MFC 最常用宏和指令
- hibernate的session对象核心方法注意的问题
- cpu和io进程调度时间
- Unresolved external 'LresultFromObject'
- ios创建bundle的图片资源文件(转)
- c# dataset 索引0没有值
- POJ 1323 Game Prediction#贪心
- 数字规律:Pascal‘s triangle
- winow7安装django 1.9.1
- 学习笔记:javascript内置对象:字符串对象
- .NET作品集:linux下的博客程序
- shell入门之变量测试
- sql server创建登录出发器后导致登录失败--解决方案
- Perl处理和收走子进程(退出状态码和wait)
- ASP.NET Web API相关
- 【CSS】面试知识整理
- 依赖注入(Dependency Injection)