MySQL -- binlog log data recovery _ MySQL

MySQL -- binlog log recovery data bitsCN.com
MySQL -- binlog important command for restoring data recovery data mysql> flush logs; the default log is the mysql-bin.000001, and now refresh the re-enable one with an additional mysql-bin.000002. /mysqlbinlog -- no-defaults binlog log name to view the log [root @ localhost bin] #. /mysqlbinlog -- no-defaults .. /var/mysql-bin.000001 | more // view the bin-log content [root @ localhost bin] #. /mysqlbinlog -- no-defaults .. /var/mysql-bin.000001 |. /mysql-uroot-p // restore the contents of the mysql-bin.000001 log if you need to recover from a point to a point, locate with the following actions: -- start-position start point -- stop-position end point -- start-date start time -- stop-date end time now restores the mysql-bin.000002, [root @ localhost bin] #. /mysqlbinlog -- no-defaults -- start-position 134 -- stop-position = 386 .. /var/mysql-bin.000002 |. /mysql-uroot-p/** mysqlbinlog log data recovery experiment *** // view the content below var, now there is no mysql-log.000001 similar binlog [root @ localhost var] # lsbrocms ibdata1 ib_logfile1 localhost. pid mysql-bin.indexbrotherblog ib_logfile0 localhost. err mysql test [root @ localhost var] #... /bin/mysql-uroot-p // log on to mysql> use test; // use mysql> flush logs; // refresh the binlog and open a new one, now a mysql-bin.000001 file is generated under the var directory, and the following operations record it // create a table mysql> create table user (-> id int auto_increment primary key, -> username char (30),-> password char (32)-> engine = myisam default charset = utf8; // insert several test data records: mysql> insert into user (username, password) values (); mysql> insert into user (username, password) values ); mysql> insert into user (username, password) values (); // A new binlog is opened and a file named mysql-bin.000002 is generated, the following operation is recorded in the mysql-bin.000002 file mysql> flush logs; // query the content mysql> select * from user; + ---- + ---------- + | id | username | password | + ---- + ---------- + | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 1 | 2 | + ---- + ---------- + mysql> delete from user; // delete mysql> drop table user now; // delete the table mysql> select * from user; // view the content in the table mysql>/qBye [root @ localhost var] # lsbrocms ibdata1 ib_logfile1 localhost. pid mysql-bin.000001 mysql-bin.indexbrotherblog ib_logfile0 localhost. err mysql mysql-bin.000002 test [root @ localhost var] # .. /bin/mysqlbinlog -- no-defaults mysql-bin.000001 | more // view the content in the mysql-bin.000001 [root @ localhost var] # .. /bin/mysqlbinlog -- no-defaults mysql-bin.000002 | more // view the content in the mysql-bin.000002 [root @ localhost var] # .. /bin/mysqlbinlog -- no-defaults mysql-bin.000001 | .. /bin/mysql-uroot-p // use a mysql-bin.000001 to restore data Enter password: [root @ localhost var] # .. /bin/mysql-uroot-p // enter the database to view mysql> use test; mysql> show tables; + ---------------- + | Tables_in_test | + ------------------ + | user | + ---------------- + 1 row in set (0.00 sec) mysql> select * from user; // view data, data returned + ---- + ---------- + | id | username | password | + ---- + ---------- + | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 1 | 2 | + ---- + ---------- + 3 rows in set (0.00 sec) mysql>/qBye to restore data from a point to a point, use the following operations to locate the point: -- start-position start point -- stop-position end point -- start-date start time -- stop-date end time now restores the mysql-bin.000002, [root @ localhost bin] #. /mysqlbinlog -- no-defaults -- start-position 134 -- stop-position = 386 .. /var/mysql-bin.000002 |. /mysql-uroot-p bitsCN.com