当前位置: 澳门新濠3559 > 数据库 > 正文

备份和恢复(三),如何从一个mysqldump文件中提取其

时间:2019-12-09 01:05来源:数据库
一、备份分类 按介质分类: 物理备份 指通过拷贝数据库文件方式完成备份,适用于数据库很大,数据重要且需要快速恢复的数据库。 逻辑备份 指通过备份数据库的逻辑结构和数据内

一、备份分类

按介质分类:

  • 物理备份
    指通过拷贝数据库文件方式完成备份,适用于数据库很大,数据重要且需要快速恢复的数据库。
  • 逻辑备份
    指通过备份数据库的逻辑结构和数据内容的方式完成备份,适用于数据库不是很大,或需要对导出文件做一定修改,或重建此库的情况。

优缺点:

  • 物理备份速度快于逻辑备份,因为逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式
  • 物理备份的备份恢复粒度范围是整个数据库或单个文件,对单表是否有恢复能力取决于存储引擎(MyISAM下每个表对应独立文件,可以单独恢复;InnoDB可能使用共享数据文件)
  • 物理备份要求在数据库关闭情况下执行,如果在运行情况下执行,要求备份期间数据库不能修改,逻辑备份需要在数据库运行状态下执行
  • 通常逻辑备份的文件大小比物理备份大
  • 逻辑备份不包含数据库的配置文件和日志文件内容

按状态分类:

  • 在线备份
  • 离线备份

按距离分类:

  • 本地备份
  • 远程备份

按类型分类:

  • 全量备份
    指备份中包含所有数据
  • 增量备份
    指备份中仅包含在某个指定时间段内的变化情况,需要借助二进制日志完成

备份重要性

  • 重要性
    • 备份是DBA的救命稻草
    • 没有备份,就没有复活的机会
    • 备份也可以让数据回档到某一时刻
  • 误区
    • 备份占用太多的资源,也不能促进生产
    • 可以考虑利用备份恢复生产,提高应用的可用性
    • 备份就可以随便找个地方丢,丢了也无所谓

MariaDB/MySQL备份恢复系列:
备份和恢复(一):mysqldump工具用法详述
备份和恢复(二):导入、导出表数据
备份和恢复(三):xtrabackup用法和原理详述

数据库的备份是极其重要的事情。如果没有备份,遇到下列情况就会抓狂:

二、MySQL备份方式

(1)mysqldump

mysqldump -u 用户名 -p 数据库名 数据库表 > 导出的文件名

(2)拷贝物理表生成备份
当前存储引擎下每个表都有自己独立的数据文件时可以使用这种方式。如果当前数据库是运行状态,则需要对此表加上一个只读锁,防止备份期间的修改操作。
对InnoDB存储引擎的表不太支持。

(3)select...into outfile

  • 通过select * into outfile ‘file_name’ from tbl_name生成在服务器上的文件
  • 通过mysqldump命令加--tab参数生成文件

只会生成表数据,不会生成表结构

(4)增量备份
将MySQL实例设置开启log-bin参数,备份增量生成的二进制日志到指定的备份地

(5)Xtrabackup
支持全量和增量备份

备份场景

  • 功能开发环境
    • 配置类型库
  • 测试环境
    • 性能监控相关的数据
  • 生产环境
    • 几乎全备

  UPDATE or DELETE whitout where…

三、MySQL备份演示

(1)物理备份

