Sharing of the fast recovery solution for Mysql large SQL files

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.