Two main functions of binlog

1. High availability
2. Data recovery.

How to Open Bilog

Configure in my.cnf: log_bin=/home/data/ mysql/binlog/mysql-bin

Check if it's open

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /home/data/mysql/binlog/mysql-bin       |
| log_bin_index                   | /home/data/mysql/binlog/mysql-bin.index |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

Close binlog

my.cnf logout #log_bin=/home/data/mysql/bin log/mysql-bin
Need restart data base

Open binlog on line

SET sql_log_bin=1|0 (1 is open, 0 is closed)
ps: Restart database failure

Switch the binlog command

Execute flush logs in mysql;

Modify binary log file size

Configure max_binlog_size in my.cnf

binlog cleaning

In master-slave mode, the master-slave mode needs to be closed

Set up a valid number of days to clean up binlog

my.cnf sets expire_logs_days=10; meaning to keep the binlog scroll clean for 10 days.

Manual cleaning of binlog files

According to the log file name: PURGE MASTER LOGS TO'MySQL-bin.010';
According to the log time: PURGE MASTER LOGS BEFORE'2015-03-02 13:00';
According to the log days: PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 1 DAY);
When clearing the binlog, note that the slave is consistent and cannot clear the log being copied.

Binary parsing using mysqlbinlog

Errors that may occur in executing mysqlbinlog
ERROR: Error in Log_event::read_log_event():'Found invalid event in binary log', data_len: 77, event_type: 19 Can not read entry at offset 306709: Error in log format or read error, just put the newly installed mysql bin directory in the environment variable. Or use full path: / usr/local/mysql/bin/mysqlbinlog

Resolved format (isolation level rc mode): read-commit

Statement - > sql statement
 Mixed - > DML (record all row changes) ddl(sql statement)
Row - > Record changes to all rows

• 1
• 2
• 3

• 1
• 2
• 3

rr mode is not restricted

Restoring data with binlog

Restoration conditions:

1. Old table: full backup sql file + binlog log log
2. New table: binlog log log (create during this binlog entry)

binlog recovery command

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000030 | /usr/local/mysql/bin/mysql -uroot -p

• 1

• 1

The function of the pipeline character is to execute the preceding statement first, and then import the database through the following statement.

Fixed point recovery

--start-position Starting point --stop-position End point
--start-date start time --stop-date  End time
eg:
/usr/local/mysql/bin/mysqlbinlog --start-datetime="2015-02-26 20:05:00" --stop-datetime="2015-02-26 20:07:00" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p

• 1
• 2
• 3
• 4

• 1
• 2
• 3
• 4

Actual combat notes

stay test Before that, you need to make sure that several key configurations in my.cnf are turned off, because if I open the configurations that specify ignore and write to binlog, the test table will not be able to write to binlog, and in one case, write to binlog. But data cannot be restored to tables.

#Do not write to the database in binlog binary logs
#binlog-ignore-db
#Write to database in binlog binary log
#binlog-do-db
#Specifies which database data to copy only
#replicate-do-db

• 1
• 2
• 3
• 4
• 5
• 6

• 1
• 2
• 3
• 4
• 5
• 6

Old table: full backup sql file + binlog log log

1. Formerly entered table data:

create table test(id int,name varchar(20)) engine=innodb charset=utf8;
insert into test(id,name)values(1,'a');
insert into test(id,name)values(233,'bbbb');

• 1
• 2
• 3

• 1
• 2
• 3

2. full backup

/usr/local/mysql/bin/mysqldump -uroot -p --opt --default-character-set=utf8 --extended-insert=false --triggers -R --hex-blob --all-databases --flush-logs   > /tmp/alldb.sql

• 1

• 1

3. build tables
- Enter data
insert into test(id,name)values(3,'c'); 
// A new binlog log log is created, and now a new binlog log is generated. The following actions are recorded in the new binlog log log

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    1 | a    |
|  233 | bbbb |
|    3 | c    |
+------+------+
6 rows in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12

4. Switching logs
To save the complete operation in a separate binlog
flush logs;

5. Delete data

select * from test; #Data available
delete from test;
drop table test;

• 1
• 2
• 3

• 1
• 2
• 3

6. Data recovery
First check the contents of the previous binlog:

/usr/local/mysql/bin/mysqlbinlog --no-defaults  --base64-output=decode-rows -v  mysql-bin.000042 | more

• 1

• 1

7. View the contents of the new binlog

/usr/local/mysql/bin/mysqlbinlog --no-defaults  --base64-output=decode-rows -v  mysql-bin.000043 | more

• 1

• 1

8. First restore the full backup set:
/usr/local/mysql/bin/mysql -uroot -p </tmp/alldb.sql 
9. Data recovery

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000042 | /usr/local/mysql/bin/mysql -uroot -p

• 1

• 1

10. View the changes in the table

New table new table: binlog log log (create during this binlog entry)

1. Switching logs
flush logs; 
2. Establishing new tables

create table test2(id int,name varchar(20)) engine=innodb charset=utf8;
insert into test2(id,name)values(1,'a');
insert into test2(id,name)values(2,'b');

• 1
• 2
• 3

• 1
• 2
• 3

3. Switching logs
flush logs; 
4. Later drop table;
drop table test2;

5. Then restore using binary logs

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000044 | /usr/local/mysql/bin/mysql -uroot -p

• 1

• 1

Fixed point recovery

Location:

start-position 
start-date 

• 1
• 2

• 1
• 2

Recovery according to start time

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2015-02-26 20:05:00" --stop-datetime="2015-02-26 20:07:00" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p

• 1

• 1

Recovery according to the starting position

/usr/local/mysql/bin/mysqlbinlog --start-position="20" --stop-position="2000" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p

• 1

• 1