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

Mysql binary logs are records of some mysql command operations.

Delete binary log information:

Binary logs record a large amount of information (which contains some useless information). If the binary log is not cleaned for a long time, a lot of disk space will be wasted. However, after deletion, it may be impossible to recover when the database crashes, so if you want to delete the binary log, first back it up with the database, and you can only delete the binary log before the backup. The newly generated log information cannot be deleted (you can do point-in-time restore). Nor can it be deleted directly after shutting down the mysql server because it may bring errors to the database. If you do not want to delete the binary log, you need to do the following: export the backup database and binary log files for compressed archive storage. The methods to delete binaries are as follows:

1. Use the RESET MASTER statement to delete all binary logs and re-record them.

Mysql > reset master

Mysql > show binary logs

Parsing: this operation is not recommended in a production environment at first; after all the binary logs are deleted, Mysql will recreate the new binary logs. The number of the new binary log starts at 000001.

2. Delete the binary log based on the file or point in time:

Grammatical form:

Mysql > PURGE {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

Where TO'log_name' means to delete all other files before this file, or you can use BEFORE datetime_expr to specify when the binary files before this file have been deleted.

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000007'; # delete all other files before the mysql-bin.000007 file (000001Murray 000006)

Mysql > PURGEBINARY LOGS BEFORE '2013-10-19 10 / 10 / 26 / 26 / 36; # use time to delete binary logs

★ Note: my.cnf configuration enables binary logging function to record all databases by default. If you specify a database to do binary logging, add it.

Log-bin = mysql-bin

Binlog-do-db = happy

Binlog-do-db = iwker_global

# # binary enabled status:

Mysql > show global variables like "log_bin%"

# # use the show master status command to view databases with binary logging enabled

Mysql > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | mysql-bin.000002 | 120 | iwker_global,iwker_inside,iwker_ucenter | |

+-- +

# # viewing binary logs on MySQL Server

Mysql > show binary logs

★★★ uses the mysqlbinlog tool to restore

Steps:

1. Analyze and locate the key location or time point

2. Import the files backed up by the whole library

3. Comment out the misoperation command or avoid the time point of misoperation to import.

/ / convert binary logs to readable SQL files

Mysqlbinlog mysql-bin.000001 > mysql.sql

/ / or export only the binary log of a specified database

Mysqlbinlog-d happy mysql-bin.000001 > happy_bin.sql

a. Query binary log files containing records of drop operations

Mysqlbinlog binlog.0000003 | less / / output includes all statements contained in the binlog.000003, as well as other information such as the time spent on each statement, the thread ID issued by the customer, the timestamp when the thread was issued, and so on.

Mysqlbinlog mysql-bin.000002 | grep-I-C 2 "drop table"-c

b. Restore according to time-- start-datetime,--stop-datetime

Mysqlbinlog-- start-datetime= "2010-09-29 18:00:00"-- stop-datetime= "2010-09-29 23:00:00" / var/lib/mysql/mysql-bin.000002 | mysql- u root-p

Mysqlbinlog-- stop-datetime='2016-07-21 14 14 40 mysql- uroot 10'/ var/log/mysql-bin.000001 | mysql- uroot-p

c. Restore based on the database name

Mysqlbinlog-d iwker_inside / var/lib/mysql/mysql-bin.000002

d. Location-based recovery

Mysqlbinlog-- start-position=370-- stop-position=440 / var/lib/mysql/mysql-bin.000002 | mysql- u root-p

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.