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

Summary and analysis of commonly used Docker commands and examples

Table of contents 1. Container lifecycle manageme...

Record the steps of using mqtt server to realize instant communication in vue

MQTT Protocol MQTT (Message Queuing Telemetry Tra...

Use tomcat to set shared lib to share the same jar

As more and more projects are deployed, more and ...

How to use boost.python to call c++ dynamic library in linux

Preface Recently I started using robot framework ...

XHTML Getting Started Tutorial: What is XHTML?

What is HTML? To put it simply: HTML is used to m...

How to use custom tags in html

Custom tags can be used freely in XML files and HT...

jQuery implements employee management registration page

This article example shares the specific code of ...

Detailed explanation of various join summaries of SQL

SQL Left Join, Right Join, Inner Join, and Natura...

How to implement simple data monitoring with JS

Table of contents Overview first step Step 2 Why ...

MySQL solution for creating horizontal histogram

Preface Histogram is a basic statistical informat...

Comprehensive explanation of CocosCreator hot update

Table of contents Preface What is Hot Change Coco...

Java uses Apache.POI to export HSSFWorkbook to Excel

Use HSSFWorkbook in Apache.POI to export to Excel...