MySQL数据库备份&恢复（日志管理）

最新推荐文章于 2024-06-21 01:01:02 发布

Johnny.G

最新推荐文章于 2024-06-21 01:01:02 发布

阅读量837

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/JohnnyG2000/article/details/124296435

版权

本文详细介绍了MySQL数据库中数据丢失的原因，包括误删除、硬件故障、自然灾害等，并重点讲解了日志管理，包括错误日志、查询日志、慢查询日志和极其重要的二进制日志。错误日志记录启动、运行或关闭时的问题，查询日志记录执行的SQL，慢查询日志记录执行时间过长的查询，二进制日志用于数据恢复和复制。通过设置日志参数和路径，可以开启和管理这些日志，确保数据库的安全和高效运行。

摘要由CSDN通过智能技术生成

1、数据库中数据丢失或被破坏可能原因

误删除数据库
数据库工作时，意外断电或程序意外终止
由于病毒造成的数据库损坏或丢失
文件系统损坏后，系统进行自检操作
升级数据库时，命令语句不严格
设备故障等等
自然灾害
盗窃

2、日志类型

日志文件	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志（重要）	记录所有更改数据的语句。主要用于复制和即时点恢复
慢查询日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

1）错误日志

错误日志主要记录如下几种日志：

服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
服务器运行过程中的错误信息
事件调度器运行一个时间是产生的信息
在从服务器上启动从服务器进程是产生的信息

错误日志定义：

如果–log-error没有给出，将错误日志写入控制台
【注：如果服务器将错误日志写到控制台中，它会将log_error系统变量设置为stderr（标准输出）】

[root@localhost ~]# vim /etc/my.cnf		#修改MySQL配置文件，注释错误日志行
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

#log-error=/var/log/mysqld.log		#将此行注释
pid-file=/usr/local/mysql/data/mysqld.pid

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | stderr       |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.01 sec)
#可以看出“log_error=stderr”表示标准输出

如果只给出–log-error，其它文件路径、文件名都不写的情况下，系统会给定默认位置以及默认名
【默认位置：/var/run/mysqld/ 默认名：mysqld.err】

[root@localhost ~]# vim /etc/my.cnf		#修改MySQL配置文件，只添加“log-error”
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

#log-error=/var/log/mysqld.log		
log-error		#只添加“log-error”
pid-file=/usr/local/mysql/data/mysqld.pid

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

mysql> show variables like "%log_error%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/run/mysqld/mysqld.err |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)
#可以看出“log_error=/var/run/mysqld/mysqld.err”表示为默认格式

如果–log-error给出时，只指定文件名，不指定路径，系统会在当前目录下创建该错误日志文件
【注：此种情况，错误文件名仍为自定义文件名】

[root@localhost ~]# vim /etc/my.cnf		#修改MySQL配置文件，添加错误文件名为“mysqld.log”
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

#log-error=/var/log/mysqld.log		
log-error=mysqld.log		#添加错误文件名为“mysqld.log”
pid-file=/usr/local/mysql/data/mysqld.pid

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | ./mysqld.log |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.00 sec)
#可以看出“log_error=./mysqld.log”表示放在当前目录下

如果–log-error给出时，文件名以及文件路径全部指定，系统会按照给定的文件名和文件路径创建错误日志

[root@localhost ~]# vim /etc/my.cnf		
#修改MySQL配置文件，删除“#log-error=/var/log/mysqld.log”的注释，并删除“log-error=mysqld.log”
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log		#删除“#log-error=/var/log/mysqld.log”的注释
		#删除“log-error=mysqld.log”
pid-file=/usr/local/mysql/data/mysqld.pid

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

mysql> show variables like "%log_error%";
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.01 sec)
#可以看出“log_error/var/log/mysqld.log”表示按照指定的文件名和文件路径创建错误日志

2）查询日志

查询日志定义：
【注：查询日志默认关闭】

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
#可看出查询日志默认关闭

临时开启

mysql> set global general_log=ON;	//临时开启
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
#可看出查询日志已开启

永久开启需要通过修改配置文件（类似错误日志）

[root@localhost ~]# cat /var/lib/mysql/localhost.log
/usr/sbin/mysqld, Version: 5.7.37 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-04-22T02:10:38.618622Z	    2 Query	SHOW GLOBAL VARIABLES LIKE '%general_log%'
#开启之后可以看到记录了刚刚查询的语句（所有查询的语句都会记录：如查数据库、查表等等）

