MySQL 备份与恢复详解

备份类型介绍

备份类型可以分为物理备份和逻辑备份两种

物理备份 是指通过拷贝数据库文件的方式完成备份， 这种备份方式适用于数据库很大 ，数据重要且需要快速恢复的数据库

逻辑备份是指通过备份数据库的逻辑结构（create database/table语句)和数据内容（insert语句或者文本文件）的方式完成备份。这种备份方式适用于数据库不是很大，或者你 需要对导出的文件做一定的修改，又或者是希望在另外的不同类型服务器上重新建立此数据库的情况

物理备份通常要求在数据库关闭的情况下执行 ，但如果是在数据库运行情况下执行，则要求备份期间数据库不能修改

逻辑备份的速度要慢于物理备份，是因为 逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式 ；通常输出的备份文件大小也要比物理备份大；另外逻辑备份也不包含数据库的 配置文件和日志文件内容； 备份和恢复的粒度可以是所有数据库，也可以是单个数据库，也可以是单个表；逻辑备份需要再数据库运行的状态下执行 ；它的执行 工具 可以是mysqldump或者是select … into outfile两种方式

备份又可以分为在线备份和离线备份两种 在线备份是指在数据库运行的状态下执行的备份 而离线备份是指在数据库关闭情况下执行的备份

备份还可以分为本地备份和远程备份两种

本地备份是指备份是在和当前 MySQL 运行的相同主机上发起和执行 而离线备份是指在数据库关闭情况下执行的备份

备份还可以分为本地备份和远程备份两种 本地备份是指备份是在和当前MySQL运行的相同主机上发起和执行

远程备份是指备份是在和当前MySQL运行的不同主机上发起和执行 比如mysqldump命令可以连接本机MySQL，也可以连接远程MySQL；在比如select …into outfile命令可以通过本地或者远程的MySQL客户端执行，但生成的文件则会存放在MySQL实例运行的主机上

对物理备份来说启动备份的过程是MySQL实例主机，但备份的地址有可能是远程的某个 存储

备份还可以分为全量备份和增量备份两种
全量备份是指备份中包含所有的数据，而增量备份是指备份中仅包含在某个指定时间段内的变化情况  
全量备份的方法正如之前说到的物理备份和逻辑备份方式  
而增量备份的方法在MySQL中需要借助二进制日志完成

MySQL备份方法

1. mysqldump命令执行备份

2. 通过 拷贝物理表文件生成备份 ：当前存储引擎下每个表都有自己独立的数据文件时就可以使用拷贝物理表文件的方式。如果 当前数据库是运行状态，则需要下对此表加上一个只读锁 ，防止备份期间的修改操作FLUSH TABLES tbl_list WITH READ LOCK；这种表级的备份方式 对MyISAM存储引擎支持很好 ，因为MyISAM的表天生就分成了三个独立的数据文件（ .frm,   .MYD, and *.MYI）， 但对InnoDB存储引擎的表就不太支持

3.  通过select … into outfile方式生成文本文件：第一种方式是通过SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name命令生成在服务器上的文本文件，另一种方式是通过mysqldump命令加–tab参数生成文本文件; 但这种方式只会生成表数据，不会生成表结构

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

5. Xtrabackup工具执行全量备份或增量备份

MySQL 物理拷贝文件

对MyISAM表来说：如果当前此表在 拷贝过程中没有任何写操作时 ，可以直接拷贝

10.0.0.201 master01 10.0.0.204 master02

没有主从关系

创建一个myisam表插入数据进行模拟测试：

1 2 3 4 5 6 7 8 9 10 11 12

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; insert into students_myisam values(1, 'a' ,1,1),(2, 'b' ,2,2),(3, 'c' ,3,3);

把这个表相关的三个文件students_myisam.frm,students_myisam.MYD, students_myisam.MYI文件拷贝到另外 
的数据库实例对应的数据库目录下 [root@master01 course]# scp students_myisam.* 10.0.0.204:/usr/local/mysql/data/course/

