目前公司的生产环境的数据库操作维护都由我负责,每次上生产都是提心吊胆啊,生怕把数据抹掉了。。。
居安思危,为了能心安一些,抽出一个周末的下午学习下binlog,让自己活的更心安些(真诚的手动微笑)
前期准备:
先看下自己的数据库有没有开启binlog
在数据库中执行如下语句
SHOW VARIABLES LIKE 'log_%';
如上图所示说明未开启,进行如下操作
第一步,在my.cnf文件中配置binlog的开关
log-bin=mysql-bin
配置完如下(我用测试环境进行配置的,没有什么特殊的配置。就在原基础上加了数据库大小写不敏感、最大连接数1000、开启binglog):
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
lower_case_table_names=1
innodb_file_per_table
max_connections=1000
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log-bin=mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
重启mysql
systemctl restart mysqld
在数据库中执行如下语句
SHOW VARIABLES LIKE 'log_%';
查询结果如图所示,说明binlog开启成功
开始binlog日志的相关学习
1.查看所有binlog日志列表 mysql> show master logs; 2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值 mysql> show master status; 3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件 mysql> flush logs; 注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志; 4.重置(清空)所有binlog日志 mysql> reset master;
找到数据库的binlog日志(与数据库数据在同一目录下,我的是在/var/lib/mysql)
# at 50954
#171216 17:34:41 server id 1 end_log_pos 51138 CRC32 0x688e7e8f Query thread_id=3 exec_time=0 error_code=0
use `testbase`/*!*/;
SET TIMESTAMP=1513416881/*!*/;
update `testbase`.`oe_user` set `name` = 'auxin111阿斯蒂芬7788' where `id` = '2c9aec355f158f7f015f283d64870002'
/*!*/;
# at 51138
这就是binlog中记录的sql执行记录
今天就先学到这儿了。。。饿了。。。心静不下来了………………..