mysql的binlog日志-优快云博客

本文详细介绍了MySQL的binlog日志系统，包括其三种格式：statement、row和mixed，以及如何通过修改配置文件来开启binlog。文章还讲解了binlog的存放位置、如何查看binlog状态和内容，以及如何进行binlog日志的管理和恢复操作，包括打断、清空日志和按特定条件恢复数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、基础概念：

1、概念：binlog日志是mysql的二进制日志，包含mysql的增删改的内容

格式有三种：

（1）statement：记录执行语句，写入一条sql语句即记录一条。

如一条命令更改数千条结果，则使用此格式。

（2）row：记录磁盘变化。只关注磁盘变化。

如一个复杂的语句执行后磁盘并未有很多变化，用此格式。

（3）mixed：由系统根据语句决定。

2、开启：

#vim /etc/my.cnf

log-bin=日志名

#binlog格式(非必要)

binlog_format=MIXED

3.存放位置：

1）yum的binlog默认在 /var/lib/mysql 下

2）源码包的binlog在 /usr/local/mysql/data/ 下

4、查看是否开启：

> show variables like "%bin%";
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| log_bin_trust_routine_creators          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+

5、打断logbin日志，重新开始以个新binlog日志：

> flush logs;

6、查看最后一个binlog日志的位置：

> show master status;
+------------------------+-----------+-------------------+-------------------------+
| File                        | Position  | Binlog_Do_DB | Binlog_Ignore_DB   |
+-----------------------+------------+--------------------+-------------------------+
| mysql-bin.000007 |      106    |                          |                                |
+-----------------------+------------+--------------------+-------------------------+
1 row in set (0.00 sec)

每一日志总是从106开始的。

7、清空所有binlog日志：

> reset master 
+------------------------+-----------+-------------------+-------------------------+
| File                   | Position  | Binlog_Do_DB      | Binlog_Ignore_DB        |
+------------------------+-----------+-------------------+-------------------------+
| mysql-bin.000001       |      106  |                   |                         |
+-----------------------+------------+-------------------+-------------------------+
1 row in set (0.00 sec)

8、查看binlog日志内容

# mysqlbinlog --no-defaults mysql-bin000007 |more

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mysql-bin000007' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

二、binlog恢复数据库。

1、基本语法：

# mysqlbinlog --no-defaults mysql-bin.00000x | mysql -uroot -p123 库名

2、参数（灵活恢复）

--start-position="500"

--stop-position="712"

--start-date="2017-01-05 10:00"

--stop-date="2017-01-05 10:30"

3、数据恢复演示

1）模拟前准备，得到模拟故障时间段的binlog日志

操作：打断日志，创建一个新表，然后再次打断日志。得到该区间的binlog日志mysql-bin.000009。

> flush logs;

Query OK, 0 rows affected (0.03 sec)



> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000009 |      106 |              |                  |

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

1 row in set (0.00 sec)



> create table bin_test(id int);

Query OK, 0 rows affected (0.05 sec)



> insert into bin_test values(1),(2);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0



> select * from bin_test;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+
2 rows in set (0.00 sec)



> flush logs;

Query OK, 0 rows affected (0.03 sec)

2）删除新创建的 bin_test 表，模拟数据丢失。（略）

3）恢复数据。

# mysqlbinlog --no-defaults mysql-bin.000009 | mysql -uroot -p123 school

4）验证（略）

转载于:https://my.oschina.net/weige1840/blog/2962437