MariaDB / MySQL - Binary Logs 设置

1. 准备存 bin-log 的地方

$ mkdir -p /var/log/mariadb/bin-log

2. 编辑 /etc/my.cnf (my.ini)

[mysqld]

log_bin=/var/log/mariadb/bin-log

expire_logs_days = 7

3. 启动 MariaDB

$ systemctl start mariadb

4. 查看数据库关于 Binary Log 设置

检查 Binary Logs 是否开启

$ mysql -h主机名 -P端口 -u用户名 -p密码

MariaDB> show variables like 'log_bin';

MariaDB> show binary logs;
MariaDB> show master logs;

重设
MariaDB> reset master;
(在slave上, 用 reset slave, 之前应 stop slave, 之后再 start slave)

清理
MariaDB> purge binary logs to 'log-bin.000001';
MariaDB> purge binary logs before '2011-05-28 12:05:38';

5. 查看 Binary Log

MariaDB> show master status;
MariaDB> show binlog events;

MariaDB> quit;

$ cd /var/log/mariadb/bin-log

全备份

$ mysqlbinlog log-bin.000001 > tmp.log

部分备份

# --start-datetime / --stop-datetime
$ mysqlbinlog --start-datetime='2015-07-01 00:00:00' --stop-datetime='2016-07-15 00:00:00' log-bin.000001 > tmp.log
$ mysqlbinlog --start-datetime='2015-07-01 00:00:00' log-bin.000001 > tmp.log
$ mysqlbinlog --stop-datetime='2016-07-15 00:00:00' log-bin.000001 > tmp.log

# --start-position / --stop-position
$ mysqlbinlog --start-position='10001' --stop-position='10002' log-bin.000001 > tmp.log
$ mysqlbinlog --start-position='10001' log-bin.000001 > tmp.log
$ mysqlbinlog --stop-position='10002' log-bin.000001 > tmp.log

6. 关闭与开启 Binary Logs

关闭
MariaDB> SET sql_log_bin=0;
MariaDB> flush logs;

恢复
$ mysql -h主机名 -P端口 -u用户名 -p密码 < tmp.log

开启
MariaDB> SET sql_log_bin=1;