注意相关目录下的权限改成mysql
[root@master02 course]# chown mysql.mysql students_myisam.*

对InnoDB表来说： 即使是innodb_file_per_table=on的情况下直接拷贝文件也不行

把这个表相关的两个文件students.frm，students.ibd文件拷贝到另外的数据库实例对应的数据库目录下

查看错误日志信息： 2018-09-18T09:26:39.479752Z 5667 [Warning] InnoDB: Cannot open table test/students from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to  http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html  for how to resolve the issue. 有一部分放在共享表空间ibdata里面，所以不能直接拷贝文件

对InnoDB来说，可以通过拷贝整个data目录的方式来完成备份和恢复 ,拷贝到另外实例， 上记得授权

Mysqldump 详解

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

Mysqldump工具使用的三种方式：
shell> mysqldump [options] db_name [tbl_name …] #备份某个库的那些表  
shell> mysqldump [options] –databases db_name … #备份那些苦  
shell> mysqldump [options] –all-databases #备份所有库

options里的关键参数： –host=host_name, -h host_name指定要导出的目标数据库所在的主机，默认是localhost –user=user_name, -u user_name指定链接目标数据库的数据库用户名 –password[=password], -p[password]指定链接目标数据库的数据库密码 –port=port_num, -P port_num指定链接目标数据库的端口 –add-drop-database表示在使用–databases或者是–all-databases参数时在每个create database命令前都加上drop database的命令 –add-drop-table表示在每个create table命令前加上drop table命令

–add-drop-trigger表示在每个create trigger命令前加上drop trigger命令

–replace表示使用replace命令插入数据而不是使用insert命令

--default-character-set=charset_name指定默认的字符集，默认是UTF8

–set-charset表示将SET NAMES default_character_set命令写入到导出备份文件中，默认是开启状态

--dump-slave[=value]参数表示从复制的slave从库导出备份，且其中包含了change master to语句 ，则利用此备份文件可以直接建立另一个复制从库。   value参数如果不写或者=1的情况下，则change master to语句写入到dump文件中 ，如果 设置=2则代表也写入到dump文件中，只是会注释掉

--master-data[=value]参数表示从复制的主库上导出备份 ，和–dump-slave参数配置类似，可以利用此备份文件直接建立另一个复制从库。其value的取值含义也和dump-slave相同。 使用该参数会自动打开—lock-all-table参数 ， 除非同时使--single-transaction参数

–tab=dir_name, -T dir_name参数表示将备份文件以文本文件的方式生成，dir_name指定了存放的文件路径，每个表会生成两个文件，一个是.sql文件保存的表结构信息，一个是.txt文件保存的表数据信息

–all-databases, -A参数代表导出所有数据库里的所有的表

–databases, -B参数代表导出指定的一个或多个数据库

–ignore-table=db_name.tbl_name参数代表导出过程中忽略某个指定表的导出，如果要忽略多个表则这个参数要使用多次

–no-data, -d参数代表不要导出数据，只导出表结构

–routines, -R参数代表也要把存储过程和函数导出来

–triggers参数代表也要将触发器导出来

–where=’where_condition’, -w ‘where_condition’ 参数代表仅导出符合条件的数据，比如 –where=”user=’jimf’” -w”userid>1” -w”userid<1

–lock-all-tables, -x参数代表在导出过程中对每个数据库的每个表加上一个只读锁

–no-autocommit参数代表对每个表的数据导出内容用set autocommit=0和commit两个语句包围

–single-transaction参数代表将事务隔离级别设置为可重复读并在导出开始时执行start transaction开启一个新的事务，在dump的执行过程中也不会阻止任何的读写操作， 一般都会加上改参数

数据导出：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

