mysql数据表复制,mysql怎么复制表的结构和数据

mysql数据表复制,mysql怎么复制表的结构和数据,MySQL 复制表的方法

本文主要介绍用MySQL复制表格的方法,帮助你更好的理解和学习使用MySQL。感兴趣的朋友可以了解一下。

目录

1、mysqldump执行过程:

特性

2.导出CSV文件(最灵活的)执行过程

特性

3.物理拷贝(最快)过程

限制

摘要

1、mysqldump

执行流程:

1.将数据导出到sql文件。

MySQL dump-h $ host-P $ port-u $ user-add-locks=0-no-create-info-single-transaction-set-gtid-purged=OFF db1t-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文件(最灵活)

执行过程

I .导出到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文件中的一行,但如果字段值中有“换行符,制表符”,它也将包含在文件中,并且将使用“”对其进行转义。

第二,导入数据

将文件/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 _ m b-1-0;

B.再次执行load data语句,将与主数据库相同的数据插入到备份数据库的db2.t表中。

关于“本地”:

1)没有“本地”,就是读取服务器的文件。该文件必须位于secure_file_priv指定的目录或子目录中;

2)添加“local”读取客户端的文件,只要mysql客户端有访问这个文件的权限。此时,MySQL客户端会先将本地文件发送到服务器(其他会话涉及的操作),然后执行上述加载数据过程。

3.上面的导出操作不会导出表结构,所以如果导出表结构,可以使用mysqldump同时导出CSV和表结构。

MySQL dump-h $ host-P $ port-u $ user-single-transaction-set-gtid-purged=OFF db1t-where= a900 -tab=$ secure _ file _ priv

在$secure_file_priv定义的目录下,会创建一个t.sql文件来保存建表语句,还会创建一个t.txt文件来保存CSV数据。

3.物理拷贝(最快)

5.6之前,无法复制。frm和。ibd文件直接复制到要复制的目录中,因为除了这两个文件之外,还需要在数据字典中注册一个Innodb表。但是从5.6开始可以解决这个问题,其中引入了可移动空间,通过导出和导入表空间可以实现复制。

过程

假设我们现在的目标是在db1库下复制一个与表T相同的表R。具体执行步骤如下:

1.执行create table r like t创建一个具有相同表结构的空表;

2.执行alter table r discard tablespace,然后r.ibd文件将被删除;

3.为导出执行flush table t。此时db1目录下会生成一个t.cfg文件;

4.执行db1目录下的cpt.cfgrcfgCP t . IBD r . IBD;这两个命令(这里需要注意的是,MySQL进程要对复制得到的两个文件有读写权限);

5.执行解锁表,此时t.cfg文件将被删除;

6.执行alter table r import tablespace,将这个r.ibd文件作为表R的新表空间,因为这个文件的数据内容与t.ibd相同,所以表R中有与表t相同的数据。

注意:

1.步骤3执行flsuh table命令后,db1.t整个表处于只读状态,直到执行unlock tables命令才释放读锁;

2.当执行导入表空间时,将修改r.ibd的表空间id,以使文件中的表空间id与数据字典中的表空间id一致。并且这个表空间id存在于每个数据页中。所以如果是非常大的文件(比如TB级别),每个数据页都需要修改,所以你会看到这个import语句的执行需要一些时间。当然,与逻辑导入方法相比,导入语句需要的时间非常短。

限制

1.必须是全表复制,不能是条件复制。

2.数据需要复制到服务器,在用户无法登录数据库主机的情况下无法使用。

3.因为是通过复制物理文件实现的,所以源表和目标表只有在使用InnoDB引擎时才能使用。

摘要

1.前两个是逻辑备份,也就是说,它们可以跨引擎使用,但最后一个不能。

2.前两个可以有条件复制,后一个不行。

3.第二个函数是最灵活的,但是集群从库中接收的时候需要时间(需要先把CSV文件数据复制到本地临时文件)。最后一个执行效率最高,但是不能跨引擎,只能全文复制。

以上是MySQL复制表格方法的详细内容。更多关于MySQL复制表的信息,请关注我们的其他相关文章!

mysql数据表复制,mysql怎么复制表的结构和数据