Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL how to quickly copy a table", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL how to quickly copy a table" bar!

There are many ways to copy a table.

First, the easiest way:

Create table T2 as select * from T1

Second, logical export:

1. Mysqldump logic export

Mysqldump-h$host-P$port-u$user-- add-locks=0-- no-create-info-- single-transaction-- set-gtid-purged=off test01 T2-- where='c1 > 4'- result-file=/mysql/backup/t2.sql

-- single-transaction

There is no need to lock T2, but to use the start transaction with consistent snapshop method

-- add-locks=0

Indicates that "lock tbales T2 write" is not added in the output file result.

-- no-create-info

There is no need to export the table structure

-set-gtid-purged=off

Do not export gtid related information

-- result-file

Specify the path to the exported file

2. Export csv file

Create T3 table structure

Create table t3 like t2

Export the data that needs to be exported T2

Select * from T2 where C1 > 4 into outfile'/ mysql/backup/t2.csv'

Import the csv file into T3

Load data infile'/ mysql/backup/t2.csv' into table test01.t3

Third, the method of physical copy

1. Create the T3 table structure

Create table t3 like t2

2. T3.ibd will be deleted when alter table T3 discard tablespace; is executed.

3. Execute flush table T2 for export; and a t2.cfg file will be generated in the test01 directory

4. Copy t2.ibdline t2.cfg, pay attention to the permission

Cp t2.cfg t3.cfg

Cp t2.ibd t3.ibd

5. Unlock tables. T2.cfg will be deleted at this time.

6. Execute alter table T3 import tablespace; to make t3.ibd the new tablespace for T3, and the data and T2 are the same.

Summary:

1. For large tables, the physical copy method is the fastest, which is useful in the case of mistakenly deleting tables, but it also has some disadvantages:

Must be a full table copy

Must be able to connect to the server

Both the source and target tables must be innodb storage engines.

2. The mysqldump method can generate insert statements and add filter conditions to copy part of the data, but can not use the more complex conditions such as join.

3 、 select.... Into outfile's approach is the most flexible, but the disadvantage is that you can only export one table at a time.

Thank you for your reading, the above is the content of "MySQL how to quickly copy a table". After the study of this article, I believe you have a deeper understanding of how MySQL can quickly copy a table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.