数据库备份--体系结构四种日志文件及特点 错误 常规 慢查询日志数据库常用备份工具和数据恢复实验(冷备和物理备份)
数据库备份
1.MySQL 体系结构
MySQL属于c/s结构，服务端程序为mysqld，服务启动后，客户端程序从本地 或 远程连接服务端
常见的Client程序，
mysql、mysqldump、mysqlshow、mysqlbinlog等，
也包括通过MySQL API和连接器连接到MySQLD的各类编程语言程序
1.MySQLD 服务器进程 连接层 sql层 储存层 的存储特点
mysqld程序为服务器进程可以划分为下面三个层
连接层：处理连接
SQL 层：处理所连接的应用程序发送的 SQL 查询 日志
存储层：处理数据存储，MySQL数据可以按不同格式和结构 存储在不同物理介质上，也称为存储引擎
1.MySQLD 连接层
连接层可通过多种通信协议接受来自应用程序的连接：
TCP/IP
UNIX 套接字
共享内存
命名管道
每个客户端 连接服务器端 会启动一个线程来响应
通过 验证用户名 + 口令 + 客户端主机名或IP 对该连接进行验证
2.MySQLD SQL层
建立连接后，MySQL 服务器将处理以下进程：
授权和解析器：解析器验证语法是否正确，然后，授权验证是否允许所连接的用户运行特定查询 (已验证通过的用户是否有访问数据库对象的权限)
优化器：创建每个查询的执行计划，这是有关如何以最优化的方式执行查询的分步指令集；确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分
查询执行：完成每个查询的执行计划
查询高速缓存：(可选配置）可配置的查询高速缓存，可用于存储（并立即返回）执行查询的 结果集 (保存,提高速度效率)
查询日志记录：可以启用以跟踪执行的查询
3.MySQLD 存储层 存储引擎
MySQLD可以支持不同类型的“存储引擎”来存储数据
InnoDB 是默认存储引擎，支持事务、全文索引和外键约束，因此适用于各种混合查询。
其他存储引擎包括：
MyISAM：适用于频繁读取但很少更新的数据
MEMORY：在内存中存储所有数据
NDB (MySQL Cluster集群版支持）：供 MySQL Cluster 用来为高可用性数据提供冗余的可伸缩拓扑
ARCHIVE：适用于不更新偶尔查询的历史数据，提供压缩功能节约磁盘存储空间
存储引擎各个特点：
INNODB引擎：
默认存储引擎
支持事务
支持行级锁
支持外键
适用于具有数据一致性要求和并发读写要求的通用性业务
MyISAM引擎：
MySQL早期版本默认存储引擎
读取（select）速度快
不支持事务和外键
适用于 修改较少读取频繁且 对 数据一致性要求不高 的业务
Archive引擎：
数据压缩比非常高可以节约大量磁盘空间
不支持索引、事务和外键
支持insert、select 不支持update和delete
适用于 数据量大偶尔需要查询 的 历史归档业务数据
2.MySQL配置选项 显示所有选项 改变选项值 命令行 和mysql端
MySQL提供几百个选项（参数），许多选项都有默认值（预编译的值），如果没有指定选项值服务器启动就使用默认值
可以通过调整选项的值来改变MySQL的运行状态
显示MySQLD所有选项：
mysql> show variables;
或
root# mysqld --verbose --help
改变选项值的方式
1.命令行选项
root>mysqld --default_storage_engine=innodb;
root>systemctl set-environment MYSQLD_OPTS='--skip-grant-tables --skip-networking'
2.系统变量
mysql> show variables like 'character_set_server%';
mysql> set character_set_server='utf8';
通用格式：
set [global | session] var_name=var_value
global全局选项 对所有新连接会话生效 下一个会话受影响，session对当前会话生效 只影响当前
选项有些不能直接改值，需要把选项和值放到选项文件中，重启MySQLD才能生效
选项文件 /etc/my.cnf
通过set [global | session] var_name=var_value 修改的选项，
在重启mysqld后，还原回原来的默认值，这样不方便；一个一个修改选项值容易出错也不方便，
因此mysql提供选项文件/etc/my.cnf，方便批量修改选项文件
选项使用优先级
1.命令行选项 2. 选项文件 3.默认值
mysqld重启时
先看 有没有 命令行选项，如果有 优先使用
如果没有， 查看/etc/my.cnf中有没有，如果有使用
如果没有， 使用默认值
查看选项文件中为mysqld设置了哪些选项
root# my_print_defaults mysqld
2.MySQL 四种日志文件及特点 错误 常规 慢查询日志
MySQL 使用多种日志文件来记录服务器的运行活动，
常见的日志包括：
错误日志 (error log)
记录mysqld启动、运行和停止过程中遇到的问题
日志位置
mysql> show variables like 'log_error';
常规日志 (general query log)
记录客户端连接 以及服务器从客户端收到的 各类SQL语句
慢查询日志 (slow query log)
记录运行时间超过选项long_query_time设定阈值的查询语句 缺省为10s
二进制日志 (binary log)
记录 使数据库数据 产生变化的 各类语句
日志文件的特点：
有可能会占用大量磁盘空间
通常存储在文件中
可以选择存储在mysql的表中，便于查询和分析
仅限于常规查询和慢查询日志
除了二进制日志，通常以文本格式记录数据
1.MySQL 错误日志 默认开启 日志位置
默认开启
日志位置
mysql> show variables like 'log_error';
查看所有日志
show variables like '%log%';
2.MySQL 常规日志 默认关闭 日志位置 开启和关闭常规日志
默认关闭
日志位置
mysql> show variables like 'general_log%';
show variables like '%log%';
默认名称：hostname.log
开启常规日志
mysql> set global general_log=on; 只是数据库开启（永久开启 同时修改/etc/my.cnf以保存服务重启后状态）
测试
mysql> select count(*) from mysql.user;执行查询
root# cat /var/lib/mysql/主机名称.log
mysql> set global general_log=off; 关闭
开启 和 关闭
查看
3.MySQL 慢查询日志 默认关闭 日志位置 开启和关闭慢查询日志
默认关闭
日志位置
mysql> show variables like 'slow_query_log%';
mysql> show variables like 'long_query_time%';
默认名称：hostname-slow.log
long_query_time默认时长10秒，执行超过10秒的查询语句会记录到慢查询日志中
开启慢查询日志
mysql> set global slow_query_log=on;永久保存修改 /etc/my.cnf 以保存服务重启后状态
测试
mysql> select benchmark(1000000000,100*100);使用函数来查询
#benchmark函数用于测试查询执行时间，第一个参数执行次数，第二个参数执行命令，结果都是0，主要看运行时间
root# cat /var/lib/mysql/hostname-slow.log
mysql> set global slow_query_log=off; 关闭
4.MySQL 二进制日志BINLOG 默认关闭 两个用途库复制 和恢复
内容：
BINLOG记录数据库的变更过程。
例如创建数据库、建表、修改表等DDL操作、以及数据表的相关DML操作，这些操作会导致数据库产生变化，开启binlog以后导致数据库产生变化的操作会按照时间顺序以“事件”的形式记录到binlog二进制文件
默认关闭
binlog有两个用途：
数据库复制
数据恢复
1.BINLOG用途–数据库复制
用户对主数据库SQL语句操作中的 DML、DDL、DCL、TCL 等语句 会对数据库的状态产生 变化操作，
变化以事件的形式按照时间顺序记录到主数据库的BINLOG二进制日志中
在建立从属数据库时，为了复制主库的数据使从库和主库一模一样，
主库需要开启BINLOG日志，然后配置从属数据库，将主库产生的BINLOG日志定期通过网络传递到从数据库
从数据库库接受到主库传递过来的BINLOG后，按照日志中记录的变更事件重新应用日志
利用BINLOG日志最终起到从库和主库数据保持一致的状态， 即复制了和主数据库一模一样的从数据库
2.MySQL BINLOG用途–数据库恢复
1.MySQL 开启BINLOG
默认没有开启BINLOG
查看BINLOG是否开启
mysql> show variables like '%log_bin%';
如果log_bin的值为OFF 说明没有开启binlog
开启BINLOG
root# vi /etc/my.cnf在[mysqld]下面添加下面2行内容
server-id=1 mysql5.7以上需要添加 必须有sever-id 启动服务时给一个编号 否则会报错
log-bin=/binlogs/mysql-bin创建目录/binlogs
root# systemctl restart mysqld
mysql> show variables like '%log_bin%';
## 可关闭selinux
vim /etc/selinux/config
如果log_bin的值为ON说明binlog已开启
root# ls -l /var/lib/mysql/mysql-bin*
分开放 binglog 安全
2.BINLOG记录格式
binlog记录格式分为：
语句级（statement）
行级 （row） (5.5, 5.6, 5.7版本默认)
混合级 （mixed）
mysql> show variables like '%binlog_format%';
基于语句（STATEMENT）的二进制日志记录：
包含实际 SQL 语句
包括 DDL（CREATE、 DROP 等）和 DML（UPDATE、 DELETE 等）语句
日志 占用相对较小的磁盘空间 和网络带宽
并非所有主库的的语句都能在远程从库上正确应用，
例如一些包括now(),sleep(),uuid()等函数的语句在从库上无法做到和主库一致，数据和主库产生偏差
要求主库和从库上复制的表和列完全相同
基于行（ROW）的二进制日志记录：
日志占用相对较大的磁盘空间和网络带宽
所有主库的的语句都能在远程从库上正确应用 (复制数据库时最安全)
要求从库上MySQL版本和行结构与主库必须完全相同
DDL、DML、DCL、TCL等语句对应的事件部分采用二进制格式
从日志中查找特定语句对应的事件难度相对较高
混合级（mixed）的二进制日志记录：
由 MySQL 选取最适合单个事件的格式，基于语句还是基于行来记录事件
MySQL 通常会使用基于语句的二进制日志，但在需要时可恢复到基于行的复制
mysql> show binary logs;列出 所有binlog日志
mysql> show master status;列出 当前binlog日志
3.BINLOG日志切换
下列3种情况会导致BINLOG日志切换：
1： MySQL 启动或重启
2： 日志量到达了max_binlog_size的设定值
3： 执行flush logs; 命令手动切换日志
mysql> flush logs;
show binary logs;
查看日志
4.查看BINLOG内容
binlog以紧凑的二进制方式存储
日志中包含数据和表结构更改事件及其时间戳
无法使用普通的文本查看软件查看其内容
MySQL提供mysqlbinlog 工具
可将二进制数据转成SQL文本格式
在标准输出中查看binlog内容
root# mysqlbinlog /var/lib/mysql/mysql-bin.000001
结合more命令查看binlog内容
root# mysqlbinlog /var/lib/mysql/mysql-bin.000001 | more
将binlog内容的SQL语句重定向到文件
root# mysqlbinlog /var/lib/mysql/mysql-bin.000003 > /root/3.sql
mysqlbinlog 输出中，事件前面会提供相关信息的头注释：
cat /root/3.sql
两个 at 之间就是一个事件记录
查看BINLOG内容练习
mysql> flush logs; #切换日志
mysql> show binary logs; #查看所有日志
mysql> show master status; #当前日志
mysql> use test; #切换到test数据库
mysql> create table k(id int); #创建测试表t
mysql> insert into k values(1),(2),(3); #插入3条测试数据
mysql> select * from k;
root# cd /var/lib/mysql
root# mysqlbinlog mysql-bin.000005 > /root/5.sql
#获得use test在日志中的行号
root# cat /root/4.sql | grep -ni "use `test`"
#获得创建表t在日志中的行号
root# cat /root/4.sql | grep -ni "create table t"
查看binlog中部分内容，自定义起始和结束头标注
mysqlbinlog --start-position=219 --stop-position=546 /var/lib/mysql/mysql-bin.000005
5.查看BINLOG中的事件
查看第一个binlog日志中的事件
mysql> show binlog events;
查看指定binlog日志中的事件
mysql> show binlog events in 'mysql-bin.000005';
查看指定binlog日志中的事件，指定事件起始头注释
mysql> show binlog events in 'mysql-bin.000005' from 219;
5.常见MySQL日志用途总结
错误日志(error log)：
作用：启动关闭运行过程错误排查
记录内容：
错误日志 包括mysqld服务器 启动和关闭过程中 的诊断、报错、告警和提示信息；
在服务器运行过程中，错误日志也记录，用户连接报错信息，
以及例如：某些数据表需要做维护等的提示信息。
常规日志(general query log)：
作用：记录客户端发给服务器端执行的所有语句，有助于客户端问题排查
记录内容：
常规日志记录mysqld服务器的活动，内容包括：用户连接，断开连接，客户端发给服务器执行的每一条SQL语句。
如果你怀疑客户端存在问题，想知道客户端究竟传给服务器什么语句， 开启常规日志会非常有用。
慢查询日志(slow query log)：
作用：记录执行速度慢的查询语句，用于定位查询语句优化的候选对象
记录内容：
慢查询日志记录执行时间超过long_query_time指定时间的查询语句。
可以用来查找执行时间较长的查询语句，作为查询语句优化的候选对象。
二进制日志(binary log)：
作用：数据库复制与数据恢复
记录内容：
二进制日志记录数据库写操作产生的相关事件，
例如：DDL DML语句中的增删改操作等语句对数据产生的变化，也包括每个语句相应的数据更新用了多长时间等信息。
二进制日志有两个主要用途：
1.数据库复制 2.数据恢复
3.MySQL备份
1.MySQL 数据库 备份术语 备份分三类
备份术语 8种
物理备份
逻辑备份
基于数据库复制的备份
完全备份
不完全备份
热备
温备
冷备
1.分类1 – 数据文件的格式 物理备份 逻辑备份 基于数据库备份
1.1物理备份
生成数据库文件的完整副本（二进制），可以使用标准命令，如 cp、tar、xcopy、windows图形复制粘贴
备份可以在不同的计算机体系结构间还原。
例如：linux的mysql还原给windows的mysql
比逻辑备份和还原的速度快
数据库文件在物理备份期间不能有更改，因为要保证数据的一致性
对于默认引擎为innodb的数据库需要停止MySQL服务后再进行物理备份（冷备）
Dmeo 数据库 备份到root目录下
1.2逻辑备份
将数据库和表转换为一个文本文件，里面包括可以重构数据库和表的SQL语句
可以使用该文本文件在运行不同体系结构的其他主机上重新装入数据库
要求 MySQL 服务器在备份期间运行 (不能冷备)
可以备份本地和远程 MySQL 数据库服务器
通常比物理备份（二进制）的速度慢
逻辑备份文件的大小 可能会 超过所备份的数据库物理文件大小
只有 creatr inster 两个语句
1.3基于数据库复制的备份
创建一个 主库的复制库从库， 主库作为生产库，从库作为备份库
主库定期向从库传递binlog文件，并在从库应用保证从库和主库的一致性
从库也可以做物理备份或逻辑备份
缺点：
成本较高，因为必须有另一台服务器和存储设备用于从库
从库相对于主库会有延迟
2.分类2 –备份的完整度 完全备份 不完全备份
完全备份 所有库备份
备份所有数据库文件：/var/lib/mysql/*
备份所有binlog文件: /var/lib/mysql/mysql-bin.*
备份选项文件: /etc/my.cnf
不完全备份
仅仅备份 部分数据库 的文件
3.分类3 –按照 MySQL服务器的状态 热备 温备 冷备
1.热备
数据库不关闭， 在仍然有用户读取或修改数据的过程中进行备份
热备 不阻止用户正常的数据库操作，有些热备工具甚至能捕获备份进行期间发生的更改
并不是所有引擎都支持热备，innodb引擎可以支持热备，但MyISAM引擎不能热备，可以温备和冷备
2.温备
数据库不关闭，处于只读模式，备份可以在用户读取数据时进行
温备优点是不必完全锁定数据库访问用户，其不足之处在于用户无法在进行备份时修改数据库的数据
3.冷备
关闭数据库，备份在用户不能访问数据时进行，因此用户无法读取或修改数据
冷备会阻止执行任何使用数据的活动，如果备份时间较长，会造成用户较长的时间里无法访问数据
MySQL 数据库常用备份工具
1.)cp、tar、xcopy (windows) 或 复制粘贴
属于操作系统自带的 物理备份工具
用于冷备或温备数据库 可以温备MyISAM引擎，对于innodb引擎必须关闭数据库
/var/lib/mysqlmysql数据库文件位置
systemctl stop mysqld关闭数据库服务
cp -a /var/lib/mysql /backup备份整个数据库目录到/backup
注：cp 的 -a 选项代表复制目录同时保留复制对象的属主、属组和权限，这样还原备份时，不用修改文件或目录的属性
还原备份时：
mv /var/lib/mysql /var/lib/mysql.old 先把原来的数据目录改名
cp -a /backup/mysql /var/lib
复制过程锁表（不关库）：
mysql> flush tables with read lock;所有表变成只读模式，可以开始温备innodb引擎外的表
温备完了，解除只读模式
mysql> unlock tables;
2.)mysqldump
属于mysql提供的逻辑备份工具
将数据库的内容 转储到文本文件
可以指定所有数据库、特定数据库或特定表
可以备份本地或远程数据库
和存储引擎无关
适合数据量较小的数据库数据导出
不能关闭数据库，因此备份策略和时机可以灵活掌握
基本用法
备份当前数据库的testdb数据库到db_testdb.sql文件
root# mysqldump -uroot -p --opt testdb > db_testdb.sql
mysqldump确保备份一致性
备份过程中客户可能仍然对数据库表在做修改
mysqldump提供相关选项保证备份的相关数据库处于同一个版本
一致性选项
--master-data=2
备份过程锁住所有表，禁止select之外的所有语句
会把备份时刻的头标注和当前binlog文件名写到备份文件中
root# mysqldump -uroot -p --master-data=2 testdb > db_testdb1.sql
--master-data=2 和 --single-transaction 一起用 (备份事务引擎库)
对于innodb引擎的数据表，可以在备份时确保一致性
--single-transaction选项开启一个新事务进行备份，事务读一致性确保备份的多个表属于一个版本（热备）
对于不支持事务的其他引擎不确保备份的一致性
--lock-all-tables
锁定所有数据库的所有表来确保备份一致性（温备）
--flush-logs
先切换binlog日志，再开始备份
删除选项：
--add-drop-database
将drop database语句添加到每个create database之前，还原时会先删除已有数据库再创建
--add-drop-table
将drop table语句添加到每个create table语句之前，还原时会先删除已有数据表再建表
备份MySQL 编程组件：
--routines 导出存储过程和函数到备份文件
--triggers 导出触发器到备份文件
默认最高选项 --opt
--opt选项相当于同时使用以下选项：
--add-drop-table
--add-locks
还原时给insert语句前加独占写锁，写入时不许其他用户更新数据，写入完成解锁
--create-options
添加所有数据对象的create语句,如：database,table
--quick
不把备份过程中的SQL语句放到查询缓冲区中，输出到标准输出
--extended-insert
使用多行插入语法，例如：insert into t values(1),(2),(3)...
--lock-tables
给备份过程中遇到的每个表加只读锁，备份时其他修改表的用户要等待该表备份完成
--set-charset
添加set names default_character_set到输出文件
--disable-keys
逻辑备份
添加disable keys和enable keys到备份输出文件，还原插入记录时，插入完成后再建立索引提高还原效率
mysqldump常用备份语句
备份所有数据库，包括存储过程、触发器、事件
mysqldump -uroot -p --all-databases --master-data=2 --routines --triggers --events --flush-logs > /root/all-db.sql
备份指定一个或多个数据库
mysqldump -uroot -p --databases test demo world > /root/dbs.sql
备份指定一个数据库的一个或多个表
mysqldump -uroot -p demo stu class bmi > /root/db_demo_tables.sql
还原mysqldump备份
使用 mysql 命令重新装入 mysqldump 备份
db_test.sql 是包括了test数据库的备份，其中表k备份时包括了1,2,3三条记录
mysql>use testdb;
mysql> insert into k values(4),(5),(6); 再插入3条记录
mysql> select * from k;
root# mysql -uroot -p testdb < /root/db_testdb.sql
mysql> use test;
mysql>select * from k;
test数据库回到了原来备份时的状态
使用 mysql 的source命令执行备份(里面都是SQL语句)
示例：
db_test.sql 是包括了test数据库的备份，其中表t备份时包括了1,2,3三条记录
mysql>use test;
mysql> insert into t values(4),(5),(6); 再插入3条记录
mysql> select * from t;
mysql> source /root/db_test.sql;
mysql> use test;
mysql>select * from t;
test数据库回到了原来备份时的状态
4.数据库恢复 分类
恢复数据库需要 运用binlog日志
数据库各个时间点的变更已经按照时间顺序记录到了binlog日志中
恢复的过程主要是应用（重做）binlog日志中记录的变更
在应用binlog日志的过程中，数据库中的数据逐渐从一个时间点的变化到较新的时间点
恢复分类
完全恢复
从还原时的状态恢复到最后一个日志的最后一条语句
不完全恢复
从还原时的状态恢复到指定日志的某个位置
成功恢复的条件
要有备份 (非常重要，没有备份意味着存在数据的永久丢失的风险！)
日志完整 (没有日志意味着数据库只能回到备份时间点的状态)
数据恢复实验1 冷备 不完全恢复
特点：冷备不完全恢复
场景：
当前库在停库时，做了完全冷备。
有人误删除了linux生产机的demo库，生产库不能停机，把demo库在windows测试机上恢复回来，然后导出demo库，再导回到linux生产机
1.冷备虚拟机mysql全部数据库
systemctl stop mysqld
mkdir /root/backup
cp -a /var/lib/mysql /root/backup
如果binlog存放的位置不在默认位置,还需要备份binlog日志
2.重启虚拟机mysql服务然后连接
systemctl start mysqld
mysql -uroot -p
3.切换日志生成测试数据
mysql> flush logs;
mysql> use demo
mysql> create table a like stu;
mysql> insert into a select * from stu where cno=1;
mysql> select * from a;
此时x表中的数据在冷备中不存在
4.切换日志，然后模拟误删除demo库
mysql> flush logs;
mysql>drop database demo;
5.在日志中找到误删除demo时的日志编号和位置号
mysql> show binary logs;
mysql>show binlog events in 'mysql-bin.000008';
记下删除时候的 日志号 和 位置号 219
6.生成恢复sql语句
root# mysqlbinlog mysql-bin.000006 mysql-bin.000007 > /root/recover.sql
root# mysqlbinlog --stop-position=219 mysql-bin.000008>> /root/recover.sql
7.发送到windows端，mysql备份冷备 和 recover.sql
Windows端mysql关闭服务，导入冷备到 windos 端文件目录 将mysql改名为data目录
将mysql拖到 目录下 改名为data 之前data改为data1 备份
将root下recover.sql 也拖过去
启动服务
Mysql登录
密码为服务端root的密码
8.启动windows mysql服务，然后登录mysql
mysql> show databases;
mysql> source f:\MySQL157\recover.sql
8.windows读取 source 记录
Windows cmd下导出 demo库到 demo.sql
mysqldump -uroot -p --databases demo>c:\demo.sql
9.上传demo.sql到linux虚拟机
10.Linux虚拟机中source demo.sql，然后检验恢复成果
mysql>source /root/demo.sql
mysql> show databases;
mysql> use demo
mysql> select * from x;
数据恢复实验2（选做）特点：物理完全冷备 完全恢复
业务场景描述
管理员做了数据库的全备（冷备全备），
启动数据库以后，用户继续使用数据库，正常的DDL、DML等操作。数据库突然不能正常使用了，硬盘损坏，导致数据文件全备丢失，值得庆幸的是数据库全备和二进制日志存放在其他硬盘对应的目录上，完好无损，现在需要管理员把数据从全备还原，然后恢复数据到损毁前的状态
1.建立数据库备份（物理备份，冷备）
root# systemctl stop mysqld
root# cp -a /var/lib/mysql /backup
root# cd /backup/mysql
root# ls
root# ls /binlogs
注：此时知道备份时日志走到了mysql-bin.000001
2.记录备份时日志序号
root# touch /backup/mysql/000001
注：记录日志序号是为了记录冷备时日志走到了哪个日志，这样恢复时就知道从这个日志起的下一个日志号开始恢复，省得恢复时选择起始日志需要对照日志和备份文件的修改时间才能知道需要重做的起始日志号（本例中冷备并没有备份二进制日志）
root# ls /backup/mysql
3.插入测试数据
root# systemctl start mysqld
root# mysql -uroot -p
mysql> use testdb;
mysql> insert into values(4),(5),(6);
mysql> select * from k;
###
truncate table t;截断删除内容、释放空间但不删除定义，也就是数据表的结构还在。与drop不同的是,它只是清空表数据而已,它比较温柔。
4.切换日志模拟时间在往前走
mysql> flush logs;切换日志
mysql> show binary logs;启动数据库会切换1次新日志
mysql> show master status;查看当前日志
注：当前日志走到了3号
5.添加一个用户用于测试
mysql> create user ujiuye@localhost identified by '123456';
mysql> use mysql;
mysql> select user,host,authentication_string from user;
注：做备份的时候没有ujiuye这个用户，用户存放在mysql库的user表中
6.切换日志模拟时间往前走
mysql> flush logs;
mysql> show binary logs;
mysql> show master status;
7.模拟数据库损坏
root# rm -rf /var/lib/mysql 小心操作别删错了目录
mysql> quit
root# mysql -uroot -p 重新连接失败
远程登录可以
注：此时数据文件已损毁，但mysqld进程还存活，查看mysqld进程并杀掉
ps -ef | grep -v grep | grep mysqld 查看mysqld进程
killall -9 mysqld 多用几次这个命令，杀掉mysqld进程
ps -ef | grep -v grep | grep mysqld 确保无输出，即mysqld已杀掉
8.还原之前冷备份
注：刚才杀mysqld进程时，该进程会尝试重启，会在/var/lib/mysql下创建默认系统表空间及其innodb引擎的重做日志，可以先将/var/lib/mysql目录删除
root# rm -rf /var/lib/mysql
root# cp -a /backup/mysql /var/lib
9.找出需要恢复的起止日志
root# ls /var/lib/mysql 备份时的日志是几号？
注：此时备份已经还原，备份时日志是1号，应该从2号日志开始恢复
root# ls /binlogs 查看出故障时日志走到了几号？
注：查看二进制日志存放目录，找编号最大的日志，即，出故障时日志走到了4号日志，得出需要恢复的日志是从2号到4号
10.重启数据库（数据回到冷备时刻状态）
systemctl restart mysqld
select user,host from mysql.user;
show binary logs;
root# ls /binlogs
重启后会多出一个7号日志（mysqld重启会切换日志）
root# systemctl start mysqld
root# ls /binlogs
注：恢复时只恢复从2号到6号，这部分是需要重做的，如果到7号没有必要
11.查看还原时数据状态 查看有无456 和新创建的用户ujiuye
mysql -uroot -p
use testdb;
select * from k;
select user,host,authentication_string from mysql.user;
注：备份时test库t表有3条记录，mysql库user表中无ujiuye用户
12.恢复数据到故障之前状态
注：前述步骤已经知道需要按照顺序恢复从2号到4号日志中记录的SQL语句，可以使用mysqlbinlog命令把二进制中的SQL显示出来，以下两种方式均可：
本例采用方式1的方式
quit
cd /binlogs
mysqlbinlog mysql-bin.00000{2..6} | mysql -uroot –p
mysql –uroot –p
注：恢复完成，再次进入mysql环境
方式1：
root# mysqlbinlog mysql-bin.00000{2..6} | mysql -uroot -p
或者
root#mysqlbinlog mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 |mysql –uroot -p
注：使用管道符将2到4号的日志内容交给mysql 执行应用（重做） 按照日志备份 依次排列即可
方式2：
root# mysqlbinlog mysql-bin.00000{2..6}>/backup/recover.sql
mysql> source /backup/recover.sql
注：先转存到恢复文件recover.sql，再到mysql环境中执行
13.登录验证恢复结果
mysql> use test;
mysql> select * from k;
mysql> select user,host,authentication_string from mysql.user;
14.实验总结
实验的过程展示了数据库故障时利用物理备份进行数据恢复的过程，
最重要是 满足 有备份且完整，和二进制日志没有缺失
这两个条件，就能把数据找回来。实验步骤是按照以下图示中描述的内容完成的。
数据恢复可总结为两点，首先判断能否恢复，然后再做具体恢复，相关要点如下所示：
判断能否恢复
1）遇到故障时先查看是否有备份；无备份即数据丢失
2）检查日志是否完好无损且连续
3）有备份且日志齐全可做恢复
4）有备份但日志缺失，数据可以还原到备份时的状态，丢失部分数据
主要恢复步骤:
1）修复硬件
2）还原备份
3）查看还原时数据状态
4）计算出需要恢复的起止日志
5）生成数据恢复SQL语句
6）重做恢复SQL语句执行恢复
7）检查恢复后数据
