Shulou(Shulou.com)06/01 Report--

Binlog is enabled and viewed:

> show variables like 'log_bin'; # to see if it is enabled

> set sql_log_bin=1 | | set sql_log_bin=0; # enabled | | disabled

> show binary logs; / / get the list of binlog files, corresponding to mysql-bin.index

> show master logs; / / View the binlog on the master

> show master status; / / View the binlog currently being written

> show binlog events; / / View the first binlog content

> show binlog events in 'mysql-bin.000002' specifies viewing.

Binlog deletion:

1. Close the mysql master and slave, shut down binlog, and then restart the database

2. Set automatic cleanup:

Mysql-e "show variables like 'expire_log%';"

> mysql-e 'set global expire_logs_days=3;' / / sets the automatic cleanup time to 3 days

> mysql-e 'flush logs' / / lets binary logs regenerate new files and automatically cleans up expired logs

(if the execution does not take effect, make sure that the mysql-bin.index is consistent with the external file, otherwise delete the excess, and then > flush logs;)

3 、

> PURGE MASTER LOGS BEFORE DATE_SUB (CURRENT_DATE, INTERVAL 10 DAY); / / Delete MySQL binlog logs from 10 days ago

> purge master logs before '2012-03-30 1715 20 00; / / Delete the binlog log files in the log index before the specified date

> purge master logs to 'mysql-bin.000002'; / / Delete the binlog log file in the log index of the specified log file

> reset master; or reset slave; / / empty the binlog; of master and the relay log of empty slave

If you delete the latest ones by mistake, make sure that the records in the mysql-bin.index are the same as the existing files outside, otherwise delete the redundant ones and then flush logs

4. Direct rm

Enter the database data directory & & check which binlog log is currently in use. Except for this, you can use rm. Delete it and correspond to the mysql-bin.index content.

If you delete the currently used binlog by mistake, you will find that mysql no longer records the binlog log. You have to change the contents of the mysql-bin.index file first, correspond to it, and then go to flush logs.

-

Introduction to binlog:

There are three ways to copy mysql:

Replication based on SQL statement (statement-based replication, SBR)

Row-based replication (row-based replication, RBR)

Mixed mode replication (mixed-based replication, MBR)

Three formats of binlog:

Statement

Every sql that will modify the data will be recorded in the binlog. There is no need to record the changes of each row, so the log volume is reduced and io is saved.

Row

Version 5.1.5 of MySQL began to support row level replication. It does not record the context-sensitive information of sql statements, but only saves which records are modified.

Mixed

Starting from version 5.1.8, MySQL provides Mixed format, which is actually a combination of Statement and Row. General statement modifications use statment format to save binlog. For some functions, if statement cannot complete the master-slave copy operation, it is saved in row format.

File configuration:

Binlog_format = MIXED / / binlog log format

Log_bin = directory / mysql-bin.log / / binlog log name

Expire_logs_days = 7 / / binlog expiration cleanup time

Max_binlog_size 100m / / binlog each log file size, default 1G

Binlog recovery:

1. To enable binlog, it is best to change the configuration file or open it directly in the database.

2. Analog data is written in accordance with the following appendix. Query the current data, and then record the current content, as follows:

Mysql > select * from number

+-+

| | 1 | 2016-06-29 23:27:15 |

| | 2 | 2016-06-29 23:27:15 |

| | 3 | 2016-06-29 23:27:15 |

| | 4 | 2016-06-29 23:27:15 |

| | 5 | 2016-06-29 23:27:15 |

| | 6 | 2016-06-29 23:27:15 |

| | 7 | 2016-06-29 23:27:15 |

| | 8 | 2016-06-29 23:27:15 |

| | 9 | 2016-06-29 23:27:15 |

| | 10 | 2016-06-29 23:27:15 |

+-+

3. Perform a full backup, mysqldump-F-master-data=2 backup | gzip > backup_all.sql.gz / /-F refresh binlog is 0002 (or we'd better have a full backup at 4 / 5 o'clock every morning)

