Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Sharing of Fast recovery Scheme for Mysql large SQL Files

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Preface

In the process of using Mysql database, we often need to back up and restore the database. The most simple and convenient way is to export SQL data files and import SQL data files to complete backup and recovery. However, with the growth of the project, the amount of data is getting larger and larger, and each recovery has become a headache.

When I recently pulled down the 5GB-sized database in the project to recover locally, it took 40-50 minutes to recover. Thinking about the future data expansion, the amount of data is getting larger and larger, and the recovery cost is getting higher and higher, so I looked up some information, and 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 insertion, which merges multiple insert statements into one statement, which is 3-4 times less efficient than the backup without-extended-insert enabled.

The sql file data exported using-- extended-insert=false is like this, one insert statement per line, and the execution efficiency is very low.

Tables exported using-- extended-insert=true, such as the one shown below, have a very long insert statement that is inserted in bulk.

two。 Adjust MYSQL Quick insert parameters

If your database storage engine is a MYISAM parameter, you can set this parameter to 512m or 256m MyISAM will use a special tree cache to make batch inserts faster.

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

View insert buffer size

SHOW VARIABLES LIKE'% bulk%'

Set the insert buffer size (global)

SET GLOBAL bulk_insert_buffer_size = 1024024512

Set the insert buffer size (session)

SET bulk_insert_buffer_size = 1024024256

If you need to set this value when Mysql is restarted, you need to add my.cnf to this configuration.

[mysqld] bulk_insert_buffer_size = 256m

3. Close check item

For the Innodb engine, we can turn off some system checks to achieve faster insertion.

/ / turn off auto-commit SET autocommit=0;// turn off only check set unique_checks = 0 spare set unique_checks / turn off foreign key to check SET foreign_key_checks=0;// backup on-- extended-insert parameter

About Innodb bulk data loading related documents: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html

4. Practice

After the above optimization is done, your Mysql recovery efficiency will be instantly improved by one level. When the above parameters are not optimized, it will take 40 minutes to recover the database. After setting it up, it will only take about 16 minutes. My database file capacity is about 5GB.

Above all, if Tuguan has any better plans and suggestions, I hope you will discuss it together, Happy Coding.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report