3）慢查询日志

慢查询日志定义：
如果查询时长超过long_query_time的定义值（默认10秒）即为慢查询

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#该默认时间可修改（自定义）
#一般大多数互联网企业都会定位3秒（超过3秒为慢查询）

mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
#默认没有开启慢查询日志，开启之后，超过界定时间的查询语句都会记录在慢查询日志中

只能通过修改配置文件开启慢查询日志

[root@localhost ~]# vim /etc/my.cnf		
#修改MySQL配置文件，开启慢查询日志，指定慢查询日志文件名以及文件路径，修改慢查询界定时间为3s
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

slow_query_log=ON		#开启慢查询日志
slow_query_log_file=/var/lib/mysql/localhost-slow.log		#指定慢查询日志文件名以及文件路径
long_query_time=3		#修改慢查询界定时间为3s

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.01 sec)
#可看出慢查询日志已开启，并且文件名和文件路径与所指定一致

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
#可看出慢查询界定时间已修改为3s

查看慢查询日志

[root@localhost ~]# mysqldumpslow /var/lib/mysql/localhost-slow.log
#仅能通过此种方法查看慢查询日志，不然可能会出现乱码或其它情况导致难以查看

4）二进制日志（极其重要）

二进制日志的概念：
二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE语句）的所有语句。
语句以“事件”的形式保存，它描述数据更改。二进制日志还包含关于每个更新数据库的语句的执行时间信息，不包含没有修改任何数据的语句。

二进制日志主要目的：
在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

二进制日志会分三种不同的情况进行记录（智能匹配）：

第一种情况：假如一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加1000：
UPDATE sales.january SET amount=amount+1000;
【此时如果要记录执行后的结果数据的话，日志会非常大，因此在这种情况下应记录执行语句，这种方式就是基于语句的二进制日志】
第二种情况：如果向某个字段插入的是当前的时间：
INSERT INTO tb SET Birthdate=CURRENT_TIME();
【此时就不能记录语句了，因为不同时间执行的结果是不一样的，这时应该记录这一行的值，这种就是基于行（row）的二进制日志】
第三种情况：可能会结合两种方式来记录
【这种叫做混合方式的二进制日志】

二进制日志参数：

mysql> SHOW GLOBAL VARIABLES LIKE '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)
#需要注意的是“binlog_format=ROW”表示默认模式为“基于行（ROW）的二进制日志”

#可以修改“binlog_format”的默认值
mysql> set global binlog_format = "STATEMENT";		#基于语句的二进制日志
mysql> set global binlog_format = "ROW";		#基于行（ROW）的二进制日志
mysql> set global binlog_format = "MIXED";		#混合方式的二进制日志”

二进制日志定义：

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.00 sec)

只能通过修改配置文件开启二进制日志

[root@localhost ~]# vim /etc/my.cnf		
#修改MySQL配置文件，开启二进制日志，指定二进制日志文件名以及文件路径，指定“server_id”
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=3

log_bin=/var/lib/mysql/mysql-bin		#bin_log指定文件名和文件路径
server_id=131		#MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IP地址主机位）

[root@localhost ~]# systemctl restart mysqld	#重启MySQL服务

[root@localhost ~]# ll /var/lib/mysql		#查看二进制日志文件路径
total 122976
-rw-r-----. 1 mysql mysql       56 Mar 26 14:37 auto.cnf
-rw-------. 1 mysql mysql     1680 Mar 26 14:37 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Mar 26 14:37 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Mar 26 14:37 client-cert.pem
-rw-------. 1 mysql mysql     1676 Mar 26 14:37 client-key.pem
-rw-r-----. 1 mysql mysql      291 Apr 22 11:00 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Apr 22 11:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Apr 22 11:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 26 14:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Apr 22 11:00 ibtmp1
-rw-r-----. 1 mysql mysql      262 Apr 22 10:15 localhost.log
-rw-r-----. 1 mysql mysql      540 Apr 22 11:00 localhost-slow.log
drwxr-x---. 2 mysql mysql     4096 Mar 26 14:37 mysql
-rw-r-----. 1 mysql mysql      154 Apr 22 11:00 mysql-bin.000001	#二进制日志
-rw-r-----. 1 mysql mysql       32 Apr 22 11:00 mysql-bin.index		#二进制日志格式
-rw-r-----. 1 mysql mysql     7980 Apr 22 10:00 mysqld.log
srwxrwxrwx. 1 mysql mysql        0 Apr 22 11:00 mysql.sock
-rw-------. 1 mysql mysql        5 Apr 22 11:00 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Mar 26 14:37 performance_schema
-rw-------. 1 mysql mysql     1680 Mar 26 14:37 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Mar 26 14:37 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Mar 26 14:37 server-cert.pem
-rw-------. 1 mysql mysql     1676 Mar 26 14:37 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Mar 26 14:37 sys

