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
Without further ado, let me give you the code. Th...
1. Command Introduction The seq (Sequence) comman...
1. Reason I just needed to reinstall MySQL on a n...
MySQL and connection related timeouts Preface: To...
Table of contents definition grammar Examples 1. ...
1. Check the kali linux system version Command: c...
Common Nginx configuration allows cross-domain se...
Download image Selecting a MySQL Image docker sea...
Writing a Dockerfile Taking the directory automat...
background Navicat is the best MySQL visualizatio...
Modify the group to which a user belongs in Linux...
Table of contents Initialize the project Writing ...
Download the MySQL installer Official download ad...
Today, when I logged into the company's inter...
How to write join If you use left join, is the ta...