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

Wednesday, August 22, 2018

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
  1. 导入:
-- 创建数据库
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
  1. 索引与数据文件:
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;
  1. 第二个方案是,如果记录了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';
  1. 停服
  2. 删除所有数据库,包括MySQL数据库下的表空间文件:.idb,ibdata,ib_log
  3. 删除所有的InnoDB表的.frm文件
  4. 配置新的表空间,例如innodb_file_per_table = on
  5. 重启服务,导入第一步导出的文件

(完)

MySQL

MySQL将输出重定向到文件方法汇总

手动修改AUTO_INCREMENT