mysqldump backup skills sharing

Foreword:

mysqldump is a commonly used tool in daily life. When exporting databases, mysqldump is often used. This article will introduce how to use the mysqldump tool and share some backup tips.

1. Introduction to the use of mysqldump

mysqldump is a logical backup tool that comes with the MySQL system, which is mainly used to dump databases. It mainly generates a series of SQL statements, which can be encapsulated into a file, which contains the SQL commands needed to rebuild the database such as CREATE DATABASE, CREATE TABLE, INSERT, etc. When we need to restore these data, we only need to execute this file to restore the corresponding data.

The basic usage syntax of mysqldump is as follows:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Execute mysqldump --help or refer to the official MySQL documentation. We found that there are many configurable parameters for the mysqldump tool. The following briefly describes some of the commonly used parameters.

The above table shows some common mysqldump related options. When you don't understand the function of a parameter, you can execute mysqldump --help to get help. For Boolean parameters, there is generally an opposite parameter. For example, --triggers is enabled by default, and you can use --skip-triggers to disable it.

2. A few backup tips

Although mysqldump is not suitable for large data backup, it is widely used in the field of data export because of its flexibility and convenience, and the ability to customize parameters according to the scene.

Based on my own experience, the author briefly shares a few mysqldump backup tips:

• It is recommended to use the --single-transaction parameter to obtain consistent backups and reduce lock tables.
• Export according to demand, as long as you want the data, try to reduce the export file size.
• If you want to build a slave library, it is recommended to use the --master-data = 2 parameter to record the binlog information of the master library.
• If you want to back up stored procedures, custom functions, and events, please add the -R -E parameters, which are not enabled by default.
• Don't add any parameters you don't understand, just press the default.

Here are a few ways to use mysqldump in different scenarios:

# 备份全部数据库（包含存储过程、自定义函数及事件）
mysqldump -uroot -pxxxxxx --single-transaction -R -E --all-databases > /tmp/all_database.sql

# 要求记录 binlog 位点信息 可用于搭建从库
mysqldump -uroot -pxxxxxx --single-transaction -R -E --all-databases --master-data=2 > /tmp/all_database.sql

# 备份指定数据库
mysqldump -uroot -pxxxxxx --single-transaction -R -E --databases db1 > /tmp/db1.sql
mysqldump -uroot -pxxxxxx --single-transaction -R -E --databases db1 db2 > /tmp/db1_db2.sql

# 备份部分表
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 > /tmp/tb1.sql
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sql

# 导出某个表，数据以单行insert显示
mysqldump -uroot -pxxxxxx --single-transaction --skip-extended-insert db1 tb1 > /tmp/tb1.sql

# 导出单表的部分数据
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 --where=" create_time >= '2021-06-01 00:00:00' " > /tmp/tb1.sql
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 --where='id < 10' > /tmp/tb1.sql

# 排除某些表导出
mysqldump -uroot -pxxxxxx --single-transaction --databases db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > /tmp/db1.sql

# 只导出结构或只导出数据
mysqldump -uroot -pxxxxxx db1 --no-data > /tmp/db1_jiegou.sql
mysqldump -uroot -pxxxxxx db1 --no-create-info > /tmp/db1_data.sql

# 只导出某个库的存储过程及自定义函数
mysqldump -uroot -pxxxxxx -d -t -R db1 > /tmp/db1_routine.sql

# 远程导出 即MySQL服务端不在本地
mysqldump -uroot -pxxxxxx -hxxx.xxx.xx -P3306 --single-transaction --databases db1 > /tmp/db1.sql

summary:

This article mainly introduces how to use the mysqldump tool and some common scenarios. As a practical tool, mysqldump hopes that you can learn it. It is faster than exporting with a graphical interface such as Navicat, and the file size is small.