MySQL备份与恢复
一、逻辑备份
二、物理备份
- 1、xtrabackup备份介绍
2、获取xtrabackup备份工具
3、xtabackup软件的安装
4、xtrabackup全库备份与恢复
5、xtrabackup增量备份与恢复
MySQL备份与恢复（重点）
一、用户管理与权限管理
- ☆ 用户管理
- ☆ 权限管理
二、MySQL备份概述
6、备份方法
三、MySQL逻辑备份
四、mysqldump + binlog实现增量备份

MySQL备份与恢复

一、逻辑备份

1、回顾什么是逻辑备份

逻辑备份就是把数据库、数据表或者数据进行导出，导出到一个文本文件中。

2、逻辑备份工具

mysqldump：提供全库级、数据库级别以及表级别的数据备份

mysqldump + binlog二进制日志实现增量备份

3、逻辑的导出与导入

☆ 导出（数据备份）

无论是什么存储引擎，以下方式本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份。备份的是数据记录。

案例：把tb_student数据表中的数据记录进行逻辑导出

# mysql -uroot -p
Enter password:123
mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db_itheima.tb_student;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

出现以上问题的主要原因在于我们没有指定MySQL逻辑导出时指定的路径

# vim my.cnf
[mysqld]
...
secure_file_priv=/tmp/sqlbak/
# service mysql_3306 restart

mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db_itheima.tb_student;
ERROR 1 (HY000): Can't create/write to file '/tmp/sqlbak/tb_student.txt' (Errcode: 13 - Permission denied)

出现以上问题的主要原因在于mysql账号对/tmp/sqlbak没有写入权限

# setfacl -m u:mysql:rwx /tmp/sqlbak

☆ 导入（数据还原）

# mysql -uroot -p
Enter password:123
mysql> load data local infile '/tmp/sqlbak/tb_student.txt' into table tb_student;
或
# mysqlimport dbname /path/file -p  =>   要求，导出的文件必须和数据表名称完全一致
例如：
# mysqlimport db_itheima /tmp/sqlbak/tb_student.txt -p
Enter password:123

典型应用场景：可以把一些规则的文本文件导入到数据表中

案例：把/etc/passwd文件中的数据导入到password数据表中

第一步：创建一个password数据表，password是mysql保留关键字，建议使用``两个反撇号括起来

