mysql 排除部分表数据搭建主从

一、 背景

某大库主从同步中断，由于中断时间过长，从库relay log已被删除，需要重建。由于库太大，直接用mysqldump导出可能需要一天甚至几天，导入时间可能更长。沟通发现主库中存储历史数据的几个大表只会不断insert数据而不会update,delete旧数据，到指定时间后会drop分区。从库中不需要查询旧数据，即使故障切换到从库没有了这部分数据影响也不太大。

综上，准备在搭建主从时只导出大表的表结构而不导出数据，其余表则导出数据+表结构。

 

二、 操作步骤

0. 确定要排除的分区表

数据目录中分区表数据文件格式为：“表名#P#分区时间”，例如要排除的表为 test1、test2、test3

1. 在主库导出test1、test2、test3之外的其他表

mysqldump -uroot -p --single-transaction -R --default-character-set=utf8 --triggers --events --hex-blob --databases mydb --ignore-table=mydb.test3 --ignore-table=mydb.test2 --ignore-table=mydb.test1 --master-data=2 > mydb_`date "+%Y%m%d"`.sql

2. 在mydb主库导出历史大表的表结构

mysqldump -uroot -p -d mydb test3 test2 test1 > tables_`date "+%Y%m%d"`.sql

3. 从库获取主库备份文件

scp root@ip:/bak/*_20191108.sql /data/bakcup

4. 从库导入表数据

#导入其他表数据
mysql -uroot -p
reset master;
mysql -uroot -p < mydb_20191108.sql

#导入分区表结构
mysql -uroot -p
reset master;
use mydb;
source tables_20191108.sql

5. 在从库配置主从关系

less mydb_20191108.sql

找到以下部分

-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='xxxxx';
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00xxxx', MASTER_LOG_POS=xxxxxx;

从库执行

stop slave;
reset master;
SET @@GLOBAL.GTID_PURGED='上面查到的值';
CHANGE MASTER TO MASTER_HOST='ip',MASTER_USER='root',MASTER_PASSWORD='xxx',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;

 

三、 报错小结

1. 导入时遇到报错

mysql -uroot -p < mydb_20191108.sql
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

因为导出文件中有SET @@GLOBAL.GTID_PURGED='xxx'语句，该参数只能在@@GLOBAL.GTID_EXECUTED为空时设置，须先执行reset master清空@@GLOBAL.GTID_EXECUTED，再进行导入。

mysql -uroot -p
reset master;
mysql -uroot -p < mydb_20191108.sql

2. start slave遇到报错

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决方法

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.17 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 1354  <--  主从延迟，从库追上主库时为0

参考

http://www.unixfbi.com/578.html

https://www.cnblogs.com/shengdimaya/p/7681550.html