mysql慢查询日志增量备份_Mysql之Binlog日志 完全备份|增量 +binlog日志基于时间点恢复数据...

最新推荐文章于 2023-01-07 15:34:40 发布
最新推荐文章于 2023-01-07 15:34:40 发布
阅读量79 收藏
点赞数
文章标签： mysql慢查询日志增量备份
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39639686/article/details/113116402
版权

MariaDB [test]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| jeebbs |

| mysql |

| performance_schema |

| shop |

| test |

| xtrabackup |

| zrlog |

+--------------------+

8 rows in set (0.00 sec)

MariaDB [test]> use test;

Database changed

MariaDB [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| tb1 |

| tb2 |

+----------------+

2 rows in set (0.00 sec)

MariaDB [test]> select * from tb1;

+----+--------+-----+

| id | name | age |

+----+--------+-----+

| 3 | simple | 23 |

| 4 | alex | 34 |

| 5 | simle | 23 |

+----+--------+-----+

3 rows in set (0.00 sec)

下面开始进行场景模拟

[root@node01 ~]#crontab -l

0 4 * * * /usr/bin/mysqldump -uroot -p -B test -F -R -x --master-data=2 gzip >/tmp/test_$(date +%F).sql.gz

命令的语法格式：

mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库

mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库

[root@node01 ~]# mysqldump -uroot -B test -F -R -x --master-data=2 |gzip >/tmp/test_$(date +%F).sql.gz

[root@node01 ~]# ll /tmp/

total 504

-rw-r--r-- 1 root root 513534 Apr 17 01:32 test_2018-04-17.sql.gz

参数说明：

-A, --all-databases  备份所有表

-x, --lock-all-tables：锁定所有表

-l, --lock-tables：锁定备份的表

--single-transaction：启动一个大的单一事务实现备份

-B, --databases db_name1 db_name2 ...：备份指定的数据库

-C, --compress：压缩传输；

-E, --events：备份指定库的事件调度器；

-R, --routines：备份存储过程和存储函数；

--master-data[=#]：

1：记录CHANGE MASTER TO语句；此语句未被注释；

2：记录为注释语句；

--flush-logs, -F：锁定表之后执行flush logs命令；

-----------------

待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！

由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生

一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作

[root@node01 ~]# mysql -e "show master status"

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 245 | | |

+------------------+----------+--------------+------------------+

也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。

2)

早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。

比如：在ops库下member表内插入、修改了数据等等：

先是早上进行插入数据：

MariaDB [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28);

Query OK, 3 rows affected (0.52 sec)

Records: 3 Duplicates: 0 Warnings: 0

MariaDB [test]> update tb1 set name='诸葛亮' where id=5;

Query OK, 1 row affected (0.52 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [test]> select * from tb1;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 3 | simple | 23 |

| 4 | alex | 34 |

| 5 | 诸葛亮 | 23 |

| 6 | 张三 | 23 |

| 7 | 李四 | 24 |

| 8 | 店小二 | 28 |

+----+-----------+-----+

6 rows in set (0.00 sec)

在中午12:00的时候，误删了一条数据，过了一会才知道数据被误删了，此时表里又插入了许多数据

MariaDB [test]> delete from tb1 where id=5;

MariaDB [test]> insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28);

Query OK, 3 rows affected (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 0

这种时候，一定不要慌张！！！

先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；

先备份一下最后一个binlog日志文件

[root@node01 mysql]# cp -a mysql-bin.000003 /root/

接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003

这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件

[root@node01 mysql]# mysql -e "flush logs"

[root@node01 mysql]# mysql -e "show master status"

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 | 245 | | |

+------------------+----------+--------------+------------------+

读取binlog日志，分析问题。

MariaDB [(none)]> show binlog events in 'mysql-bin.000003'\G;

*************************** 1. row ***************************

Log_name: mysql-bin.000003

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 245

Info: Server ver: 5.5.56-MariaDB, Binlog ver: 4

*************************** 2. row ***************************

Log_name: mysql-bin.000003

Pos: 245

Event_type: Query

Server_id: 1

End_log_pos: 313

Info: BEGIN

*************************** 3. row ***************************

Log_name: mysql-bin.000003

Pos: 313

Event_type: Intvar

Server_id: 1

End_log_pos: 341

Info: INSERT_ID=6

*************************** 4. row ***************************

Log_name: mysql-bin.000003

Pos: 341

Event_type: Query

Server_id: 1

End_log_pos: 479

Info: use `test`; insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28)

*************************** 5. row ***************************

Log_name: mysql-bin.000003

Pos: 479

Event_type: Xid

Server_id: 1

End_log_pos: 506

Info: COMMIT /* xid=143 */

*************************** 6. row ***************************

Log_name: mysql-bin.000003

Pos: 506

Event_type: Query

Server_id: 1

End_log_pos: 574

Info: BEGIN

*************************** 7. row ***************************

Log_name: mysql-bin.000003

Pos: 574

Event_type: Query

Server_id: 1

End_log_pos: 679

Info: use `test`; update tb1 set name='诸葛亮' where id=5

*************************** 8. row ***************************

Log_name: mysql-bin.000003

Pos: 679

Event_type: Xid

Server_id: 1

End_log_pos: 706

Info: COMMIT /* xid=144 */

*************************** 9. row ***************************

Log_name: mysql-bin.000003

Pos: 706

Event_type: Query

Server_id: 1

End_log_pos: 774

Info: BEGIN

*************************** 10. row ***************************

Log_name: mysql-bin.000003

Pos: 774

Event_type: Query

Server_id: 1

End_log_pos: 863

Info: use `test`; delete from tb1 where id=5

*************************** 11. row ***************************

Log_name: mysql-bin.000003

Pos: 863

Event_type: Xid

Server_id: 1

End_log_pos: 890

Info: COMMIT /* xid=146 */

*************************** 12. row ***************************

Log_name: mysql-bin.000003

Pos: 890

Event_type: Query

Server_id: 1

End_log_pos: 958

Info: BEGIN

*************************** 13. row ***************************

Log_name: mysql-bin.000003

Pos: 958

Event_type: Intvar

Server_id: 1

End_log_pos: 986

Info: INSERT_ID=9

*************************** 14. row ***************************

Log_name: mysql-bin.000003

Pos: 986

Event_type: Query

Server_id: 1

End_log_pos: 1124

Info: use `test`; insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28)

