MySQL Log management
1: Open Binlog Log
2: Read Binlog log
3: Recover data with binlog logs.
a binlog log is generated
#vim /etc/my.cnf log-bin=/var/lib/mysql-bin/slave2 server-id=2 //mysql5.7版本以上要写 # mkdir /var/lib/mysql-bin # chown mysql.mysql /var/lib/mysql-bin/ # systemctl restart mysqld
Note:
- Restarting MYSQLD will truncate the old log to generate a new log
- Flushing logs truncates old logs to generate new logs
mysql> flush logs #mysqladmin flush-logs -u root -p123 #mysql -u root -p123 -e "flush logs"
- Remove all Binlog (disabled)
mysql> Reset Master
- Delete some logs
Mysql> PURGE BINARY LOGS to ' mysql-bin.010 ';
Mysql> PURGE BINARY LOGS before ' 2016-04-02 22:46:26 ';
- Pausing the Binlog log function (valid for the current session only)
Mysql> SET sql_log_bin=0;
Mysql> SET sql_log_bin=1;
Two read Binlog logs:# mysqlbinlog mysql.000002
Three by DateTime read:
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"# mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54"
Four press position read:
# mysqlbinlog mysql.000002 --start-position=260# mysqlbinlog mysql.000002 --stop-position=260# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 |mysql -u root -p123
MySQL Log management