MySQL Binlog Data Recovery: Detailed Explanation of Accidentally Deleting a Database

MySQL Bin log data recovery: accidentally delete the database

Preface: Because I accidentally deleted an entire Mysql database schema on the test machine, I didn't make a backup because it was a test machine. Now I use MySQL's Bin log method to restore the database before deletion.

Of course, the premise of Bin log data recovery is that the Bin log function has been turned on. If you have not backed up the data and have not turned on the Bin log, you may need to consider other methods such as snapshots to recover from the system perspective.

Bin log is often used for incremental data backup and recovery, as well as database master-slave replication. If it is not enabled, you can enable it as follows:

1. Open the binlog function of mysql

MySQL supports incremental backup, but the MySQL bin log function must be turned on.

Modify the mysql configuration file. Linux is /etc/my.cnf, Windows is mysql installation directory/my.ini
Add a line of code for log-bin under [mysqld], as follows:

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed.

2. Check the binary log status in the following way: Is it enabled?

mysql> show variables like 'log_%';

3. View all binary log files:

mysql> show libary logs;

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 201 |
|mysql-bin.000002 | 351 |
| mysql-bin.000003 | 276 |
|mysql-bin.000004 | 201 |
|mysql-bin.000005 | 16509 |

4. Mysql views the operation log of the binary log file

#mysqlbinlog --start-position=0 /mydata/data/mysql-bin.000089

[root@test mysql]# mysqlbinlog --start-position=0 --stop-position=500 mysql-bin.000091
Warning: option 'start-position': unsigned value 0 adjusted to 4
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151022 18:00:43 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.38-log created 151022 18:00:43 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
y7MoVg8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADLsyhWEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#151022 23:27:50 server id 1 end_log_pos 198 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1445527670/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=0, @@session.unique_checks=0, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1608515584/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP SCHEMA IF EXISTS `pandora`<pre name="code" class="sql">/*!*/;
# at 198
#151022 23:27:50 server id 1 end_log_pos 346 Query thread_id=2 exec_time=0 error_code=0

5. Restore data through Bin log. Because I deleted the entire Schema and didn't back it up, and I happened to have bin log enabled, I re-executed all the historical bin logs and restored them to the version before the accidental deletion (I have a total of 91 files here, processed in batches): (9999999999999: This is to save the trouble of finding the start and end positions of each bin log file, and set an infinite number to simplify the operation.)

#mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
#mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
#mysqlbinlog /var/lib/mysql/mysql-bin.000003 --start-position=0 --stop-position=9999999999999 | mysql -uroot -p123456
... ...

So the conclusion is:

1. 1. Remember to back up regularly;
2. 2. If there is a backup, recovery will be faster. You can make incremental backups from the backup time point. You don’t need to run all 91 files in batches from the beginning like I did. Of course, I can also do batch processing with an editor which is pretty fast.
3. 3. In addition, be sure to open the Bin-log log. If you don’t make a backup, you can also restore it through the Bin-log log.
4. 4. Be careful when operating.

other:

1. There is also a sql_log

mysql> show variables like 'sql_log_%';

Mysql turns on and off the sql binary log:
mysql> set sql_log_bin=0; //Close
set session sql_log_bin=0;

2. Find the file location:

find / -name my.cnf

3. Linux View the full path of the current directory

pwd command:
/var/lib/mysql

4. Check the current binary log status:

mysql>show master status;

5. Set the number of days for binary logs rollback in my.cnf/my.ini:

expire_logs_days = 7

6. View the Master bin log

mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| log-bin.000001 | 98 |
 +-----------------+-----------+
1 row in set (0.00 sec)
--------------------- 

The above is the detailed explanation and integration of MySQL Binlog data recovery introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!