1、查看binlog状态:show variables like '%log_bin%';
由于还没设置binlog,设置binlog: SET @@global.log_bin=1;
查询证实:
mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
2、进入mycat:mysql -utest -ptest -h192.168.199.128 -P8066 –DTESTDB
mysql> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------+
| dn1 | SELECT * FROM travelrecord LIMIT 100 |
| dn2 | SELECT * FROM travelrecord LIMIT 100 |
| dn3 | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.35 sec)
暂时有3个分片,将dn2,dn3的数据转到另一台机器dn4。思路:
1)在dn2,dn3上,分别用mysqldump,加锁导出数据
2)导出的数据复制到dn4,mysqldump导入,
3)修改mycat配置文件,为切换作准备,
4)dn4上,用mysqlbinlog远程将dn2,dn3上新增的数据导入
5)restart mycat,切换完成,检测数据
(未完待续)