Shulou(Shulou.com)06/02 Report--

This article is to share with you about how to achieve data export and import in MYSQL 8, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The data export and import of MySQL actually ranges from MYSQL 5.5 to 5.7. most of the methods adopted are as follows

1 backup method, through mydumper, mysqldump, mysqlpump and other methods to export the data of a table, in the import to the destination table, but this export import in addition to the speed of mydumper is also relatively fast, the speed of other mysqldump is not flattering, especially when importing data is relatively slow, and import data will also cause pressure on replication, resulting in replication delay and other problems.

2 select into file and load data INFILE method, through which the data can be quickly imported and exported.

But the problem of the second way is to use the data flow between the client and the server to add the burden of the server. in the case of a large amount of data, we should consider the method of batch or adjusting group_replication_communication_max_message_size to limit the current. Of course, we should also consider the question of whether the INDEX is built at the same time or after import, and so on.

3 the overall table of unloading and loading, in fact, this thing can be done on MYSQL 5.X, but in fact there is a certain degree of danger, and in MYSQL 8 after the overall data table information is no longer stored separately, FRM file is a thing of the past, so this operation becomes much safer, while the speed of this operation is the fastest to avoid importing data through the server.

Do the following

1 create a table with the same name on the library to be imported on the destination side

Create table salaries (id int)

2 stop inserting table salaries into data on the source side

FLUSH TABLES salaries FOR EXPORT

3 copy the data to the destination database

4 unlock the table on the source side so that the data can be inserted normally

5 import the file to the destination

6 unlock the destination table

Complete

It seems to be more convenient to operate, but there are a few points to pay attention to here, otherwise it may lead to disaster.

1 you must use the FLUSH TABLES FOR EXPORT; command on the source side, not only to prevent writing data, and to brush dirty pages into the data file, but more importantly to prevent misoperation of discard to delete your original table directly, which can not be found at all.

2 the number of fields and settings of the destination table must be the same as those of your original table, and foreign key constraints can be removed, otherwise the import will fail.

Why should emphasize the first point, because currently write how to quickly export MYSQL data of the text, I think basically did not emphasize the attention of discard is to directly clean up the file (for rookies that is not responsible), and the operator to pay attention to misoperation will lose metadata, and there are some people on the Internet asked, I DISCARD form, how to restore (you can see the general).

In addition, someone asked me recently, his company's development of the MYSQL database is often inexplicably deleted, although it is a test library, but also really annoying, and even if the installation of an audit is helpless, because others is misoperation, how can you, here teach everyone a way, even if he has the authority of ROOT, can not delete the library.

The way to do this is to place a file under the data folder where you want to prevent deletion of the library, such as the following

Deletion will report an error directly.

The above is how to achieve data export and import in MYSQL 8. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.