MySQL DBA Advanced Operations Learning Note-Initial incremental recovery of MySQL database

9.10.3 preliminary incremental recovery MySQL data combat

A. Rename the Log-bin parameter in the MY.CNF configuration file to MYSQLBIN_ZBF, so that updating a thing in the MySQL database will be updated in this log. This will cause this log to be large, it doesn't matter that the system can be split automatically or manually.

[[email protected] var]# vim /etc/my.cnf…省略……# Uncomment the following if you want to log updateslog-bin=mysqlbin_zbf# binary logging format - mixed recommended……省略…

B. my.cnf restart MySQL after configuring.

[[email protected] var]# /etc/init.d/mysqld restartShutting down MySQL. SUCCESS! Starting MySQL. SUCCESS!

C. After a reboot, you can see the mysqlbin_zbf.000001 log file under MySQL data file, which records the statements that have changed operations on the data before you do not have to configure Log-bin.

[[email protected] data]# lsibdata1  ib_logfile1  mysqlbin_zbf.000001  mysql.err  performance_schemaib_logfile0  mysqlmysqlbin_zbf.index   mysql.pid  zbf

D. Restart the log in MySQL and destroy the database data to simulate incremental recovery.

(1) Viewing table data

mysql> select * from student;+----+-----------+-----+--------+| id | name  | age | dept   |+----+-----------+-----+--------+|  1 | zbf666|  29 | linux  ||  2 | lisi  |  28 | mysql  ||  3 | zhangsan  |  21 | python ||  4 | woshishei |  24 | java   |+----+-----------+-----+--------+

(2) Modify the value of the Name field to Luhan

mysql> update student set name=‘luhan‘;Query OK, 4 rows affected (0.03 sec)Rows matched: 4  Changed: 4  Warnings: 0

(3) View table data again

mysql> select * from student;+----+-------+-----+--------+| id | name  | age | dept   |+----+-------+-----+--------+|  1 | luhan |  29 | linux  ||  2 | luhan |  28 | mysql  ||  3 | luhan |  21 | python ||  4 | luhan |  24 | java   |+----+-------+-----+--------+4 rows in set (0.00 sec)

(4) then quit MySQL. Use Mysqlbinlog to view the mysqlbin_zbf.000001 log file

[[email protected] ~]# mysqlbinlog /usr/local/mysql/data/mysqlbin_zbf.000001……省略……..use `zbf`/*!*/;SET TIMESTAMP=1517061765/*!*/;update student set name=‘luhan‘/*!*/;# at 267……省略….

We see update student set Name= ' Luhan ' this statement, we want to delete this statement when we recover it, and then we can get the data in. Because the data will be restored when the data is missing, so the previous log files are backed up. The best way to recover data is to stop the library, and the best way to stop is to cut.

(5) Simulation can not stop the idea of the database, stop the database is the best. Cut into another one.

[[email protected] data]# mysqladmin -uroot -p123456 flush-log[[email protected] data]# ll总用量 28708-rw-rw----. 1 mysql mysql 18874368 1月  27 22:18 ibdata1-rw-rw----. 1 mysql mysql  5242880 1月  27 22:18 ib_logfile0-rw-rw----. 1 mysql mysql  5242880 1月  11 20:30 ib_logfile1drwx------. 2 mysql root  4096 1月  11 20:08 mysql-rw-rw----. 1 mysql mysql  547 1月  27 22:29 mysqlbin_zbf.000001-rw-rw----. 1 mysql mysql  107 1月  27 22:29 mysqlbin_zbf.000002-rw-rw----. 1 mysql mysql   44 1月  27 22:29 mysqlbin_zbf.index-rw-r-----. 1 mysql root  7937 1月  27 21:57 mysql.err-rw-rw----. 1 mysql mysql5 1月  27 21:57 mysql.piddrwx------. 2 mysql mysql 4096 1月  11 20:08 performance_schemadrwx------. 2 mysql mysql 4096 1月  27 21:32 zbf

We see the cut is finished mysqlbin_zbf.000002, after cutting the data is written to mysqlbin_zbf.000002 inside write, now we have to deal with is mysqlbin_zbf.000001.

(6) Generate Bin.sql file

If there are more than one bin-log in the enterprise to restore all, but also have a lot of libraries and tables, Bin.sql is written in all the library and table modified data, restore BIN.SQL inside the data will cause the primary key duplication, cannot insert data. So you specify the libraries and tables.

[[email protected] data]# mysqlbinlog -d zbf mysqlbin_zbf.000001 >bin.sql

Edit Bin.sql can find out when the operation of the database results in data loss, we find the UPDATE statement, the UPDATE statement is deleted.

[[email protected] data]# vim bin.sql……省略……#180127 23:29:00 server id 1  end_log_pos 767   Query   thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1517066940/*!*/;update student set name=‘luhan‘/*!*/;……省略…# at 767

(7) Start incremental recovery

[[email protected] data]# mysql -uroot -p123456 zbf <bin.sql[[email protected] data]# mysql -uroot -p123456 -e "select * from zbf.student";+----+-----------+-----+--------+| id | name  | age | dept   |+----+-----------+-----+--------+|  1 | zbf666|  29 | linux  ||  2 | lisi  |  28 | mysql  ||  3 | zhangsan  |  21 | python ||  4 | woshishei |  24 | java   |+----+-----------+-----+--------+

MySQL DBA Advanced Operations Learning Note-Initial incremental recovery of MySQL database