mysql之 mysqldump 备份恢复详解-阿里云开发者社区

开发者社区> 技术小美> 正文

mysql之 mysqldump 备份恢复详解

简介:
+关注继续查看

   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
  
  
 

本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/6935358.html  ,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9975 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10879 0
mysqldump备份所有数据库,恢复单个库的场景预演
场景:创建两个数据库,每个数据库创建几张表。导出全数据库的备份,导入一个数据库的方式: ---------------------------------------数据的demo-------------------------------------------------- ...
865 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13737 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11875 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7316 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
4447 0
+关注
6906
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载