利用binlog日志恢复mysql数据

测试环境

环境

度娘帮助查到Ubuntu的配置文件位置

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

配置文件位置.png

开启binlog

注意需要开启id 设置binlog_format模式

开启binlog.png

重启mysql

sudo service mysql restart

验证开启成功

sudo mysql
show variables like 'log_%;'

开启成功.png

建立数据库 表

建立数据库.png

插入数据

插入数据

查看表

模拟数据库的备份

查看现在的binlog的日志
show master status;

现在的日志

数据库会在每天凌晨4点进行一次完全备份的定时计划任务。这里手动执行下,将数据库备份。备份到/home/wg/下
sudo mysqldump -uroot -p -B -F -R -x --master-data=2 my_test | gzip > ./bak_$(date +%F).sql.gz
注意 date后面的空格。
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
备份

一个新的binlog日志

mysql-bin.000002 是用来记录之后对数据库的所有“增删改”操作
在前面的
可知

mysql-bin.000002的位置

未操作之前的mysql-bin.000002

日常操作数据库my_test

增加一条数据 修改一条数据


修改daming 增加xiaohong

然后删表drop table test1;

删表

恢复步骤(遇到很多问题,我在后面做了记录)

  • 利用bak_2019-03-27.sql.gz备份 恢复今天的初始数据
    找到备份的文件夹
    先解压 sudo gzip -d bak_2019-03-27.sql
    然后sudo mysql -uroot -p < bak_2019-03-27.sql

    解压 恢复

    然后查看表回到了开始的地方,但是还没有恢复到想要的
    查看恢复的

  • 先备份最后一个binlog日志
    sudo cp mysql-bin.000002 mysql-bin.000002.bak
    查看mysql-bin.000002
    show binlog events in 'mysql-bin.000002';

    找到

    通过分析,造成数据库破坏的pos点区间是介于 780--904 之间(这是按照日志区间的pos节点算的)。所以只要恢复到780就可以了 后面的904是操作drop的时间节点

  • 恢复语法
    恢复命令的语法格式:
    mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
    常用参数选项解释:
    --start-position=780 起始pos点
    --stop-position=904 结束pos点
    --start-datetime="2019-3-27 12:04:08" 起始时间点
    --stop-datetime="2019-3-27 12:49:46" 结束时间点
    --database=my_test 指定只恢复my_test数据库(一台主机上往往有多个数据库,只限本地log日志)

!!!!Ubuntu我没用熟悉吧 牢记 在mysqlbinlog和mysq前面都要加上sudo!!!!

For example 指定pos结束点恢复(部分恢复)
恢复到780节点
sudo mysqlbinlog mysql-bin.000002 --stop-position 780 | sudo mysql -uroot -p my_test


最后完美成功
欧克 完成.png

遇到的问题problem

第一问

Access denied for user 'root'@'localhost'问题

用mysql uroot -p 进入报错.png

我用sudo mysql 进入成功这样进入就报错 似乎没有加上sudo 不知道加上会不会成功,愚蠢的我居然没试过。如果加上还报错。那么可能就要修改数据库密码 如果sudo mysql 都进不去 可能出事密码为空导致。。。下面是修改密码步骤:
如果sudo mysql能进入请跳至步骤3
1.sudo mysql 不能进入 需要换一个账号进入 默认有一个-udebian-sys-maint账号 查看它的密码。
进入sudo vim /etc/mysql/debian.cnf
进入

得到密码

2.用-udebian-sys-maint账号登陆
sudo mysql -udebian-sys-maint -p

登陆进去了

3.进入mysql数据库查看user表。 如果是下面的样子

如果这样子

修改密码的sql
update user set authentication_string=password('你的密码') where user='root';
如果root那一栏是这样子
这样子

修改密码的sql
update user set authentication_string =password('你的密码'),plugin='msyql_native_password' where user='root';

第二问

报错2.png

就是因为把test1这个表删除了 所以先要用备份文件恢复test1.

第三问

报错3.png

找不到mysql-bin.000002这个文件 所以我们要到有这个文件的那个路径下 再执行命令 路径在上面提到过!

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容