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

1. Open MySQL Log

(1)Configure my.cnf profile

First turn on the log-bin parameter in my.cnf configuration file and set log-bin to mysql bin_linzhongniao, so that when updating data in mysql database, it will be recorded in this log file. There is a downside to this which can lead to large log files, but it doesn't matter, the system can split them automatically or we can split them manually. View mysql log files with mysql binlog

[root@linzhongniao ~]# grep "log-bin" /data/3306/my.cnflog-bin = /data/3306/mysqlbin_linzhongniao

(2)After configuring my.cnf restart mysql

[root@linzhongniao ~]# /data/3306/mysql restartRestarting MySQL... Stoping MySQL.... Starting MySQL......

(3)After restart you can see mysql log files under mysql data files.

It records statements that have changed data, without configuring log-bin before.

[root@linzhongniao ~]# ls /data/3306/mysqlbin_linzhongniao.*/ The requested URL/data/3306/mysqlbin_linzhongxiao.00000/data/3306/mysqlbin_linzhongxiao.index/data/3306/mysqlbin_linzhongxiao.000022.Simulation incremental recovery does not stop database method

(1) View table data

mysql> use linzhongniao;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select database();+--------------+| database() |+--------------+| linzhongniao |+--------------+1 row in set (0.00 sec)mysql> show tables;+------------------------+| Tables_in_linzhongniao |+------------------------+| test |+------------------------+1 row in set (0.01 sec)mysql> select * from test;+----+-------------+| id | name|+----+-------------+| 1 | linzhogniao || 2 | wwn1314 || 3 | lisi|| 4 | woshishei || 5 | xiaozhang |+----+-------------+5 rows in set (0.00 sec)

(2) Change the value of id = 1 to nishishei

mysql> update test set name='nishishei' where id='1'; Query OK, 1 row affected (0.10 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from test; +----+-----------+| id | name |+----+-----------+| 1 | nishishei || 2 | wwn1314 || 3 | lisi || 4 | woshishei || 5 | xiaozhang |+----+-----------+5 rows in set (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

(3) Then exit mysql and use mysqlbinlog to check the mysqlbin_linzhongxiao.000002 log file

[root@linzhongniao ~]# mysqlbinlog /data/3306/mysqlbin_linzhongniao.000002# at 183#181018 20:24:08 server id 1 end_log_pos 299 Query thread_id=1 exec_time=0 error_code=0use `linzhongniao`/*!*/; SET TIMESTAMP=1539865448/*!*/; update test set name='nishishei' where id='1'/*!*/;

Look at the statement update test set name='nishisei' where id='1. When restoring, delete this statement and import the data. Because the data will be written into the recovery data, resulting in data loss, so the previous log file backup out. A good way to recover data is to stop the database. You can't stop the database. You can cut the log file manually. This will generate a new log file to store the data.

(4) Backup log files of incorrect operations

[root@linzhongniao data]# cp mysqlbin_linzhongniao.000002 /opt/

(5) Cutting log files

[root@linzhongniao ~]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-log[root@linzhongniao ~]# ll /data/3306/mysqlbin_linzhongniao.00000* -rw-rw----. 1 mysql mysql 126 Oct 18 20:15 /data/3306/mysqlbin_linzhongniao.000001 -rw-rw----. 1 mysql mysql 468 Oct 18 20:41 /data/3306/mysqlbin_linzhongniao.000002 -rw-rw----. 1 mysql mysql 107 Oct 18 20:41 /data/3306/mysqlbin_linzhongniao.000003

We see that mysqlbin_linzhongxiao.000003 appears after cutting. After cutting, write the data to mysqlbin_linzhongxiao.000003. Now what we have to deal with is to import the data recorded in mysqlbin_linzhongxiao.000002 log file into the database again.

(6) Generate bin.sql file

In the work bin-log log file records the data of multiple libraries, there are multiple tables and libraries, in the recovery of data when only the wrong operation of the library and table operations can be. Because multiple libraries and tables cause duplicate primary keys and cannot insert data when restoring data, specify libraries and tables when restoring data. Backup specified libraries with the mysqlbinlog-d parameter.

[root@linzhongniao ~]# mysqlbinlog -d linzhongniao /opt/mysqlbin_linzhongniao.000002 >bin.sql

Editing bin.sql can find out when the operation of the database causes data loss, we find the update statement, the update statement will be deleted.

[root@linzhongniao ~]# grep -i "update" bin.sql update test set name='nishishei' where id='1'

(7) Start incremental recovery

[root@linzhongniao data]# mysql -uroot -p123456 -S /data/3306/mysql.sock linzhongniao

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.