Linux Learning Note: mysqlbinlog

First, the Binlog feature is not open by default and needs to be modified my.cnf to turn on

[[email protected] ~]# grep bin/disk2/mysql_multi_instances/3308/my.cnf

Log-bin =/disk2/mysql_multi_instances/3308/mysql-bin << Most important is this command to open, and must be written under [mysqld]

Binlog_cache_size = 1M

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

View the setting values for the Binlog, so you can view

[Email protected] ~]# mysql-uroot-pjet-speed-e "show variables;" | grep bin

Second, simulate a disaster recovery

The contents of the library are as follows

Mysql> select * from student;

+----+-----------+-------+| ID | name |  Score |+----+-----------+-------+| 1 |   Coosh |  100 | | 2 |    mingming |  90 | | 3 |    Xiaozhang |  60 | | 4 |    Mama | |+----+-----------+-------+4 rows in Set (0.00 sec)

Full library backup once;

[Email protected] ~]# mysqldump-uroot-pjet-speed-b mydb-f-x--events--master-data=1|gzip >mydb.sql.gz

Insert three data in the student table;

mysql> INSERT into student (Name,score) VALUES (' Hsooc ', +), (' Ginmginm ', ' n '), (' Amam ', 63);

Query OK, 3 Rows Affected (0.00 sec)

Records:3 duplicates:0 warnings:0

And then erase the whole student table.

mysql> drop table student;

Query OK, 0 rows Affected (0.00 sec)

Start disaster recovery

Take a look at the backup file mydb.sql.gz

[Email protected] ~]# Zcat mydb.sql.gz

--MySQL dump 10.13 distrib 5.1.71, for Redhat-linux-gnu (x86_64)

--

--Host:localhost Database:mydb

-- ------------------------------------------------------

--Server version 5.1.71-log

/*!40101 SET @[email protected] @CHARACTER_SET_CLIENT */;

/*!40101 SET @[email protected] @CHARACTER_SET_RESULTS */;

/*!40101 SET @[email protected] @COLLATION_CONNECTION */;

/*!40101 SET NAMES UTF8 */;

/*!40103 SET @[email protected] @TIME_ZONE */;

/*!40103 SET time_zone= ' +00:00 ' */;

/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 */;

/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;

/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' */;

/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 */;

--

--Position to start replication or point-in-time recovery from

--

Change MASTER to master_log_file= ' mysql-bin.000005 ', master_log_pos=106;

"A bunch of content"

The last sentence of the above is the--master-data=1 effect, reminding the DBA that the next SQL Point is located in the file mysql-bin.000005, positioned at 106.

Let's recover the data from this backup file first, and then use Mysqlbinlog for incremental recovery later.

[[email protected] ~]# gzip-d mydb.sql.gz << get text file Mydb.sql

[Email protected] ~]# mysql-uroot-p mydb <mydb.sql

[Email protected] ~]# mysql-uroot-p-E "Use mydb;select * from student;"

+----+-----------+-------+| ID | name |  Score |+----+-----------+-------+| 1 |   Coosh |  100 | | 2 |    mingming |  90 | | 3 |    Xiaozhang |  60 | | 4 |    Mama | |+----+-----------+-------+

Recovered to 4th record

View Binlog File

[[Email protected] ~]# mysqlbinlog -d mydb /var/lib/mysql/mysql-bin.000005|grep  -B 20  ' drop table '   # at 106#160122 22:16:13 server id  1  end_log_pos 134   intvarset insert_id=5/*!*/;# at 134# 160122 22:16:13 server id 1  end_log_pos 279   query    thread_id=12    exec_time=0     error_code=0use  ' MyDB '/*!*/; set timestamp=1453472173/*!*/; set @ @session. pseudo_thread_id=12/*!*/; set @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. Unique_checks=1,  @ @session. autocommit=1/*!*/; set @ @session. sql_mode=0/*!*/; set @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c latin1  *//*!*/; set @ @session. character_set_client=8,@ @session. Collation_connection=8,@ @session. collation_server=8/*!*/; set @ @session. lc_time_names=0/*!*/; set @ @session .collation_database=default/*!*/;insert into student  (name,score)  values (' Hsooc ', ' + '),  (' Ginmginm ', "Max"),  (' Amam ', +)/*!*/;# at 279#160122 22:17:05 server  id 1  end_log_pos 360   Query   thread_id=12     exec_time=0     error_code=0set timestamp=1453472225/*!*/;d ROP  table student

The

Uses-D to filter the output of the specified database content. You can see that the drop table student is at 279 bit, and the previous position 134 is the point we are going to restore to. That means our recovery point is from 106 to 134;

[[email protected] ~]# mysqlbinlog -d mydb /var/lib/mysql/mysql-bin.000005  --start-position=106 --stop-position=279 >mydb.binlog.sql[[email protected] ~]#  less mydb.binlog.sql /*!40019 set @ @session .max_insert_delayed_threads=0*/;/*!50003  Set @[email protected] @COMPLETION_TYPE, completion_type=0*/;D elimiter /*!*/;# at 4# 160122 22:13:53 server id 1  end_log_pos 106   start:  Binlog v 4, server v 5.1.71-log created 160122 22:13:53# warning:  this binlog is either in use or was not closed  properly. binlog  ' Itmivg8baaaazgaaagoaaaabaaqans4xljcxlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaau WAEGGGAAAAICAGC '/*!*/;# at 106#160122 22:16:13 server id 1  eNd_log_pos 134   intvarset insert_id=5/*!*/;# at 134#160122 22:16:13  server id 1  end_log_pos 279   query   thread_id= 12    exec_time=0     error_code=0use  ' MyDB '/*!*/; set timestamp=1453472173/*!*/; set @ @session. pseudo_thread_id=12/*!*/; set @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. Unique_checks=1,  @ @session. autocommit=1/*!*/; set @ @session. sql_mode=0/*!*/; set @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c latin1  *//*!*/; set @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=8/*!*/; set @ @session. lc_time_names=0/*!*/; set @ @session .collation_database=default/*!*/;insert into student  (name,score)  values (' Hsooc ', '),  (' ginmginm ', '),  (' AMAm ',/*!*/;D elimiter ;# end of log filerollback /* added by  mysqlbinlog */;/*!50003 set [email protected]_completion_type*/;mydb.binlog.sql  (END)

Import and view, successful recovery.

[[email protected] ~]# mysql -uroot -p mydb <mydb.binlog.sql                [[email protected] ~]#  mysql -uroot -p -e  "use mydb;select * from student;" +----+-----------+-------+| id | name      | score |+-- --+-----------+-------+|  1 | coosh     |   100  | |   2 | mingming  |    90 | |   3 | xiaozhang |    60 | |   4 | mama      |    63 | |   5 | hsooc     |   100 | |   6 | ginmginm  |    90 | |   7 | amam      |    63 |+----+-----------+-------+ 

Linux Learning Note: mysqlbinlog