MySQL database operation and maintenance data recovery method

mysql> SHOW CREATE DATABASE test_db;
mysql> CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
mysql> INSERT INTO student (name,age) VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 1497 |
|mysql-bin.000002 | 397 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> UPDATE STUDENT SET name = 'admin';

mysql> INSERT INTO student(name,age) VALUES('Hbase',23),('BlackHole',30);

#Log in to the database and lock the table. At this time, the table can only be read but not written.mysql> USE test_db;
mysql> LOCK TABLE student READ;
#Then reopen (note that it is reopen) a session window, otherwise the lock will be released after the session is exited. Then compress and back up the existing data and binary log files [root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
[root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
#Import the most recent full backup data [root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/test_db.sql 

#View the binary log files and log points during full backup [root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
  Log_name File_size
  mysql-bin.000001 1497
  mysql-bin.000002 397
#Convert the binary log file after point 861 into a sql file [root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
#Use vim editor to edit this sql file, find the unconditional UPDATE statement, delete it, and then import the sql script content after deleting the UPDATE statement into the database [root@WB-BLOG bin]# vim /tmp/tmp.sql
  use `test_db`/*!*/;
  SET TIMESTAMP=1522088753/*!*/;
  update student set name = 'admin' #Delete this sentence [root@WB-BLOG bin]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql
#Log in to the database to check whether the data has been restored. You can check whether the accidentally modified data has been restored, then unlock the table and prepare the data again. mysql> UNLOCK TABLES;

#Log in to the slave database and then shut down the SQL thread mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
#Then record the binary log file information of the master library currently applied in the slave library [root@node02 mysql_data]# mysql -e "SHOW SLAVE STATUS \G" > slave_`date +%F`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction > /tmp/mysql_test_db_`date +%F`.sql

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

#Log in to the main database, modify the external user of the database so that it does not provide services temporarily, and then roll the log mysql> UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';
Query OK, 1 rows affected (0.00 sec)
#Refresh the privilege tablemysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#Rolling logmysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
#Transfer the slave database backup data and the slave database slave information at the time of backup to the master database [root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/

[root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*

[root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/mysql_test_db_2018-03-26.sql 
#Note: The above operation cannot lock the table of the main database, otherwise the full backup data cannot be imported.

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
  Master_Log_File: master-bin.000002 #The name of the master binary log file applied during backup Read_Master_Log_Pos: 395 #The location of the master binary log file applied during backup

[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395 > /tmp/tmp.sql
#Merge master-bin.000003, master-bin.000004, master-bin.000005 into the /tmp.sql file [root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395 > /tmp/tmp.sql

[root@node01 mysql_logs]# vim /tmp/tmp.sql
  use `test_db`/*!*/;
  update student set name = 'admin' #Delete this sentence [root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql 

[root@node01 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1 > /tmp/master_test_db_`date +%F`.sql
[root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
#If the slave database is set to read-only, you need to remove the read-only restriction first mysql> SET GLOBAL read_only = OFF;
Query OK, 0 rows affected (0.00 sec)
#Import data from the database [root@node02 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/master_test_db_2018-06-24.sql
# Enable read-only slave mysql> SET GLOBAL read_only = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.user set Host = '192.168.0.%' WHERE User = 'tomcat';
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)