二进制日志查看

#执行几条SQL语句
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table test1(id int,name char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看二进制日志
#此方法查看只能看到乱码，无法查看内容
[root@localhost ~]# cat /var/lib/mysql/mysql-bin.000001		                     _þbinObbw{5.7.37-logObb8

**4ڙUObb#򍒓bb"Aفu<bb^!  Ustd!!-
                              testtestcreate database test~"Iªbb"AzP]ʂªbbr鿂!  Ustd!!-
     testtestcreate table test1(id int,name char(20))Iʹϝbb"A-þ>³ϝbbH  Ustd!!-test

#查看二进制文件方法一：
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |       131 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                |
| mysql-bin.000001 | 123 | Previous_gtids |       131 |         154 |                                                      |
| mysql-bin.000001 | 154 | Anonymous_Gtid |       131 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                 |
| mysql-bin.000001 | 219 | Query          |       131 |         313 | create database test                                 |
| mysql-bin.000001 | 313 | Anonymous_Gtid |       131 |         378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                 |
| mysql-bin.000001 | 378 | Query          |       131 |         492 | use `test`; create table test1(id int,name char(20)) |
| mysql-bin.000001 | 492 | Anonymous_Gtid |       131 |         557 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                 |
| mysql-bin.000001 | 557 | Query          |       131 |         629 | BEGIN                                                |
| mysql-bin.000001 | 629 | Table_map      |       131 |         680 | table_id: 108 (test.test1)                           |
| mysql-bin.000001 | 680 | Write_rows     |       131 |         729 | table_id: 108 flags: STMT_END_F                      |
| mysql-bin.000001 | 729 | Xid            |       131 |         760 | COMMIT /* xid=9 */                                   |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
11 rows in set (0.00 sec)

