Shulou(Shulou.com)06/01 Report--

This article shares with you how to use MySQL binary logs. Using binary logs can achieve remote disaster recovery backup, read-write separation, data recovery and other functions. Through the summary of this article, I hope you can learn to use MySQL binary logs.

Open bin-log log

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

Log-bin=mysql-binbinlog_format=mixedserver-id = 1expire_logs_days = 10

When this item is configured in log-bin, the binary logging feature is enabled. Mysql-bin is the bin-log log file name.

Expire_logs_days = 10 indicates that only the last 10 days of bin-log logs are stored.

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

Operation and maintenance Tip: binary log files and database data files had better not be placed on the same hard disk. If the hard disk where the data files are stored is broken, you can use the binary log of another hard disk to recover the data.

A few useful commands

Flush logs: generate a new bin-log log

Show master status: view the status of the last bin-log log.

Reset master: clear all bin-log files

Mysql > show master status

View of Mysql Log

Because the log is binary, it would be garbled to view it with the general command cat or vim. Mysql provides us with the tool mysqlbinlog. You can check it out with it.

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

Pos node at the beginning of at:sql

Server_id: the service number of the database host

Pos node at the end of end_log_pos 154:sql

The common options for mysqlbinlog are as follows:

-- start-datetime: reads from the binary log a specified time equal to the timestamp or later than the local computer

-- stop-datetime: read the specified time value less than the timestamp or equal to the local computer from the binary log as above

-- start-position: reads the specified position event location from the binary log as a start.

-- stop-position: reads the specified position event location from the binary log as the end of the event

-djinmem: view only the 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 (.sql is recommended)

Sql file import database: mysql-uplink *-packs * database name

< 备份文件目录 现在模拟一种场景：一数据库每晚3点定时备份,第二天网站正常运行了半天，突然在下午5点钟的时候，程序员小A不小心，DELETE 时候没有加WHERE 条件，然后其中某张表数据全没了。然后小A找到技术总监大圣，让大圣帮忙恢复数据。 binlog_test 数据库只有一张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 |+---------+----------+---------------------+ 凌晨3点到了，备份数据 mysqldump binlog_test -l -F >

/ root/sql_backup/20180706.sqlll / root/sql_backup/ total consumption 4murrwMurrMurrMurray-1 root root 2149 July 6 13:42 20180706.sql = data backup completed =

The website is running normally for a period of time, and many users have signed up.

INSERT INTO `user` (username) values ('user1'), (' user2'), ('user3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0select * 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 | +-- +-+ = added 3 users user1 user2 and user3=

At 5 o'clock in the afternoon, Xiao A began to be stupid.

DELETE FROM user;Query OK, 6 rows affected (0.00 sec) = no where condition, all data gone =

Xiao A found the Big Sage to help recover the data. The Big Sage first recovered the data at 3: 00 a. M. last night.

Service nginx stop; # first closed nginx, making it impossible for website users to access the database Stoping nginx... for the time being Done MariaDB [binlog_test] > flush logs; # generate a new binlog log 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 这时候大圣已经把昨晚凌晨3点时候数据恢复了 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 | +-= data recovery completed at 3 am last night =

Next, restore the data between 3: 00 a. M. and DELETE.

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

# / usr/local/mariadb/bin/mysqlbinlog-- stop-position=629 > 'mysql-bin.000002' > | mysqlbinlog _ 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 | +-- +-+ = all the data is back.

After reading this article, have you learned how to use MySQL binary logs? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.