山东 网站建设,青海公路建设市场信用信息服务网站,在线3d建模网站,邯郸招聘信息最新招聘MySQL的binlog日志是MySQL日志中非常重要的一种日志#xff0c;记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。
定期备份固然可以在服务器发生宕机的时候快速的恢复数据#xff0c;但传统的全量备份不可…MySQL的binlog日志是MySQL日志中非常重要的一种日志记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。
定期备份固然可以在服务器发生宕机的时候快速的恢复数据但传统的全量备份不可能做到实时所以在发生宕机的时候也会损伤一部分数据如果这个时候开启了binlog日志那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复。如果了解Redis的朋友可能想到了Redis有两种持久化方式分别是AOF和RDB。RDB就类似于MySQL的全量复制AOF就类似于MySQL的binlog。
关于数据的恢复这里在说一点既然binlog这么好是不是开启了binlog就不需要定期做备份了呢不要这样做。为什么呢因为binlog的数据量非常大另外就是使用binlog做数据的恢复性能会非常低。因为binlog是对操作的记录比如某一时刻我先插入了一条数据然后将这条数据删除了本身数据是没了但有两条操作。如果是全量备份肯定没有这条数据如果使用binlog需要执行一条插入和一条删除操作因此性能和文件大小都是比较大的。
啰嗦了一堆下面说关于数据恢复的问题
先创建一个数据库一个表再插入两行数据
mysql create database mytest; ERROR 1007 (HY000): Cant create database mytest; database exists mysql use mytest; Database changed mysql create table t1(id int ,name varchar(20)); Query OK, 0 rows affected (0.02 sec)
mysql insert into t1 values (1,xiaoming); Query OK, 1 row affected (0.02 sec)
mysql insert into t1 values (2,xiaohong); Query OK, 1 row affected (0.01 sec)
备份数据库
[mysqlt3-dtpoc-dtpoc-web04 mysql]$ cd /home/mysql/mysql/bin [mysqlt3-dtpoc-dtpoc-web04 bin]$ ./mysqldump -uroot -p -B -F -R -x --master-data2 mytest | gzip /testdata/mysql/backup/bak_$(date %F).sql.gz Enter password: [mysqlt3-dtpoc-dtpoc-web04 bin]$ cd /testdata/mysql/backup/ [mysqlt3-dtpoc-dtpoc-web04 backup]$ ls bak_2023-08-22.sql.gz
参数说明
-B指定数据库
-F刷新日志 执行一下flush logs
-R备份存储过程等
-x锁表
--master-data在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
这样呢我们就把数据做了一个完整的备份。下面来删除数据库然后通过备份数据进行恢复数据库。
[mysqlt3-dtpoc-dtpoc-web04 backup]$ gzip -d bak_2023-08-22.sql.gz [mysqlt3-dtpoc-dtpoc-web04 backup]$ ls bak_2023-08-22.sql
[mysqlt3-dtpoc-dtpoc-web04 bin]$ ./mysql -uroot -p /testdata/mysql/backup/bak_2023-08-22.sql Enter password:
这样我们就把数据导入到库里了。
继续上面的操做我们新增xiaoli和xiaozhao这两条数据并把xiaozhao这条记录删除掉。
在删除之前我们先来刷新binlog日志生成一个新的日志那么我们之后所要操做的内容都会被记录到新的日志文件中。通过前面binlog日志的详细说明我们知道每次刷新和服务重启的时候都会生成一个binlog日志文件。 mysql flush logs; Query OK, 0 rows affected (0.01 sec)
mysql show master status; ------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 154 | | | | ------------------------------------------------------------------------------- 1 row in set (0.00 sec) 我们注意binlog的文件是0003位置是在154这两个信息很重要
下面我们来做插入和删除操作
mysql insert into t1 values (3,xiaoli); Query OK, 1 row affected (0.00 sec)
mysql insert into t1 values (4,xiaozhao); Query OK, 1 row affected (0.01 sec)
mysql select * from t1; ---------------- | id | name | ---------------- | 1 | xiaoming | | 2 | xiaohong | | 3 | xiaoli | | 4 | xiaozhao | ---------------- 4 rows in set (0.00 sec)
这个时候我们应该是来查看一下binlog日志的状态以便与我们一会来进行恢复到此状态但是真正的环境中我们并不知道这个状态因此这里也就不去查看这个状态了这个状态的值可以通过后面查看binlog日志文件来进行分析。下面我们开始误操作
我们来把xiaozhao删除掉 mysql delete from t1 where id4; Query OK, 1 row affected (0.00 sec)
这样数据就删除掉了下面我们再来查看binlog的状态
show master status; mysql show master status; ------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 1776 | | | | ------------------------------------------------------------------------------- 1 row in set (0.00 sec) 这个时候我们发现我删除操作是个错误的操作要进行恢复那么该如何恢复呢这个时候我们就可以通过binlog的position来进行恢复。 在进行其他的处理之前我们建议马上再执行一次flush logs也就是让出错的部分就集中在这么一个binlog日志文件中。
我们来查看0003的binlog日志。 mysql show binlog events in mysql-bin.000003; -------------------------------------------------------------------------------------------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | -------------------------------------------------------------------------------------------------------- | mysql-bin.000003 | 4 | Format_desc | 123454 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 123454 | 154 | | | mysql-bin.000003 | 154 | Anonymous_Gtid | 123454 | 219 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000003 | 219 | Query | 123454 | 293 | BEGIN | | mysql-bin.000003 | 293 | Rows_query | 123454 | 351 | # insert into t1 values (1,xiaoli) | | mysql-bin.000003 | 351 | Table_map | 123454 | 401 | table_id: 130 (mytest.t1) | | mysql-bin.000003 | 401 | Write_rows | 123454 | 448 | table_id: 130 flags: STMT_END_F | | mysql-bin.000003 | 448 | Xid | 123454 | 479 | COMMIT /* xid191 */ | | mysql-bin.000003 | 479 | Anonymous_Gtid | 123454 | 544 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000003 | 544 | Query | 123454 | 618 | BEGIN | | mysql-bin.000003 | 618 | Rows_query | 123454 | 676 | # delete from t1 where namexiaoli | | mysql-bin.000003 | 676 | Table_map | 123454 | 726 | table_id: 130 (mytest.t1) | | mysql-bin.000003 | 726 | Delete_rows | 123454 | 773 | table_id: 130 flags: STMT_END_F | | mysql-bin.000003 | 773 | Xid | 123454 | 804 | COMMIT /* xid192 */ | | mysql-bin.000003 | 804 | Anonymous_Gtid | 123454 | 869 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000003 | 869 | Query | 123454 | 943 | BEGIN | | mysql-bin.000003 | 943 | Rows_query | 123454 | 1001 | # insert into t1 values (3,xiaoli) | | mysql-bin.000003 | 1001 | Table_map | 123454 | 1051 | table_id: 130 (mytest.t1) | | mysql-bin.000003 | 1051 | Write_rows | 123454 | 1098 | table_id: 130 flags: STMT_END_F | | mysql-bin.000003 | 1098 | Xid | 123454 | 1129 | COMMIT /* xid193 */ | | mysql-bin.000003 | 1129 | Anonymous_Gtid | 123454 | 1194 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000003 | 1194 | Query | 123454 | 1268 | BEGIN | | mysql-bin.000003 | 1268 | Rows_query | 123454 | 1328 | # insert into t1 values (4,xiaozhao) | | mysql-bin.000003 | 1328 | Table_map | 123454 | 1378 | table_id: 130 (mytest.t1) | | mysql-bin.000003 | 1378 | Write_rows | 123454 | 1427 | table_id: 130 flags: STMT_END_F | | mysql-bin.000003 | 1427 | Xid | 123454 | 1458 | COMMIT /* xid194 */ | | mysql-bin.000003 | 1458 | Anonymous_Gtid | 123454 | 1523 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000003 | 1523 | Query | 123454 | 1597 | BEGIN | | mysql-bin.000003 | 1597 | Rows_query | 123454 | 1646 | # delete from t1 where id4 | | mysql-bin.000003 | 1646 | Table_map | 123454 | 1696 | table_id: 130 (mytest.t1) | | mysql-bin.000003 | 1696 | Delete_rows | 123454 | 1745 | table_id: 130 flags: STMT_END_F | | mysql-bin.000003 | 1745 | Xid | 123454 | 1776 | COMMIT /* xid196 */ | -------------------------------------------------------------------------------------------------------- 32 rows in set (0.00 sec)
我们看到delete_rows 结束点是1776这个点起始点是在1523这个点我们可以把操作的这些数据删除到我们上次备份的内容然后通过执行binlog来进行恢复当然恢复到1523这个点之前。 比如我上次备份的是整个数据库我就可以把整个数据库删除然后通过备份文件恢复然后再通过binlog做增量恢复。这样数据就回来了。这里就不再进行删库了我们直接演示使用binlog日志恢复数据的方法 语法如下
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
比如我们要把所有的操作都恢复不包括我们的删除我们知道删除是在1523点上
[mysqlt3-dtpoc-dtpoc-web04 bin]$ ./mysqlbinlog /testdata/mysql/log/bin/mysql-bin.000003 --start-position 154 --stop-position 1523 | ./mysql -uroot -p mytest Enter password:
mysql select * from t1; ---------------- | id | name | ---------------- | 1 | xiaoming | | 2 | xiaohong | | 3 | xiaoli | | 3 | xiaoli | | 4 | xiaozhao | ---------------- 5 rows in set (0.00 sec) 、 我们发现xiaozhao又回来了当然了这里多了一个xiali是因为我之前并没有删除备份前的数据。当然了我们在恢复的过程中可以选择只恢复xiaozhao这么一块内容
下面是binlog日志恢复中一些常用的参数
--start-datetime从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position从二进制日志中读取指定position 事件位置作为开始。
--stop-position从二进制日志中读取指定position 事件位置作为事件截至
