Mysql(5日志备份恢复)

一.日志管理    

MySQL 的日志默认保存位置为 /usr/local/mysql/data

先看下mysql的日志文件有无：

修改配置文件添加：错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

修改配置文件添加：通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的

修改配置文件添加：二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

修改配置文件添加：慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

就可以看到了：

查看通用查询日志是否开启：

查看二进制日志是否开启：

查看慢查询日功能是否开启：

查看慢查询时间设置：

在数据库中设置开启关闭慢查询的方法：

开启：

关闭：

二.MySQL 完全备份与恢复

备份的目的：灾难恢复

造成数据丢失的原因：

程序错误，人为操作错误，运算错误，磁盘故障，自然灾难和盗窃。

从物理和逻辑角度，备份可分为：物理备份，逻辑备份。

物理备份：对数据库操作系统的物理文件

逻辑备份：对数据库逻辑组件

从数据库的背的策略角度，备份可分为：完全备份，差异备份，增量备份

完全备份：每次都会对数据库进行完整的备份

差异备份：备份自从上次完全备份之后被修改的文件

增量备份：只有上次完全备份或增量备份后备修改的文件才会被备份

常见的备份方法：

物理冷备：备份时数据库处于关闭状态，直接打包数据库文件，速度快，恢复简单

专用备份工具：mysqldump常用的逻辑备份工具

启动二进制日志进行增量备份：进行增量备份，需要刷新二进制文件

第三方工具备份：免费的mysql热备工具xtrabackup

实现完全备份：

物理冷备份与恢复：

先看下数据库的表数据：

关闭数据库：

将mysq根目录下的data目录打包并改名都爱其他目录下：

在开启一台服务器，将打好的包远程复制到新开的服务器上：

将test2服务器下data目录改名：

将opt下的data目录移动过来：

开启mysql：

已经完成完全备份了。

用mysqldump进行逻辑热备份数据库：

完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -uXXX -pXXX  --databases 库1 [库2 ....] > XXX.sql

先创建目录：

完全备份 MySQL 服务器中所有的库：

mysqldump -uXXX -pXXX  --all-databases > XXX.sql 

由于这样备份的文件内容太多，只想要看结构：

备份指定库中的所有的表数据，不包含库对象：

mysqldump -uXXX -pXXX  库名 > XXX.sql

备份指定库中的一个或多个表数据，不包含库对象：

mysqldump -uXXX -pXXX  库名 表1 [表2 ....] > XXX.sql

 

完全恢复

1.先登录到数据库，再执行 source XXX.sql （如果sql文件是只备份了表数据的文件，需要先创建库并 use 切换库后再执行）

先将表数据删掉点：

退出数据库，用命令删除库：

“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出

查看库：

恢复库：mysql -u root -p <xxx.sql

也可以查看库：

恢复数据表：

mysql -uXXX -pXXX 库名 < XXX.sql

先将库删除：

直接恢复表内容：

需要新建库，才可以恢复表数据：

查看表数据：

恢复成功。

三.MySQL 增量备份与恢复

在设置好日志的服务器下，通过刷新二进制日志间接实现增量备份：

mysqladmin -uXXX -pXXX flush-logs

一个文件默认最大存放空间为1G：

存放索引文件的文件：

查看二进制日志内容：

mysqlbinlog --no-defaults --base64-output=decode-rows -v <BINLcOG>

二进制日志(binlog)有3种不同的记录格式：statement(基于SQL语句)、row(基于行)、mixed(混合模式)，默认格式是statement。

给表添加内容：

这是sql语句模式：

可以看到什么时间哪个库的哪个表做了什么修改：

给配置文件添二进制文件配置：

给数据库添加内容：

使用二进制日志增量恢复：

mysqlbinlog --no-defaults <BINLOG> | mysql -uXXX -pXXX

在添加内容：

看下二进制文件内容：

恢复增量备份：

基于位置点恢复：

mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  <BINLOG> | mysql -uXXX -pXXX

基于时间的恢复：

将id=3删除