MySQL operation and maintenance binary log

The MySQL binary log stores SQL statements that cause or may cause data changes. Functions such as real-time remote disaster recovery backup, read-write separation, and data recovery can be completed through binary logs. Next, let's take a look at the Mysql binary log.

Enable bin-log log

Mysql does not enable bin-log log by default, we need to add the configuration ourselves.

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

log-bin After configuring this item, the binary log function is enabled. mysql-bin is the bin-log log file name.

expire_logs_days = 10 indicates that only the bin-log logs of the last 10 days will be stored.

Generally, bin-log logs are stored under the mysql installation path /var/

Operation and maintenance tips: It is best not to put binary log files and database data files on the same hard disk. If the hard drive storing the data files is damaged, you can use the binary log of another hard drive to recover the data

Several useful commands

• flush logs: Generate new bin-log logs

• show master status: View the last bin-log log status.

• reset master: clear all bin-log files

mysql > show master status

Viewing Mysql log

Because the log is a binary log, using the general command cat or vim to view it will result in garbled code. . Mysql provides us with the tool mysqlbinlog. Use it to view it.

./mysqlbinlog ../var/mysql-bin.000015
……
# at 123
#200601  8:35:19 server id 1  end_log_pos 154 CRC32 0xd25b404e  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
……

• at: pos node when sql starts

• server_id: the service number of the database host;

• end_log_pos 154: pos node at the end of sql

Mysqlbinlog common options are as follows:

• --start-datetime: Read the specified time from the binary log that is equal to the timestamp or later than the local computer

• --stop-datetime: Read the specified time from the binary log that is less than the timestamp or equal to the local computer The time value is the same as above

• --start-position: Read the specified position event position from the binary log as the start.

• --stop-position: Read the specified position event position from the binary log as the event as of

• -d,--database= name: Only view log operations of the specified database

Use bin-log logs to recover data

• Export sql file Command: mysqldump database name [data table name 1 [data table name 2...]] > External file directory (recommended to use .sql)

• sql file import database: mysql - u** -p** Database name

Now simulate a scenario: a database is backed up regularly at 3 o'clock every night, and the website runs normally for half a day the next day. Suddenly at 5 o'clock in the afternoon, programmer A accidentally did not add a WHERE condition during DELETE, and then all the data in one of the tables was lost. Then Xiao A found the technical director Dasheng and asked him to help recover the data.

The binlog_test database has only one user table

The data before backup at three o'clock in the morning is as follows:

+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

At 3 o'clock in the morning, the data was backed up

mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql
ll /root/sql_backup/
总用量 4
-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql
=======数据备份完成=========

The website has been running normally for a period of time, and many users have registered.

INSERT INTO `user` (username) values('user1'),('user2'),('user3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============新增了3个用户user1 user2 及user3==============

At 5 o'clock in the afternoon, Little A started to act stupid

DELETE FROM user;
Query OK, 6 rows affected (0.00 sec)
=========没where条件，数据全没了===========

Little A found the Great Sage to help restore the data. The Great Sage first restored yesterday's data. The data was restored at 3 a.m.

service nginx stop;  # 大圣先关闭了nginx，使网站用户暂时访问不了数据库
Stoping nginx...  done 
MariaDB [binlog_test]> flush logs;  #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1536 |              |                  |
+------------------+----------+--------------+------------------+
mysql -v -f binlog_test < /root/sql_backup/20180706.sql

At this time, the Great Sage had restored the data at 3 a.m. last night

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+
=============昨晚凌晨三点数据恢复完成===============

Next, the data between 3 a.m. and DELETE was restored

First find the pos point of delete. After backup, the log is 000002. After deletion, flush logs are also 000003, so just find the pos before 000002 delete.

# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >
'mysql-bin.000002' >
| mysql binlog_test;

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============数据都回来了========================

The above is the detailed content of MySQL operation and maintenance binary log. For more information, please follow other related articles on the PHP Chinese website!