mysql> use db_itheima;
mysql> CREATE TABLE `password` (
  `uname` varchar(50) DEFAULT NULL,
  `pass` char(2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `home` varchar(50) DEFAULT NULL,
  `shell` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第二步：把/etc/passwd文件拷贝到/tmp/sqlbak目录下，起名叫password.txt

# cp /etc/passwd /tmp/sqlbak/password.txt

第三步：使用mysqlimport把password.txt文件中的内容写入到数据表

# mysqlimport db_itheima --fields-terminated-by=':' --lines-terminated-by='\n' /tmp/sqlbak/password.txt -p
选项说明
--fields-terminated-by=':'，指定导出文件的分隔符为冒号:
--lines-terminated-by='\n'，指定每一行的结尾使用的符号,\n代表换行符

4、扩展：逻辑备份还原小工具（支持远程连接）

MySQL其实还支持很多管理软件（Windows版）：MySQL-Front、Navicat

备份与恢复 - 图1

第一步：选择MySQL连接

备份与恢复 - 图2

第二步：输入MySQL连接信息

备份与恢复 - 图3

第三步：功能演示=>创建数据库、创建数据表、插入数据…

第四步：数据库的备份与还原

① 逻辑的导出与导入

备份与恢复 - 图4

② 数据表的备份与还原

备份与恢复 - 图5

③ 数据库的备份与还原

备份与恢复 - 图6

二、物理备份

1、xtrabackup备份介绍

㈠ xtrabackup优缺点

优点：

备份过程==快速、可靠(==因为是物理备份)；
支持增量备份，更为灵活
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快；

缺点：

只能对innodb表增量备份，myisam表增量备份时是全备
innobackupex备份MyISAM表之前要对全库进行加READ LOCK，阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写。

㈡ xtrabackup备份原理

innobackupex首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql的redo log的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中。
物理拷贝innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方
复制结束后，执行flush table with read lock操作进行全库锁表准备备份非InnoDB文件
物理复制.frm .myd .myi等非InnoDB引擎文件到备份目录
查看二进制日志的位置
解锁表unlock tables
停止xtrabackup_log进程

备份与恢复 - 图7

扩展：redo log日志？

事务日志或称redo日志，在mysql中默认以ib_logfile0,ib_logfile1名称存在。

㈢ xtrabackup备份恢复原理

备份与恢复 - 图8

具体文字描述如下（了解）：

在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。
上面就是xtrabackup的备份过程。接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了。
以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。
备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的recover和restore，InnoDB的prepare过程可以称为recover（恢复），MyISAM的数据复制过程可以称为restore（还原）。
Mysql5.7.3以后开启二进制日志需要加上server-id选项，不然报错

2、获取xtrabackup备份工具

网站域名：http://www.percona.com

备份与恢复 - 图9

3、xtabackup软件的安装

第一步：上传软件包及依赖库到Linux服务器端

第二步：安装依赖库

# rpm -ivh libev-4.15-3.el7.x86_64.rpm

第三步：安装xtrabackup软件

# yum install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm -y
# rpm -ql percona-xtrabackup-24

4、xtrabackup全库备份与恢复

备份核心思路：

① 使用innobackupex对数据库中的所有库进行全量备份，备份完成后，其不能立即进行数据恢复（数据不完整，缺少12:00 ~ 12:30这个过程中的数据）

② 预备阶段，备份过程中产生的xtrabackup_log应用到全量备份集

③ 模拟故障（删除数据） => rm -rf data/*

④ 动员运维工程师进行全库恢复

⑤ 测试验证

第一步：准备数据

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table t1(id int,name varchar(10)) engine=myisam;
mysql> insert into t1 values (1,'吕布');
mysql> create table t2(id int,name varchar(10)) engine=innodb;
mysql> insert into t2 values (1,'貂蝉');

第二步：专门准备一个数据库备份账号，开通相应权限

创建备份用户admin，并授予相应权限
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
mysql> flush privileges;
说明：
在数据库中需要以下权限：
RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE

第三步：使用innobackupex工具进行全库备份

# innobackupex --user=admin --password=123 /full_xtrabackup
说明：备份目录默认会自动创建，也可以手动创建
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

第一次运行报错：

200829 15:48:59  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','admin',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
200829 15:48:59 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).

出现以上问题的主要原因在于我们的mysql.sock并不在/var/lib/mysql目录下。为什么其会自动连接/var/lib/mysql目录下的mysql.sock呢？

原因1：可能在/etc目录下还有my.cnf文件，影响了innobackupex的执行。

原因2：innobackupex拥有自己的默认配置，默认读取了/var/lib/mysql/mysql.sock文件

解决方案：

方案1：把你的套接字文件创建一个软链接，放置于/var/lib/mysql/mysql.sock文件中

# mkdir /var/lib/mysql
# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

方案2：在innobackupex中添加一个-S选项，执行套接字

# innobackupex -S /tmp/mysql.sock --user=admin --password=123 /full_xtrabackup

第四步：预备阶段，把备份这段时间内产生的日志整合到全量备份中

# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2020-08-29_15-59-22

第五步：模拟数据库故障

# rm -rf /mysql_3306/data/*
# pkill mysqld

第六步：快速的恢复数据库中的数据

# rm -rf /mysql_3306/data/*
# innobackupex --copy-back /full_xtrabackup/2020-08-29_15-59-22

第一次恢复报错：

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Error: datadir must be specified.

出现以上问题的主要原因在于，innobackupex工具无法找到MySQL中的数据目录

解决方案：把my.cnf配置文件传递给innobackupex，让其自动识别这个文件中的datadir

# innobackupex --defaults-file=/mysql_3306/my.cnf --copy-back /full_xtrabackup/2020-08-29_15-59-22

第七步：恢复数据时，一定要记得更改/mysql_3306/data目录下的文件拥有者以及所属组权限，否则mysql无法启动

# chown -R mysql.mysql /mysql_3306/data

第八步：启动MySQL，测试其是否正常

# service mysql_3306 start
# mysql -p
Enter password:123

5、xtrabackup增量备份与恢复

什么是增量？增量一定要有一个前提，必须要先有一个全量。

备份与恢复 - 图10

① 全量备份

② 把全量备份产生的日志整合到全量备份

③ 进行数据的增删改操作

④ 增量备份

⑤ 把增量备份及增量备份产生的日志文件全部整合到全量备份中

⑥ 模拟故障

⑦ 数据恢复

⑧ 启动数据库，测试验证数据

第一步：准备数据

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table t1(id int,name varchar(10)) engine=myisam;
mysql> insert into t1 values (1,'吕布');
mysql> create table t2(id int,name varchar(10)) engine=innodb;
mysql> insert into t2 values (1,'貂蝉');

第二步：创建一个账号，专门用于备份

创建备份用户admin，并授予相应权限
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
mysql> flush privileges;
说明：
在数据库中需要以下权限：
RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE

第三步：全量备份

# rm -rf /full_xtrabackup/*
# innobackupex --user=admin --password=123 /full_xtrabackup

第四步：把全备过程中产生的日志进行整合（特别注意）

# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/2020-08-29_17-06-48/
选项说明：
--apply-log 表示整合日志
--redo-only    表示只应用已经提交的事务，不回滚未提交的事务（12:00 ~ 12:30产生很多事务操作，事务处理=>开启事务，成功了提交事务，写入硬盘；失败了回滚事务，不写入硬盘）
注意：如果已经回滚了未提交事务，那么就无法再应用增量备份。

Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full ones. This is, perhaps, the stage where more attention is needed:
• First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.
• Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
对于增量备份的Prepare阶段，有2个需要注意的地方，一个是提交的事务需要replayed，一个未提交的事务需要rollback。

If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment and the remaining increments. Having this in mind, the procedure is very straight-forward using the —redo-only option, starting with the base backup:
如果在Prepare阶段replay了已提交的事务以及回滚了未提交的事务，则后续的增量备份无法添加到当前全备。因此在Prepare阶段全备应使用—redo-only选项。

—redo-only should be used when merging all incrementals except the last one. That’s why the previous line doesn’t contain the —redo-only option. Even if the —redo-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
对于存在多次增量的情形，仅仅只有最后一个增量不需要使用—redo-only 选项。如果使用了的话，rollback将由服务器启动的时候来完成。

第五步：修改数据（进行增删改操作，让其产生增量数据）

# mysql -p
Enter password:123
mysql> insert into db_itheima.t1 values (2,'王朗');
mysql> insert into db_itheima.t1 values (3,'袁术');

第六步：做增量备份

# innobackupex --user=admin --password=123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/2020-08-29_17-06-48
选项说明：
--incremental 增量备份目录
--incremental-basedir 这个增量是相对于哪个全量的

第七步：把增量备份产生的数据以及日志文件整合到全量备份中

# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2020-08-29_17-06-48 --incremental-dir=/incre_backup/2020-08-29_17-23-43
说明：
1.--redo-only除了最后一个不用加之外，其他的增量应用都要加，最后一个应用的时候可以直接进入回滚未提交事务阶段;如果加了也没事，服务启动的时候会进入recovery过程，来回滚
2.应用增量备份的时候只能按照备份的顺序来应用。如果应用顺序错误，那么备份就不可用。如果无法确定顺序，可以使用xtrabackup-checkpoints来确定顺序。
周天做了一个全量，周一、周二、周三、周四、周五、周六全部做增量

到此，增量备份就全部结束了！

第八步：模拟数据库故障

# rm -rf /mysql_3306/data/*
# pkill mysqld

第九步：数据恢复

# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/2020-08-29_17-06-48
# chown -R mysql.mysql /mysql_3306/data

启动数据库，测试验证是否正常

# service mysql_3306 start
# mysql -p
Enter password:123

MySQL备份与恢复（重点）

一、用户管理与权限管理

☆ 用户管理

1、创建MySQL用户

注意：MySQL中不能单纯通过用户名来说明用户，必须要加上主机。如jack@10.1.1.1

基本语法：

mysql> create user '用户名'@'被允许连接的主机名称或主机的IP地址' identified by '用户密码';
mysql> select user,host from mysql.user;

案例：创建一个MySQL账号，用户名：tom，用户密码：123

mysql> create user 'tom'@'localhost' identified by '123';
或
mysql> create user 'tom'@'127.0.0.1' identified by '123';

案例：创建一个MySQL账号（要求开通远程连接），主机IP地址：10.1.1.23，用户名：harry，用户密码：123

mysql> create user 'harry'@'10.1.1.23' identified by '123';

测试：在IP地址为10.1.1.23的主机上

# yum install mysql -y
# mysql -h 10.1.1.10 -P 3306 -uharry -p
Enter password:123
选项说明：
10.1.1.10 ：MySQL服务器端的IP地址

yum安装mysql：代表安装的是MySQL的客户端

yum安装mysql-server：代表安装的是MySQL的服务器端

案例：创建一个MySQL账号（要求开通远程连接），主机IP的网段：10.1.1.0，用户名：jack，用户密码：123

mysql> create user 'jack'@'10.1.1.%' identified by '123';

案例：创建一个MySQL账号（要求开通远程连接），要求面向所有主机开放，用户名：root，用户密码：123

mysql> create user 'root'@'%' identified by '123';

2、删除MySQL用户

基本语法：

mysql> drop user '用户名'@'主机名称或主机的IP地址';
特别说明：
如果在删除用户时没有指定主机的名称或主机的IP地址，则默认删除这个账号的所有信息。

案例：删除tom这个账号

mysql> drop user 'tom'@'localhost';

案例：删除jack这个账号

mysql> drop user 'jack'@'10.1.1.%';

案例：创建两个harry账号（localhost/10.1.1.23），然后删除其中的某个

mysql> create user 'harry'@'localhost' identified by '123';
mysql> create user 'harry'@'10.1.1.23' identified by '123';
mysql> drop user 'harry'@'10.1.1.23';

扩展：删除MySQL账号的另外一种方式

mysql> delete from mysql.user where user='root' and host='%';
mysql> flush privileges;

3、修改MySQL用户

特别说明：MySQL用户重命名通常可以更改两部分，一部分是用户的名称，一部分是被允许访问的主机名称或主机的IP地址。

基本语法：

mysql> rename user 旧用户信息 to 新用户信息;

案例：把用户’root’@’%’更改为’root’@’10.1.1.%’

mysql> rename user 'root'@'%' to 'root'@'10.1.1.%';

案例：把’tom’@’localhost’更名为’harry’@’localhost’

mysql> create user 'tom'@'localhost' identified by '123';
mysql> rename user 'tom'@'localhost' to 'harry'@'localhost';

扩展：使用update语句更新用户信息

mysql> update mysql.user set user='tom',host='localhost' where user='harry' and host='localhost';
mysql> flush privileges;

☆ 权限管理

1、权限说明

所有权限说明https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

USAGE    无权限,只有登录数据库,只可以使用test或test_*数据库
ALL        所有权限
以下权限为指定权限
select/update/delete/super/replication slave/reload...
with grant option 选项表示允许把自己的权限授予其它用户或者从其他用户收回自己的权限

默认情况下，分配权限时如果没有指定with grant option，代表这个用户不能下发权限给其他用户，但是这个权限分配不能超过自身权限。

2、权限保存位置（了解）

mysql.user                所有mysql用户的账号和密码，以及用户对全库全表权限（*.*）
mysql.db                非mysql库的授权都保存在此(db.*)
mysql.table_priv        某库某表的授权(db.table)
mysql.columns_priv        某库某表某列的授权(db.table.col1)
mysql.procs_priv        某库存储过程的授权

3、给用户授权

基本语法：

mysql> grant 权限1,权限2 on 库.表 to 用户@主机
mysql> grant 权限(列1,列2,...) on 库.表 to 用户@主机

库.表表示方法：.代表所有数据库的所有数据表，db_itheima.*代表db_itheima数据库中的所有数据表，db_itheima.tb_admin，代表db_itheima数据库中的tb_admin表

案例：给tom账号分配db_itheima的查询权限

mysql> grant select on db_itheima.* to 'tom'@'localhost';
mysql> flush privileges;

案例：给tom账号分配db_itheima.tb_student数据表的权限（要求只能更改age字段）

mysql> grant update(age) on db_itheima.tb_student to 'tom'@'localhost';
mysql> flush privileges;

案例：添加一个root@%账号，然后分配所有权限

mysql> create user 'root'@'%' identified by '123';
mysql> grant all on *.* to 'root'@'%';
mysql> flush privileges;

4、查询用户权限

查询当前用户权限

mysql> show grants;

查询其他用户权限

mysql> show grants for '用户名称'@'授权的主机名称或IP地址';

5、with grant option选项

with grant option选项作用：代表此账号可以为其他用户下发权限，但是下发的权限不能超过自身权限。

mysql> grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
mysql> grant all on *.* to 'harry'@'10.1.1.%' identified by '123'; 
如以上命令所示：
amy拥有下发权限的功能，而harry不具备下发权限的功能。

如果grant授权时没有with grant option选项，则其无法为其他用户授权。

6、使用grant创建用户

说明：5.7以后不推荐，未来会被弃用！

基本语法：

mysql> grant 权限 on 数据库.数据表 to '新用户名称'@'授权主机名称或IP地址' identified by '用户的密码';

案例：创建一个root账号，主机为%，授予所有权限，密码为123

mysql> grant all on *.* to 'root'@'%' identified by '123';

7、revoke回收权限

基本语法：

revoke 权限 on 库.表 from 用户;
撤消指定的权限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的权限
mysql> revoke select on db01.* from 'tom'@'10.1.1.1';

案例：从tom账号中回收select权限

mysql> revoke select on db_itheima.* from 'tom'@'localhost';
mysql> flush privileges;
mysql> show grants for 'tom'@'localhost';

案例：从tom账号中回收update权限

mysql> revoke update(age) on db_itheima.tb_student from 'tom'@'localhost';
mysql> flush privileges;
mysql> show grants for 'tom'@'localhost';

二、MySQL备份概述

1、关于数据保存你要知道

思考：备份和冗余有什么区别？

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

2、到底要备份什么

☆ 备份什么

数据库：一堆物理文件的集合；日志文件(二进制日志)+数据文件+配置文件

① 数据文件

② 配置文件 => my.cnf

③ 日志文件（主要是二进制日志文件）

☆ MySQL体系结构

扩展：MySQL体系结构（MySQL => DBMS软件到底是由哪些层构成的）

备份与恢复 - 图11

☆ 存储引擎层（MyISAM与InnoDB引擎）

存储引擎层：简单来说，就是数据的存储方式。在MySQL中，我们可以使用show engines查看当前数据库版本支持哪些引擎，常见的数据存储引擎：InnoDB、MyISAM、NDB…

常见面试题：请简述MySQL的MyISAM引擎与InnoDB引擎的区别

① MyISAM引擎：擅长数据的查询，支持全文索引

② InnoDB引擎：Supports transactions, row-level locking, and foreign keys，支持事务处理、行级锁、支持外键。

☆ 存储层（数据文件是如何进行存储的）

问题：存储引擎到底是如何保存数据文件的？

mysql> create database db_itheima default charset=utf8;

提出问题：我能不能不进入MySQL终端，然后在命令行中直接创建一个数据库呢？-e

当数据库创建完毕后，查看/mysql_3306/data文件夹：

备份与恢复 - 图12

db_itheima文件夹中还有一个文件db.opt，存放内容为数据库的编码格式。

MyISAM引擎

mysql> use db_itheima;
mysql> create table tb_user(id int, name char(1)) engine=myisam default charset=utf8;

创建完成后，查看db_itheima目录信息，发现产生了3个文件

备份与恢复 - 图13

.frm ：框架文件，定义数据表结构 .MYI ：INDEX索引，主要用于存放索引文件 *.MYD：数据文件

InnoDB引擎

mysql> use db_itheima;
mysql> create table tb_user(id int, name char(1)) engine=innodb default charset=utf8;

创建完成后，查看db_itheima目录信息，发现产生了2个文件

备份与恢复 - 图14

.frm ：框架文件，定义数据表结构 .ibd：索引文件+数据文件

其实InnoDB引擎不仅仅会产生以上两个文件，其在外部data目录中也会产生一个文件（确切来说不能叫做产生文件，而应该叫做共享文件）

备份与恢复 - 图15

所以由此可知，InnoDB引擎的数据备份不能简简单单的通过拷贝方式实现，必须使用专业的备份工具。

☆ 日志文件（MySQL中我们需要了解哪些日志）

日志类型	写入日志的信息
error错误日志	存放数据库的启动、停止或运行时的错误信息（找ERROR）
binlog二进制日志	数据库的所有更改操作（DDL/DML/DCL），不包含select或者show这类语句。

error错误日志的命令规则与存放的目录：/data目录下 + 主机名称.err

更改错误日志的存放位置：

# vim my.cnf
[mysqld]
...
log_error=data数据目录/主机名称.err或mysql.err

binlog二进制日志应用场景：

用于主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同。

用于数据的恢复操作

binlog二进制日志如何开启？

默认binlog日志是关闭的，可以通过修改配置文件完成开启，如下：

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=data数据目录/binlog

当我们更改了my.cnf配置文件，一定要记得重启MySQL服务器。service命令

3、备份过程须考虑的因素

必须制定详细的备份计划(策略)（备份频率、时间点、周期）
备份数据应该放在非数据库本地，并建议有多份副本
必须做好==数据恢复的演练==（每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据。）
根据数据应用的场合、特点选择正确的备份工具。
数据的一致性
服务的可用性

4、备份的类型

☆ 逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL）。
适用于中小型数据库，效率相对较低。一般在数据库正常提供服务的前提下进行，如：mysqldump、mydumper、into outfile（表的导出导入）等。
备份实质：就是把要备份的数据导出成.sql或.txt文件

☆ 物理备份

直接复制数据库文件
适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份）；如：tar、cp、xtrabackup（数据库可以正常提供服务）、lvm snapshot、rsync等
备份的实质：对数据文件 + 配置文件 + 日志文件进行拷贝操作

☆ 在线热备（数据冗余、AB复制、主从复制）

MySQL的replication架构，如M-S|M-S-S|M-M-S等
实时在线备份

5、备份工具

㈠社区版安装包中的备份工具

① mysqldump(逻辑备份，只能全量备份）

1）企业版和社区版都包含
2）本质上使用SQL语句描述数据库及数据并导出
3）在MYISAM引擎上锁表，Innodb引擎上锁行
4）数据量很大时不推荐使用

② mysqlhotcopy(物理备份工具）

1）企业版和社区版都包含
2）perl写的一个脚本，本质上是使用锁表语句后再拷贝数据
3）只支持MYISAM数据引擎

㈡企业版安装包中的备份工具

mysqlbackup

1）在线备份
2）增量备份
3）部分备份
4）在某个特定时间的一致性状态的备份

㈢第三方备份工具

① XtraBackup和innobackupex(物理备份）

1）Xtrabackup是一个对**InnoDB**做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具**InnoDB Hotbackup**的一个很好的替代品。
2）Xtrabackup有两个主要的工具：xtrabackup、innobackupex
a、xtrabackup只能备份InnoDB和XtraDB两种数据表，不能备份myisam类型的表。
b、innobackupex是将Xtrabackup进行封装的perl脚本，所以能同时备份处理innodb和myisam的存储引擎，但在处理myisam时需要加一个读锁。

② mydumper(逻辑备份，备份SQL语句）

多线程备份工具

https://launchpad.net/mydumper/mydumper-0.9.1.tar.gz 2015-11-06（最后更新时间）

6、备份方法

完全备份（全备）
增量备份（增量备份基于全量备份）

备份与恢复 - 图16

三、MySQL逻辑备份

1、mysqldump基本备份

本质：导出的是sql语句文件

优点：无论是什么存储引擎，都可以用mysqldump备成sql语句

缺点：速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份.

提供三种级别的备份，表级，库级和全库级

2、mysqldump基本语法

表级别备份
mysqldump [OPTIONS] database [tables]
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

准备一些要备份的数据：

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table tb_student(
    id int not null auto_increment,
    name varchar(20),
    age tinyint unsigned default 0,
    gender enum('male','female'),
    subject enum('ui','java','yunwei','python'),
    primary key(id)
) engine=innodb default charset=utf8;
mysql> insert into tb_student values (null,'刘备',33,'male','java');
mysql> insert into tb_student values (null,'关羽',32,'male','yunwei');
mysql> insert into tb_student values (null,'张飞',30,'male','python');
mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');
mysql> insert into tb_student values (null,'大乔',18,'female','ui');

3、mysqldump表级备份与还原

☆ 备份

案例：把db_itheima数据库中的tb_student数据表进行备份

# mkdir /tmp/sqlbak
# mysqldump db_itheima tb_student > /tmp/sqlbak/tb_student.sql -p
Enter password:123

☆ 还原

# mysql 数据库名称 < .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> use db_itheima
mysql> source .sql文件的位置

4、mysqldump库级备份与还原

☆ 备份

案例：把db_itheima数据库进行备份

# mysqldump --databases db_itheima > /tmp/sqlbak/db_itheima.sql -p
Enter password:123

☆ 还原

# mysql < .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> source .sql文件的位置

5、mysqldump全库级备份

在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志！！！

开启二进制日志

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/mysql_3306/data/binlog
# service mysql_3306 restart

mysqldump高级选项说明：

常用选项	描述说明
—flush-logs, -F	开始备份前刷新日志（二进制日志）binlog.000001 => binlog.000002
—flush-privileges	备份包含mysql数据库时刷新授权表 => 刷新用户和授权信息
—lock-all-tables, -x	MyISAM一致性，服务可用性（针对所有库所有表）
—lock-tables, -l	备份前锁表（针对要备份的库）
—single-transaction	适用InnoDB引擎，保证一致性，服务可用性
—master-data=2	表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行
—master-data=1	表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行

—master-data参数其他说明：

1）恢复时会执行，默认是1
2）需要RELOAD privilege并必须打开二进制文件
3）这个选项会自动打开—lock-all-tables，关闭—lock-tables

☆ 备份

前提：一定要开启二进制日志

# mysqldump --all-databases --master-data --single-transaction > /tmp/sqlbak/all.sql -p
Enter password:123

☆ 还原

# mysql < all.sql -p
Enter password:123

总结

mysqldump工具备份的是SQL语句，故备份不需要停服务
使用备份文件恢复时，要保证数据库处于运行状态
只能实现全库，指定库，表级别的某一时刻的备份，本身不能增量备份
适用于中小型数据库

四、mysqldump + binlog实现增量备份

1、核心思路

搞明白一件事：到底什么是增量备份？

答：① 要有全量备份 ② 继续增删改数据 ③ 再次需要备份时，不需要进行全量备份，只需要备份binlog日志文件即可（因为binlog日志记录了增删改操作的所有SQL语句）

2、增量备份实验步骤

第一步：先准备数据（前提）

第二步：开启二进制，然后做全量备份（全库备份）

第三步：继续对数据库进行增删改操作

第四步：突然发生了硬件故障，数据库丢失了

第五步：恢复全量备份导出的数据（不完整，可能只有90%）

第六步：备份二进制日志，根据其信息（导入剩余的10%的数据）

…

完成

3、增量备份的具体实践

第一步：准备数据

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table tb_student(
    id int not null auto_increment,
    name varchar(20),
    age tinyint unsigned default 0,
    gender enum('male','female'),
    subject enum('ui','java','yunwei','python'),
    primary key(id)
) engine=innodb default charset=utf8;
mysql> insert into tb_student values (null,'刘备',33,'male','java');
mysql> insert into tb_student values (null,'关羽',32,'male','yunwei');
mysql> insert into tb_student values (null,'张飞',30,'male','python');
mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');
mysql> insert into tb_student values (null,'大乔',18,'female','ui');

第二步：开启二进制日志，重启服务，然后进行全库备份

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/mysql_3306/data/binlog
# service mysql_3306 restart
# rm -rf /tmp/sqlbak/*
# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > /tmp/sqlbak/all.sql -p

第三步：继续对数据库进行增删改操作

mysql> insert into tb_student values (null,'小乔',16,'female','ui');
mysql> delete from tb_student where id = 3;

第四步：突然发生了硬件故障，数据库丢失了

# mysql -e "drop database db_itheima;" -p
Enter password:123

…故事开始了（删库）

第五步：动员运维工程师开始进行数据恢复，马上把最新的二进制文件进行备份

# cp /mysql_3306/data/binlog.000003 空格 /tmp/sqlbak/

第六步：先进行全库恢复

# mysql < /tmp/sqlbak/all.sql -p
Enter password:123

第七步：通过binlog增量备份还原数据到100%

学会读二进制日志文件，必须通过专业的工具

# mysqlbinlog /tmp/sqlbak/binlog.000003  =>  重点找事故的临界点，如drop database
确认at位置
# mysqlbinlog --start-position=4 --stop-position=740 /tmp/sqlbak/binlog.000003 |mysql -p

到此恢复100%数据

备份与恢复

MySQL备份与恢复

一、逻辑备份

1、回顾什么是逻辑备份

2、逻辑备份工具

3、逻辑的导出与导入

☆ 导出（数据备份）

☆ 导入（数据还原）

4、扩展：逻辑备份还原小工具（支持远程连接）

二、物理备份

1、xtrabackup备份介绍

㈠ xtrabackup优缺点

㈡ xtrabackup备份原理

㈢ xtrabackup备份恢复原理

2、获取xtrabackup备份工具

3、xtabackup软件的安装

4、xtrabackup全库备份与恢复

5、xtrabackup增量备份与恢复

MySQL备份与恢复（重点）

一、用户管理与权限管理

☆ 用户管理

1、创建MySQL用户

2、删除MySQL用户

3、修改MySQL用户

☆ 权限管理

1、权限说明

2、权限保存位置（了解）

3、给用户授权

4、查询用户权限

5、with grant option选项

6、使用grant创建用户

7、revoke回收权限

二、MySQL备份概述

1、关于数据保存你要知道

2、到底要备份什么

☆ 备份什么

☆ MySQL体系结构

☆ 存储引擎层（MyISAM与InnoDB引擎）

☆ 存储层（数据文件是如何进行存储的）

☆ 日志文件（MySQL中我们需要了解哪些日志）

3、备份过程须考虑的因素

4、备份的类型

☆ 逻辑备份

☆ 物理备份

☆ 在线热备（数据冗余、AB复制、主从复制）

5、备份工具

㈠ 社区版安装包中的备份工具

① mysqldump(逻辑备份，只能全量备份）

② mysqlhotcopy(物理备份工具）

㈡ 企业版安装包中的备份工具

㈢ 第三方备份工具

① XtraBackup和innobackupex(物理备份）

② mydumper(逻辑备份，备份SQL语句）

6、备份方法

三、MySQL逻辑备份

1、mysqldump基本备份

2、mysqldump基本语法

3、mysqldump表级备份与还原

☆ 备份

☆ 还原

4、mysqldump库级备份与还原

☆ 备份

☆ 还原

5、mysqldump全库级备份

☆ 备份

☆ 还原

总结

四、mysqldump + binlog实现增量备份

1、核心思路

2、增量备份实验步骤

3、增量备份的具体实践

㈠社区版安装包中的备份工具

㈡企业版安装包中的备份工具

㈢第三方备份工具