4. Continue to simulate data writing, execute the contents under the appendix, and view:

+-+-

| | 1 | 2016-06-29 23:27:15 |

| | 2 | 2016-06-29 23:27:15 |

| | 3 | 2016-06-29 23:27:15 |

| | 4 | 2016-06-29 23:27:15 |

| | 5 | 2016-06-29 23:27:15 |

| | 6 | 2016-06-29 23:27:15 |

| | 7 | 2016-06-29 23:27:15 |

| | 8 | 2016-06-29 23:27:15 |

| | 9 | 2016-06-29 23:27:15 |

| | 10 | 2016-06-29 23:27:15 |

| | 11 | 2016-06-29 23:31:03 |

| | 12 | 2016-06-29 23:31:03 |

| | 13 | 2016-06-29 23:31:03 |

| | 14 | 2016-06-29 23:31:03 |

| | 15 | 2016-06-29 23:31:03 |

| | 16 | 2016-06-29 23:31:03 |

| | 17 | 2016-06-29 23:31:03 |

| | 18 | 2016-06-29 23:31:03 |

| | 19 | 2016-06-29 23:31:03 |

| | 20 | 2016-06-29 23:31:03 |

+-+ +

5. Delete the data table: delete from number

6. Simulate data writing again, select * from bumber

+-+

| | id | updatetime |

+-+

| | 21 | 2016-06-29 23:41:06 |

| | 22 | 2016-06-29 23:41:06 |

| | 23 | 2016-06-29 23:41:06 |

| | 24 | 2016-06-29 23:41:06 |

| | 25 | 2016-06-29 23:41:06 |

| | 26 | 2016-06-29 23:41:06 |

| | 27 | 2016-06-29 23:41:06 |

| | 28 | 2016-06-29 23:41:06 |

| | 29 | 2016-06-29 23:41:06 |

| | 30 | 2016-06-29 23:41:06 |

+-+

7. Restore data:

Lock the table first without data manipulation: lock table number read

After that, it is best to refresh the log, generate a new binlog, and operate the problematic binlog separately (backup the problematic binlog first)

Enter the database to see the pos point of the problem: show binlog events in 'mysql-bin.000002'; / / then delete the pos point record of the problem

Import full backup in the morning or at a certain time: mysql backup

< backup_all.sql binlog日志恢复法一： mysqlbinlog mysql-bin.000002 | mysql -u用户名 -p密码 数据库名 [mysqlbinlog --start-position=1038 --stop-position=1164 --database=zyyshop mysql-bin.00002 | mysql -v zyyshop ] 常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 ---start-datetime="2013-11-29 13:18:54" 起始时间点 ---stop-datetime="2013-11-29 13:21:53" 结束时间点 ---database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志) binlog日志恢复法二： mysqlbinlog mysql-bin.000002 >

Tmp.sql

Delete vim tmp.sql / / when you find delete

Mysql backup < tmp.sql / / then type the operation into the database

Note: because there is no data insertion in the lock table, if any data is written, the latest binlog should also be imported into the database.

8. If you check it, you will find that all the data have come back.

Appendix:

1. Create a table

Create databases backup;CREATE TABLE `number` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'number', `updatetime` timestamp NOT NULL DEFAULT '0000-00-0000: 00 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Test procedures for writing data:

# coding:utf8#python2.7import MySQLdbimport timedef connect_mysql (db_host= "192.168.11.169", user= "martin", passwd= "martin", db= "backup", charset= "utf8"): conn = MySQLdb.connect (host=db_host,user=user,passwd=passwd,db=db,charset=charset) conn.autocommit (True) return conn.cursor ()

3. Data insertion:

For i in range (0 Y-%m-%d 10): # time=time.strftime ("% Y-%m-%d% H:%M:%S") sql = 'insert into number (updatetime) values (% s)' values = [(time.strftime ("% Y-%m-%d% H:%M:%S"))] db1 = connect_mysql () print db1.executemany (sql,values)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.