主从传输表空间的坑

主从传输表空间的坑

主库import tablespace只会在binlog中记录alter table xxx import tablespace语句,而不会记录表中的数据的插入语句

[mysql@master2 ~]$ mysqlbinlog -vv --base64-output=decode-rows /data/mysqldata/3306/binlog/mysql-bin.000013 --start-position=694/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 694#170713  7:46:11 server id 23306  end_log_pos 759 CRC32 0xfa604449    GTID    last_committed=2    sequence_number=3SET @@SESSION.GTID_NEXT= '5691c701-382a-11e5-bbc4-000c293d13e1:19'/*!*/;# at 759#170713  7:46:11 server id 23306  end_log_pos 869 CRC32 0x35860c26    Query    thread_id=8    exec_time=0    error_code=0use `fandb`/*!*/;SET TIMESTAMP=1499903171/*!*/;SET @@session.pseudo_thread_id=8/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;alter table dept import tablespace/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

假如在源端flush table xxx for export后,只将xxx.{ibd,cfg}拷贝到主库,那么当主库alter table xxx discard tablespace时,从库也会执行discard tablespace
而当主库执行alter table xxx import tablespace时,由于主库有拷贝过来的xxx.{ibd,cfg},所以可以执行成功,而从库没有,会失败

2017-07-10T21:48:13.649264Z 25 [Warning] Slave: InnoDB: ALTER TABLE `fandb`.`dept4` IMPORT TABLESPACE failed with error 44 : 'Tablespace not found' Error_code: 18162017-07-10T21:48:13.649293Z 25 [Warning] Slave: Tablespace is missing for table `fandb`.`dept4`. Error_code: 18122017-07-10T21:48:13.649321Z 25 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 1015

slave会停止

修复方法是,将xxx.{ibd,cfg}拷贝到从库,然后

set sql_log_bin=off;alter table xxx import tablespace;set sql_log_bin=on;(mysql@localhost) [fandb]> set gtid_next='5691c701-382a-11e5-bbc4-000c293d13e1:6';Query OK, 0 rows affected (0.00 sec)(mysql@localhost) [fandb]> begin;Query OK, 0 rows affected (0.00 sec)(mysql@localhost) [fandb]> commit;Query OK, 0 rows affected (0.00 sec)(mysql@localhost) [fandb]> set gtid_next='automatic';(mysql@localhost) [fandb]> start slave sql_thread;