How to copy MySQL table

1.mysqldump

Execution process:

1. Export the data as a sql file.

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

Export the data to a sql file and save it. The meanings of the above parameters are:

1. The function of –single-transaction is that when exporting data, there is no need to lock the table db1.t, but to use the START TRANSACTION WITH CONSISTENT SNAPSHOT method;

2. –add-locks is set to 0, which means that "LOCK TABLES t WRITE;" will not be added to the output file result;

3. –no-create-info means that there is no need to export the table structure;

4. –set-gtid-purged=off means that GTID-related information is not output;

5. –result-file specifies the path of the output file, where client means that the generated file is on the client machine.

2. Execute the file and add it to the table

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

source is not a SQL statement, but a client command. That is, the server executes the sql statements in the file one by one, so the binlog records the specific sql.

Features

1. The generated sql file is saved on the client

2. The default way to save data is multiple record pairs, as shown in the following format

If you want to save only one record as one statement, you can add the parameter –skip-extended-insert.

2. Export CSV file (most flexible)

Execution process

1. Export to CSV file

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

Notice:

1. into outfile specifies the location where the file is generated (/server_tmp/), which must be restricted by the secure_file_priv parameter.

The optional values ​​and functions of the secure_file_priv parameter are:

1) If it is set to empty, it means that there is no limit on the location where the file is generated, which is an unsafe setting;

2) If it is set to a string representing a path, the generated file can only be placed in the specified directory or its subdirectory;

3) If set to NULL, it means that the select ... into outfile operation is prohibited on this MySQL instance.

2. If there is a file with the same name in the same directory, an error will be reported

3. Generally, one record corresponds to one line in a CSV file. However, if a field value contains "newline or tab character", it will also be included in the file and escaped with "\".

2. Import data

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

process:

1. Open the file /server_tmp/t.csv, use the tab character (\t) as the separator between fields and the line feed character (\n) as the separator between records, and read the data;

2. Start the transaction.

3. Determine whether the number of fields in each row is the same as that in table db2.t:

1) If they are different, an error is reported directly and the transaction is rolled back;

2) If they are the same, construct a row, call the InnoDB engine interface, and write it into the table.

4. Repeat step 3 until the entire /server_tmp/t.csv file is read in and commit the transaction.

Features

1. The file is saved on the server

2. Regarding binlog records, the process is as follows:

1) After the main database is executed, the content of the /server_tmp/t.csv file is directly written to the binlog file.

2) Write the statement load data local infile '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t` to the binlog file.

3) Transfer the binlog to the standby database.

4) When the apply thread of the standby database executes this transaction log:

a. First read the contents of the t.csv file in the binlog and write it to the local temporary directory /tmp/SQL_LOAD_MB-1-0;

b. Execute the load data statement again to insert the same data as that in the primary database into the db2.t table of the standby database.

About "local":

1) Without "local", the server-side file is read. This file must be in the directory or subdirectory specified by secure_file_priv;

2) Add "local" to read the client's file, as long as the mysql client has permission to access the file. At this time, the MySQL client will first transfer the local file to the server (operations involved in other sessions), and then execute the above load data process.

3. The above export operation does not export the table structure. Therefore, if you want to export the table structure, you can use mysqldump to export CSV and table structure at the same time.

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

A t.sql file will be created in the directory defined by $secure_file_priv to save the table creation statement, and a t.txt file will be created to save the CSV data.

3. Physical copy (fastest)

Before 5.6, it is not possible to directly copy the .frm and .ibd files to the directory you want to copy them to, because an Innodb table needs to be registered in the data dictionary in addition to these two files. However, this problem can be solved starting from 5.6. Transportable space was introduced in 5.6, and the copy can be achieved by exporting + importing tablespaces.

process

Assume that our current goal is to copy a table r that is identical to table t in the db1 database. The specific execution steps are as follows:

1. Execute create table r like t to create an empty table with the same table structure;

2. Execute alter table r discard tablespace. The r.ibd file will be deleted.

3. Execute flush table t for export. A t.cfg file will be generated in the db1 directory.

4. Execute the following commands in the db1 directory: cp t.cfg r.cfg; cp t.ibd r.ibd. (Note that the MySQL process must have read and write permissions for the two copied files.)

5. Execute unlock tables, and the t.cfg file will be deleted;

6. Execute alter table r import tablespace and use the r.ibd file as the new tablespace of table r. Since the data content of this file is the same as that of t.ibd, table r will have the same data as table t.

Notice:

1. After executing the flsuh table command in step 3, the entire db1.t table is in read-only state until the unlock tables command is executed.

2. When executing import tablespace, the tablespace ID of r.ibd will be modified to make the tablespace ID in the file consistent with that in the data dictionary. And this table space id exists in every data page. Therefore, if it is a very large file (such as TB level), each data page needs to be modified, so you will see that the execution of this import statement takes some time. Of course, compared to the logical import method, the import statement takes very little time.

Limitations

1. The entire table must be copied, not conditional copy

2. Need to copy data to the server, cannot be used when the user cannot log in to the database host

3. Since it is implemented by copying physical files, it can only be used when both the source table and the target table use the InnoDB engine.

Summarize

1. The first two are logical backups, which means they can be used across engines, but the last one cannot.

2. The first two can be conditionally copied, but the last one cannot

3. The second function is the most flexible, but it is time-consuming when the cluster receives data from the library (the CSV file data needs to be copied to a local temporary file first). The last one has the highest execution efficiency, but it cannot cross engines and can only perform full copy.

The above is the details of how to copy MySQL tables. For more information about MySQL copy tables, please pay attention to other related articles on 123WORDPRESS.COM!