Note: In some cases, such as large tables modify the table structure, re-establishing partitions (setting up partitions for existing tables, but not taking effect on historical data), or table backups, we need to transfer the data of the table from one table to another table. This article introduces three ways to transfer table data by MySQL tables;
Method one
Use the following two lines of command to export the data into a SQL and then import it into the target table;
# Exportmysqldump -u root -p --no-create-info --skip-extended-insert -t partition_demo tb_user > tb_user_data.sql # Importmysql -u root -p partition_demo < tb_user_data.sql
in,
partition_demo: database name;
tb_user: table name;
tb_user_data.sql: The exported file name can be preceded by the file path, and the file content is actually an insert statement;
root: database username;
Method 2
If the table data is not large, you can directly use the following row of SQL to insert the data directly into another table. Of course, the following SQL is a full field insertion. If you need to make a mapping, you can select several fields to insert;
insert into tb_user select * from tb_user_source;
Method Three
Canal can be used to transfer in code. The advantage of this method is that it is flexible and controllable. Tables with large data volumes can also be used. In addition, online transfer can be realized without affecting online services. Refer to the following two articles:
Getting started with Canal
Implement MySQL master-slave synchronization using Canal
Summarize
The above three methods are something bloggers can think of, but they are definitely more than these three. If you use the database connection tool, you can also export the data directly and then import it. It is very convenient to operate, but the efficiency is worrying.
This is the end of this article about the three ways to transfer data in MySQL tables. For more related content on MySQL tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!