You are here
马哥 34_04 _MySQL系列之十二——MySQL日志管理 有大用
1)错误日志: 不仅仅是错误日志,还有其它类型的信息
log_error
log_warnings
2)一般查询日志:select,,,,DML语句,都可以记录下来,,,因为内容太多,默认一般不会启用它
general_log
general_log_file #一般查询日志不一定保存在文件中,也可以保存在表中(表不存在,得自己创建,,,还得用一个指令log_output指定日志文件格式)
log
log_output #指定日志文件格式(是表,或文件或不记录)
3)慢查询日志: 慢速,执行了很长时间的查询操作,才记录;;;;主要是为了查看性能问题,一般要启用它
一个查询的快慢,不仅取决于自己这个sql语句的执行,还决定于事务的隔离级别,锁表或相关资源等相关的操作
long_query_time 定义慢查询时间,超过这个时间就算慢了
log_slow_queries={YES|NO} 是否记录慢查询
slow_query_log 也是 是否记录慢查询
slow_query_log_file
事实上,1秒的查询就算很慢的了
4)二进制日志: 不管什么数据库存储引擎,只记录 (主要是 DML , DDL) DCL语句,数据库授权语句产生的日志信息,能够引起数据库改变或潜在引起数据库变化的语句的信息的执行的操作,它都会记录下来;;;;; 用于实现mysql的复制,以及mysql即时点恢复(即时点还原)的重要依据;;;;二进制日志工作原理将来有必要看下;;;;;;;;;它不能使用普通的文本查看命令去查看,只能使用mysql提供的专用命令(mysqlbinlog)查看;;;;工作特性很独特,一般位于数据目录当中,以mysql-bin开头或当前主机名开头,,跟上 什么什么-bin,,,,,,,mysql每重启一次,它都会滚动一次,,,,二进制日志要记录任何数据库改变的操作,一个非常繁忙的在线处理系统,(比如电子商城,每笔交易产生,会产生大量记录,,既要记录操作本身,又要记录数据,),,,,,, 它的主要目的是即时点恢复,即时点恢复的方法就是将里面的语句重放(记录的是操作,把操作重新执行一遍,就是重放)完成的,,所以它是复制和即时点恢复的重要工具(重要凭据),,,,,,到底记录的是什么?是sql语句命令,还是sql语句所引起的操作?
mysqlbinlog
二进制日志的格式:
基于语句的: statement,只要重放的结果是一样的,基于语句的要简单的多(比如一句 insert ,update 很简单)
基于行: row这一行引起的相关改变,把这一行的相关改变记录下来,,,,如果update一下无数行的话,就太麻烦了
混合方式:mixed ,,,先基于语句,如果不对的话(比如update set 时间字段=current_time() ,这就不对了,,,因为下次 current_time() ),再基于行
当我们把所有的操作都记入二进制日志以后,假如我们备份一下数据库,过两天数据库服务器崩溃了,想恢复到崩溃之前的那一刻
二进制日志事件: 每一次操作可以称为一个事件,(可能是基于语句的复制,也可能基于行的复制)
二进制日志事件的格式: 1)事件产生的时间, 2)事件在日志文件中的相对位置,
二进制日志文件:
索引文件
二进制日志文件
查看当前正在使用的二进制日志文件
mysql> show master status;
mysql> show binary logs; #查看当前仍然拥有的所有二进制日志文件
mysql> show binlog events in '二进制日志文件名' [FROM pos] # from pos 表示从哪个位置开始显示
mysql> purge binary logs to '某二进制日志文件名'; #清除这个文件之前的所有二进制日志文件,(不包括这个文件)
mysqlbinlog # 如果不指stop,那么就是直到文件的尾部
# 不给任何选项,显示整个文件的内容
--start-datetime
--stop-datetime
--start-position
--stop-position
5)中继日志:格式与二进制一样,因为是从二进制日志那边复制过来的,作用与功能不完全一样,作用是在当前的从服务器上要执行一遍
6)事务日志:保证ACID的重要的组件,将随机IO转换为顺序IO,从而提高性能,保证事务提交以后不会丢失,(当然,只有事务式存储引擎才需要用到事务日志,比如Innodb)
mysql的复制功能,就是从服务器上从主服务器上把它的二进制日志里面所产生的任何操作(通过mysql客户端服务器端协议从主服务器的二进制日志文件中读取过来一份并保存在本地的某一个日志文件(中继日志)中,由mysql本地的某个服务器线程(也就是从服务器的进程)(两个 sql线程 和io线程)每一次从日志文件中读取一行,并在本地的数据库上操作一次)
mysql 靠各种服务器变量或启动时的各种参数,来定义各类日志启用与否,以及如果启用的话,日志文件是什么
mysql> show global variables like '%log%'; #查看与 log 相关的服务器变量
+-----------------------------------------+----------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 | #含binlog的都是与二进制日志相关的变量
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF | #一般查询日志,是不是启用一般查询日志的,含general_log的就是一般查询日志 #一般都会关闭,因为每一个操作都记录,会产生大量的io
| general_log_file | /mydata/data/mail.log | #一般查询日志记录在 数据目录下, 本机名称.log
| innodb_flush_log_at_trx_commit | 1 | #含innodb的就是与innodb相关的事务日志
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF | # 也是与一般查询日志相关,在5.6当中,已经废弃了,
| log_bin | ON | # 与二进制相关的 # 定义日志的输出格式?
| log_bin_trust_function_creators | OFF |
| log_error | /mydata/data/mail.magedu.com.err | #这里就是错误日志文件(既然己经定义的错误日志文件,它就应该记录错误日志信息 默认是在数据目录下,主机名.err) #把这个去掉,就不再记入错误日志了
#服务器运行的时候,不支持set的方式进行修改的(与文件相关的操作,在服务器重启以后才能生效的)
#(所以改完以后,如果一定要服务器重启以后才能有效,我们只能把它写在配置文件当中)
| log_output | FILE | # 定义日志的输出格式 是文件,表或者NONE(不记在任何地方)
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF | # 慢查询日志
| log_warnings | 1 | # 这个可以 set 方法改的 1表示记录警告到错误日志
| max_binlog_cache_size | 18446744073709547520 | #定义最大文件大小等各种格式
| max_binlog_size | 1073741824 | #定义最大文件大小等各种格式
| max_binlog_stmt_cache_size | 18446744073709547520 | #定义最大文件大小等各种格式 stmt?是语句(statement)的意思?
| max_relay_log_size | 0 |
| relay_log | | #中继日志
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF | #是否记录慢查询日志 好多指令重复,可能是历史原因
| slow_query_log_file | /mydata/data/mail-slow.log | #慢查询日志文件位置
| sql_log_bin | ON | #手动来定义是否启用或关闭二进制日志的
| sql_log_off | OFF | #手动来定义是否启用或关闭二进制日志的,应该是普通日志吧
| sync_binlog | 0 | #是不是同步 从缓存到磁盘文件的
| sync_relay_log | 0 | #是不是同步 从缓存到磁盘文件的
| sync_relay_log_info | 0 | #是不是同步 从缓存到磁盘文件的
+-----------------------------------------+----------------------------------+
41 rows in set (0.00 sec)
mysql>
错误日志:
1)mysqld启动和关闭过程中的信息,(启动正常与否,读取的配置文件,配置文件中设定的数据目录在哪里)
2)服务器运行过程当中所产生的错误信息
3)事件调度器运行一个事件时产生的信息 (event产生的信息)
4)在从服务器上启动(停止)从服务器进程时所产生的日志信息,当然记录在从服务器上的错误日志文件当中
mysql在启动的时候,默认情况下没有启动任何日志功能,只是借助于配置文件启用了错误日志的功能
警告信息 默认是没有记录入错误日志的
log={yes|NO} # select,insert,update,delete也都会记录下来,而且要记入一般查询日志当中,通常为OFF,5.6已弃用
log_output={TABLE|FILE|NONE} #默认为TABLE,我们看到的是mysql的配置文件给我们设定后的结果(或编译后设定的结果),,,,一般查询日志可记入TABLE或 FILE,,,NONE表示不记录,其它设定就会都失效, #log_output=TABLE,FILE 表示同时记入表和文件中
long_query_time #表示实际执行时间,不是在cpu上的执行时长,所以负载重的服务器更易产生慢查询,支持毫秒级
log_slow_queries={YES|NO} #是否记录慢查询
slow_query_log={ON|OFF} #是否启用慢查询日志 1,ON,启用,,,0,OFF禁用,,,也取决于 log_output
mysql> show global variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| max_long_data_size | 1048576 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show global variables like '%log%';
+-----------------------------------------+----------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /mydata/data/mail.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /mydata/data/mail.magedu.com.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON | #它也自动就成ON了
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | ON | #它变成ON了
| slow_query_log_file | /mydata/data/mail-slow.log | #如果这里没有的话,必须手动配置文件,重启服务器(涉及到修改文件的操作,只能重启服务器才能生效;;开放或关闭某个功能的操作,可以动态设定)
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+----------------------------------+
41 rows in set (0.00 sec)
mysql>
对于二进制日志,mysql有自己的策略,它会选择最优的来记录,前提是我们要设定它要记录什么,
当我们把所有的操作都记入二进制日志以后,假如我们备份一下数据库,过两天数据库服务器崩溃了(数据文件所在的硬盘坏了),想恢复到崩溃之前的那一刻
数据文件与二进制日志文件应该不在同一个硬盘上(这样子的话,数据文件所在的硬盘坏了,就不怕了)
二进制日志的文件里面的东西 恢复到数据库叫 即时点还原
假如数据库没备份的话,二进制日志文件都在,只要从第一次开始的操作都在二进制日志文件里,那我们也可以恢复,只不过时间长罢了,时间非常慢(比如几天)
如果我们做过一次数据库的备份了,那么备份之前的二进制日志文件都没有用了,都可以删除了
二进制日志文件:
1) 文件头(首部信息,哪个时刻,产生的第几号日志文件)
2) 事件
starttime:事件产生的起始时间 (结束时间我们就不记录了)
position:位置 按行记录的,按照事件当中的每一个字符的位置来记录的(此事件开始时间位置,比如100,表示此事件从第100个字节开始?也是上一次结束的字节位置吧)
找某一个事件,可以根据 starttime 来找,也可以根据 position 来找
起始时间,结束时间 来找事件
起始位置,结束位置 来找事件
mysql二进制日志文件滚动,不改变老文件名字,只创建新文件的名字
服务器重启了n次,那么日志文件就会有n个(启动一次,产生一个二进制日志文件)
[root@mail ~]# cd /mydata/data
[root@mail data]# ls
cactidb mysql mysql-bin.000011 mysql-bin.000022
edb mysql-bin.000001 mysql-bin.000012 mysql-bin.000023
hellodb mysql-bin.000002 mysql-bin.000013 mysql-bin.000024
ibdata1 mysql-bin.000003 mysql-bin.000014 mysql-bin.index #这是二进制日志索引文件,是文本文件
ib_logfile0 mysql-bin.000004 mysql-bin.000015 performance_schema
ib_logfile1 mysql-bin.000005 mysql-bin.000016 students
jiaowu mysql-bin.000006 mysql-bin.000017 test
mail.magedu.com.err mysql-bin.000007 mysql-bin.000018 test2
mail.magedu.com.pid mysql-bin.000008 mysql-bin.000019 testdb
mail-slow.log mysql-bin.000009 mysql-bin.000020
mydb mysql-bin.000010 mysql-bin.000021
[root@mail data]#
[root@mail data]# cat mysql-bin.index
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020
./mysql-bin.000021
./mysql-bin.000022
./mysql-bin.000023
./mysql-bin.000024
[root@mail data]#
[root@mail data]# mv ~/.my-bak.cnf ~/.my.cnf
[root@mail data]#
[root@mail data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show master status; #查看正在使用的二进制日志文件
#Position 表示事件开始的位置(上一次结束的位置吧?)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000024 | 459 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> use jiaowu
Database changed
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| sct |
| students |
| tutors |
+------------------+
4 rows in set (0.00 sec)
mysql>
mysql> show master status; #show tables 没有引起数据库改变,所以position不变 还是 459
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000024 | 459 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> insert into tutors (tname,gender,age) values('stu100','M',30); #会引起数据库改变
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show master status; # position 变化了 ,,,,710-459 就是刚才insert事件所占据的空间
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000024 | 710 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> show binlog events in 'mysql-bin.000024'; #查看这个二进制日志文件里面记录的信息
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000024 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 |
| mysql-bin.000024 | 107 | Query | 1 | 176 | BEGIN |
| mysql-bin.000024 | 176 | Query | 1 | 309 | use `mysql`; update user set password=password('zhong1926') where user like 'root' |
| mysql-bin.000024 | 309 | Query | 1 | 379 | COMMIT |
| mysql-bin.000024 | 379 | Query | 1 | 459 | use `mysql`; flush privileges |
| mysql-bin.000024 | 459 | Query | 1 | 529 | BEGIN |
| mysql-bin.000024 | 529 | Intvar | 1 | 557 | INSERT_ID=15 |
| mysql-bin.000024 | 557 | Query | 1 | 683 | use `jiaowu`; insert into tutors (tname,gender,age) values('stu100','M',30) |
| mysql-bin.000024 | 683 | Xid | 1 | 710 | COMMIT /* xid=30 */ |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
# 看倒数第二行 use `jiaowu`; insert into tutors (tname,gender,age) values('stu100','M',30)
看看 从 459 至 710 中间的数据 ,我们只执行了 insert ,看二进制日志里面是多个从 begin (529)开始到commit (710)结束,所以二进制日志产生额外开销的
看看 107 文件首部,记录的是服务器版本号,二进制日志文件格式的版本号等,固定都是从107开始 (一般来说mysql5固定头只有107个字节)
mysql>
mysql> show binlog events in 'mysql-bin.000024' from 107; # from 107 ,,从 107 的位置开始显示
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
(Pos起始位置) (Server_id 服务器id号,表示由哪个服务器产生的二进制日志) (End_log_pos 结束位置)
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000024 | 107 | Query (查询) | 1 | 176 | BEGIN |
| mysql-bin.000024 | 176 | Query | 1 | 309 | use `mysql`; update user set password=password('zhong1926') where user like 'root' |
| mysql-bin.000024 | 309 | Query | 1 | 379 | COMMIT |
| mysql-bin.000024 | 379 | Query | 1 | 459 | use `mysql`; flush privileges |
| mysql-bin.000024 | 459 | Query | 1 | 529 | BEGIN |
| mysql-bin.000024 | 529 | Intvar (inter variable) (内部变量) | 1 | 557 | INSERT_ID=15 |
| mysql-bin.000024 | 557 | Query | 1 | 683 | use `jiaowu`; insert into tutors (tname,gender,age) values('stu100','M',30) |
| mysql-bin.000024 | 683 | Xid (事务id) | 1 | 710 | COMMIT /* xid=30 */ |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
事实上是有时间,这里没有显示出来而已
通过 mysqlbinlog 来查看二进制日志相关信息 (当然也包括时间)
mysql>
[root@mail data]# man mysqlbinlog
[root@mail data]# pwd
/mydata/data
[root@mail data]# ls
cactidb mysql mysql-bin.000011 mysql-bin.000022
edb mysql-bin.000001 mysql-bin.000012 mysql-bin.000023
hellodb mysql-bin.000002 mysql-bin.000013 mysql-bin.000024
ibdata1 mysql-bin.000003 mysql-bin.000014 mysql-bin.index
ib_logfile0 mysql-bin.000004 mysql-bin.000015 performance_schema
ib_logfile1 mysql-bin.000005 mysql-bin.000016 students
jiaowu mysql-bin.000006 mysql-bin.000017 test
mail.magedu.com.err mysql-bin.000007 mysql-bin.000018 test2
mail.magedu.com.pid mysql-bin.000008 mysql-bin.000019 testdb
mail-slow.log mysql-bin.000009 mysql-bin.000020
mydb mysql-bin.000010 mysql-bin.000021
[root@mail data]#
[root@mail data]# mysqlbinlog mysql-bin.000024 #查看日志文件的内容,它是二进制格式,不能使用cat查看
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/; #上面是文件头部
# at 4 #第4个位置
#201009 15:55:44 #产生时间 2020年10月01日 15时55分44秒 server id 1 #服务器id end_log_pos 107 #结束位置 Start: binlog v 4, server v 5.5. 28-log created 201009 15:55:44 at startup #左边的都是文件头部
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
gBeAXw8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACAF4BfEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107 #从107开始,才是真正的事件开始
#201010 9:51:32 #时间 server id 1#哪个服务器 end_log_pos 176 #结束位置 Query #事件类型 thread_id=4 #线程号 exec_tim e=0 #执行时长 error_code=0 #错误代码
#下面是一堆语句
SET TIMESTAMP=1602294692/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq ue_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/ ;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio n.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 176 #上面 从107 到这里176 ,中间是同一个事件 这里at表示一个事件的开始 176这个位置,是上个事件的结束位置,也是下个事件的开始位置
#201010 9:51:32 server id 1 end_log_pos 309 Query thread_id=4 exec_tim e=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1602294692/*!*/;
update user set password=password('zhong1926') where user like 'root'
/*!*/;
# at 309
#201010 9:51:32 server id 1 end_log_pos 379 Query thread_id=4 exec_tim e=0 error_code=0
SET TIMESTAMP=1602294692/*!*/;
COMMIT
/*!*/;
# at 379
#201010 9:51:41 server id 1 end_log_pos 459 Query thread_id=4 exec_tim e=0 error_code=0
SET TIMESTAMP=1602294701/*!*/;
flush privileges
/*!*/;
# at 459
#201010 16:08:06 server id 1 end_log_pos 529 Query thread_id=6 exec_tim e=0 error_code=0
SET TIMESTAMP=1602317286/*!*/;
BEGIN
/*!*/;
# at 529
#201010 16:08:06 server id 1 end_log_pos 557 Intvar
SET INSERT_ID=15/*!*/;
# at 557
#201010 16:08:06 server id 1 end_log_pos 683 Query thread_id=6 exec_tim e=0 error_code=0
use jiaowu/*!*/;
SET TIMESTAMP=1602317286/*!*/;
insert into tutors (tname,gender,age) values('stu100','M',30)
/*!*/;
# at 683
#201010 16:08:06 server id 1 end_log_pos 710 Xid = 30
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@mail data]#
[root@mail data]# mysqlbinlog --start-position=557 --stop-position=683 mysql-bin.000024 #指定开始位置 结束位置
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201009 15:55:44 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 201009 15:55:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
gBeAXw8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACAF4BfEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
#以上头,它都会包含的
# at 557
#201010 16:08:06 server id 1 end_log_pos 683 Query thread_id=6 exec_time=0 error_code=0
use jiaowu/*!*/;
SET TIMESTAMP=1602317286/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into tutors (tname,gender,age) values('stu100','M',30)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@mail data]#
[root@mail data]# mysqlbinlog --start-datetime='2020-10-10 16:08:06' mysql-bin.000024 #指定某一时间开始之后的所有 #注意时间格式
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201009 15:55:44 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 201009 15:55:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
gBeAXw8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACAF4BfEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
#文件头部反正都显示的
# at 459
#201010 16:08:06 server id 1 end_log_pos 529 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1602317286/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 529
#201010 16:08:06 server id 1 end_log_pos 557 Intvar
SET INSERT_ID=15/*!*/;
# at 557
#201010 16:08:06 server id 1 end_log_pos 683 Query thread_id=6 exec_time=0 error_code=0
use jiaowu/*!*/;
SET TIMESTAMP=1602317286/*!*/;
insert into tutors (tname,gender,age) values('stu100','M',30)
/*!*/;
# at 683
#201010 16:08:06 server id 1 end_log_pos 710 Xid = 30
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@mail data]#
二进制日志里面的内容信息其实都是sql语句,其实可以把它们保存下来的
[root@mail data]# mysqlbinlog --start-datetime='2020-10-10 16:08:06' mysql-bin.000024 > ~/a.sql #导出到 a.sql
把这个a.sql导入到另外一个数据库上,它能够执行的
所以我们通常可以通过 mysqlbinlog 把日志文件读取出来,输出重定向到一个脚本文件里面,在另外一个服务器上执行一遍,就能够完成即时点还原了,,,但是要注意起始位置要与上一次备份恢复的位置相一致
[root@mail data]#
重启服务器,二进制日志会滚动的
[root@mail data]# service mysqld restart
Shutting down MySQL. [确定]
Starting MySQL.. [确定]
[root@mail data]#
[root@mail data]# service mysqld restart
Shutting down MySQL. [确定]
Starting MySQL.. [确定]
[root@mail data]#
mysql> show master status; #看到日志滚动了,重启了两次 日志由 mysql-bin.000024 变成了 ysql-bin.000026
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000026 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> flush logs; # flush logs 命令可以手动滚动二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000027 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> flush hosts; #这个命令不能实现日志滚动
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000027 | 177 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
# flush logs 只会滚动二进制日志,错误日志只会关闭再打开,不会滚动错误日志
在从服务器上它会滚动中继日志,错误日志仍然是关闭再打开,
备份完删除想某些二进制日志,
不能手动 rm 二进制日志,,否则下次重启时mysql会崩溃的
使用 purge 命令来删除想某些二进制日志,
mysql> help purge
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr } #把 log_name 之前的所有二进制日志文件内容全删了
The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html).
The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.
This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.
URL: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
mysql>
[root@mail data]# pwd
/mydata/data
[root@mail data]# ls
cactidb mysql-bin.000001 mysql-bin.000013 mysql-bin.000025
edb mysql-bin.000002 mysql-bin.000014 mysql-bin.000026
hellodb mysql-bin.000003 mysql-bin.000015 mysql-bin.000027
ibdata1 mysql-bin.000004 mysql-bin.000016 mysql-bin.index
ib_logfile0 mysql-bin.000005 mysql-bin.000017 performance_schema
ib_logfile1 mysql-bin.000006 mysql-bin.000018 students
jiaowu mysql-bin.000007 mysql-bin.000019 test
mail.magedu.com.err mysql-bin.000008 mysql-bin.000020 test2
mail.magedu.com.pid mysql-bin.000009 mysql-bin.000021 testdb
mail-slow.log mysql-bin.000010 mysql-bin.000022
mydb mysql-bin.000011 mysql-bin.000023
mysql mysql-bin.000012 mysql-bin.000024
[root@mail data]#
mysql> purge binary logs to 'mysql-bin.000003'; #清除这个文件之前的所有二进制日志文件,(不包括这个文件)
ERROR 1373 (HY000): Target log not found in binlog index
mysql>
[root@mail data]# cat mysql-bin.index #果然没有看到 mysql-bin.000003
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020
./mysql-bin.000021
./mysql-bin.000022
./mysql-bin.000023
./mysql-bin.000024
./mysql-bin.000025
./mysql-bin.000026
./mysql-bin.000027
[root@mail data]#
mysql> purge binary logs to 'mysql-bin.000012'; #清除这个文件之前的所有二进制日志文件,(不包括这个文件)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show binary logs; #查看当前仍然拥有的二进制日志文件 mysql-bin.000012之前的都被删掉了
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000012 | 1038693 |
| mysql-bin.000013 | 26239 |
| mysql-bin.000014 | 1038693 |
| mysql-bin.000015 | 126 |
| mysql-bin.000016 | 126 |
| mysql-bin.000017 | 126 |
| mysql-bin.000018 | 126 |
| mysql-bin.000019 | 999 |
| mysql-bin.000020 | 883 |
| mysql-bin.000021 | 14046 |
| mysql-bin.000022 | 2849 |
| mysql-bin.000023 | 390 |
| mysql-bin.000024 | 729 |
| mysql-bin.000025 | 126 |
| mysql-bin.000026 | 150 |
| mysql-bin.000027 | 177 |
+------------------+-----------+
16 rows in set (0.00 sec)
mysql>
mysql> show global variables like '%log%'; # 没看到定义二进制日志文件的位置,所以是默认位置,默认名称
+-----------------------------------------+----------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 | #含binlog的就是与二进制日志相关的服务器变量
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED | #二进制日志格式 这是混合格式
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /mydata/data/mail.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON | #是不是记录二进制日志的
| log_bin_trust_function_creators | OFF |
| log_error | /mydata/data/mail.magedu.com.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /mydata/data/mail-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+----------------------------------+
41 rows in set (0.00 sec)
mysql>