mysql表恢复报错binlog_利用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这个文件 所以我们要到有这个文件的那个路径下 再执行命令 路径在上面提到过！