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 ...
[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
[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
| 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 的具体使用方法。逻辑备份适合中小型系统，大型系统请考虑物理备份。
admin ·
