Comprehensive Guide to Using mysqldump for MySQL Backup and Restore

1. Introduction to mysqldump

mysqldump is the logical backup tool that comes bundled with MySQL.

Its backup principle is to connect to the MySQL server via the protocol, query the data that needs to be backed up, and convert the result into INSERT statements; restoring the data simply requires executing those INSERT statements.

2. mysqldump Command Overview

[root@]> mysqldump -help
Usage: mysqldump [OPTIONS] database_name [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

1. Login options:
-u user   : specify user
-h host   : specify host
-p        : prompt for password
-E, --events          : backup event scheduler
-R, --routines        : backup stored procedures and functions

2. Backup options:
--all-databases       : backup all databases
--databases db1 db2   : backup specified databases
--single-transaction  : perform hot backup for transactional engines
--flush-logs          : rotate binary log files
--master-data=2       : 1 – create a new binary file for each database (default); 2 – create a single binary file
--quick               : use when backing up large tables

Option Descriptions

Parameter Name

Abbreviation

Meaning

--host

-h

Server IP address

--port

-P

Server port number

--user

-u

MySQL username

--password

-p

MySQL password

--databases

Specify databases to back up

--all-databases

Back up all databases on the MySQL server

--compact

Compact mode, produces less output

--comments

Add comment information

--complete-insert

Output complete INSERT statements

--lock-tables

Lock all tables before backup

--no-create-db/--no-create-info

Do not generate CREATE DATABASE statements

--force

Continue backup even if errors occur

--default-character-set

Specify default character set

--add-locks

Lock tables during backup

Command Backup Examples

**Export command collection**
# Export the data and structure of the "users" table in the "education" database
mysqldump -u[username] -h[host] -p[password] -P[port] database_name table_name >output_file.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 education users >d:/user.sql

# Export all databases, including system databases (all.sql is saved in the bin folder by default)
mysqldump -uroot -proot --all-databases >all.sql

# Export multiple tables:
mysqldump -uroot -proot --databases test --tables t1 t2 >two.sql

# Export only table structure (add "-d")
mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users >d:/user.sql

# Export only table data (add "-t")
mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users >d:/user.sql

# Export only the structure of the "test" database
mysqldump -uroot -proot --no-data --databases test >s1.sql
# Import:
mysql -uroot -proot -h127.0.0.1 -P3306 test

3. Restore Commands

System Commands

mysql -h[host] -P[PORT] -u[username] -p[password] [database_name] < d:XX.sql
# Example 1:
mysql -uroot -proot -h127.0.0.1 -P3306 education < d:/database.sql
mysql -uroot -proot -h127.0.0.1 -P3306 < d:/all_database.sql
# Example 2:
mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /backup/mysqldump/db_name.db
# Note: If the database does not exist before import, it must be created, and the name must match the one in the .db file.

source Method

mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

If this article was helpful, please like, watch, and share; it greatly encourages me to continue creating quality content. Thank you 🙏🏻. For additional learning material, add my WeChat: DaDangJia-01 .