*************************** 15. row ***************************

Log_name: mysql-bin.000003

Pos: 1124

Event_type: Xid

Server_id: 1

End_log_pos: 1151

Info: COMMIT /* xid=147 */

*************************** 16. row ***************************

Log_name: mysql-bin.000003

Pos: 1151

Event_type: Rotate

Server_id: 1

End_log_pos: 1194

Info: mysql-bin.000004;pos=4

16 rows in set (0.00 sec)

ERROR: No query specified

通过分析，造成数据库破坏的pos点区间是介于 774--863 之间(这是按照日志区间的pos节点算的)。

先把凌晨4点全备份的数据恢复：

[root@node01 ~]# mysql -uroot

但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--12:00之间的数据还没有恢复回来！！

怎么办呢？

莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。

恢复命令的语法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

通过刚刚分析binlog日志，我们已经找到了造成数据库破坏的pos点区间是介于 774--863 之间

a)完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)

b)指定pos结束点恢复(部分恢复)：

-stop-position=706 pos结束节点(按照事务区间算，是706)恢复截止到706

--start-position=958 pos开始点

[root@node01 ~]# mysqlbinlog mysql-bin.000003 --stop-position=706 > step1.sql

[root@node01 ~]# mysqlbinlog mysql-bin.000003 --start-position=958 > step2.sql

[root@node01 ~]# mysql -uroot --database=test

[root@node01 ~]# mysql -uroot --database=test

MariaDB [(none)]> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> select * from tb1;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 3 | simple | 23 |

| 4 | alex | 34 |

| 5 | 诸葛亮 | 23 |

| 6 | 张三 | 23 |

| 7 | 李四 | 24 |

| 8 | 店小二 | 28 |

| 9 | 张三丰 | 23 |

| 10 | 李奎 | 24 |

| 11 | 曹操 | 28 |

