您的位置:威尼斯官方网站 > 威尼斯正规官网 > 那么该操作也会写入二进制日志

那么该操作也会写入二进制日志

发布时间:2019-10-10 20:11编辑:威尼斯正规官网浏览(140)

    mysql的二进制日志binlog能够说是mysql最重大的日志,它记录了有着数据更新sql,以事件方式记录,还包括语句所实践的损耗的时间,mysql的二进制日志是专门的工作安全型的。binlog日志重要用来mysql主从复制和数据苏醒。最先的小说地址:代码汇个人博客

    简单的讲理解binlog

    binlog是四个二进制格式的文本,用于记录客户对数据库更新的sql语句音讯,然而不满含select和show那类操作,因为那类操作对数码自个儿并不曾改变。然后,若操作自己并从未形成数据库发生变化,那么该操作也会写入二进制日志。

    暗许景况下,binlog日志是二进制格式的,不可能选取查看文本工具的下令(比如,cat,vi等)查看,而接纳mysqlbinlog分析查看。

    日常的话开启二进制日志大致会有1%的习性损耗,别的介绍能够查阅 官方网址文书档案介绍

    binlog作用

    binlog日志有三个最重视的选取意况

    1、mysql主从复制

    威尼斯官方网站,MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来到达master-slave数据一致的目标。能够参照:《mysql 主从复制 基于binlog 轻巧实践》

    2、数据苏醒

    通过运用mysqlbinlog程序管理二进制日志文件,来使恢复生机数据。

    binlog日志满含两类公事

    1、二进制日志索引文件(文件名后缀为.index)用于记录全部的二进制文件2、二进制日志文件(文件名后缀为.00000*)记录数据库全数的DDL和DML(除了数量查询语句select)语句事件。在mysql 主从复制中有看见。

    开启binlog日志

    1、修改配置文件

    能够经过如下命令查看mysql读取的安插文件,顺序排前的先行

    root@ba586179fe4b:/# mysql --help|grep 'my.cnf' order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
    

    编排配置文件,然后重启mysql

    root@ba586179fe4b:/# vi /etc/my.cnf[mysqld]# 开启二进制日志功能,mysql-bin 是日志的基本名或前缀名log-bin=mysql-bin
    

    2、登陆数据库 mysql -u root -p123456

    查看binlog日志是还是不是开启,log_bin为ON表示开启binlog日志

    mysql> show variables like 'log_%'; --------------------------------- ------- | Variable_name | Value | --------------------------------- ------- | log_bin | ON || log_bin_trust_function_creators | OFF || log_error | || log_output | FILE || log_queries_not_using_indexes | OFF || log_slave_updates | OFF || log_slow_queries | OFF || log_warnings | 1 | --------------------------------- ------- 8 rows in set 
    

    binlog日志常用操作命令

    1、查看全体binlog日志列表

    mysql> show master logs; ------------------ ----------- | Log_name | File_size | ------------------ ----------- | mysql-bin.000001 | 8184 || mysql-bin.000002 | 107 | ------------------ ----------- 2 rows in set 
    

    2、查看master状态,即最终一个binlog日志的数码名称,及其最终三个操作事件pos结束点值

    mysql> show master status; ------------------ ---------- -------------- ------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------ ---------- -------------- ------------------ | mysql-bin.000002 | 107 | | | ------------------ ---------- -------------- ------------------ 1 row in set 
    

    3、刷新log日志,自此刻启幕发出贰个新编号的binlog日志文件

    mysql> flush logs;Query OK, 0 rows affected 
    

    4、重新设置全体binlog日志

    mysql> reset master;Query OK, 0 rows affected 
    

    查看binlog日志内容,常用有三种方式

    注: binlog是二进制文件,普通文书查看器cat more vi等都爱莫能助展开,必需选取自带的 mysqlbinlog 命令查看,binlog日志与数据库文件在同目录中(小编的条件安排安装是挑选在/usr/local/mysql/data中)在MySQL5.5以下版本选用mysqlbinlog命令时假若报错,就增进"--no-defaults"选项

    mysql数据贮存在/var/lib/mysql目录,通过mysqlbinlog打开日志文件

    /usr/local/mysql/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000001
    

    截取最后三遍实行的日记片段

    # at 1778#190221 6:58:51 server id 1 end_log_pos 1899 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1550732331/*!*/;INSERT INTO `proxy` (`id`, `name`) VALUES ('6', 'code')/*!*/;# at 1899#190221 6:58:51 server id 1 end_log_pos 1926 Xid = 45
    
    • 首要参数解释:
      • server id 1 : 数据库主机的服务号;
      • end_log_pos 1899: sql甘休时的pos节点
      • thread_id=1: 线程号

    上边这种艺术读抽出binlog日志的全文内容相当多,不便于辨别查看pos点音信,这里介绍一种特别便利的询问命令:

    mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

    分选解析:

    IN 'log_name' 钦定要询问的binlog文件名(不钦赐正是第一个binlog文件)FROM pos 钦命从哪个pos初始点最早查起(不钦定正是从整个文件第八个pos点最初算)LIMIT [offset,] 偏移量row_count 查询总条数

    mysql> show master logs; ------------------ ----------- | Log_name | File_size | ------------------ ----------- | mysql-bin.000001 | 326 | ------------------ ----------- 1 row in set mysql> show binlog events in 'mysql-bin.000001'G;*************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 1End_log_pos: 107 Info: Server ver: 5.5.62-log, Binlog ver: 4*************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 107 Event_type: Query Server_id: 1End_log_pos: 178 Info: BEGIN*************************** 3. row *************************** Log_name: mysql-bin.000001 Pos: 178 Event_type: Query Server_id: 1End_log_pos: 299 Info: use `codehui`; INSERT INTO `proxy` (`id`, `name`) VALUES ('6', 'code')*************************** 4. row *************************** Log_name: mysql-bin.000001 Pos: 299 Event_type: Xid Server_id: 1End_log_pos: 326 Info: COMMIT /* xid=61 */4 rows in set ERROR: No query specifiedmysql>
    

    地点那条语句能够将点名的binlog日志文件,分成有效事件行的点子赶回,并可利用limit内定pos点的伊始偏移,查询条数。

    • 查询第4个的binlog日志:
    mysql> show binlog eventsG;
    
    • 钦赐询问 mysql-bin.000021 那一个文件:
    mysql> show binlog events in 'mysql-bin.000021'G;
    
    • 钦点询问 mysql-bin.000021 那几个文件,从pos点:8224初始查起:
    mysql> show binlog events in 'mysql-bin.000021' from 8224G;
    
    • 钦命询问 mysql-bin.000021 这一个文件,从pos点:8224开首查起,查询10条
    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10G;
    
    • 钦命询问 mysql-bin.000021 那几个文件,从pos点:8224起先查起,偏移2行,查询10条
    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10G;
    

    其一数据测量检验相比劳累,大家先进楷模拟个场景

    codehui数据库会在每一天晚上1点采取安顿任务举办二次备份,这里先手动试行一下备份义务。然后就有了数据库停止今天黎明先生1点的数据库备份文件。早上9点和早上12点数据库都实践了增加和删除改操作,然后晚上18点一贯删掉了codehui数据库,场景大致正是那样,上面举行测量试验数据的过来。

    先在codehui数据库插入测量试验数据

    mysql> use codehui;Database changedmysql> CREATE TABLE `test` ( -> `id` int NOT NULL AUTO_INCREMENT, -> `name` varchar CHARACTER SET utf8 DEFAULT NULL, -> PRIMARY KEY  -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected mysql> desc test; ------- -------------- ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ------- -------------- ------ ----- --------- ---------------- | id | int | NO | PRI | NULL | auto_increment || name | varchar | YES | | NULL | | ------- -------------- ------ ----- --------- ---------------- 2 rows in set mysql> INSERT INTO `test` (`id`, `name`) VALUES ('1', 'code');Query OK, 1 row affected mysql> INSERT INTO `test` (`id`, `name`) VALUES ('2', 'php');Query OK, 1 row affected mysql> select * from test; ---- ------ | id | name | ---- ------ | 1 | code || 2 | php | ---- ------ 2 rows in set mysql>
    

    1、先备份一下数据库备份数据库方法mysqldump,详细请参见 mysql 数据备份

    root@ba586179fe4b:/# /usr/local/mysql/bin/mysqldump -uroot -p123456 -B -F -R -x --master-data=2 codehui|gzip > /opt/backup/codehui.bak.sql.gzroot@ba586179fe4b:/# ls /opt/backupcodehui.bak.sql.gz
    

    mysqldump备份方法参数表达:

    -B:钦赐数据库-F:刷新日志-Tiggo:备份存款和储蓄进度等-x:锁表

    --master-data:在备份语句里增多CHANGE MASTEPRADO语句以致binlog文件及职责点消息

    鉴于地点在全备份的时候使用了-F选项,那么当数据备份操作刚起首的时候系统就能够自行刷新log,那样就能够自动发出贰个新的binlog日志,那些新的binlog日志就能够用来记录备份之后的数据库"增加和删除改"操作

    mysql> show master status; ------------------ ---------- -------------- ------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------ ---------- -------------- ------------------ | mysql-bin.000003 | 107 | | | ------------------ ---------- -------------- ------------------ 1 row in set 
    

    也正是说, mysql-bin.000003 是用来记录晚上1点过后对数据库的持有"增加和删除改"操作。

    2、晚上9点对数据库实行"增"操作,新插入3条数据

    mysql> INSERT INTO `test` (`id`, `name`) VALUES ('3', 'java'),('4','golang'),('5','shell');Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test; ---- -------- | id | name | ---- -------- | 1 | code || 2 | php || 3 | java || 4 | golang || 5 | shell | ---- -------- 5 rows in set 
    

    3、上午12点对数据库进行"改"操作,修改1条数据

    mysql> UPDATE `test` SET `name`='mysql' WHERE `id`='1';Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from test; ---- -------- | id | name | ---- -------- | 1 | mysql || 2 | php || 3 | java || 4 | golang || 5 | shell | ---- -------- 5 rows in set 
    

    4、中午18点,某技术员因心思一点也不快筹算跑路,删掉了数据库codehui

    mysql> drop database codehui;Query OK, 3 rows affected 
    

    5、此刻先别慌,他遗忘了我们还会有大招,正是binlog日志。先留意查阅最终一个binlog日志,并记录下主要的pos点,到底是哪位pos点的操作变成了数据库的毁坏;

    我们先备份一下末尾二个binlog日志

    root@ba586179fe4b:/# cp -v /var/lib/mysql/mysql-bin.000003 /opt/backup/'/var/lib/mysql/mysql-bin.000003' -> '/opt/backup/mysql-bin.000003'
    

    此刻施行三回刷新日志索引操作,重新开端新的binlog日志记录文件。理论说 mysql-bin.000003 这几个文件不会再有接二连三写入了(便于咱们深入分析原因及追寻pos点),今后全部数据库操作都会写入到下贰个日志文件;

    mysql> flush logs;Query OK, 0 rows affected mysql> show master status; ------------------ ---------- -------------- ------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------ ---------- -------------- ------------------ | mysql-bin.000004 | 107 | | | ------------------ ---------- -------------- ------------------ 1 row in set 
    

    6、读取日志 解析难题,读取日志方法方面已经提及,这里运用第二种

    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 | 1 | 107 | Server ver: 5.5.62-log, Binlog ver: 4 || mysql-bin.000003 | 107 | Query | 1 | 178 | BEGIN || mysql-bin.000003 | 178 | Query | 1 | 327 | use `codehui`; INSERT INTO `test` (`id`, `name`) VALUES ('3', 'java'),('4','golang'),('5','shell') || mysql-bin.000003 | 327 | Xid | 1 | 354 | COMMIT /* xid=184 */ || mysql-bin.000003 | 354 | Query | 1 | 425 | BEGIN || mysql-bin.000003 | 425 | Query | 1 | 544 | use `codehui`; INSERT INTO `proxy` (`id`, `name`) VALUES ('1', 'my') || mysql-bin.000003 | 544 | Xid | 1 | 571 | COMMIT /* xid=188 */ || mysql-bin.000003 | 571 | Query | 1 | 642 | BEGIN || mysql-bin.000003 | 642 | Query | 1 | 784 | use `codehui`; UPDATE `proxy` SET `name`='mysql' WHERE  AND (`name`='my') LIMIT 1 || mysql-bin.000003 | 784 | Xid | 1 | 811 | COMMIT /* xid=190 */ || mysql-bin.000003 | 811 | Query | 1 | 882 | BEGIN || mysql-bin.000003 | 882 | Query | 1 | 995 | use `codehui`; UPDATE `test` SET `name`='mysql' WHERE `id`='1' || mysql-bin.000003 | 995 | Xid | 1 | 1022 | COMMIT /* xid=192 */ || mysql-bin.000003 | 1022 | Query | 1 | 1109 | drop database codehui || mysql-bin.000003 | 1109 | Rotate | 1 | 1152 | mysql-bin.000004;pos=4 | ------------------ ------ ------------- ----------- ------------- ---------------------------------------------------------------------------------------------------- 15 rows in set 
    

    到此处是不很提神,看见了备份之后试行的具备的"增加和删除改"记录。通过深入分析,形成数据库破坏的pos点区间是介于 1022--1109 之间(那是依据日志区间的pos节点算的),只要苏醒到1109前就可。

    7、复苏清晨1点的备份数据,也等于刚刚手动备份的数据

    root@ba586179fe4b:/# cd /opt/backup/root@ba586179fe4b:/opt/backup# lscodehui.bak.sql.gz mysql-bin.000003root@ba586179fe4b:/opt/backup# gzip -d codehui.bak.sql.gzroot@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < codehui.bak.sql
    

    那般就重振旗鼓了以致于当日黎明先生前的备份数据都过来了,之后的多少经过binlog日志mysql-bin.000003实行还原。

    mysql> show databases; -------------------- | Database | -------------------- | information_schema || codehui || mysql || performance_schema | -------------------- 4 rows in set mysql> use codehui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables; ------------------- | Tables_in_codehui | ------------------- | demo || proxy || test | ------------------- 3 rows in set mysql> select * from test; ---- ------ | id | name | ---- ------ | 1 | code || 2 | php | ---- ------ 2 rows in set 
    

    8、从binlog日志复苏数据

    过来命令的语法格式:

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

    • 常用参数选项解释:
      • --start-position=875 起始pos点
      • --stop-position=954 结束pos点
      • --start-datetime="2015-9-25 22:01:08" 初阶时间点
      • --stop-datetime="2019-9-25 22:09:46" 结束时间点
      • --database=codehui 钦赐只回复codehui数据库(一台主机上数次有三个数据库,只限本地log日志)
    • 不经常用选项:
      • -u --user=name 连接到长途主机的客商名
      • -p --password[=name] 连接到远程主机的密码
      • -h --host=name 从远程主机上赢得binlog日志
      • --read-from-remote-server 从有些MySQL服务器上读取binlog日志

    小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。那个命令、文件尽量写成相对路线;

    A、 完全复苏(须要手动编辑mysql-bin.000003,将那条drop语句剔除掉)

    root@ba586179fe4b:/opt/backup# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sqlroot@ba586179fe4b:/opt/backup# vi /opt/backup/000003.sql #删除里面的drop语句# 删掉drop语句前后的# at 到 /*!*/之间的内容root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < /opt/backup/000003.sql
    

    查阅数据,已经平复了

    mysql> use codehui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test; ---- -------- | id | name | ---- -------- | 1 | mysql || 2 | php || 3 | java || 4 | golang || 5 | shell | ---- -------- 5 rows in set # 然后删除codehui数据库,测试第二种方法mysql> drop database codehui;Query OK, 3 rows affected # 重新导入凌晨1点的备份数据,root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < codehui.bak.sql
    

    B、 钦点pos甘休点过来:

    --stop-position=571 pos甘休节点(遵照事务区间算,是571)

    • 注意:
      • 此pos停止节点介于"test"表原始数据与立异"name='mysql'"此前的多少,那样就能够回复到 改造"name='mysql'"在此以前的多寡了。

    操作如下

    root@ba586179fe4b:/opt/backup# mysqlbinlog --stop-position=571 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehuimysql> use codehui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test; ---- -------- | id | name | ---- -------- | 1 | code || 2 | php || 3 | java || 4 | golang || 5 | shell | ---- -------- 5 rows in set 
    

    C、 钦点pos点区间回复:

    更新 "name='mysql'" 这条数据,日志区间是Pos[882] --> End_log_pos[995],按专门的学问区间是:Pos[811] --> End_log_pos[1022]

    单身恢复生机 "name='mysql'" 那步操作,可这么:依据binlog日志区间单独恢复生机:

    root@ba586179fe4b:/opt/backup# mysqlbinlog --start-position=882 --stop-position=995 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui
    

    遵从专业区间单独恢复生机

    root@ba586179fe4b:/opt/backup# mysqlbinlog --start-position=811 --stop-position=1022 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui
    

    一经要还原区间内的多条日志,按专门的学业区间复苏就能够。

    mysql> use codehui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test; ---- -------- | id | name | ---- -------- | 1 | mysql || 2 | php || 3 | java || 4 | golang || 5 | shell | ---- -------- 5 rows in set 
    

    查看数据库恢复了"name='mysql'",那样就过来了剔除前的数量状态了。

    D、 也可指定期期距离复苏:除了用pos点的情势开展回复,也得以由此点名时间距离举办恢复,定时间回复必要用mysqlbinlog命令读取binlog日志内容,找时间节点。

    # 起始时间点--start-datetime="YYYY-MM-DD H:I:S"# 结束时间点--stop-datetime ="YYYY-MM-DD H:I:S"# 用法举例mysqlbinlog --start-position=811 --start-datetime="YYYY-MM-DD H:I:S" --stop-datetime="YYYY-MM-DD H:I:S" --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui
    

    本文由威尼斯官方网站发布于威尼斯正规官网,转载请注明出处:那么该操作也会写入二进制日志

    关键词:

上一篇:类型可以让你表示函数的域和值域

下一篇:没有了