Preface In the process of using MySQL database, it is often necessary to back up and restore the database. The simplest and most convenient method is to complete the backup and recovery by exporting and importing SQL data files. However, as the project grows, the amount of data becomes larger and larger, and each recovery becomes a headache. When I recently downloaded a 5GB database from a project and restored it locally, it took 40-50 minutes. Thinking about the future data expansion, the amount of data will increase, and the recovery cost will increase, so I looked up some information and found that you can improve your recovery efficiency through the following settings. 1. Change backup parameters First of all, we need to improve our recovery efficiency by changing the parameters when backing up the database. mysqldump --extended-insert The --extended-insert parameter of mysqldump indicates batch insert, which will merge multiple insert statements into one statement, and the backup import efficiency is 3-4 times different from that without --extended-insert turned on. The sql file data exported using --extended-insert=false is like this, with one insert statement per line, and the execution efficiency is very low The table exported with --extended-insert=true is as shown in the figure below. A very long insert statement will be inserted in batches. 2. Adjust MYSQL quick insert parameters If your database storage engine is MYISAM, you can set this parameter to 512M or 256M. MyISAM will use a special tree cache to make faster batch insertions. Related documentation: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size The default value is 8M = 8388608byte Check the insert buffer size SHOW VARIABLES LIKE '%bulk%'; Set insert buffer size (globally) SET GLOBAL bulk_insert_buffer_size =1024*1024*512; Set the insert buffer size (session) SET bulk_insert_buffer_size =1024*1024*256; If you need to set the value to be retained when Mysql is restarted, you need to add this configuration to my.cnf [mysqld] bulk_insert_buffer_size = 256M 3. Close the inspection item For the Innodb engine, we can turn off some system checks to achieve a faster insertion solution. //Turn off autocommit SET autocommit=0; // Turn off unique checking set unique_checks = 0; //Turn off foreign key checks SET foreign_key_checks=0; // Enable the --extended-insert parameter when backing up Related documents about Innodb bulk data loading: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html 4. Practice After doing the above optimization, your MySQL recovery efficiency will instantly improve to a higher level. Before doing the above parameter optimization, it takes 40 minutes to restore the database each time. After setting, it only takes about 16 minutes. My database file capacity is about 5GB. That’s all for now. If you have better solutions and suggestions for Tiguan, I hope you can discuss them with me. Happy Coding. Summarize The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
<<: Examples of using Docker and Docker-Compose
>>: A brief discussion on the principle of Vue's two-way event binding v-model
Table of contents 1. Container lifecycle manageme...
Statement 1: <link rel="shortcut icon"...
MQTT Protocol MQTT (Message Queuing Telemetry Tra...
As more and more projects are deployed, more and ...
Preface Recently I started using robot framework ...
What is HTML? To put it simply: HTML is used to m...
Custom tags can be used freely in XML files and HT...
This article example shares the specific code of ...
SQL Left Join, Right Join, Inner Join, and Natura...
Table of contents Overview first step Step 2 Why ...
Preface Histogram is a basic statistical informat...
Table of contents Preface What is Hot Change Coco...
transform and translate Transform refers to trans...
Use HSSFWorkbook in Apache.POI to export to Excel...
When using MySQL, dates are generally stored in f...