Mysql data recovery, binlog details
Binlog data recovery is a life-saving straw to save the wrong operation and data damage, so it is very necessary for technical personnel to understand and use binglog
Binlog is generally used for
- In the master/slave replication, the master node enables binlog to transfer binary logs to the slave node to achieve data consistency between the master and slave nodes
- The second point of course is for data recovery, using the mysqlBinlog tool to restore data
Because MY own website encountered the mysql table was deleted by mistake, I was still quite calm after dropping the table. Although I was not sure whether I could save the data back, there might be no master or slave for personal websites, and the configuration of mysql was not set perfectly. In this case, of course, the biggest concern is whether binlog is enabled. If binlog is not enabled and the data is not backed up, I feel basically GG
Since I installed mysql through a Docker container, I forgot where the my.cnf configuration file was placed
- Use find / -name my.cnf to find where the file is
root@0d5861775029:/# find / -name my.cnf
find: '/proc/1/map_files': Operation not permitted
find: '/proc/182/map_files': Operation not permitted
find: '/proc/187/map_files': Operation not permitted
find: '/proc/1601/map_files': Operation not permitted
find: '/proc/1731/map_files': Operation not permitted
find: '/proc/1741/map_files': Operation not permitted
/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/var/lib/dpkg/alternatives/my.cnf
root@0d5861775029:/#
Copy the code- Look at the my.cnf configuration to find the binlog and mysql data store location
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here! includedir /etc/mysql/conf.d/Copy the code- Datadir = /var/lib/mysql
root@0d5861775029:/etc/mysql# cd /var/lib/mysql/
root@0d5861775029:/var/lib/mysql# ls
#innodb_temp binlog.index client-key.pem ib_logfile1 mysql.ibd server-cert.pem undo_002
auto.cnf ca-key.pem db_blog ibdata1 performance_schema server-key.pem
binlog.000001 ca.pem ib_buffer_pool ibtmp1 private_key.pem sys
binlog.000002 client-cert.pem ib_logfile0 mysql public_key.pem undo_001
Copy the codeI’ve done all the foreplay, but it’s not really binlog, it’s my personal way of discovering the directory of data and I’m going to talk a little bit more about binlog
1. Enable binlog
- Check whether binlog is enabled
- ON: The function is enabled
- Check out more content like this
show variables like 'log_%';
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 rowin set (0.00 sec)
Copy the codemysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | stderr |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------+
18 rows in set (0.00 sec)
Copy the code- Edit my.cnf to enable binlog
Set/add log-bin=mysql-bin in [mysqld] block to enable (mysql-bin is the base or prefix name of the log);Copy the codeThen restart mysql
2. View the binlog operation command
- View the list of all binlog logs
mysql> show logs;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'logs'at line 1 mysql> show master logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000001 | 3091158 | No | | binlog.000002 | 141156437 | No | +---------------+-----------+-----------+ 2 rowsin set (0.17 sec)
Copy the code- Check the master state, which is the name of the last binlog number and the end position of the last operation event pos
mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000002 | 141156437 | | | |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Copy the code- Refreshing the log will generate a newly numbered binlog log file
mysql> flush logs;
Copy the code- Reset (clear) all binlog logs
mysql> reset master;
Copy the code3. View the contents of binlog logs
- Use the mysqlbinlog command to view
Since binlog is a binary file, ordinary file viewers cannot open it. You must use the mysqlbinlog command to view it
- Mysqlbinlog binlog.000002 Using mysqlbinlog cannot be observed
- Check the binlog in mysql
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; A. IN B. IN C. IN D. IN'log_name'Specify the binlog file name to query (if not specified, the first binlog file) FROM pos Specify the pos start point FROM which to query (if not specified, the first pos start point of the entire file) LIMIT [offset,] offset (if not specified, 0) row_count Query to the total number of article (without specifying all the lines) capture part of the query result: * * * * * * * * * * * * * * * * * * * * * * * * * * * 20 in a row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: Mysql - bin. 000021 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - > the binlog query log filename Pos: 11197 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > pos starting point: the Event_type: Query -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > event types: Query Server_id: 1 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > identification is performed by which server End_log_pos: 11308 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > pos end points: 11308 (i.e., downward pos starting point) Info: use ` zyyshop `; INSERT INTO 'team2' VALUES (0,345,'asdf8er5') -- - > execute SQL statements * * * * * * * * * * * * * * * * * * * * * * * * * * * 21 in a row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql - bin. 000021 Pos: 11308 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > pos starting point: 11308 (i.e., the upside of pos end points) the Event_type: Query Server_id: 1 End_log_pos: 11417 Info: use `zyyshop`; / *! 40000 ALTER TABLE `team2` ENABLE KEYS */ *************************** 22. row *************************** Log_name: mysql-bin.000021 Pos: 11417 Event_type: Query Server_id: 1 End_log_pos: 11510 Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
Copy the code- Specify to query binlog.000002 logs
mysql> show binlog events in 'binlog.000002' limit 10;+---------------+------+----------------+-----------+-------------+----------------------------------------------------- ------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+----------------------------------------------------- ------------------------------------------------------------------------------------------------------------------+ | Binlog. 000002 | | 4 Format_desc | 1 | 124 | Server ver: 8.0.16, binlog ver: 4 | | binlog.000002 | 124 | Previous_gtids | 1 | 155 | | | binlog.000002 | 155 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 234 | Query | 1 | 482 | CREATE USER 'schwarzeni'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$H{; gmzB@[}K1i\nBcce80ezg8j3o0qDdYocc1OxBkShlQyzmOV/c4rGP69' /* xid=7 */ | | binlog.000002 | 482 | Anonymous_Gtid | 1 | 561 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 561 | Query | 1 | 801 | CREATE USER 'cuishifeng'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$f8Zs\ZhY(9]HPTcaN83yCTNmHs/LQsa2DerCX.ZVgd4InrYiCpj75mA' /* xid=8 */ | | binlog.000002 | 801 | Anonymous_Gtid | 1 | 878 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 878 | Query | 1 | 968 | FLUSH PRIVILEGES | | binlog.000002 | 968 | Anonymous_Gtid | 1 | 1047 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000002 | 1047 | Query | 1 | 1256 | ALTER USER 'cuishifeng'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*10320381F36BE49A18F09B06A4BC005223975101' /* xid=12 */ | +---------------+------+----------------+-----------+-------------+----------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 10 Rows in set (0.00 SEC)Copy the code- Specify binlog.000002, starting at pos :968
mysql> show binlog events in 'binlog.000002' from 968 limit10; +---------------+------+----------------+-----------+-------------+----------------------------------------------------- ----------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+----------------------------------------------------- ----------------------------------------------------------------------------+ | binlog.000002 | 968 | Anonymous_Gtid | 1 | 1047 | SET @@SESSION.GTID_NEXT='ANONYMOUS' |
| binlog.000002 | 1047 | Query | 1 | 1256 | ALTER USER 'cuishifeng'@The '%' IDENTIFIED WITH 'mysql_native_password' AS '*10320381F36BE49A18F09B06A4BC005223975101' /* xid=12 */ |
| binlog.000002 | 1256 | Anonymous_Gtid | 1 | 1333 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1333 | Query | 1 | 1423 | flush privileges |
| binlog.000002 | 1423 | Anonymous_Gtid | 1 | 1500 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1500 | Query | 1 | 1646 | GRANT ALL PRIVILEGES ON *.* TO 'cuishifeng'@The '%' /* xid=70 */ |
| binlog.000002 | 1646 | Anonymous_Gtid | 1 | 1723 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1723 | Query | 1 | 1813 | flush privileges |
| binlog.000002 | 1813 | Anonymous_Gtid | 1 | 1890 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'| | binlog.000002 | 1890 | Query | 1 | 1968 | FLUSH TABLES | +---------------+------+----------------+-----------+-------------+----------------------------------------------------- ----------------------------------------------------------------------------+ 10 rowsin set (0.00 sec)
Copy the codeYou can see from the log that the mysql command was executed and has a starting location, which is very useful for recovering data
- Query the first (earliest) binlog
mysql> show binlog events;
Now restore data from binlog
- Common commands
Restore syntax format:# mysqlbinlog mysql - bin. 0000 xx | mysql -u username -p password database nameCommon options: --start-position=953 start pos point --stop-position=1437 end POS point --start-datetime="The 2013-11-29 13:18:54"Start time --stop-datetime="The 2013-11-29 13:21:53"End Time --database= Zyyshop Specifies that only zyyshop databases are to be restoredlog-u --user=name Connect to the remote server as username. -p --password[=name] password to connect to remote server Password for connecting to the remote host -h --host=name Get the binlog from the server. --read-from-remote-server Read binary logs from a MySQL server. Read a summary of the binlog log from a MySQL server: actually pass the contents of the binlog log read to the MySQL command through the pipe. These commands and files should be written in absolute paths as far as possible.Copy the codeLog recovery is equivalent to executing the DDL statement at the time. If the statement of log recovery, for example, you have a table in the library and execute the log to create the table, it must not work, so it is best to specify a specific location to restore
- Look at the binlog to determine where to recover from
mysql> show binlog events in 'mysql-bin.000023'; The following is the final clip: +------------------+------+------------+-----------+-------------+----------------------------------------------------- -------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+------------+-----------+-------------+------------------------------------------------------ ------+ | mysql-bin.000023 | 922 | Xid | 1 | 953 | COMMIT /* xid=3820 */ | | mysql-bin.000023 | 953 | Query | 1 | 1038 | BEGIN | | mysql-bin.000023 | 1038 | Query | 1 | 1164 | use `zyyshop`; update zyyshop.ttset name='bill' where id=4|
| mysql-bin.000023 | 1164 | Xid | 1 | 1195 | COMMIT /* xid=3822 */ |
| mysql-bin.000023 | 1195 | Query | 1 | 1280 | BEGIN |
| mysql-bin.000023 | 1280 | Query | 1 | 1406 | use `zyyshop`; update zyyshop.tt set name='small 2' whereid=2| | mysql-bin.000023 | 1406 | Xid | 1 | 1437 | COMMIT /* xid=3823 */ | | mysql-bin.000023 | 1437 | Query | 1 | 1538 | drop database zyyshop | +------------------+------+------------+-----------+-------------+------------------------------------------------------ ------+ Through analysis, the pos point interval causing database damage is between 1437 and 1538, as long as it is restored to before 1437.Copy the codemysqlbinlog --start-position=953 --stop-position=1538 --database=zyyshop binlog.000002 | mysql -uroot -p123456 -v zyyshop
Copy the code- Restore at specified time
- I know when I misoperated
mysql> drop table tt;
@ --start-datetime="The 2013-11-29 13:18:54"Start time @ --stop-datetime="The 2013-11-29 13:21:53"End point# mysqlbinlog --start-datetime="2013-11-29 13:18:54" --stop-datetime="2013-11-29 13:21:53" --database=zyyshop binlog.000002 | mysql -uroot -p123456 -v zyyshop
Copy the codeHope this article can help delete the operation of friends to recover data smoothly