MySQL集群方案下的数据表复制与删除

数据表复制

在不考虑实时写入数据的情况下，数据表复制比较简单，方案有很多。

1. 客户端工具

使用客户端工具可以很方便的完成这种需求，例如全平台支持的navicat、mysql workbench。建立好源、目标数据库连接，选择数据表传输就行了，还能有比较完善的日志。

这里需要注意一下，mysql workbench最新版本的貌似不兼容低版本的MySQL server，例如MySQL5.1。使用低版本的应该可以，没有测试。这是MySQL Bugs上提到的，可以看下

2. mysqldump

1. 导出：

mysqldump -uroot -p -h127.0.0.1 -P3306 \

--default-character-set=utf8 --hex-blob --skip-tz-utc --single-transaction \

--database test --table `cat /tmp/tables.txt` \

--log-error=/tmp/error.log > /tmp/b.sql

2. 导入：

-- 创建数据库
show create database test;
CREATE DATABASE test;
    	
-- 导入
source /tmp/b.sql or mysql -uroot -p123456 import_test < /tmp/a.sql

或者导入导出命令结合在一起：

mysqldump -uroot -p -h127.0.0.1 -P3306 \

--default-character-set=utf8 --hex-blob --skip-tz-utc --single-transaction \

--database test --table `cat /tmp/tables.txt` \

--log-error=/tmp/error.log | mysql -utest -p -h127.0.0.1 import_test

3. 复制文件

1. 表结构文件:

scp /var/lib/mysql/test/lqjx*.frm user@ip:/var/lib/mysql/test

2. 索引与数据文件:

scp /var/lib/mysql/ibdfile1 user@ip:/var/lib/mysql/

问题：这里需要考虑表空间问题，以及文件的大小情况，如果文件过大，复制导致的io会对系统产生影响。

4. 搭建主从

线上从库做主库，将目标机器做从库，做一个同步。 这个方案是我自己想的，不确定具有可操作性。

迁移结论：

对于普通的需求，客户端工具可以作为第一选择，操作方便，日志明确，比较稳定。 如果需要用命令导出的话，文件过大不利于传输和导入。

数据表删除

在集群环境下的drop操作，主要影响的点有两个：

1. 主从延迟
2. 服务器io

主从延迟

主从延迟是因为MySQL的单线程复制导致的，跟从库性能关系不大。根据这点我想到两种方案：

1. 主从复制是通过binlog进行同步的，想办法让我的drop table语句不记录binlog就行了。可以通过下面这种方案实施，思路是通过一个连接，在这个连接中执行语句不记录binlog，完成需要的操作后再恢复。注意，这种方案需要在每台机器上从进行相同的操作，否则会出现不同步的情况。

-- 设置当前session下的语句不记录binlog
set sql_bin_log = 0;

-- 删除数据表
drop table t1;

-- 恢复记录
set sql_bin_log = 1;

2. 第二个方案是，如果记录了binlog，设置从库不同步指定的sql就行了。配置主从的时候会设置同步binlog的position，是否可以通过设置跳过耗时的position来绕过同步该sql呢？或者我们可以设置一下，不同步具有某些关键字的sql。当然这两种方案都是凭空想象的，并没有进行实际的操作。

服务器io

其实在上面执行drop table我们就需要把io问题的解决方案结合在一起操作，如果MySQL启用了innodb_file_per_table表空间选项，那么你可以尝试搜索msyql如何删除大表关键字，网上有很多相关的教程，方案原理很清晰，操作也不复杂。原理如下：

对于InnoDB表来说，如果设置了每个表一个表空间，也就是每个表会对应两个文件，分别是.frm，和.ibd。drop table会先删除frm也就是表结构记录，然后删除数据文件即ibd。而后面的操作是性能瓶颈，那么我们可以通过设置ibd的硬链接来增加引用计数，这样的话drop table就会非常快，因为只会将引用计数减一，不会删除实际文件。这样其实也解决了主从延迟的问题。

因为MyISAM表默认数据就是单独存放的，即放在单独的.MYD文件内，也可以用上面的方法。

而后续的实际删除操作，我们可以通过truncate来逐步删除，减少对系统io的影响。

but，如果没有开启innodb_file_per_table，所有的表共用系统表空间，那么drop table操作，空间是不会被回收的，所以对系统io和主从延迟的影响不大，语句会非常快的执行完成。如果需要回收空间要按以下步骤操作：

1. 使用mysqldump导出所有的InnoDB表，不要忘了MySQL数据库下的表。可以用下面的sql查询test数据库的InnoDB表：

select table_name from information_schema.tables where table_schema='test' and engine='innodb';

2. 停服
3. 删除所有数据库，包括MySQL数据库下的表空间文件：.idb，ibdata，ib_log
4. 删除所有的InnoDB表的.frm文件
5. 配置新的表空间，例如innodb_file_per_table = on
6. 重启服务，导入第一步导出的文件

（完）