导出一个数据库： [root@master01 ~]# mysqldump -u root -p -P 3306 --databases course>backup.sql 导出多个数据库： [root@master01 ~]# mysqldump -u root -p -P 3306 --databases course test>course.sql #或则 [root@master01 ~]# mysqldump -u root -p -P 3306 -B course test>course.sql 导出所有数据库： [root@master01 ~]# mysqldump -u root -p -P 3306 --all-databases>course.sql 导出一个数据库的某几个表： [root@master01 ~]# mysqldump -u root -p -P 3306 course students students_myisam>course.sql 仅导出course数据库的数据而不包含表结构： [root@master01 ~]# mysqldump -u root -p -P 3306 --no-create-info course>course.sql 仅导出course数据库中的students和students_myisam两个表的数据： [root@master01 ~]# mysqldump -u root -p -P 3306 --no-create-info course students students_myisam>course.sq 仅导出course数据库的表结构： [root@master01 ~]# mysqldump -u root -p -P 3306 --no-data course>course.sql 导出course数据库中除了teacher和score两个表的其他表结构和数据： [root@master01 ~]# mysqldump -u root -p -P 3306 --ignore-table=course.teacher --ignoretable=course.score course>course.sql 导出course数据库的表和存储过程和触发器： [root@master01 ~]# mysqldump -u root -p -P 3306 --routine --trigger course>course.sql 导出course数据库中符合 where 条件的数据： [root@master01 ~]# mysqldump -u root -p -P 3306 -- where = "sid in (1,2)" course students students_myisam>course.sql 远程导出course数据库，导出文件在发起导出命令的服务器上： [root@master02 data]# mysqldump -u root -p -P 3306 -h 10.0.0.201 course > course.sql

关于备份锁表的问题

FLUSH TABLES WITH READ LOCK简称(FTWRL)，该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)。由FTWRL总共需要持有两把全局的MDL锁，并且还需要关闭所有表对象，因此这个命令的杀伤性很大，执行命令时容易导致库hang住 FTWRL主要包括3个步骤： 1.上全局读锁(lock_global_read_lock) 2.清理表缓存(close_cached_tables) 3.上全局COMMIT锁(make_global_read_lock_block_commit)

FTWRL每个操作的影响
上全局读锁会导致所有更新操作都会被堵塞；关闭表过程中，如果有大查询导致关闭表等待，那么所有访问这个表的查询和更新都需要等待；上全局COMMIT锁时，会堵塞活跃事务提交

从主库备份正确方式： [root@master ~]# mysqldump -u root -p -P 3306 –master-data=2 –single-transaction course >course.sql 此备份方式会在备份开始之初在所有表上加一个只读锁(FLUSH TABLES WITH READ LOCK)， 当成功获取了该锁并开始备份之后，此锁就会立即释放。而后续的dump过程中不会影响其他的读写操作

从主库备份如果–dump-slave参数，则备份失败： [root@master01 ~]# mysqldump -u root -p -P 3306 –dump-slave=2 –single-transaction course >course.sql Enter password: mysqldump: Couldn’t execute ‘START SLAVE’: The server is not configured as slave; fix in config file or with CHANGE MASTER TO (1200)

在从库上备份数据：  
[root@slave1 course]# mysqldump -u root -p -P 330 –dump-slave –single-transaction test >course.sql

Mysql 导入备份文件

格式如下 第一种mysql

第二种： 也可以使用source命令导入一个备份文件：

在sql的备份的那里登录 mysql > use dbname; mysql > use dbbackup.sql;

使用mysqldump命令导出文本文件  
通过制定-tab=dir_name 参数来指定文本文件的导出路劲  
需要在my.cnf 中先配置  
secure_file_priv=/usr/local/mysql/backup/

比如导出course数据库的所有表的文本文件到=/usr/local/mysql/backup/目录下： 这个目录也需要mysql 权限才能导出

[root@master01 backup]# mysqldump -u root -p –tab=/usr/local/mysql/backup course

–fields-terminated-by=str参数指定每个字段值之间的间隔符，默认是tab

–fields-enclosed-by=char参数指定每个字段值使用什么字符括起来，默认是没有

–fields-optionally-enclosed-by=char参数指定对每个非数字类型的字段值使用什么字符括起来，默认没有

