如何开启Mariadb或MySQL Binlog?

MySQL & MariaDB

1 前言

一个问题,一篇文章,一出故事。
笔者需要备份Mariadb的binlog,于是整理此文。

2 最佳实践

2.1 确认存储空间足够(可选)

由于开启binlog会增加存储的压力,我们不建议将binlog存于根分区,可参考如下步骤迁移数据库存储目录,

如何迁移MariaDB或MySQL数据目录?

2.2 查询binlog的当前定义

mysql -uroot -p

以上登录数据库,然后使用如下查看binlog的变量,

show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_compress                | OFF   |
| log_bin_compress_min_len        | 256   |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
7 rows in set (0.00 sec)

需要注意的是,当前“log_bin”处于“OFF”状态,接着我们运行如下命令,

show variables like '%binlog%';

可见如下显示,

+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events              | ON                   |
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_commit_wait_count                | 0                    |
| binlog_commit_wait_usec                 | 100000               |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_optimize_thread_scheduling       | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_stmt_cache_size                  | 32768                |
| encrypt_binlog                          | OFF                  |
| gtid_binlog_pos                         |                      |
| gtid_binlog_state                       |                      |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| read_binlog_speed_limit                 | 0                    |
| sync_binlog                             | 0                    |
| wsrep_forced_binlog_format              | NONE                 |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)

需要注意的是当前“ binlog_format”格式为“MIXED”(建议使用“ROW”)

2.3 修改binlog的定义

vim /etc/my.cnf.d/mysql-server.cnf

配置修改如下,

[mysqld]
log_bin=binlog
binlog_format=ROW
expire_logs_days=7

修改完毕后,我们建议使用如下命令测试配置,

/etc/init.d/mysqld configtest

2.4 重载服务使配置生效

/etc/init.d/mysqld restart

需要注意的是,根据实际情况,也可以使用如下命令,

systemctl restart mysqld.service

需要注意的是,如果不方便重启请使用如下命令直接配置并立刻生效,

set global log_bin=binlog;
set global binlog_format=ROW;
set global expire_logs_days=7;

2.5 确认binlog的定义生效

mysql -uroot -p

以上登录数据库,然后使用如下查看binlog的变量,

show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_compress                | OFF                      |
| log_bin_compress_min_len        | 256                      |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+
7 rows in set (0.00 sec)

需要注意的是,当前“log_bin”处于“ON”状态,接着我们运行如下命令,

show variables like '%binlog%';

可见如下显示,

+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events              | ON                   |
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_commit_wait_count                | 0                    |
| binlog_commit_wait_usec                 | 100000               |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | ROW                  |
| binlog_optimize_thread_scheduling       | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_stmt_cache_size                  | 32768                |
| encrypt_binlog                          | OFF                  |
| gtid_binlog_pos                         |                      |
| gtid_binlog_state                       |                      |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| read_binlog_speed_limit                 | 0                    |
| sync_binlog                             | 0                    |
| wsrep_forced_binlog_format              | NONE                 |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)

需要注意的是当前“ binlog_format”格式已经更高为“ROW”,接着我们运行如下命令,

show variables like '%expire_logs_days%';

可见如下显示,

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.001 sec)

需要注意的是“expire_logs_days”定义binlog数据配置为保留7天(防止日志过大占用过多空间)

2.6 备份binlog(可选)

如何备份binlog非本章重点,如有需要请参阅以下章节,

如何备份MySQL数据?

没有评论

发表评论

MySQL & MariaDB
如何迁移MariaDB或MySQL数据目录?

1 前言 一个问题,一篇文章,一出故事。 笔者之前的数据库使用默认部署在根目录下,现在想迁移至专用的 …

MySQL & MariaDB
如何基于CentOS 8.x安装MariaDB?

1 基础知识 – MariaDB数据库管理系统是MySQL的一个分支 – M …

Bash
如何备份MySQL数据?

1 基础知识 MySQL如果是非企业版本,一般情况下使用MySQL进行全备份,但是binlog却可以 …