CREATE TABLE `students_myisam` (
  `sid` int(11) NOT NULL,
  `sname` varchar(64) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `idx_sname` (`sname`),
  KEY `idx_gender` (`gender`),
  KEY `dept_id` (`dept_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> insert into students_myisam values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

开始备份
[root@localhost course]# pwd
/data1/mysql/data/course
[root@localhost course]# ll students_my*
-rw-r----- 1 mysql mysql 8660 Mar  5 11:11 students_myisam.frm
-rw-r----- 1 mysql mysql   60 Mar  5 11:12 students_myisam.MYD
-rw-r----- 1 mysql mysql 5120 Mar  5 11:12 students_myisam.MYI
把这个表相关的三个文件拷贝到另外的数据库实例对应的数据库目录下(记得需要修改文件权限)
[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam.* ./
[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam.*
mysql> select * from students_myisam;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | a     |      1 |       1 |
|   2 | b     |      2 |       2 |
|   3 | c     |      3 |       3 |
+-----+-------+--------+---------+
3 rows in set (0.00 sec)

对于InnoDB表来说,即使设置了innodb_file_per_table=on时,直接拷贝也不行

CREATE TABLE `students_myisam2` (
  `sid` int(11) NOT NULL,
  `sname` varchar(64) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `idx_sname` (`sname`),
  KEY `idx_gender` (`gender`),
  KEY `dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into students_myisam2 values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);                                                     
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

开始备份
[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam2.* ./
[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam2.*
mysql> select * from students_myisam2;
ERROR 1146 (42S02): Table 'cmdb_v2.students_myisam2' doesn't exist

日志报错:
180305 11:23:53 [ERROR] Cannot find or open table cmdb_v2/students_myisam2 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

所以对于InnoDB来说可以通过拷贝整个data目录方式来完成备份和恢复。

(2)Mysqldump
用来生成MySQL的逻辑备份文件,其文件内容就是构成数据库对象和数据内容的可重复执行的SQL语句。

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

options的关键参数:

-h, --host=name  要导出的目标数据库所在主机,默认是localhost
-u, --user=name  链接目标数据库的数据库用户名
-p, --password[=name]  链接目标数据库的数据库密码
-P, --port=#  链接目标数据库的端口

--add-drop-database  在使用--databases或--all-databases参数时在每个create database命令前都加上drop database命令
--add-drop-table  在每个create table命令前加上drop table命令
--default-character-set=name  指定默认的字符集,默认是UTF8
--replace  使用该命令插入数据而不是使用insert命令
--set-charset  将set names default_character_set命令写入到导出备份文件中,默认是开启状态
--dump-slave[=#]  表示从复制的slave从库导出备份,且其中包含了change master 通语句。value参数如果不写或=-1的情况下,则change master to语句写入dump文件中,设置为2则表示也写入dump文件中,只是会注释掉
--master-data[=#]  表示从复制的主库上导出备份。value参数与--dump-slave相同。使用该参数会自动打开lock-all-table参数,除非同时使用--single-transaction参数
-T, --tab=name  表示将备份文件以文本文件的方式生成,并指定存放文件路径,每个表会生成两个文件,一个是.sql文件保存表结构,一个是.txt文件保存表数据信息
-A, --all-databases  导出所有数据库里的所有表
-B, --databases  导出指定的一个或多个数据库
--ignore-table=name  代表导出过程中忽略某个指定表的导出,如果要忽略多个表则这个参数使用多次
-d, --no-data  代表只导出表结构
-R, --routines  代表导出时也要把存储过程和函数也导出来
--triggers  代表导出时也将触发器导出来
-w, --where=name  代表导出符合条件的数据
-x, --lock-all-tables  代表在导出过程中对每个数据库的每个表加上一个只读锁
--no-autocommit  代表对每个表的数据导出内容用set autocommit=0和commit两个语句包裹
--single-transaction  代表将事务隔离级别设置为可重复读并在导出开始执行start transaction开启一个新事务,在dump执行过程中也不会阻止任何读写操作

例子:

导出一个数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 --databases course > backup.sql

导出多个数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 --databases course test > course.sql
[root@localhost ~]# mysqldump -uroot -p -P3306 -B course test > course.sql

导出所有数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 --all-databases > all.sql

仅导出course数据库的数据,不包括表结构
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course > course.sql

仅导出course数据库中的students和students_myisam两个表
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course students students_myisam > students.sql

仅导出course数据库的表结构
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-data course > course.sql

导出course数据库中除了teacher和score两个表的其他表结构和数据
[root@localhost ~]# mysqldump -uroot -p -P3306 --ignore-table=course.teacher --ignore-table=course.score course > course.sql

导出course数据库的表和存储过程和触发器
[root@localhost ~]# mysqldump -uroot -p -P3306 --routines --triggers course > course.sql

导出course数据库中符合条件的数据
[root@localhost ~]# mysqldump -uroot -p -P3306 --where="sid in (1,2)" course students students_myisam > course.sql

远程导出course数据库
[root@localhost ~]# mysqldump -uroot -p -P3306 -h192.168.1.178 cmdb_v2 students_myisam > students.sql

在主库备份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --master-data=2 --single-transctions course > course.sql
(在备份开始之初,在所有表上加一个只读锁(flush table with read lock),当成功获取该锁并开始备份之后,此锁就会立即释放,后续dump过程不会影响其他的读写操作)

在从库备份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --dump-slave --single-transctions test > test.sql

mysqldump

  • 重要命令
    • mysqldump --master-data=2 --single-transaction -A > db-`data+%Y%M%d`.sql
    • 命名规则:dbname-port-日期.sql
  • 重要参数
    • order-by-primary MyISAM>InnoDB
    • opt 默认带的一个参数
    • dump-slave 从库上dump数据
    • set-gtid-purged=OFF|ON|AUTO
  • mysqldump支持where条件导出
    • mysqldump --where='1=1 limit 10000' dbname > dbname_1w.sql

1.备份分类

按照是否能够继续提供服务,将数据库备份类型划分为:

  • 热备份:在线备份,能读能写
  • 温备份:能读不能写
  • 冷备份:离线备份

按照备份数据库对象分类:

  • 物理备份:直接复制数据文件
  • 逻辑备份:将数据导出至文件中,必要时将其还原(也包括备份成sql语句的方式)

按照是否备份整个数据集分为:

  • 完全备份:备份从开始到某段时间的全部数据
  • 差异备份:备份自完全备份以来变化的数据
  • 增量备份:备份自上次增量备份以来变化的数据

分类方式不同,不同分类的备份没有冲突的关系,它们可以任意组合。

  table was DROPPed accidentally…

这里注意,导出时报以下错误:

[root@codis-178 ~]# mysqldump -uroot -p -P3306 -h192.168.1.68 course > course.sql
Enter password: 
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

这是由于mysqldump版本低导致,也就是5.5版本不能导出5.7版本
如何解决?
用5.7或更高版本的mysqldump覆盖或者指定目录运行即可?

使用mysqldump命令导出文本文件,通过指定--tab=dir_name参数来指定文件路径

添加配置
secure-file-priv=/tmp/

[root@localhost ~]# mysqldump -uroot -p -P3306 --tab=/tmp course
[root@localhost ~]# ll /tmp/course.sql
-rw-r--r-- 1 root root 1544 Mar  5 13:28 /tmp/course.sql

还可指定文件格式

  • --fields-teminated-by=str
    指定每个字段值之间的间隔符,默认是tab
  • --fields-enclosed-by=char
    指定每个字段值使用什么字符括起来,默认是没有
  • --fields-optionsally-enclosed-by=char
    指定对每个非数字类型的字段使用什么字符括起来,默认没有
  • --lines-terminated-by=str
    指定行之间的结束符,默认是newline
[root@localhost ~]# mysqldump -uroot -p -P3306 --tab=/tmp course --fields-terminated-by=, --fields-enclosed-by="'" --lines-terminated-by="n" course
Enter password: 
[root@localhost ~]# cat /tmp/course.txt 
'1','math','3'
'2','english','2'
'3','chinese','4'
'4','history','1'
'5','biology','5'

(3)select... into outfile
用来导出表中符合条件的数据到文本文件,不导出表结构

mysql> select * from students_myisam into outfile '/tmp/students_myisam_test.txt' fields terminated by ',' enclosed by "'" lines teerminated by 'rn';
Query OK, 3 rows affected (0.00 sec)
[root@localhost ~]# cat /tmp/students_myisam_test.txt 
'1','a','1','1'
'2','b','2','2'
'3','c','3','3'

mysql> select * from students_myisam where sid in (1,2) into outfile '/tmp/students_myisam_test2.txt' fields terminated by ',' encllosed by "'" lines terminated by 'rn';
Query OK, 2 rows affected (0.01 sec)
[root@localhost ~]# cat /tmp/students_myisam_test2.txt 
'1','a','1','1'
'2','b','2','2'

参数说明:

  • terminated by
    字段以什么字符分隔
  • enclosed by
    字段以什么字符括起来
  • escaped by
    转义字符,默认是反斜杠
  • lines
    每条记录的分隔符,默认是换行符
  • local
    指定从客户主机读文件,没有指定则文件必须在服务器上
  • replace
    新行将代替有相同的唯一值的现有行
  • ignore
    跳过有唯一键的现有行的重复行的输入,不指定时当遇到重复行会报错

select outfile + awk

  • select id,col1 from zst01.tb1 where id < 1000 into outfile '/tmp/zst01_tb1.sql';
  • 备份和恢复(三),如何从一个mysqldump文件中提取其中一个表。update zst01.tb1 set col1='abc' where id <1000;
  • cat /tmp/zst01_tb1.sql|awk '{print "update zst01.tb1 set col1="$2," where id="$1";"}'>roll_zst01_tb1.sql
  • mysql zst01<roll_zst01_tb1.sql
  • 5.5,5.6,5.7版本并且binlog是row格式可以使用binlog2sql命令还原

2.备份内容和备份工具

需要备份的内容:文件、二进制日志、事务日志、配置文件、操作系统上和MySQL相关的配置(如sudo,定时任务)。

物理备份和逻辑备份的优缺点:

  • 物理备份:直接复制数据文件,速度较快。
  • 逻辑备份:将数据导出到文本文件中或其他格式的文件中。有MySQL服务进程参与,相比物理备份而言速度较慢;可能丢失浮点数精度;但可以使用文本工具二次处理;可以跨版本和跨数据库系统进行移植。

备份策略:要考虑安全,也要考虑还原时长

  • 完全备份+增量
  • 完全备份+差异

备份工具:

  • 澳门新濠3559,mysqldump:逻辑备份工具。要求mysql服务在线。MyISAM(温备),InnoDB(热备)
  • mysqlhotcopy:物理备份工具,温备份,实际上是冷备。加锁、flush table并进行cp或scp。即将废弃的工具
  • cp:冷备
  • lvm快照:几乎热备。注意点是:先flush table、lock table、创建快照、释放锁、复制数据。因为要先flush table和lock table,这对于MyISAM来说很简单很容易实现。但对于InnoDB来说,因为事务的原因,锁表后可能还有缓存中的数据在写入文件中,所以应该监控缓存中的数据是真的已经完全写入数据文件中,之后才能进行复制数据。
  • xtrabackup:开源。MyISAM(温备),InnoDB(热备),速度较快。

  INNODB was corrupt…

四、备份的重要概念

(1)锁
在执行mysqldump时,会添加flush tables with read lock(FTWRL),用于备份时获取一致性备份(数据与binlog位点匹配)。
由于FTWRL总共需要持有两把全局MDL锁,并且还需要关闭所有表对象,因此这个命令杀伤力很大,执行命令时容易导致库hang住。

FTWEL主要包括三个步骤:
1.上全局读锁(lock_global_read_lock)
导致所有更新操作都会被堵塞
2.清理表缓存(close_cached_tables)
关闭表过程中,如果有大量查询导致关闭表等待,那么所有访问该表的查询和更新都需要等待
3.上全局commit锁(make_global_read_lock_block_commit)
会堵塞活跃事务提交

第一个session
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.15 sec)

第二个session
mysql> select * from dept;
+----+------------------+
| id | dept_name        |
+----+------------------+
|  1 | Education        |
|  2 | Computer Science |
|  3 | Mathematics      |
+----+------------------+
3 rows in set (0.00 sec)

mysql> update dept set dept_name="Sport" where id=1;
此时写操作会被阻止,等待超时

第一个session
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysqldump,mysqlpump原理

  1. flush tables; #可以拿到metadata lock
  2. flush tables with read lock; #整个实例只读
  3. set session transaction isolation repeatable read; #可重复读
  4. start transaction /*!40100 with consistent snapshot */; #开启事务快照,/*!NNNNN */的意思是大于NNN版本的才生效中间的语句,否则不生效
  5. show @@global.gtid_executed;
  6. show master status;
  7. dump non-InnoDB tables,首先导出非事务引擎表
  8. unlock tables;
  9. show databases;
  10. show create database if not exists 'dbname';
  11. savepoint sp; #还原点,特殊语法,如果下面东西出错可以回到这个点上
  12. show table status like 'tb_xx';
  13. set session character_set_results='binary'; #以二进制格式存储表结构
  14. show create table 'tb_xx';
  15. set session character_set_results='utf8'; #以utf8格式备份表数据
  16. select /*!40100 SQL_NO_CACHE */ * from tb_xx;
  17. rollback to savepoint sp; #回到断点处,备份下一张表
  18. ...
  19. 最后是:rollback to savepoint sp; RELEASE SAVEPOINT sp #释放断点

3.mysqldump用法详述

官方手册:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump默认会从配置文件中的mysqldump段读取选项,配置文件读取的顺序为:

/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf

  entire datacenter loses power…

  从数据安全的角度来说,服务器磁盘都会做raid,MySQL本身也有主从、drbd等容灾机制,但它们都无法完全取代备份。容灾和高可用能帮我们有效的应对物理的、硬件的、机械的故障,而对我们犯下的逻辑错误却无能为力。每一种逻辑错误发生的概率都极低,但是当多种可能性叠加的时候,小概率事件就放大成很大的安全隐患,这时候备份的必要性就凸显了。那么在众多的MySQL备份方式中,哪一种才是适合我们的呢?

flush tables with read lock与lock table read local的区别

第一个session
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update dept set dept_name="Sport" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第二个session
执行备份操作,当存在--master-data参数时,导出成功
[root@localhost ~]# mysqldump -uroot -p -P3306 --master-data course > course.sql

当普通导出时,发生锁等待情况
[root@localhost ~]# mysqldump -uroot -p -P3306 course > course.sql
Enter password:

在第一个session中查看
mysql> show processlist;

(2)可重复读隔离级别

mydumper

  • 特性
    • 并行,高性能的MySQL逻辑导出导入工具
    • 非常方便管理导出的文件(文件拆分成单表储存,metadata信息)
    • 导入时支持正则表达式
  • 实现过程
    • 主线程flush tables with read lock,施加全局只读锁,以阻止DML语句写入,保证数据的一致性
    • 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中,义工即使点恢复使用
    • N个(线程数可以指定,默认是4)dump线程start transaction with consistent snapshot;开启读一致的事务
    • dump non-InnoDB tables,首先导出非事务引擎表
    • 主线程unlock tables非事务引擎备份完后,释放全局只读锁
    • dump InnoDB tables;基于事务导出InnoDB表 #这个地方之前也有一个savepoint sp,和mysqldump一样
    • 事务结束

3.1 语法选项

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

 常见的备份方式

  MySQL本身为我们提供了mysqldump、mysqlbinlog远程备份工具,percona也为我们提供了强大的Xtrabackup,加上开源的mydumper,还有基于主从同步的延迟备份、从库冷备等方式,以及基于文件系统快照的备份,其实选择已经多到眼花缭乱。而备份本身是为了恢复,所以能够让我们在出现故障后迅速、准确恢复的备份方式,就是最适合我们的,当然,同时能够省钱、省事,那就非常完美。下面就我理解的几种备份工具进行一些比较,探讨下它们各自的适用场景。

start transaction和start transaction with consistent snapshot语句的区别

  • start transaction
    是第一条语句的执行时间点,就是事务开始的时间点,第一条select语句建立一致性读的snapshot;
  • start transaction with consistent snapshot
    是立即建立事务的一致性读snapshot,同时开启事务;
第一个session
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)

第二个session
mysql> select * from A;
+------+-------+
| sid  | score |
+------+-------+
|    8 |    94 |
+------+-------+
1 row in set (0.01 sec)

第一个session
mysql> start transaction;
Query OK, 0 rows affected (0.04 sec)

第二个session
mysql> insert into A value(9,87);
Query OK, 1 row affected (0.07 sec)

第一个session
mysql> select * from A;
+------+-------+
| sid  | score |
+------+-------+
|    8 |    94 |
|    9 |    87 |
+------+-------+
2 rows in set (0.00 sec)

可以看到session2修改后的记录

第一个session
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

第二个session
mysql> insert into A value(10,76);
Query OK, 1 row affected (0.08 sec)

第一个session
mysql> select * from A;
+------+-------+
| sid  | score |
+------+-------+
|    8 |    94 |
|    9 |    87 |
+------+-------+
2 rows in set (0.00 sec)

不可以看到session2修改后的记录,需要提交

说明:
start transaction执行后,事务并没有开始,所以insert发生在session1的事务开始之前,所以可以读到修改后的值。
start transaction with consistent snapshot已经开始了事务,所以不能读到。

如何从一个mysqldump文件中提取其中一个表

  • sed -n -e '/CREATE TABLE.*`tb_name`,/UNLOCK_TABLES/p' db_bak.sql > tb_name.sql
  • 还有其他方法吗
  • 可以用权限管理
  • grant all privileges on db.tb1 to 'hetan'@'127.0.0.1' identified by 'hetan';
  • mysql -f -h127.0.0.1 -uhetan -p db<db_bak.sql
  • 这样就只恢复tb1这张表,而不会恢复别的表,因为这个用户只有tb1的权限,恢复时要加-f,强制执行,跳过报错

3.1.1 连接选项

-u, --user=name        指定用户名
-S, --socket=name      指定套接字路径 
-p, --password[=name]  指定密码
-P, --port=#           指定端口
-h, --host=name        指定主机名 

-r, --result-file=name
将导出结果保存到指定的文件中,在Linux中等同于覆盖重定向。在windows中因为回车符rn的原因,使用该选项比重定向更好

  1. mysqldump & mydumper

  mysqldump是最简单的逻辑备份方式。在备份myisam表的时候,如果要得到一致的数据,就需要锁表,简单而粗暴。而在备份innodb表的时候,加上–master-data=1 –single-transaction 选项,在事务开始时刻,记录下binlog pos点,然后利用mvcc来获取一致的数据,由于是一个长事务,在写入和更新量很大的数据库上,将产生非常多的undo,显著影响性能,所以要慎用。

澳门新濠3559 1

  • 优点:简单,可针对单表备份,在全量导出表结构的时候尤其有用。
  • 缺点:简单粗暴,单线程,备份慢而且恢复慢,跨IDC有可能遇到时区问题。
    mydumper是mysqldump的加强版。相比mysqldump:
  • 内置支持压缩,可以节省2-4倍的存储空间。
  • 支持并行备份和恢复,因此速度比mysqldump快很多,但是由于是逻辑备份,仍不是很快。

五、恢复

(1)普通恢复

导入一个备份文件
mysql -uroot -p course < course.sql
或者
进入数据库,并切换到实例下
source course.sql

(2)恢复文本文件

  • 先导入表结构
  • 再导入数据文件
数据文件导入使用mysqlimport或是load data infile
mysqlimport -uroot -p --fields-terminated-by=, --fields-enclosed-by="'" --lines-terminated-by="n" course /tmp/course.txt

use course;
load data infile '/tmp/course.txt' into table students fields terminated by ',' enclosed by "'" lines terminated by 'rn';

(3)全量恢复
将备份文件中的所有数据进行恢复,恢复完成后的数据就是生成备份的那一刻的数据状态。

(4)基于时间点的恢复
将数据库恢复到指定的某个时间点的状态,通常需要依赖二进制日志将指定时间点前的所有数据库操作都重新操作一遍。
步骤:
1.通过全量备份将数据库恢复到上一个全量恢复的时间点
2.利用二进制日志恢复到指定时间点

开启二进制日志

log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=10

测试实验:

mysql> alter table students add tstamp timestamp;
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> flush logs;
Query OK, 0 rows affected (0.30 sec)

mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(9,'Mix',1,2,now()),(10,'Tom',0,1,now());
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(11,'Luis',-1,2,now()),(12,'Sun',0,3,now());
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(13,'Martis',-1,1,now()),(14,'Oifer',1,3,now());
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)

mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname  | gender | dept_id | tstamp              |
+-----+--------+--------+---------+---------------------+
|   1 | abc    | 1      |       1 | 2018-03-05 14:46:41 |
|   2 | Andy   | -1     |       1 | 2018-03-05 14:46:41 |
|   3 | Bob    | -1     |       1 | 2018-03-05 14:46:41 |
|   4 | Ruth   | -1     |       2 | 2018-03-05 14:46:41 |
|   5 | Mike   | -1     |       2 | 2018-03-05 14:46:41 |
|   6 | John   | 0      |       3 | 2018-03-05 14:46:41 |
|   7 | Cindy  | 1      |       3 | 2018-03-05 14:46:41 |
|   8 | Susan  | 1      |       3 | 2018-03-05 14:46:41 |
|   9 | Mix    | 1      |       2 | 2018-03-05 14:50:04 |
|  10 | Tom    | 0      |       1 | 2018-03-05 14:50:04 |
|  11 | Luis   | -1     |       2 | 2018-03-05 14:51:48 |
|  12 | Sun    | 0      |       3 | 2018-03-05 14:51:48 |
|  13 | Martis | -1     |       1 | 2018-03-05 14:52:27 |
|  14 | Oifer  | 1      |       3 | 2018-03-05 14:52:27 |
+-----+--------+--------+---------+---------------------+
14 rows in set (0.00 sec)

mysql> truncate table students;
Query OK, 0 rows affected (0.21 sec)

mysql> select * from students;
Empty set (0.00 sec)

首先恢复students表的全量备份
mysql> source backup.sql;
mysql> select * from students;
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp              |
+-----+-------+--------+---------+---------------------+
|   1 | abc   | 1      |       1 | 2018-03-05 14:46:41 |
|   2 | Andy  | -1     |       1 | 2018-03-05 14:46:41 |
|   3 | Bob   | -1     |       1 | 2018-03-05 14:46:41 |
|   4 | Ruth  | -1     |       2 | 2018-03-05 14:46:41 |
|   5 | Mike  | -1     |       2 | 2018-03-05 14:46:41 |
|   6 | John  | 0      |       3 | 2018-03-05 14:46:41 |
|   7 | Cindy | 1      |       3 | 2018-03-05 14:46:41 |
|   8 | Susan | 1      |       3 | 2018-03-05 14:46:41 |
+-----+-------+--------+---------+---------------------+
8 rows in set (0.01 sec)

恢复某个时间点数据
[root@localhost data]# mysqlbinlog mysql-bin.000002 | mysql -uroot -p
Enter password: 
mysql> select * from students;
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp              |
+-----+-------+--------+---------+---------------------+
|   1 | abc   | 1      |       1 | 2018-03-05 14:46:41 |
|   2 | Andy  | -1     |       1 | 2018-03-05 14:46:41 |
|   3 | Bob   | -1     |       1 | 2018-03-05 14:46:41 |
|   4 | Ruth  | -1     |       2 | 2018-03-05 14:46:41 |
|   5 | Mike  | -1     |       2 | 2018-03-05 14:46:41 |
|   6 | John  | 0      |       3 | 2018-03-05 14:46:41 |
|   7 | Cindy | 1      |       3 | 2018-03-05 14:46:41 |
|   8 | Susan | 1      |       3 | 2018-03-05 14:46:41 |
|   9 | Mix   | 1      |       2 | 2018-03-05 14:50:04 |
|  10 | Tom   | 0      |       1 | 2018-03-05 14:50:04 |
+-----+-------+--------+---------+---------------------+
10 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog mysql-bin.000003 | mysql -uroot -p
Enter password: 
mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname  | gender | dept_id | tstamp              |
+-----+--------+--------+---------+---------------------+
|   1 | abc    | 1      |       1 | 2018-03-05 14:46:41 |
|   2 | Andy   | -1     |       1 | 2018-03-05 14:46:41 |
|   3 | Bob    | -1     |       1 | 2018-03-05 14:46:41 |
|   4 | Ruth   | -1     |       2 | 2018-03-05 14:46:41 |
|   5 | Mike   | -1     |       2 | 2018-03-05 14:46:41 |
|   6 | John   | 0      |       3 | 2018-03-05 14:46:41 |
|   7 | Cindy  | 1      |       3 | 2018-03-05 14:46:41 |
|   8 | Susan  | 1      |       3 | 2018-03-05 14:46:41 |
|   9 | Mix    | 1      |       2 | 2018-03-05 14:50:04 |
|  10 | Tom    | 0      |       1 | 2018-03-05 14:50:04 |
|  11 | Luis   | -1     |       2 | 2018-03-05 14:51:48 |
|  12 | Sun    | 0      |       3 | 2018-03-05 14:51:48 |
|  13 | Martis | -1     |       1 | 2018-03-05 14:52:27 |
|  14 | Oifer  | 1      |       3 | 2018-03-05 14:52:27 |
+-----+--------+--------+---------+---------------------+

如果恢复某个日志文件中的一部分内容,可以通过指定--start-datetime或是--stop-datetime参数来确定开始恢复和停止的时间。

mysqlbinlog --start-datetime="2018-02-05 10:23:41" /data1/mysql/data/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --stop-datetime="2018-03-05 15:00:00" /data1/mysql/data/mysql-bin.000001 | mysql -uroot -p

InnoDB Crash Recovery

澳门新濠3559 2

innobase crash recovery

澳门新濠3559 3

crash

澳门新濠3559 4

innodb-plugin fast crash recovery

澳门新濠3559 5

  • LSN :Log Sequence Number
    • 日志的唯一编号
    • 在物理日志文件中存储位置(byte)
    • Log sequence number:当前最新的LSN
    • Log flushed up to :已经写入Redo中的LSN
    • Pages flushed up to:buffer pool中获得脏页上的最小的LSN
    • Last checkpoint at :数据文件中持久化到的LSN
  • show engine innodb statusG
LOG
---
Log sequence number 2565545
Log flushed up to   2565545
Pages flushed up to 2565545
Last checkpoint at  2565536
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
  • 测试redo文件定义是不是够一个小时使用
    • pager grep sequence
    • show engine innodb statusG #lsn1
    • select sleep(60);
    • show engine innodb statusG #lsn2
    • select (lsn2-lsn1)/1024/1024*60 as MB_per_hour;

3.1.2 筛选选项

--all-databases, -A  
指定dump所有数据库。等价于使用--databases选定所有库
--databases, -B  
指定需要dump的库。该选项后的所有内容都被当成数据库名;在输出文件中的每个数据库前会加上建库语句和use语句
--ignore-table=db_name.tbl_name  
导出时忽略指定数据库中的指定表,同样可用于忽略视图,要忽略多个则多次写该选项
-d, --no-data       
不导出表数据,可以用在仅导出表结构的情况。
--events, -E  
导出事件调度器
--routines, -R   
导出存储过程和函数。但不会导出它们的属性值,若要导出它们的属性,可以导出mysql.proc表然后reload
--triggers  
导出触发器,默认已开启
--tables 
覆盖--databases选项,导出指定的表。但这样只能导出一个库中的表。格式为--tables database_name tab_list
--where='where_condition', -w 'where_condition'  
指定筛选条件并导出表中符合筛选的数据,如--where="user='jim'"

  2. 基于文件系统的快照

  基于文件系统的快照,是物理备份的一种。在备份前需要进行一些复杂的设置,在备份开始时刻获得快照并记录下binlog pos点,然后采用类似copy-on-write的方式,把快照进行转储。转储快照本身会消耗一定的IO资源,而且在写入压力较大的实例上,保存被更改数据块的前印象也会消耗IO,最终表现为整体性能的下降。而且服务器还要为copy-on-write快照预留较多的磁盘空间,这本身对资源也是一种浪费。因此这种备份方式我们使用的不多。

澳门新濠3559 6

六、Xtrabackup

Xtrabackup是一个对MySQL做数据备份的工具,支持在线热备份(备份时不影响数据读写)。

特点:

  • 备份过程快、可靠
  • 备份过程不会打断正在执行的事务
  • 能够基于压缩等功能节约磁盘空间和流量
  • 自动实现备份检验
  • 还原速度快
[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
[root@localhost ~]# tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
[root@localhost ~]# cp percona-xtrabackup-2.4.9-Linux-x86_64/bin/* /usr/bin/

全量备份
[root@localhost data1]# mkdir backup
[root@localhost backup]# xtrabackup --backup --target-dir=/data1/backup/ -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1
...
...
...
xtrabackup: Transaction log of lsn (11460068) to (11460077) was copied.
180305 15:14:34 completed OK!

恢复
[root@localhost mysql]# mv data data_bak
[root@localhost mysql]# ls
data_bak
[root@localhost mysql]# mkdir data
[root@localhost mysql]# chown -R mysql:mysql data

首先执行prepare,将所有的数据文件都准备到同一时间点,因为在备份过程中所有数据文件都在不同的时间点,如果直接恢复会导致冲突
[root@localhost mysql]# xtrabackup --prepare --target-dir=/data1/backup/
...
...
...
InnoDB: Shutdown completed; log sequence number 11461479
180305 15:16:48 completed OK!

全量恢复
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/data1/backup/ --datadir=/data1/mysql/data
...
...
...
180305 15:20:17 [01] Copying ./test1/app01.ibd to /data1/mysql/data/test1/app01.ibd
180305 15:20:17 [01]        ...done
180305 15:20:17 completed OK!
[root@localhost mysql]# chown -R mysql:mysql data

增量备份
[root@localhost backup]# mkdir base
[root@localhost backup]# chown -R mysql:mysql base
[root@localhost backup]# xtrabackup --backup --traget-dir=/data1/backup/base -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1

mysql> insert into students values(15,'aa',1,1,now());
Query OK, 1 row affected (0.07 sec)

mysql> insert into students values(16,'bb',1,2,now());
Query OK, 1 row affected (0.09 sec)

[root@localhost ~]# xtrabackup --backup --target-dir=/data1/backup/inc1 --incremental-basedir=/data1/backup/base -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1

mysql> insert into students values(17,'cc',0,3,now());
Query OK, 1 row affected (0.19 sec)

mysql> insert into students values(18,'dd',-1,3,now());
Query OK, 1 row affected (0.22 sec)

[root@localhost ~]# mkdir -p /data1/backup/inc2
[root@localhost ~]# chown -R mysql:mysql /data1/backup/inc2
[root@localhost ~]# xtrabackup --backup --target-dir=/data1/backup/inc2 --incremental-basedir=/data1/backup/inc1 -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1

增量恢复
[root@localhost mysql]# xtrabackup --prepare --apply=log-only --target-dir=/data1/backup/base --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --prepare --apply-log-only --target-dir=/data1/backup/base --incremental-datadir=/data1/backup/inc1 --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --prepare --target-dir=/data1/backup/base --incremental-datadir=/data1/backup/inc2 --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/data1/backup/base/ --datadir=/data1/mysql/data

mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname  | gender | dept_id | tstamp              |
+-----+--------+--------+---------+---------------------+
|   1 | abc    | 1      |       1 | 2018-03-05 14:46:41 |
|   2 | Andy   | -1     |       1 | 2018-03-05 14:46:41 |
|   3 | Bob    | -1     |       1 | 2018-03-05 14:46:41 |
|   4 | Ruth   | -1     |       2 | 2018-03-05 14:46:41 |
|   5 | Mike   | -1     |       2 | 2018-03-05 14:46:41 |
|   6 | John   | 0      |       3 | 2018-03-05 14:46:41 |
|   7 | Cindy  | 1      |       3 | 2018-03-05 14:46:41 |
|   8 | Susan  | 1      |       3 | 2018-03-05 14:46:41 |
|   9 | Mix    | 1      |       2 | 2018-03-05 14:50:04 |
|  10 | Tom    | 0      |       1 | 2018-03-05 14:50:04 |
|  11 | Luis   | -1     |       2 | 2018-03-05 14:51:48 |
|  12 | Sun    | 0      |       3 | 2018-03-05 14:51:48 |
|  13 | Martis | -1     |       1 | 2018-03-05 14:52:27 |
|  14 | Oifer  | 1      |       3 | 2018-03-05 14:52:27 |
|  15 | aa     | 1      |       1 | 2018-03-05 15:34:55 |
|  16 | bb     | 1      |       2 | 2018-03-05 15:35:55 |
|  17 | cc     | 0      |       3 | 2018-03-05 15:42:14 |
|  18 | dd     | -1     |       3 | 2018-03-05 15:42:23 |
+-----+--------+--------+---------+---------------------+
18 rows in set (0.00 sec)

xtrabackup

  • 安装
    • 下载rpm包,yum install 安装

3.1.3 DDL选项

--add-drop-database 
在输出中的create database语句前加上drop database语句先删除数据库
--add-drop-table    
在输出的create table语句前加上drop table语句先删除表,默认是已开启的
--add-drop-trigger  
在输出中的create trigger语句前加上drop trigger语句先删除触发器
-n, --no-create-db   
指定了--databases或者--all-databases选项时默认会加上数据库创建语句,该选项抑制建库语句的输出
-t, --no-create-info 
不在输出中包含建表语句
--replace            
使用replace代替insert语句

  3. Xtrabackup

  这或许是最为广泛的备份方式。percona之所以家喻户晓,Xtrabackup应该功不可没。它实际上是物理备份+逻辑备份的组合。在备份innodb表的时候,它拷贝ibd文件,并一刻不停的监视redo log的变化,append到自己的事务日志文件。在拷贝ibd文件过程中,ibd文件本身可能被写”花”,这都不是问题,因为在拷贝完成后的第一个prepare阶段,Xtrabackup采用类似于innodb崩溃恢复的方法,把数据文件恢复到与日志文件一致的状态,并把未提交的事务回滚。如果同时需要备份myisam表以及innodb表结构等文件,那么就需要用flush tables with lock来获得全局锁,开始拷贝这些不再变化的文件,同时获得binlog位置,拷贝结束后释放锁,也停止对redo log的监视。
它的工作原理如下:

澳门新濠3559 7

  由于mysql中不可避免的含有myisam表,同时innobackup并不备份表结构等文件,因此想要完整的备份mysql实例,就少不了要执行flush tables with read lock,而这个语句会被任何查询(包括select)阻塞,在阻塞过程中,它又反过来阻塞任何查询(包括select)。如果碰巧备份实例上有长查询先于flush tables with read lock执行,数据库就会hang住。而当flush tables with read lock获得全局锁后,虽然查询可以执行,但是仍会阻塞更新,所以,我们希望flush tables with read lock从发起到结束,持续的时间越短越好。

  为了解决这个问题,有两种比较有效的方法:

xtrabackup备份

  • 全备
    • 大于2.3版本innobackupex是xtrabackup软连接
    • innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql.sock -uroot -pmysql /data/backup/
    • xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf --backup S /tmp/mysql.sock -uroot -pmysql --target-dir=/data/backup/
  • 特别注意
    • 备份时要加上配置文件,不加配置文件备份的就是默认的实例,也就是/etc/my.cnf的实例的数据库,所以要加上--defaults-file参数
  • 恢复
    • innobackupex --apply-log /data/backup/2017-06-04_11-45-43/ #指定redo应用完毕,之后再恢复
    • 需要恢复的MySQL需要关闭
    • datadir清空
    • 手工将apply的文件copy过去,或是innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --copy-back /data/backup/2017-06-04_11-45-43/
    • 更改copy过去的权限
    • 启动MySQL

3.1.4 字符集选项

--default-character-set=charset_name 
在导出数据的过程中,指定导出的字符集。很重要,客户端服务端字符集不同导出时可能乱码,默认使用utf8
--set-charset 
在导出结果中加上set names charset_name语句。默认启用。

  1. 尽量不用myisam表。

xtrabackup实现原理

澳门新濠3559 8

  • xtrabackup_log就是复制的redo_log,在备份文件中是xtrabackup_logfile,redo的复制起点就是last checkpoint at的值,就是show engine innodb status中的值
  • xtrabackup_binlog_info的值是show master status产生的
  • xtrabackup_binlog_pos_innodb的值是xtrabackup_logfile中的,也就是binlog最后写回到redo中的binlog文件名和position,也可以理解为事务最后一次提交的位置

3.1.5 复制选项

--apply-slave-statements
--delete-master-logs
--dump-slave[=value]
--include-master-host-port

--master-data[=value]  
该选项主要用来建立一个replication,当值为1时,导出文件中记录change master语句;
当值为2时,change master语句被写成注释语句,默认值为空。
该选项自动忽略--lock-tables,当没有使用--single-transaction时自动启用--lock-all-tables。

  2. Xtrabackup增加了–rsync选项,通过两次rsync来减少持有全局锁的时间。

  优化后的备份过程如下:

澳门新濠3559 9

  • 优点:在线热备,全备+增备+流备,支持限速,支持压缩,支持加密。
  • 缺点:需要获取全局锁,如果遇到长查询,等待时间将不可控,因此要做好监控,必要时杀死长查询或自杀;遇到超大的实例,备份过程较长,redo log太大会影响恢复速度,这种情况下最好采用延迟备份。

xtrabackup增量原理

澳门新濠3559 10

  • xtrabackup_info文件中有个值innodb_to_lsn,在ibd文件中只复制比这个值大的page
  • ibdata1全拷贝

3.1.6 格式化选项

--compact                   
简化输出导出的内容,几乎所有注释都不会输出

--complete-insert, -c       
在insert语句中加上插入的列信息

--create-options            
在导出的建表语句中,加上所有的建表选项

--tab=dir_name, -T dir_name 
将每个表的结构定义和数据分别导出到指定目录下文件名同表名的.sql和txt文件中,其中.txt
文件中的字段分隔符是制表符。要求mysqldump必须和MySQL Server在同一主机,且mysql用
户对指定的目录有写权限,并且连接数据库的用户必须有file权限。且指定要dump的表,不能和
--databases或--all-databases一起使用。它的实质是执行select into outfile。

--fields-terminated-by=name            
指定输出文件中的字段分隔符

--fields-enclosed-by=name              
指定输出文件中的字段值的包围符,如使用引号将字符串包围起来引用

--fields-optionally-enclosed-by=name   
指定输出文件中可选字段引用符

--fields-escaped-by=name               
指定输出文件中的转义符

--lines-terminated-by=name            
指定输出文件中的换行符   

-Q, --quote-names                       
引用表名和列名时使用的标识符,默认使用反引号"`" 

  4. mysqlbinlog 5.6

  上述所有的备份方式,都只能把数据库恢复到备份的某个时间点:mysqldump和mydumper,以及snapshot是备份开始的时间点;Xtrabackup是备份结束的时间点。要想实现point in time的恢复,还必须备份binlog。同时binlog也是实现增备的宝贵资源。

  幸运的是,mysql 5.6为我们提供了远程备份binlog的选项:

  mysqlbinlog --raw --read-from-remote-server --stop-never

  它会伪装成mysql从库,从远程获取binlog然后进行转储。这对线上主库容量不够无法保存较多binlog的场景非常有用。但是,它毕竟不像真正的mysql从库实例,状态监控和同步都需要单独部署。因此个人觉得采用blackhole来备份全量的binlog是更好的选择。笔者曾经实现过一个自动搭建blackhole从库的工具,稍加修改,就可以完美搭建出blackhole从库。一旦同步起来,基本一劳永逸,很少出问题,主从切换的时候跟着切了就行。

3.1.7 性能选项

--delayed-insert  
对于非事务表,在insert时支持delayed功能,但在MySQL5.6.6开始该选项已经废弃
--disable-keys, -K  
在insert语句前后加上禁用和启用索引语句,大量数据插入时该选项很适合。默认开启
--insert-ignore  
使用insert ignore语句替代insert语句
--quick, -q 
快速导出数据,该选项对于导出大表非常好用。默认导出数据时会一次性检索表中所有数据并加入
到内存中,而该选项是每次检索一行并导出一行

  提示:

  • 不要小看binlog的备份。当5.6的多线程复制大规模使用后,从库追赶主库命令点的耗时将被极大缩短,这样我们把每天一次的全量备份改为每3天一次、甚至每周一次的全量备份,和持续的binlog增量备份。遇到故障需要恢复数据的时候,重放3、5天的binlog也是极快的。降低备份频率最直接的好处是,省钱、省事。
  • blackhole对于备份binlog是极好的。一方面可以长久的备份binlog用于恢复数据库,另一方面,在其上配置半同步复制,可以有效防止主库的binlog丢失。

3.1.8 加锁和事务相关选项

--add-locks         
在insert语句前后加上lock tables和unlock tables语句,默认已开启。

--flush-logs, -F 
在开始dump前先flush logs,如果同时使用了--all-databases则依次在每个数据库dump前flush,
如果同时使用了--lock-all-tables,--master-data或者--single-transaction,则仅flush
一次,等价于使用flush tables with read lock锁定所有表,这样可以让dump和flush在完全精
确的同一时刻执行。

--flush-privileges   
在dump完所有数据库后在数据文件的结尾加上flush privileges语句,在导出的数据涉及mysql库或
者依赖于mysql库时都应该使用该选项

--lock-all-tables, -x  
为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁,一锁锁永久且锁所有。
该选项自动禁用--lock-tables和--single-transaction选项

--lock-tables, -l  
在dump每个数据库前依次对该数据库中所有表加read local锁(多次加锁,lock tables...read local),
这样就允许对myisam表进行并发插入。对于innodb存储引擎,使用--single-transaction比
--lock-tables更好,因为它不完全锁定表。因为该选项是分别对数据库加锁的,所以只能保证每个数
据库的一致性而不能保证所有数据库之间的一致性。该选项主要用于myisam表,如果既有myisam又有
innodb,则只能使用--lock-tables,或者分开dump更好

--single-transaction 
该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给
服务端。该选项对于导出事务表如innodb表很有用,因为它在发出start transaction后能保证导
出的数据库的一致性时而不阻塞任何的程序。该选项只能保证innodb表的一致性,无法保证myisam表
的一致性。在使用该选项的时候,一定要保证没有任何其他连接在使用ALTER TABLE,CREATE TABLE,
DROP TABLE,RENAME TABLE,TRUNCATE TABLE语句,因为一致性读无法隔离这些语句。
--single-transaction选项和--lock-tables选项互斥,因为lock tables会隐式提交事务。
要导出大的innodb表,该选项结合--quick选项更好

--no-autocommit  
在insert语句前后加上SET autocommit = 0,并在需要提交的地方加上COMMIT语句

--order-by-primary  
如果表中存在主键或者唯一索引,则排序后按序导出。对于myisam表迁移到innobd表时比较有用,但是
这样会让事务变得很长很慢

 总结

  备份方式各有千秋,而对我们来说,面对数千实例,选择合适的备份工具来实现统一配置、统一规划,构建智能调度的备份云平台才是王道。毕竟,多种备份方式共存的运维成本是不容忽视的。

  从使用经验来看,用Xtrabackup全备数据,用blackhole增备binlog,并定期对备份数据的有效性进行验证,是当下比较好的选择。

UPDATE or DELETE whitout where table was DROPPed accidentally INNODB was corrupt...

3.2 mysqldump导出示例

3.2.1 简单备份示例

创建示例数据库和表。

# 创建第一个数据库
CREATE DATABASE backuptest;
USE backuptest;
# 创建innodb表
CREATE TABLE `student` (
    `studentid` INT (11) NOT NULL,
    `sname` CHAR (30) NOT NULL,
    `gender` enum ('male', 'female') DEFAULT NULL,
    `birth` date DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE = INNODB DEFAULT CHARSET = latin1

INSERT INTO student
VALUES
    (1,'malongshuai','male',curdate()),
    (2,'gaoxiaofang','female',date_add(curdate(), INTERVAL - 2 YEAR)),
    (3,'longshuai','male',date_add(curdate(), INTERVAL - 5 YEAR)),
    (4,'meishaonv','female',date_add(curdate(), INTERVAL - 3 YEAR)),
    (5,'tun'er','female',date_add(curdate(), INTERVAL - 4 YEAR));

# 创建myisam表,并且字符集设置为UTF8
CREATE TABLE teacher (
    tid INT NOT NULL PRIMARY KEY,
    tname VARCHAR (30),
    gender enum ('male', 'female'),
    classname CHAR (10)
) ENGINE = myisam DEFAULT charset = utf8;

INSERT INTO teacher
VALUES
    (1,'wugui','male','计算机网络'),
    (2,'woniu','female','C语言'),
    (3,'xiaowowo','female','oracle');

# 创建第二个数据库
CREATE DATABASE backuptest1;
USE backuptest1;
create table student1 as select * from backuptest.student;
create table teacher1 charset=utf8 engine=myisam as select * from backuptest.teacher;

mysqldump三种备份方式如下:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

备份单个库,此处备份mysql库。重定向符号可以在Linux中等价于mysqldump的-r选项,所以下面的语句是等价的。

[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock mysql >/tmp/mysql.bak
[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -r /tmp/mysql1.bak mysql

查看备份文件,会发现dump单个库的时候不会在输出文件中记录建库语句和use语句

[root@xuexi ~]# less /tmp/mysql.bak 
-- MySQL dump 10.13  Distrib 5.6.35, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.6.35-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
......

备份多个库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest backuptest1 >/tmp/mutil_db.bak

备份所有库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --all-databases >/tmp/all_db.bak

备份多个库或所有库时,会在dump文件中加入建库语句和use语句。实际上,只要使用--databases选项,即使只备份一个库也会加上建库语句和use语句。

[root@xuexi ~]# grep -C 2 -i 'use' /tmp/mutil_db.bak  
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest`;

--
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest1`;

--

所以使用mysqldump备份的时候,无论何时都建议--databases或者--all-databases选项二选一,这样就免去了连进数据库的过程。

3.2.2 使用DDL选项备份示例

DDL选项如下:

--add-drop-database
--add-drop-table   
--add-drop-trigger 
-n, --no-create-db  
-t, --no-create-info
--replace     

--no-create-db将抑制建库语句,所以不建议使用。

--no-create-info将抑制建表语句。使用和不使用的对比如下:

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-create-info --databases backuptest >/tmp/backuptest1.bak
vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

澳门新濠3559 11

--replace将会把insert语句替换为replace语句。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --replace --databases backuptest >/tmp/backuptest2.bak
shell> grep -i 'replace' /tmp/backuptest2.bak                                                                     
REPLACE INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31');
REPLACE INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

3.2.3 使用字符集选项示例

dump数据的时候,客户端和数据库的字符集不一致的话会进行字符集转换,转换的过程是不可逆的,所以有可能会导致乱码。

例如,插入一个带有中文字符的记录到字符集为latin1的表student中。

insert INTO backuptest.`student` VALUES (6,'马','male','2017-03-31');

如果提示无法插入,则设置客户端字符集和连接字符集为latin1,character_set_client、character_set_connection、character_set_results,使用set names latin1即可,它会设置它们3个。

插入成功之后,其他会话连接数据库查询将会是乱码的。dump的时候也是乱码的,因为dump默认会使用utf8字符集,在latin1转码为utf8的过程中出现了乱码。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31'),(6,'马','male','2017-03-31');
INSERT INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

再使用乱码的文件来恢复的话,肯定是乱码的结果。

这时可以指定dump时的字符集为latin1来使得dump数据时无需转换字符集。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --default-character-set=latin1 --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31'),(6,'马','male','2017-03-31');

测试完成之后,将新插入的含有中文字符的记录删除。

delete from backuptest.student where studentid=6;

3.2.4 使用格式化选项示例

格式化选项如下:

--compact                   
--complete-insert, -c       
--create-options            
--tab=dir_name, -T dir_name 
--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name            
-Q, --quote-names                       

选项--compact可以极大的简化输出,让输出变得更加简单明晰。但是却不安全,因为它不止会简化注释语句,还会简化部分非注释语句,如insert前后的lock语句。所以该选项仅用来调试使用。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --compact --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

澳门新濠3559 12

"--complete-insert, -c"选项会在每个insert语句中列出插入列列表,而默认情况下mysqldump备份时是不加的。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -c --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak  

澳门新濠3559 13

--tab选项是将表的结构定义和数据分开dump,结构定义语句dump到表的同名.sql文件中,数据dump到表的同名.txt文件中。--tab指定这些文件的输出目录。但要求mysqldump和MySQL服务器必须在同一台服务器上,且mysql系统用户对指定的输出目录有写权限,连接数据库的用户还需要有file权限。而且该选项不能和--databases或--all-databases一起使用。因为它只能定义表的结构。

一般和该选项一起使用的有:该部分内容见select ... into outfile。

--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name  

3.2.5 使用筛选选项示例

--all-databases, -A  
--databases, -B  
--ignore-table=db_name.tbl_name  
-d, --no-data       
--events, -E  
--routines, -R   
--triggers  
--tables 
--where='where_condition', -w 'where_condition'  

筛选数据库此处不再赘述。

--ignore-table表示忽略数据库中的某张表不被dump。要忽略多张表的时候多次写该选项即可。例如忽略backuptest.student和backuptest1.student1。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --ignore-table backuptest.student --ignore-table backuptest1.student1 --databases backuptest backuptest1 >/tmp/backuptest.bak
shell> grep -i 'student' /tmp/backuptest.bak |wc -l
0

--no-data表示只dump表的结构,不dump数据。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -d backuptest>/tmp/backuptest.bak

grep -i 'insert' /tmp/backuptest.bak | wc -l
0

--tables指定数据库来导出其中的某些表。只支持指定一个数据库。该选项会覆盖--databases选项,所以不会建库语句和use语句。该选项可以用在分开备份innodb表和myisam表。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --tables backuptest student teacher>/tmp/backuptest.bak

--where指定某个表的筛选条件,格式为--where="condition"。如果指定了筛选列,则要导出的表中必须要含有该列。另外,显式指定表时,格式为"db_name tab_list"。以下格式都正确。

# 导出某数据库下的一个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student>/tmp/backuptest.bak
# 导出某数据库下多个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student teacher>/tmp/backuptest.bak
# 导出多个数据库中的筛选数据
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --where="gender='male'" --databases backuptest backuptest1>/tmp/backuptest.bak

3.2.6 使用事务选项示例

--add-locks选项是在insert语句前后加上lock tables和unlock tables语句。

澳门新濠3559 14

--no-autocommit选项是在insert语句前后加上set autocommit=0,并在事务结束的地方加上commit语句。这样插入数据时只需一次提交,可以大幅提升大量插入时的性能。

--flush-logs, -F

在开始dump前先flush logs,如果同时使用了--all-databases,则依次在每个数据库dump前flush;如果同时使用了--lock-all-tables,--master-data或者--single-transaction,则仅flush一次,因为这几个选项是dump前开启一个长事务或者全局锁定。等价于使用flush tables with read lock锁定所有表,这样可以让dump和flush在完全精确的同一时刻执行。

--flush-privileges  

在dump完所有数据库后在数据文件的结尾加上flush privileges语句,在导出的数据涉及mysql数据库或者依赖于mysql数据库时都应该使用该选项。

--lock-all-tables, -x 

为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁,一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项。

--lock-tables, -l 

在dump每个数据库前依次对该数据库中所有表加上read local锁(多次加锁,lock tables ... read local),这样就允许对myisam表进行并发插入。对于innodb存储引擎,使用--single-transaction比--lock-tables更好,因为它不完全需要锁定表。因为该选项是分别对数据库加锁的,所以只能保证每个数据库之间的一致性而不能保证在所有数据库之间的一致性。 该选项主要用于myisam表,如果既有myisam又有innodb,则只能使用--lock-tables了,或者分开dump更好。

--single-transaction

该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction**语句给服务端。**该选项对于导出事务表如innodb表很有用,因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。当使用该选项只能保证innodb表的一致性,对于myisam表是无法保证的。但是在使用该选项的时候,一定要保证没有任何其他数据库的连接在使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE语句,因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥,因为lock tables会隐式提交事务,要导出大的innodb表,该选项结合--quick选项更好。

3.3 mysqldump使用建议

1.从性能考虑:在需要导出大量数据的时候,使用--quick选项可以加速导出,但导入速度不变。如果是innodb表,则可以同时加上--no-autocommit选项,这样大量数据量导入时将极大提升性能。

2.一致性考虑:对于innodb表,几乎没有理由不用--single-transaction选项。对于myisam表,使用--lock-all-tables选项要好于--lock-tables。既有innodb又有myisam表时,可以分开导出,又能保证一致性,还能保证效率。

3.方便管理和维护性考虑:在导出时flush log很有必要。加上--flush-logs选项即可。而且一般要配合--lock-all-tables选项或者--single-transaction选项一起使用,因为同时使用时,只需刷新一次日志即可,并且也能保证一致性。同时,还可以配合--master-data=2,这样就可以方便地知道二进制日志中备份结束点的位置。

4.字符集考虑:如果有表涉及到了中文数据,在dump时,一定要将dump的字符集设置的和该表的字符集一样。

5.杂项考虑:备份过程中会产生二进制日志,但是这是没有必要的。所以在备份前可以关掉,备份完后开启。set sql_log_bin=0关闭,set sql_log_bin=1开启。

以下是备份不同存储引擎类型表的示例:

备份myisam表:需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --lock-all-tables --flush-logs --master-data=2 --tables backuptest teacher >/tmp/myisam.sql ;

set sql_log_bin=1

备份innodb表:需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

set sql_log_bin=1

3.4 mysqldump + 二进制日志备份

mysqldump可以实现全备份,在mysqldump之后再二进制日志备份就相当于增量备份,这样就可以实现全备份之后的定时点还原。

假设要备份的是一张innodb表。使用下面的语句:

mysqldump  -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

因为dump前会flush二进制日志,所以之后对该表的操作会记录到新的滚动日志中。然后只需备份新的二进制日志即可。

然后在该表中插入一行记录。

insert into student select 10,'xiaolonglong','male','2015-01-02';

备份新的二进制日志。

mysqlbinlog mysql-bin.000002 >/tmp/new_binlog.sql

设计刚才备份的表误操作,如删除该表。

drop table student;

使用该表的完全备份和二进制日志恢复。因为备份时使用的是--tables选项,所以要恢复需要进入数据库指定数据库,然后使用source来加载sql文件。

use backuptest;
source /tmp/innodb.sql;
source /tmp/new_binlog.sql;

3.5 mysqldump工具的评价

mysqldump备份的文件是逻辑SQL语句,总体来说,简单,便捷,在有些时候迁移数据的时候比较有用。另外,它的功能也很多,例如导出数据,导出表结构等。

但是缺点是恢复速度太慢,因为恢复数据时是通过insert不断插入记录的,它的恢复速度远不及load data infile导入数据。

在备份方式上,mysqldump备份myisam表时因为要加--lock-all-tables,这时要备份的数据库全部被上锁,可读不可写,所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction,会自动将隔离级别设置为repeatable read并开启一个事务,这时mysqldump将获取dump执行前一刻的行版本,并处于一个长事务中直到dump结束。所以不影响目标数据库的使用,可读也可写,即实现的是热备。

编辑:数据库 本文来源:备份和恢复(三),如何从一个mysqldump文件中提取其

关键词: