mysql备份还原

1.MySql备份

1.1备份mysql的脚本
在/usr/local/mysql/back/目录下创建备份脚本

#shell
export mysqldump_date=$(date +%Y%m%d_%H%M%S) &&
mysqldump --single-transaction -uroot -paiw123$%^ -A > /usr/local/mysql/back/$mysqldump_date.sql &&
gzip /usr/local/mysql/back/$mysqldump_date.sql
find /usr/local/mysql/back/ -name "*.sql" -mtime +30 -exec rm -f {} ;

-A代表所有数据库，也可以替换为database_name
export 在系统中自定义了个变量mysqldump_date，给备份和压缩命令使用
gzip 为压缩命令，默认压缩了之后会把源文件删除，压缩成.gz文件
find … 这行命令的意思为，查询/usr/local/mysql/back目录下，创建时间30天之前(-mtime +30)，文件名后缀为.sql的所有文件 执行删除命令-exec rm -f {} ;。总的意思就是：mysql的备份文件只保留30天之内的,30天之前的都删除掉。

1.2定时任务执行脚本 crontab
执行crontab -e命令修改成如下：

#每隔5分钟执行一次
0 /5 * * * ?  sh /usr/local/mysql/back/mysqldump.sh

数据恢复

mysql -uroot -p123456 shouma-sys < /usr/local/mysql/back/20211229_151645.sql

2.登录mysql查看是否开启了binlog

mysql -uroot -p123456

若为OFF，则需要开启bin-log，在/etc/my.cnf中增加如下配置：

[mysqld]
log-bin = /usr/local/mysql/back/logs/mysql-bin.log #日志目录
expire-logs-days = 14 #间隔天
max-binlog-size = 500M
server-id = 1
binlog_rows_query_log_events=ON #开启原始Sql

重启mysql，service mysqld restart。

登录mysql，查看binlog日志

show binary logs; 

查看当前正在写入的binlog

show master status;

查看binlog的内容

show binlog events in 'mysql-bin.000171';

刷新binlog

flush logs;

进入到binlog目录，查看binlog

cd /usr/local/mysql/back
mysqlbinlog --start-datetime='2021-12-29 11:00:00' --stop-datetime='2021-12-29 11:10:00 -d lilishop /usr/local/mysql/logs/mysql-bin.000173 > drop.txt

根据binlog信息，分析找到破坏数据的pos点，例如删除

3. 恢复数据：

若发生异常的时间点为图中点，则我们可以使用备份2和异常点中的binlog合并备份2的数据作为完整数据。

第一步：首先恢复最近的一个备份点数据：
第二步：恢复备份点到破坏点期间的binlog数据：

 `mysqlbinlog [options] file | mysql -uroot -proot_password database_name`

–start-datetime 开始时间，格式 2020-06-19 18:00:00
–stop-datetime 结束时间，格式同上
–start-positon 开始位置，（需要查看binlog文件）
–stop-position 结束位置，同上

mysqlbinlog --start-datetime='2021-12-29 15:16:00' --stop-datetime='2021-12-29 15:20:20' /usr/local/mysql/logs/mysql-bin.000176 | mysql -uroot -paiw123$%^ shouma-sys;

若发生异常的时间点为图中点，则我们可以使用备份2和异常点中的binlog合并备份2的数据作为完整数据。