–lines-terminated-by=str参数指定行之间的结束符，默认是newline

比如：

[root@master01 backup]# mysqldump -u root -p –tab=/usr/local/mysql/backup –fields-terminated-by=, –fields-enclosed-by=’”’ –lines-terminated-by=0x0d0a course

针对文本文件(txt)到导入，分为两步， 先是导入表结构文件，再导入数据文件 表结构文件的导入可以使用mysql db1 < t1.sql或者source命令 数据文件的导入可以使用mysqlimport或者load data infile命令

模拟用文本文件导入数据，不删除表，删除表数据，再进行导入

mysqlimport 方式：

或者使用load data infile 方式

自定义脚本定时备份

检测所有用户定义的数据库，定时备份所有的数据库，并上传到备份服务器 Python脚本还是 shell 脚本，想怎么写随你

样例脚本如下：

Select … into outfield 备份

select … into outfile命令可以用来导出表中符合条件的数据到文本文件，不导出表结构，仅用来导出数据

outfile 输出文件路劲必须在my.cnf 中配置secure_file_priv=/usr/local/mysql/backup/

TERMINATED BY用来指定字段值之间的符号，例如， “TERMINATED BY ‘,’”指定了逗号作为两个字段值之间的标志。

ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如， “ENCLOSED BY ’ ” ‘”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

ESCAPED BY子句用来指定转义字符，例如， “ESCAPED BY ‘ ‘”将“ ”指定为转义字符，取代“\”， 如空格将表示为“*N”。 LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY ‘?’”表示一行以“?”作为结束标志

比如导出students表里完整的数据：

1 2 3 4 5 6 7 8 9 10

