Sharing of the fast recovery solution for Mysql large SQL files

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.

You may also be interested in:
  • Implementation of MySQL5.7 mysqldump backup and recovery
  • Two methods of restoring MySQL data
  • Steps for restoring a single MySQL table
  • MySQL database backup and recovery implementation code
  • MySQL uses mysqldump+binlog to completely restore the deleted database principle analysis
  • Analysis of MySQL data backup and recovery implementation methods
  • How to recover deleted MySQL 8.0.17 root account and password under Windows
  • Shell script to implement mysql scheduled backup, deletion and recovery functions
  • MySQL Binlog Data Recovery: Detailed Explanation of Accidentally Deleting a Database
  • How to restore a database and a table from a MySQL full database backup
  • MySQL quick recovery solution based on time point

<<:  Examples of using Docker and Docker-Compose

>>:  A brief discussion on the principle of Vue's two-way event binding v-model

Recommend

How to start and stop SpringBoot jar program deployment shell script in Linux

Without further ado, let me give you the code. Th...

How to use the Linux seq command

1. Command Introduction The seq (Sequence) comman...

MySQL Installer 8.0.21 installation tutorial with pictures and text

1. Reason I just needed to reinstall MySQL on a n...

Detailed summary of MySQL and connection-related timeouts

MySQL and connection related timeouts Preface: To...

JS uses the reduce() method to process tree structure data

Table of contents definition grammar Examples 1. ...

How to check the version of Kali Linux system

1. Check the kali linux system version Command: c...

How to use map to allow multiple domain names to cross domains in Nginx

Common Nginx configuration allows cross-domain se...

How to deploy MySQL master and slave in Docker

Download image Selecting a MySQL Image docker sea...

Docker uses dockerfile to start node.js application

Writing a Dockerfile Taking the directory automat...

How to implement mysql database backup in golang

background Navicat is the best MySQL visualizatio...

How to modify the group to which a user belongs in Linux

Modify the group to which a user belongs in Linux...

Node.js uses express-fileupload middleware to upload files

Table of contents Initialize the project Writing ...

MySQL 5.7.23 decompression version installation tutorial with pictures and text

Download the MySQL installer Official download ad...

Mysql join table and id auto-increment example analysis

How to write join If you use left join, is the ta...