麦子学院 2017-06-10 16:27
mysql之 mysqldump 备份恢复详解
回复:0 查看:3112
mysqldump
程序的实现原理是通过我们给的参数信息加上数据库中的系统表信息来一个表一个表获取数据然后生成
INSERT
语句再写入备份文件中的。这样就出现了一个问题，在系统正常运行过程中，很可能会不断有数据变更的请求正在执行，这样就可能造成在
mysqldump
备份出来的数据不一致。也就是说备份数据很可能不是同一个时间点的数据，而且甚至可能都没办法满足完整性约束。这样的备份集对于有些系统来说可能并没有太大问题，但是对于有些对数据的一致性和完整性要求比较严格系统来说问题就大了，就是一个完全无效的备份集。
对于如此场景，我们该如何做？我们知道，想数据库中的数据一致，那么只有两种情况下可以做到。
第一、同一时刻取出所有数据；
第二、数据库中的数据处于静止状态。
对于第一种情况，大家肯定会想，这可能吗？不管如何，只要有两个以上的表，就算我们如何写程序，都不可能昨晚完全一致的取数时间点啊。是的，我们确实无法通过常规方法让取数的时间点完全一致，但是大家不要忘记，在同一个事务中，数据库是可以做到所读取的数据是处于同一个时间点的。所以，对于事务支持的存储引擎，如 Innodb
或者
BDB
等 ，我们就可以通过控制将整个备份过程控制在同一个事务中，来达到备份数据的一致性和完整性，而且
mysqldump
程序也给我们提供了相关的参数选项来支持该功能，就是通过
“--single-transaction”
选项，可以不影响数据库的任何正常服务。原理是通过快照实现的。
补充：
single-transaction
可以让
mysqldump
的时候不锁表。但是他有
3
个前提：
a
、
innodb
的引擎
b
、不能在执行的同时，有其他
alter table ,drop table,rename table,truncate table
的操作。
c
、隔离级别 必须是
REPEATABLE READ
，很多公司都会修改这个隔离级别的，比如阿里云的
rds
，默认隔离级别是
READ-COMMITTED
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
对于第二种情况我想大家首先想到的肯定是将需要备份的表锁定，只允许读取而不允许写入。是的，我们确实只能这么做。我们只能通过一个折衷的处理方式，让数据库在备份过程中仅提供数据的查询服务，锁定写入的服务，来使数据暂时处于一个一致的不会被修改的状态，等mysqldump
完成备份后再取消写入锁定，重新开始提供完整的服务。
mysqldump
程序自己也提供了相关选项如
“--lock-tables”
和
“--lock-all-tables ”
，在执行之前会锁定表，执行结束后自动释放锁定。这里有一点需要注意的就是，
“--lock-tables ”
并不是一次性将需要
dump
的所有表锁定，而是每次仅仅锁定一个数据库的表，如果你需要
dump
的表分别在多个不同的数据库中，一定要使用
“--lock-all-tables”
才能确保数据的一致完整性。
mysqldump
是
MySQL
用于转存储数据库的客户端程序。转储包含创建表和
/
或装载表的
SQL
语句 ，用来实现轻量级的快速迁移或恢复数据库，是
mysql
数据库实现逻辑备份的一种方式。
mysqldump
不适用于大型数据库备份与恢复，速度慢，不支持并行，其次
SQL
重放将耗用大量的
I/O
。
1
、查看详细
mysqldump
帮助信息
[root@mysql ~]# mysqldump --help
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
2
、
mysqldump
中主要参数介绍
2.1
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with --skip-opt.
-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
说明：缺省情况下以上2
个参数为开启状态，如果
2
个参数未使用的情况下，在转储结果之前会把全部内容载入到内存中，对于较大的数据库转储将严重影响性能。
2.2
--default-character-set=name Set the default character set.
说明：设置导出脚本的字符集，未指定的情况下为UTF8
。
2.3
-d, --no-data No row information.
说明：不输出数据行，仅导出结构
-t, --no-create-info Don't write table creation info.
说明：只导出表数据，不导出表结构
2.4
--triggers Dump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)
说明：触发器默认导出
-R, --routines Dump stored routines (functions and procedures).
说明：存储过程与函数默认不导出
2.5
--single-transaction
说明：创建一致性快照，仅仅针对innodb
引擎
-f, --force Continue even if we get an SQL error.
说明：有错误时，依旧强制dump
2.6
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
说明：在导入恢复的时候，创建表之前，先执行删除表操作。
3
、范例
注意（特别注意这两点，防止误操作，丢失数据）：
--database
会生成建库语句 默认是关闭的，如：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
意思是如果
mysqldump
的数据库不存在就导入恢复时自行创建，存在就不创建。
--add-drop-table
默认是开启的，在导入恢复时，如果该表存在，会先删除再创建 如：
DROP TABLE IF EXISTS `test`;
禁用是参数 --skip-add-drop-table
。
3.1
备份服务器上的所有数据库
shell> mysqldump -uXX -pXX --all-databases --opt --compact --flush-privileges --routines >alldb_$(date +%F).sql
恢复所有数据库
shell>mysql -uXX -pXX<alldb_$(date +%F).sql
同时备份多个数据库
shell> mysqldump -uXX -pXX --database db_1 db_2 --routines >multidb_$(date +%F).sql
同时恢复多个数据库
shell> mysql -uroot -poracle < multidb_$(date +%F).sql
备份单个数据库
shell>mysqldump -uXX -pXX db_name --opt --routines > db_name_$(date +%F).sql
恢复单个数据库
shell>mysql -uXX -pXX db_name < db_name_$(date +%F).sql
3.2
备份数据库的结构，不备份数据
shell> mysqldump -uXX -pXX db_name --no-data --routines >db_name_onlystructure_$(date +%F).sql
备份数据库的数据，不备份结构
shell> mysqldump -uXX -pXX --opt db_name --no-create-info >db_name_onlydata_$(date +%F).sql
3.3
备份数据库上的特定表
shell> mysqldump -uXX -pXX --opt db_name t_name > db.t_name_$(date +%F).sql
恢复数据库上的特定表
shell> mysql -uXX -pXX dbname < db_name_$(date +%F)_$(date +%F).sql
备份指定数据库上的多个指定表
shell> mysqldump -uroot -poracle --database test test02 --routines >multi_t_$(date +%F).sql
恢复指定数据库上的多个指定表
mysql -uroot -poracle < /tmp/multi_t_$(date +%F).sql
备份表上特定的记录
shell> mysqldump -uXX -pXX db_name t_name -w "first_name='NICK'" >db.t_name_row_$(date +%F).sql
3.4
只导出数据库中的存储过程，函数，触发器
shell> mysqldump -uXX -pXX db_name --no-create-db --no-data --no-tablespaces --no-create-info --routines >db_name_$(date +%F).sql
来源：
博客园