rm -rf /usr/local/mysql/backup/* 比如导出students表里完整的数据： mysql> select * from students into outfile '/usr/local/mysql/backup/students.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ; 导出students表里的部分数据： mysql> select * from students where sid in (1,2) into outfile '/usr/local/mysql/backup/students2.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ; 导出表关联之后的数据： mysql> select * from students a inner join dept b on a.dept_>into outfile '/usr/local/mysql/backup/students3.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ;

对select … into outfile导出的文本文件，可以通过load data infile命令导入到表里 基本语法：

terminated by分隔符：意思是以什么字符作为分隔符 enclosed by字段括起字符 escaped by转义字符 terminated by描述字段的分隔符，默认情况下是tab字符（\t） enclosed by描述的是字段的括起字符。 escaped by描述的转义字符。默认的是反斜杠（backslash： \ ） lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符

如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。

replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行
。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误  
，并且文本文件的余下部分被忽略

比如将students.txt文件中的数据导入到表里：

1 2 3 4 5 6

mysql> delte from students; mysql> load data infile '/usr/local/mysql/backup/students2.txt' into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ; 导入过程中忽略错误，继续将符合条件的数据导入： mysql> load data infile '/usr/local/mysql/backup/students.txt' ignore into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ; mysql> select * from students;

导入过程中如果碰到唯一性约束冲突则执行替换语句：

mysql 恢复

恢复类型分为全量恢复和基于时间点恢复两种 全量恢复是指将备份文件中所有的数据都进行恢复，恢复完成之后的数据就是为生成备份的那一刻的数据状态 而 基于时间点的恢复是指将数据库恢复到指定的某个时间点的状态，通常需要依赖二进制日志 将指定时间点前的所有数据库操作都重新操作一遍

基于时间点恢复

基于时间点的恢复是将数据库恢复到指定的时间点状态，通常是先通过全量恢复的方式先将数据库恢复到上一个全量

恢复的时间点， 然后再利用二进制日志恢复到指定的时间点  
Mysqlbinlog工具可以用来解析二进制日志的内容，让日志可以被执行或者是查看  
在数据库中重新执行某个二进制日志文件中的内容，可以使用如下命令

模拟数据测试基于时间点恢复：

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> use course; mysql> delte from students; mysql> alter table students add tstamp timestamp; mysql> flush logs; #刷新一下二进制日志，新生成一个二进制日志 #插入数据 ，flush logs新生成一个二进制文件 mysql> insert into students(sid,sname,gender,dept_id) values(1, 'a' ,1,1),(2, 'b' ,2,2); mysql> flush logs; mysql> insert into students(sid,sname,gender,dept_id) values(3, 'c' ,3,3),(4, 'd' ,4,3); mysql> flush logs; mysql>  insert into students(sid,sname,gender,dept_id) values(5, 'e' ,5,3),(6, 'f' ,6,3); mysql> flush logs; mysql> select * from students;

删除表里全部数据，进行模拟恢复 第一个的flush logs的时候alter table

插入a,b的那次数据的二进制日志文件

恢复第一个二进制日志文件：插入a,b

1 2 3 4 5 6 7

mysql> truncate table students; ##先删除表里的全部数据 mysql> desc students; 恢复第一个二进制日志文件：插入a,b 可以通过mysqlbinlog 查询一下二进制文件看看里面的具体内容 [root@master01 data]# mysql -u root -p < /tmp/mysqlbinlog46.sql

恢复第二个二进制日志文件：

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

比如将数据库恢复到2018-09-19 19:06:08的时刻： 涉及到mysql-bin.000048文件内容

基于时间点的恢复也可以指定日志文件中的位置 ， 通过设置–start-position和–stop-position两个参数

看一下二进制的具体内容

从一个完整的日志位置恢复 或则commit 提交以后–stop-position位置恢复

也就是 –start-position=’298’ –stop-position=’460’ #只恢复某个具体的binlog内容 或则 –stop-posionion=’460’ #恢复460日志位置之前的binlog的内容

Xtrabackup备份与恢复

Xtrabackup是一个对Mysql做数据备份的工具，支持在线热备份（备份时不影响数据读写） ,Xtrabackup有两个主要的工具：xtrabackup、 innobackupex，其中innobackupex已经逐渐被xtrabackup取代 特点： (1)备份过程快速、可靠； (2)备份过程不会打断正在执行的事务； (3)能够基于压缩等功能节约磁盘空间和流量； (4)自动实现备份检验； (5)还原速度快

下载地址： https://www.percona.com/downloads/XtraBackup/LATEST/

安装：

1 2 3 4 5

wget https: //www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/RedHat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm yum -y install libev yum -y install perl-Digest-* rpm -ivh percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

全量备份：

全量恢复

恢复过程中首先要执行prepare ， 将所有的数据文件都准备到同一个时间点 ， 因为在备份过程中所有的数据文件备份都是在不同的时间点，如果直接启动可能会导致冲突

增量备份：

创建一个初始完整备份

进行模拟数据增加，进行增量备份

1 2 3 4 5 6 7

mysql -u root -p use course insert into students values(11, 'aa' ,1,1,current_timestamp); insert into students values(12, 'bb' ,2,2,current_timestamp); exit xtrabackup --backup --target-dir=/data/backups/mysql/inc1 --incremental-basedir=/data/backups/mysql/ base -u root -p123456 --host=127.0.0.1

在加数据，进行第二次增量备份

增量备份恢复：

恢复第一次插入的数据

1 2 3 4 5 6 7 8 9 10 11

pkill mysql rm -rf /usr/local/mysql/data/ #准备上一次完整备份时间点 xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/ base --datadir=/usr/local/mysql/data #准备恢复第一次插入的数据 xtrabackup --prepare --target-dir=/data/backups/mysql/ base --incremental-dir=/data/backups/mysql/inc1 --datadir=/usr/local/mysql/data xtrabackup --copy-back --target-dir=/data/backups/mysql/ base --datadir=/usr/local/mysql/data chown -R mysql.mysql /usr/local/mysql/data/ /etc/init.d/mysql start mysql -u root -p

恢复第二次插入的数据

在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行，并且可能会在下一次增量备份中提交。使用–apply-log-only选项来防止回滚阶段。 最后一次恢复的时候不用加–apply-log-only

Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2019-01/156363.htm