主从传输表空间的坑

主从传输表空间的坑

主库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=3
SET @@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=0
use `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: 1816
2017-07-10T21:48:13.649293Z 25 [Warning] Slave: Tablespace is missing for table `fandb`.`dept4`. Error_code: 1812
2017-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;