第四十一讲 表迁移相关方法

匣子
69 阅读4分钟

表迁移

  • 如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert … select 语句即可实现。
  • 为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

mysqldump

导出

使用mysqldump命令将数据导出成一组INSERT语句。把结果输出到临时文件。

mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
  • –single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
  • –add-locks设置为0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
  • –no-create-info的意思是，不需要导出表结构；
  • –set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
  • –result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。
  • 上述命令会生成一条insert语句多个value对（这样执行写入更快），如果想要一条insert对应一个value对可以加上参数-skip-extended-insert

导入

通过下面这条命令，将这些INSERT语句放到db2库里去执行。

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

mysql客户端执行这个命令的流程是这样的：

  • 打开文件，默认以分号为结尾读取一条条的SQL语句；

  • 将SQL语句发送到服务端执行。(执行的是文件中的一条条语句，不是source 这个语句，source并不是一条SQL语句，而是一个客户端命令)

CSV文件

导出

将查询结果导出到服务端本地目录：

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

需要注意如下几点。

  • 语句会讲结果保存在服务端。如果你之星命令的客户端和mysql客户端和服务端不在同一机器上，客户端的临时目录下会产生一个csv文件
  • into outfile指定了文件生成位置（/server_tmp/）,这个位置必须接受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用如下：
    • 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
    • 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
    • 如果设置为NULL，就表示禁止在这个MySQL实例上执行select … into outfile 操作。
  • 这条命令不会帮你覆盖文件，因此你需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。
  • 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

select …into outfile方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义 。mysqldump提供了一个–tab参数，可以同时导出表结构定义文件和csv数据文件。 --tab=securefilepriv（secure_file_priv （secure_file_priv定义的目录下，创建一个t.sql文件保存建表语句，同时创建一个t.txt文件保存CSV数据。）

导入

load data命令将数据导入到目标表中。具体命令如下：

load data infile '/server_tmp/t.csv' into table db2.t;

执行流程：

  • 打开文件，以制作符（\t）作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行行数据读取
  • 启动事务
  • 判断每一行的字段数与目标表表结构是否相同：
    • 不想同，直接报错，事务回滚
    • 相同，则构成一行，调用innodb引擎接口，写入表中
  • 重复上一步骤，直到文件执行完，提交事务

主备同步：

前提： 如果binlog_format=statement，这个load语句记录到binlog里以后，怎么在备库重放呢？ （文件只保存在主库所在的主机上，如果只是把这条语句原文写到binlog中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。）

具体同步流程：

  • 主库执行完成后，将/server_tmp/t.csv文件的内容直接写到binlog文件中。

  • 往binlog文件中写入语句load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE db2.t。

  • 把这个binlog日志传到备库。

  • 备库的apply线程在执行这个事务日志时：

    • 先将binlog中的csv文件内容读出来，写入到本地临时目录/tmp/sql_load_mb-1-0中；
    • 在执行load data语句，往备库的db2.t表中插入跟主库相同的数据

load data命令有两种用法：

  • 不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下；

  • 加上“local”，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限即可。这时候，MySQL客户端会先把本地文件传给服务端，然后执行上述的load data流程。

物理拷贝方法

直接搬文件是不可行的 一个InnoDB表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统是不会识别和接受它们的。

在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。

假设我们现在的目标是在db1库下，复制一个跟表t相同的表r，具体的执行步骤如下：

  • 执行 create table r like t，创建一个相同表结构的空表；
  • 执行alter table r discard tablespace，这时候r.ibd文件会被删除；
  • 执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
  • 在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；
  • 执行unlock tables，这时候t.cfg文件会被删除；
  • 执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

注意：

  • 在第3步执行完flsuh table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁；
  • 在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改t.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，所以你会看到这个import语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的。
avatar
文章
阅读
粉丝