mysql data recovery
Author: xluren
mysql does not open mysqlbinlog by default.
Log in to mysql and execute the following commands. The results are as follows:
The return result is emptymysql> show binlog events; Empty set (0.00 sec) mysql>
So to modify the configuration file, open this option
Simply, log-bin=mysqlbin-log. The name is customized. As for the pit, I don't know yet. I've been using this name all the time.[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=mysqlbin-log
Then execute show binlog events
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show binlog events;
+---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+
| mysqlbin-log.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 |
| mysqlbin-log.000001 | 107 | Query | 1 | 174 | BEGIN |
| mysqlbin-log.000001 | 174 | Intvar | 1 | 202 | INSERT_ID=3 |
| mysqlbin-log.000001 | 202 | Query | 1 | 317 | use `url`; insert into urlinfo(url,info) values("sogou","hello") |
| mysqlbin-log.000001 | 317 | Query | 1 | 385 | COMMIT |
| mysqlbin-log.000001 | 385 | Query | 1 | 452 | BEGIN |
| mysqlbin-log.000001 | 452 | Query | 1 | 544 | use `url`; delete from urlinfo where id=3 |
| mysqlbin-log.000001 | 544 | Query | 1 | 612 | COMMIT |
| mysqlbin-log.000001 | 612 | Query | 1 | 679 | BEGIN |
| mysqlbin-log.000001 | 679 | Intvar | 1 | 707 | INSERT_ID=4 |
| mysqlbin-log.000001 | 707 | Query | 1 | 822 | use `url`; insert into urlinfo(url,info) values("sogou","hello") |
| mysqlbin-log.000001 | 822 | Query | 1 | 890 | COMMIT |
| mysqlbin-log.000001 | 890 | Query | 1 | 957 | BEGIN |
Looking at the directory where mysql data resides at the same time adds mysqlbin-log
In fact, mysqlbin-log.000001 and mysqlbin-log.000002 are newly added, recording the operation log. At the same time, it should be noted that every restart of mysqld, mysqlbing-log will be named on the original basis + 1.[root@Git mysql]# ll total 28700 -rw-rw----. 1 mysql mysql 18874368 Dec 16 20:16 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Dec 16 20:16 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 13 04:21 ib_logfile1 drwx------. 2 mysql mysql 4096 Aug 13 04:21 mysql -rw-rw---- 1 mysql mysql 2448 Dec 16 20:14 mysqlbin-log.000001 -rw-rw---- 1 mysql mysql 107 Dec 16 20:16 mysqlbin-log.000002 -rw-rw---- 1 mysql mysql 44 Dec 16 20:16 mysqlbin-log.index srwxrwxrwx 1 mysql mysql 0 Dec 16 20:16 mysql.sock drwx------ 2 mysql mysql 4096 Dec 6 06:09 mywebsite drwx------. 2 mysql mysql 4096 Aug 13 04:21 test drwx------. 2 mysql mysql 4096 Aug 13 04:35 url
You'll also find out why show binlog events don't have mysqlbin-log.000002. OK will execute the following command
From the official:
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.
mysql> show binlog events in 'mysqlbin-log.000002';
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysqlbin-log.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 |
| mysqlbin-log.000002 | 107 | Query | 1 | 174 | BEGIN |
| mysqlbin-log.000002 | 174 | Intvar | 1 | 202 | INSERT_ID=5 |
| mysqlbin-log.000002 | 202 | Query | 1 | 318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") |
| mysqlbin-log.000002 | 318 | Query | 1 | 386 | COMMIT |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)Enter Data Recovery below
The database and time are as follows:
Insert a piece of datamysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | +----+----------------+-------+ 4 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-12-16 20:30:18 | +---------------------+ 1 row in set (0.00 sec) mysql>
mysql> insert into urlinfo(url,info) values("soso","hello");
Query OK, 1 row affected (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-12-16 20:31:25 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from urlinfo;
+----+----------------+-------+
| id | url | info |
+----+----------------+-------+
| 1 | www.baidu.com | 0 |
| 2 | www.google.com | 0 |
| 4 | sogou | hello |
| 5 | sogou1 | hello |
| 6 | soso | hello |
+----+----------------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> delete from urlinfo where url='soso'; Query OK, 1 row affected (0.05 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ |2013-12-16 20:35:29 | +---------------------+ 1 row in set (0.01 sec) mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | +----+----------------+-------+ 4 rows in set (0.00 sec) mysql>
Operational Record Viewing
mysql> show binlog events in 'mysqlbin-log.000002';
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysqlbin-log.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 |
| mysqlbin-log.000002 | 107 | Query | 1 | 174 | BEGIN |
| mysqlbin-log.000002 | 174 | Intvar | 1 | 202 | INSERT_ID=5 |
| mysqlbin-log.000002 | 202 | Query | 1 | 318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") |
| mysqlbin-log.000002 | 318 | Query | 1 | 386 | COMMIT |
| mysqlbin-log.000002 | 386 | Query | 1 | 453 | BEGIN |
| mysqlbin-log.000002 | 453 | Intvar | 1 | 481 | INSERT_ID=6 |
| mysqlbin-log.000002 | 481 | Query | 1 | 595 | use `url`; insert into urlinfo(url,info) values("soso","hello") |
| mysqlbin-log.000002 | 595 | Query | 1 | 663 | COMMIT |
| mysqlbin-log.000002 | 663 | Query | 1 | 730 | BEGIN |
| mysqlbin-log.000002 | 730 | Query | 1 | 828 | use `url`; delete from urlinfo where url='soso' |
| mysqlbin-log.000002 | 828 | Query | 1 | 896 | COMMIT |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
12 rows in set (0.00 sec)
Then restore the data, that is to say, the data should be before 2013-12-16, 20:35:29.
Execute the following command:
Then?[root@Git mysql]# mysqlbinlog --database=url --start-datetime="2013-12-16 20:29:00" --stop-datetime="2013-12-16 20:34:00" mysqlbin-log.000002|mysql -h localhost -u root -phello -f [root@Git mysql]#
Deleted data appears, and I'm the easiest way to get started.mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | | 6 | soso | hello | +----+----------------+-------+ 5 rows in set (0.00 sec)
For instructions on the use of mysqlbinlog, simple command parameters are available:
mysqlbinlog supports the following options:
· ---help，-？
Display help message and exit.
· ---database=db_name，-d db_name
With this option, if mysqlbinlog reads binary log events that it cannot recognize, it prints a warning, ignores the event, and continues. Without this option, stop if mysqlbinlog reads such events.
· --host=host_name，-h host_name
Gets the binary logs of MySQL servers on a given host.
· --password[=password]，-p[password]
The password used when connecting to the server. If you use the short option form (-p), options and There must be no spaces between passwords. If not on the command line -- password or - p option The password value prompts you to enter a password.
· --port=port_num，-P port_num
The TCP/IP port number used to connect to the remote server.
--start-datetime=datetime
Start reading from events in the binary log whose first date time is equal to or later than the datetime parameter. The datetime value is relative to the local time zone on the machine running mysqlbinlog. The value format should conform to the DATETIME or TIMESTAMP data type. For example:
shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003
This option can help with point-to-point recovery.
· --stop-datetime=datetime
Stop reading from an event in the binary log whose first date time is equal to or later than the datetime parameter. For a description of the datetime value, see the -- start-datetime option. This option can help you recover in time
Simple introductory knowledge, and then continue to construct the data themselves to test.