作者：孟新 　　

　　

　　资料来源：https://www.cnblogs.com/mengxinJ/p/14387096.html 　　

　　

　　

1、mysqldump

　　

　　

　　

执行过程：

　　

　　

　　

一、将数据导出为 sql 文件。

　　

　　

　　 Mysql dump-h $ host-P $ port-u $ user-add-locks=0-no-create-info-single-transaction-set-gtid-purged=OFF d B1-where=' a900 '-result-file=/client _ tmp/t . SQL将数据导出为SQL文件并保存。上述参数的含义是： 　　

　　

　　1.——single-transaction的作用是在导出数据时，使用一致快照启动事务的方法，而不是给表db1.t添加表锁； 　　

　　

　　2.-add-locks设置为0，这意味着“锁表不写”不会添加到输出文件结果中。 　　

　　

　　3.No-No-create-info表示不需要导出表结构； 　　

　　

　　4.Set-GTID-Purged=off表示不输出与GTID相关的信息； 　　

　　

　　5.-result-file指定输出文件的路径，其中client表示生成的文件位于客户端计算机上。 　　

　　

　　

二、执行文件，添加到表中

　　

　　

　　 MySQL-h 127 . 0 . 0 . 1-p 13000-u root DB2-e ' source/client _ tmp/t . SQL ' source不是SQL语句，而是客户端命令。也就是说，服务器端专门执行文件中的sql语句，所以binlog 记录的都是具体的 sql。 　　

　　

　　

特点

　　

　　

　　

1、生成的 sql 文件保存在客户端

　　

　　

　　 2.保存数据的默认方式是多记录对，如以下格式所示 　　

　　

　　 　　

　　

　　如果您只想为一条语句保存一条记录，可以添加参数skip-skip-extended-insert。 　　

　　

　　

2、导出 CSV 文件（最灵活）

　　

　　

　　

执行过程

　　

　　

　　

一、导出为 CSV 文件

　　

　　

　　 select * from db1 . t where a900 into outfile '/server _ tmp/t . CSV '；

注意：

　　

　　

　　1，into outfile指定文件生成位置(/server_tmp/)，该位置必须受参数secure_file_priv的限制。 　　

　　

　　参数secure_file_priv的可选值和功能如下： 　　

　　

　　1)如果设置为空，说明文件生成的位置不受限制，这是一个不安全的设置； 　　

　　

　　2)如果设置为表示路径的字符串，则要求生成的文件只能放在这个指定的目录或其子目录下； 　　

　　

　　3)如果设置为NULL，则意味着在这个MySQL实例上禁止select … into outfile操作。 　　

　　

　　2.如果同一目录中存在同名文件，将会报告错误。 　　

　　

　　3.一般来说，一条记录对应于CSV文件中的一行，但如果字段值中有'换行，制表符'，它也将包含在文件中，并且' \ '将用于对其进行转义。 　　

　　

　　

二、导入数据

　　

　　

　　将file '/server_tmp/t.csv '中的数据加载到表db2.t中；流程： 　　

　　

　　1.打开文件/server_tmp/t.csv，使用tab (\t)作为字段间的分隔符，使用newline (\n)作为记录间的分隔符，读取数据； 　　

　　

　　2.开始交易。 　　

　　

　　3.确定每行中的字段数是否与表db2.t中的字段数相同： 　　

　　

　　1)如果不同，则直接报错并回滚交易； 　　

　　

　　2)如果相同，构造一行，调用InnoDB引擎接口，写入表中。 　　

　　

　　4.重复步骤3，直到读入/server_tmp/t.csv的整个文件并提交事务。 　　

　　

　　

特点

　　

　　

　　

1、文件保存在服务器端

　　

　　

　　 2.binlog的记录，过程如下： 　　

　　

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

　　

　　2)将语句LOAD DATA LOCAL INFILE '/TMP/SQL _ LOAD _ m b-1-0 '写入表` DB2 `. t `并写入binlog文件。 　　

　　

　　3)将此binlog日志发送到备份库。 　　

　　

　　4)当备份库的应用线程执行该事务日志时： 　　

　　

　　A.第一 　　

binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；

　　

b. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据。

　　

　　

关于 "local"：

　　

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

　　

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

　　

3、上面的导出操作并不会导出表结构，所以，如果向导出表结构，可以使用 mysqldump 来同时导出 CSV 和表结构

　　

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv会在$secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。

　　

3、物理拷贝（最快）

在5.6之前，想要直接把.frm和.ibd文件拷贝到要拷贝的目录下是不行的，因为一个Innodb表除了需要这两个文件还需要在数据字典中注册。但是从 5.6 开始可以解决这一问题，在 5.6 引入了可传输空间，可以通过导出 + 导入表空间来实现拷贝

　　

过程

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

　　

1、执行 create table r like t，创建一个相同表结构的空表；

　　

2、执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

　　

3、执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

　　

4、在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；

　　

5、执行 unlock tables，这时候 t.cfg 文件会被删除；

　　

6、执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

　　

　　

注意：

1、在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；

　　

2、在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

　　

局限

1、必须是全表拷贝，不能条件拷贝

　　

2、需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用

　　

3、由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用

　　

总结

1、前两个都是逻辑备份，也就是可以跨引擎使用，最后一个不行

　　

2、前两个可以条件拷贝，最后一个不行

　　

3、第二个功能是最灵活的，但是在集群从库接收时会比较耗时（需要先拷贝 CSV 文件数据到本地临时文件），最后一个执行效率是最高的，但是不能跨引擎，且只能进行全量拷贝。

　　

作者:萌新J

　　

出处:https://www.cnblogs.com/mengxinJ/p/14387096.html