+----+-----------+-----+

9 rows in set (0.00 sec)

本文参照     http://www.cnblogs.com/kevingrace/p/5907254.html

weixin_39639686
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
完全备份、增量备份、差异备份、binlog日志
01-17
总的来说，理解并正确运用完全备份、增量备份、差异备份和binlog日志是MySQL数据库管理员必备的技能，它们为数据安全提供了有力保障。在实际操作中，应根据业务需求、数据规模和资源限制选择最适合的备份方案。
关于Mysql的日志管理、备份与恢复
11-09
MySQL数据库在日常运维中，日志管理、备份与恢复是至关重要的环节，它们关系到数据的安全性和业务的连续性。本文将深入探讨这三大主题，帮助你更好地理解并掌握相关知识。 首先，我们来了解MySQL的日志系统。MySQL...
mysql 慢查询日志，灾难日志恢复，错误日志
weixin_34275734的博客
06-27 78
灾难日志 记录了所有的DDL(Create、Drop和Alter)和DML(insert、update、delete_的语句，但不包括查询的语句 打开mysql.ini 找到Binary Logging 修改为 # Binary Logging. log-bin = "abc"重启mysql找到E:\AppServ\MySQL\data下面多出两个文件abc.000...
mysql慢查询日志增量备份_MYSQL增量备份（日志备份）
weixin_39997696的博客
01-18 205
MYSQL数据库在使用过程中，一般都需要对使用的数据库进行备份处理，对于数据量较小时可以通过mysqldump命令进行数据库全备份，但是当数据库数据量达到一定程度之后，显然增量备份更加适合。MYSQL增量备份主要通过二进制日志文件进行增量备份。mysql数据库会以二进制形式自动把用户对mysql数据库的操作记录到文件，当用户希望恢复的时候可以使用备份文件进行恢复。本文主要介绍如何实现在本地服务器上...
MySQL 修改事务隔离级别、开启慢日志查询、开启binlog日志，使用日志恢复数据
Lee_SmallNorth的博客
04-06 445
MySQL版本：8.0.23（默认情况下，slow.log\binlog都被开启了） 一、修改事务隔离级别 查看MySQL事务隔离级别，默认可重复读，对于数据互联网项目推荐修改成读已提交 SELECT @@transaction_isolation; 设置读已提交 SET global transaction isolation LEVEL read committed 但是，重启之后就不生效了。需要再次设置。 二、开启慢日志 先查询日志是否开启，NO表示已开启 sho.
MySQL通过日志恢复数据
qq_23374741的博客
03-12 2831
1:登录mysql2:查询日志文件：show variables like '%log%';3：用mysqlbinlog 找到想要恢复的日志文件并拷贝到指定的test.sql文件：mysqlbinlog /var/lib/mysql/mysql-bin.000002 --base64-output=decode-rows -r test.sql注意：导出的数据为乱码，所以要加上 --base64-...
MySQL使用binlog完成数据恢复
qq_51359327的博客
05-20 142
1.准备数据 mysql> create database t1; Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> create table cool(id int,name varchar(20)); Query OK, 0 rows affected (0.05 sec) 2.备份数据库 # mysqldump -uroot -p --set-gtid-purged=OFF --m
基于 mysql-binlog-connector-java 实现增量数据的收集.zip
01-04
MySQL是世界上最流行的关系型数据库管理系统之一，而MySQL的binlog（二进制日志）是其核心特性之一，用于记录所有对数据库进行的更改操作。`mysql-binlog-connector-java` 是一个开源库，它允许Java应用程序实时读取...
mysql全量备份、增量备份实现方法
09-09
MySQL数据库的备份是确保数据安全的重要环节，全量备份和增量备份是两种常见的备份策略。全量备份是指备份数据库中的所有数据，而增量备份则仅备份自上次全量备份或最近一次增量备份以来发生更改的数据。 1. **全量...
MySQL binlog日志恢复数据详细操作步骤
巴啦啦臭魔仙的博客
01-07 8259
因为后台管理人员刚误删就反馈给我，00038 是最新的，所以确定我需要恢复的数据在 mysql-bin.000038 文件内。PS：我是直接通过导出的 sql 文件进行恢复的，数据量少可以这样操作，数据量大还是需要根据 pos 点或者时间区间进行恢复。可以在终端工具中执行如下命令查看该文件内的内容，但是内容很多的情况下查看或者查询起来并不方便。如果为 ON 则为开启状态，如果开启了就可以继续操作了，如果没开启就GG。2、可以执行如下命令，查看文件最后一个操作事件Position结束点的值。
Mysql根据日志恢复数据
weixin_54108529的博客
02-07 2549
此文详细介绍了根据mysql的二进制日志恢复丢失的数据。 在7月17日不小心删除了一个工程的整个数据，导致数据丢失。 最新的备份是5月29日。丢失的数据比较多。由于我发现这个mysql数据库开启了日志功能，日志记录从从5月3日到7月17日。 查看mysql是否开启二进制日志。bin-log日志记录了所有的DDL和DML的语句。 查看my.cnf中是否有log-bin的设置 Log-bin=mysql-bin EXPIRE_LOGS_DAYS此参数是设置日志的过期天数，过期的日志将会被自动.
MySQL慢日志查询分析方法与工具
Enweitech Software Works
05-08 3万+
MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。1）通用查询日志：记录建立的客户端连接和执行的语句。2）慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具...
修改innodb_buffer_pool_instances解决mysqlbinlog恢复慢的问题
姚远OracleACE的博客
06-23 1982
一个客户的mysql数据库恢复在最后一步是滚binlog，结果恢复特别慢，CPU占用率100%，磁盘IO几乎是零，show processlist发现线程在sleep。从general log里面看不到任何动静，似乎找不到解决的办法。 登录上去用strace分析mysqld进程发现： 全部在进行内存分配。分析mysqld的参数，发现一个参数innodb_buffer_pool_instances 被设置为64,很是可疑，就是innodb_buffer_pool被分成了64个单位，而innodb_buffe
mysql数据库通过日志恢复数据
热门推荐
qzw5235641的博客
11-12 4万+
大家应该知道mysql通过备份恢复文件的方法，其实用日志其实也可以恢复数据，方法如下 1.找到my.ini文件 （方法:先到mysql安装目录找my.ini文件如果此安装目录没有找到该文件，则此文件一般在C盘下的ProgramData文件夹中，此文件夹为隐藏文件夹需要直接查找 C:\ProgramData\MySQL\MySQL Server 5.7\）然后打开my.ini文件 ...
MySQL数据库日志知识与MySQLsla的安装及使用
qq_42491605的博客
04-27 381
MySQL数据库日志知识与企业应用实践 为了帮助管理员快速发现数据库的相关运行信息，MySQL为用户提供了几种日志种类，具体见表 默认情况下，以上所有的日志都处于非激活状态（Linux环境）。当激活日志时，所有的日志都默认配置在数据文件的目录下。管理员也可以对上述日志进行轮询切割，实现该功能常见的命令是mysqladmin flush-logs、mysqldump的“-F”或“–master-d...
mysql日志恢复数据_mysql日志恢复数据方法介绍
weixin_31785421的博客
02-18 905
利用日志恢复数据一般情况是你没对数据库进行有效和合理的备份导致了，但有一个提前就是你要做日志恢复我们就必须是开了mysql日志的，否则是无法进行日志备份的哦。mysql日志备份优缺点优点：是想恢复到某个时间点，或某个操作sql语句缺点：就产生庞大的日志文件window中mysql日志恢复方法1.开启mysql日志在my.ini 文件里找到[mysqld],在其下面增加一行log-bin代码如下复制...
基于Python flask 的豆瓣电影top250数据评分可视化
最新发布
08-03
基于Python flask 的豆瓣电影top250数据评分可视化
kafka mysql binlog,Springboot系列—利用Binlog和Kafka实时同步mysql数据到SQL SERVER一-开启Binlog日志...
05-26
需要注意的是，由于 Binlog 日志是一个增量日志，因此在启动同步任务时，需要首先将 MySQL 数据库中的数据全量复制到 SQL Server 数据库中，然后再开启 Binlog 日志，以保证数据的完整性和一致性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值