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

以上是“mysql中mysqldump备份恢复的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！