blues001 ·MySQL 的逻辑备份
逻辑备份的最大优点是对于所有存储引擎都可以用同样的方法来备份，是目前中小型系统最常使用最简单的备份方式。本小节将主要介绍 MySQL 的逻辑备份。
1. 什么是逻辑备份
简单的说，逻辑备份就是将数据库中的数据备份为文本文件，备份文件可以查看和编辑。针对中小型系统，逻辑备份要来的简单高效。
2. 常用的逻辑备份场景
MySQL 中，mysqldump 是常用的逻辑备份工具。以下是常用的备份场景：
- 备份所有数据库
shell> mysqldump [options] --all-databases
实际案例：备份所有数据库
[mysql@localhost ~]$ mysqldump -uroot -p --all-databases > /tmp/all_databases.sql
Enter password:
[mysql@localhost ~]$ ls -lrt all_databases.sql
-rw-r--r-- 1 mysql mysql 136106866 Jul 23 17:02 all_databases.sql
- 备份一个或多个数据库
shell> mysqldump [options] --databases db_name ...
实际案例：备份数据库 tempdb
[mysql@localhost ~]$ mysqldump -uroot -p --databases tempdb > /tmp/db_tempdb.sql
Enter password:
[mysql@localhost ~]$ ls -lrt db_tempdb.sql
-rw-r--r-- 1 mysql mysql 19602842 Jul 23 17:17 db_tempdb.sql
实际案例：备份数据库 tempdb 和 test111
[mysql@localhost ~]$ mysqldump -uroot -p --databases tempdb test111 > /tmp/db_tempdb_test111.sql
Enter password:
[mysql@localhost ~]$ ls -lrt db_tempdb_test111.sql
-rw-r--r-- 1 mysql mysql 19604085 Jul 23 17:23 db_tempdb_test111.sql
- 备份一个或多个表
shell> mysqldump [options] db_name [tbl_name ...]
实际案例：备份数据库tempdb的表customer
[mysql@localhost ~]$ mysqldump -uroot -p tempdb customer > /tmp/table_customer.sql
Enter password:
[mysql@localhost ~]$ ls -lrt table_customer.sql
-rw-r--r-- 1 mysql mysql 2512 Jul 23 17:35 table_customer.sql
实际案例：备份数据库 tempdb 的表 customer 和 t1
[mysql@localhost ~]$ mysqldump -uroot -p tempdb customer t1 > /tmp/table_customer_t1.sql
Enter password:
[mysql@localhost ~]$ ls -lrt table_customer_t1.sql
-rw-r--r-- 1 mysql mysql 3141 Jul 23 17:37 table_customer_t1.sql
- 备份表结构-不包含数据
实际案例：备份数据库 tempdbd 的表结构：
[mysql@localhost ~]$ mysqldump -uroot -p --databases tempdb -d > /tmp/structure_tempdb.sql
Enter password:
[mysql@localhost ~]$ ls -lrt structure_db_tempdb.sql
-rw-r--r-- 1 mysql mysql 9987 Jul 23 17:40 structure_db_tempdb.sql
实际案例：备份数据库tempdb表customer的表结构
[mysql@localhost ~]$ mysqldump -uroot -p tempdb customer -d > /tmp/structure_table_customer.sql
Enter password:
[mysql@localhost ~]$ ls -lrt structure_table_customer.sql
-rw-r--r-- 1 mysql mysql 2230 Jul 23 17:48 structure_table_customer.sql
3. 相关参数说明
mysqldump 的选项很多，可以通过 --help 查看帮助：
[mysql@localhost ~]$ mysqldump --help
以下为常用的参数（从 MySQL 官方文档摘录）
| Option Name | Description | Introduced | Deprecated |
|---|---|---|---|
| –all-databases | Dump all tables in all databases | ||
| –databases | Interpret all name arguments as database names | ||
| –default-character-set | Specify default character set | ||
| –events | Dump events from dumped databases | ||
| –force | Continue even if an SQL error occurs during a table dump | ||
| –host | Host on which MySQL server is located | ||
| –ignore-table | Do not dump given table | ||
| –master-data | Write the binary log file name and position to the output | ||
| –no-create-db | Do not write CREATE DATABASE statements | ||
| –no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | ||
| –no-data | Do not dump table contents | ||
| –order-by-primary | Dump each table’s rows sorted by its primary key, or by its first unique index | ||
| –password | Password to use when connecting to server | ||
| –port | TCP/IP port number for connection | ||
| –quick | Retrieve rows for a table from the server a row at a time | ||
| –routines | Dump stored routines (procedures and functions) from dumped databases | ||
| –set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | ||
| –single-transaction | Issue a BEGIN SQL statement before dumping data from server | ||
| –socket | Unix socket file or Windows named pipe to use | ||
| –tables | Override --databases or -B option |
4. 小结
本小节通过常用的逻辑备份案例，介绍了 MySQL 逻辑备份工具 mysqldump 的具体使用方法。逻辑备份适合中小型系统，大型系统请考虑物理备份。
2024 imooc.com All Rights Reserved |