要備份特定表或數(shù)據(jù)庫，可使用mysqldump命令；1. 備份單個數(shù)據(jù)庫：mysqldump -u [username] -p [database_name] > backup_file.sql；2. 備份多個數(shù)據(jù)庫：mysqldump -u [username] -p --databases db1 db2 > multi_backup.sql；3. 備份所有數(shù)據(jù)庫：mysqldump -u [username] -p --all-databases > full_server_backup.sql；4. 備份特定表：mysqldump -u [username] -p [database_name] table1 table2 > tables_backup.sql；5. 通過模式匹配備份表：結(jié)合mysql命令與xargs篩選如log_開頭的表；6. 使用--single-transaction、--routines等選項增強備份一致性與完整性；7. 恢復(fù)備份：mysql -u [username] -p [database_name]

To back up specific tables or databases using mysqldump, you can use command-line options to target exactly what you want to export. mysqldump is a powerful utility that generates SQL scripts containing CREATE and INSERT statements to recreate the data and structure.

Here’s how to use it effectively for different backup scenarios:

1. Back Up a Single Database

To back up an entire database:

mysqldump -u [username] -p [database_name] > backup_file.sql

Example:

mysqldump -u root -p myapp_db > myapp_backup.sql

This exports all tables, views, and routines in myapp_db into myapp_backup.sql.

2. Back Up Multiple Databases

Use the --databases option to back up multiple databases:

mysqldump -u [username] -p --databases db1 db2 db3 > multi_backup.sql

Example:

mysqldump -u root -p --databases sales inventory > business_data.sql

This includes CREATE DATABASE statements in the output, so when restoring, the databases will be created automatically if they don’t exist.

3. Back Up All Databases on the Server

To back up every database (including system databases like mysql, information_schema, etc.):

mysqldump -u [username] -p --all-databases > full_server_backup.sql

Use this with caution—it can produce large files and may include sensitive system data.

4. Back Up Specific Tables from a Database

To dump only certain tables from a database:

mysqldump -u [username] -p [database_name] table1 table2 > tables_backup.sql

Example:

mysqldump -u root -p myapp_db users orders > user_order_backup.sql

This backs up only the users and orders tables from myapp_db.

?? Note: You cannot use wildcards (% or *) directly in table names with standard mysqldump, but you can combine it with shell scripting or use --tables to prevent mysqldump from interpreting the first argument as a database name.

5. Back Up Tables Matching a Pattern (Advanced)

Since mysqldump doesn't support wildcards, you can use a shell script to select tables by name pattern.

Example: Back up all tables starting with "log_" in a database

DB="myapp_db"
USER="root"
PASSWORD="your_password"

mysql -u"$USER" -p"$PASSWORD" -D"$DB" -Bse "SHOW TABLES LIKE 'log_%'" | \
xargs mysqldump -u"$USER" -p"$PASSWORD" "$DB" > log_tables_backup.sql

This uses mysql to list tables matching 'log_%', then pipes the list to mysqldump.

6. Useful Options to Enhance Backups

You can add several options to customize the dump:

• --single-transaction: Good for InnoDB; ensures consistency without locking tables.
• --routines: Includes stored procedures and functions.
• --triggers: Includes triggers (enabled by default).
• --events: Includes event scheduler events.
• --add-drop-table: Adds DROP TABLE before each CREATE TABLE.
• --lock-tables=false: Avoids table locks (useful for mixed storage engines).

Example with options:

mysqldump -u root -p --single-transaction --routines --triggers \
  myapp_db users orders > backup_with_routines.sql

7. Restoring a mysqldump Backup

To restore:

mysql -u [username] -p [database_name] < backup_file.sql

Make sure the database exists (or use --databases/--all-databases for auto-creation).

Summary of Common Commands

Basically, mysqldump is flexible for both full and selective backups. Just specify the database and table names directly, and add options for consistency and completeness. It’s a go-to tool for logical backups in MySQL environments.

以上是如何使用mySqlDump備份特定的表或數(shù)據(jù)庫？的詳細(xì)內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！