mysql 二进制日志日期恢复 mysql 二进制日志文件

转载

mob6454cc6c8549 2023-10-18 20:42:12

文章标签 mysql 二进制日志日期恢复 mysql 数据库二进制日志 Server 文章分类 MySQL 数据库

二进制日志（binlog）

二进制日志记录了除查询操作外所有的数据库，默认情况下二进制日志并没有开启。可通过修改配置文件开启。

#查找配置文件位置
[root@iZ ~]# whereis my.cnf
my: /etc/my.cnf
#编辑配置文件，在[mysqld]模块下加入或修改。
log-bin=dir/filename
#重启mysql服务
[root@iZ ~]# service mysql restart

dir/filename为指定目录下/指定文件名。具体格式为filename.number,number格式为000001，000002等)如果没有设置dir和filename二进制日志文件将使用默认名字：主机名-bin.number,保存到默认目录数据库文件里。每次重启mysql服务器都会生成一个新的二进制文件，这些文件filename的名字不会改变，但是number会不断递增。二进制日志相关文件除了保存内容的filename.nameber文件外，还有一个关于二进制日志文件列表的文件filename.index。

mysql> #查看Binlog日志相关信息
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| log_bin                         | ON                                   |
| log_bin_basename                | /usr/local/mysql/var/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/var/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                  |
| log_bin_use_v1_row_events       | OFF                                  |
| sql_log_bin                     | ON                                   |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
mysql> #查看Binlog参数
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | IGNORE_ERROR         |
| binlog_format                           | MIXED                |
| binlog_gtid_simple_recovery             | OFF                  |
| 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                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| simplified_binlog_gtid_recovery         | OFF                  |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
19 rows in set (0.00 sec)

mysql> #查看当前日志存放位置
mysql> show variables like '%datadir%';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/mysql/var/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> #查看二进制日志目录
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 |       143 |
| mysql-bin.000009 |       167 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       120 |
+------------------+-----------+
4 rows in set (0.00 sec)

mysqladmin、mysqldump等一般存在于mysql安装目录下的bin目录中。

#使用flush-logs刷新日志文件,可以立即老化当前文件的记录，重新生成记录一个新的日志文件。
[root@iZ ~]# ./mysqladmin flush-logs

mysql> 刷新日志后重新生成了新的日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 |       143 |
| mysql-bin.000009 |       167 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       167 |
| mysql-bin.000012 |       120 |
+------------------+-----------+
5 rows in set (0.00 sec)

我们先来做点事情让二进制日志记录下来

mysql> #做些事情来让二进制日志记录下来
mysql> create table test1(
    -> `id` int(5) not null primary key auto_increment comment '主键自增id',
    -> `name` varchar(45) comment '姓名')
    -> engine=myisam default charset=utf8 comment '测试一下binlog日志';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(`name`) values('lisi'),('wangwu'),('mazi');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

上面的都不重要，现在开始划重点了，我们要开始操作二进制日志

mysql> #查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000012'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000012
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.36-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000012
        Pos: 120
 Event_type: Query
  Server_id: 1
End_log_pos: 214
       Info: create database test
*************************** 3. row ***************************
   Log_name: mysql-bin.000012
        Pos: 214
 Event_type: Query
  Server_id: 1
End_log_pos: 490
       Info: use `test`; create table test1( `id` int(5) not null primary key auto_increment comment '主键自增id',  `name` varchar(45) comment '姓名')  engine=myisam default charset=utf8 comment '测试一下binlog日志'
*************************** 4. row ***************************
   Log_name: mysql-bin.000012
        Pos: 490
 Event_type: Query
  Server_id: 1
End_log_pos: 569
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000012
        Pos: 569
 Event_type: Intvar
  Server_id: 1
End_log_pos: 601
       Info: INSERT_ID=1
*************************** 6. row ***************************
   Log_name: mysql-bin.000012
        Pos: 601
 Event_type: Query
  Server_id: 1
End_log_pos: 735
       Info: use `test`; insert into test1(`name`) values('lisi'),('wangwu'),('mazi')
*************************** 7. row ***************************
   Log_name: mysql-bin.000012
        Pos: 735
 Event_type: Query
  Server_id: 1
End_log_pos: 815
       Info: COMMIT

7 rows in set (0.00 sec)
mysql> #注意pos这个键值，我们可以利用它去获取指定的语句。601是pos号，limit 指读取多少条，可不加，默认读取后面所有。
mysql> show binlog events in 'mysql-bin.000012' from 601 limit 1\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000012
        Pos: 601
 Event_type: Query
  Server_id: 1
End_log_pos: 735
       Info: use `test`; insert into test1(`name`) values('lisi'),('wangwu'),('mazi')
1 row in set (0.00 sec)

恢复数据需要在使用mysqlbinlog命令，你可以在上面提到的存放mysqladmin的位置找到它。
我将删除刚刚所建的test表和其全部数据，不再演示，只是为了方便演示恢复数据。

[root@iZ ~]#查看指定二进制文件的内容 不展示结果了，太长，看着头疼
[root@iZ ~]# ./mysqlbinlog /usr/local/mysql/var/mysql-bin.000012
[root@iZ ~]# #将语句要还原的内容导入到指定sql文件中 
[root@iZ ~]# ./mysqlbinlog --start-position='214' --stop-position='815' /usr/local/mysql/var/mysql-bin.000012 > bin.sql
[root@iZ ~]#
[root@iZ ~]#将sql文件还原到到数据库
[root@iZ ~]# mysql -uroot -p <bin.sql 
Enter password: 
mysql> #查看是否还原成功
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+----+--------+
| id | name   |
+----+--------+
|  1 | lisi   |
|  2 | wangwu |
|  3 | mazi   |
+----+--------+
3 rows in set (0.00 sec)