#查看二进制文件方法二：使用官方提供的命令“mysqlbinlog”
[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220422 11:00:31 server id 131  end_log_pos 123 CRC32 0x55d90fd9 	Start: binlog v 4, server v 5.7.37-log created 220422 11:00:31 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TxpiYg+DAAAAdwAAAHsAAAABAAQANS43LjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABPGmJiEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdkP2VU=
'/*!*/;
# at 123
#220422 11:00:31 server id 131  end_log_pos 154 CRC32 0xd3514ef7 	Previous-GTIDs
# [empty]
# at 154
#220422 11:07:48 server id 131  end_log_pos 219 CRC32 0x801c3c75 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220422 11:07:48 server id 131  end_log_pos 313 CRC32 0x49229e7e 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1650596868/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 313
#220422 11:14:50 server id 131  end_log_pos 378 CRC32 0x82ca5d50 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 378
#220422 11:14:50 server id 131  end_log_pos 492 CRC32 0x1fb4cd49 	Query	thread_id=2	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1650597290/*!*/;
create table test1(id int,name char(20))
/*!*/;
# at 492
#220422 11:15:29 server id 131  end_log_pos 557 CRC32 0xb33efe17 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 557
#220422 11:15:29 server id 131  end_log_pos 629 CRC32 0xaba06f95 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1650597329/*!*/;
BEGIN
/*!*/;
# at 629
#220422 11:15:29 server id 131  end_log_pos 680 CRC32 0xd7fee094 	Table_map: `test`.`test1` mapped to number 108
# at 680
#220422 11:15:29 server id 131  end_log_pos 729 CRC32 0x3a6845ec 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
0R1iYhODAAAAMwAAAKgCAAAAAGwAAAAAAAEABHRlc3QABXRlc3QxAAID/gL+UAOU4P7X
0R1iYh6DAAAAMQAAANkCAAAAAGwAAAAAAAEAAgAC//wBAAAAAWH8AgAAAAFi7EVoOg==
'/*!*/;
# at 729
#220422 11:15:29 server id 131  end_log_pos 760 CRC32 0x34dc1aae 	Xid = 9
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#从上面可看出刚刚执行的SQL语句
#但由于insert语句经过了加密，所以insert语句正常无法查看

#如需查看insert语句内容，需要加上解密参数“--base64-output=DECODE-ROWS -vv”
[root@localhost ~]# mysqlbinlog --base64-output=DECODE-ROWS -vv /var/lib/mysql/mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220422 11:00:31 server id 131  end_log_pos 123 CRC32 0x55d90fd9 	Start: binlog v 4, server v 5.7.37-log created 220422 11:00:31 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#220422 11:00:31 server id 131  end_log_pos 154 CRC32 0xd3514ef7 	Previous-GTIDs
# [empty]
# at 154
#220422 11:07:48 server id 131  end_log_pos 219 CRC32 0x801c3c75 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220422 11:07:48 server id 131  end_log_pos 313 CRC32 0x49229e7e 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1650596868/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 313
#220422 11:14:50 server id 131  end_log_pos 378 CRC32 0x82ca5d50 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 378
#220422 11:14:50 server id 131  end_log_pos 492 CRC32 0x1fb4cd49 	Query	thread_id=2	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1650597290/*!*/;
create table test1(id int,name char(20))
/*!*/;
# at 492
#220422 11:15:29 server id 131  end_log_pos 557 CRC32 0xb33efe17 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 557
#220422 11:15:29 server id 131  end_log_pos 629 CRC32 0xaba06f95 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1650597329/*!*/;
BEGIN
/*!*/;
# at 629
#220422 11:15:29 server id 131  end_log_pos 680 CRC32 0xd7fee094 	Table_map: `test`.`test1` mapped to number 108
# at 680
#220422 11:15:29 server id 131  end_log_pos 729 CRC32 0x3a6845ec 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`test1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* STRING(80) meta=65104 nullable=1 is_null=0 */
### INSERT INTO `test`.`test1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* STRING(80) meta=65104 nullable=1 is_null=0 */
# at 729
#220422 11:15:29 server id 131  end_log_pos 760 CRC32 0x34dc1aae 	Xid = 9
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看现有的二进制日志文件

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       760 |
+------------------+-----------+
1 row in set (0.00 sec)
#可以看到当前仅有“mysql-bin.000001”一个二进制日志文件

查看当前正在使用的二进制日志文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      760 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以看到当前正在使用“mysql-bin.000001”二进制日志文件

日志滚动
假如在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。

触发日志滚动：

二进制日志尺寸大于界限范围；
重新启动MySQL服务；
输入命令“mysql> FLUSH LOGS;”，手动触发滚动日志

测试
【修改配置文件不做演示】

#手动触发日志滚动
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       807 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)
#可以看到当前多出了一个“mysql-bin.000002”二进制日志文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以看到当前正在使用的二进制日志文件为刚刚日志滚动创建的“mysql-bin.000002”

#重启MySQL服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pMySql@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       807 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)
#可以看到当前多出了一个“mysql-bin.000003”二进制日志文件

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)
#可以看到当前正在使用的二进制日志文件为刚刚日志滚动创建的“mysql-bin.000003”

删除二进制日志文件
二进制日志文件不能直接删除，如果使用rm等命令直接删除日志文件，可能会导致数据库的崩溃。

删除示例

#用purge master logs to 'filename.******' 命令可以删除指定编号前的所有日志
#语法：PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
mysql> purge master logs to 'mysql-bin.000002';		#表示删除“02”之前的二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)
#可以看到删除之后只剩下“02”和“03”两个二进制日志

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)
#除非删除最新的二进制日志（一般不会这么做）或者进行了日志重置（“reset”命令），不然此项不会改变

#用reset master命令删除所有日志，新日志重新从000001开始编号
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)
#可以看到序号又回到了“01”

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以看到当前正在使用的二进制日志文件又变回“01”

通过二进制日志还原数据
导出

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /root/temp_date.sql
#先将日志文件输出重定向到.sql文件
[root@localhost ~]# ll temp_date.sql 
-rw-r--r--. 1 root root 846 Apr 22 14:14 temp_date.sql
#可以看到刚导出的.sql文件已经在家目录

导入

#方法一：命令行“mysql”命令登入时导入
[root@localhost ~]# mysql -uroot -pMySql@123 < temp_date.sql

#方法二：MySQL内“source”命令导入
mysql> source /var/lib/mysql/mysql-bin.000001;

Johnny.G

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