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

1. Basic knowledge

Logs in text format and binary format are supported in previous versions of mysql5.0, but only in binary format are supported in post-mysql5.0 versions. Because binary log has more advantages in performance, information processing and so on.

2. Enable mysql binary log

Mysql binary logging is enabled by the log-bin option of the configuration file my.cnf.

By default, the mysql server creates two new files in the data root directory: XXX-bin.000001 and XXX-bin.index. If the configuration option does not give a file name, mysql will use mysql-bin to name the two files, where the .index file contains a complete list of log files. As follows:

[root@xin-c ~] # vim / etc/my.cnf

It is not open by default, so we open the comment:

Restart the service

[root@xin-c ~] # / etc/init.d/mysqld restart

[root@xin-c ~] # cd / data/mysql/

Mysql records user changes to the content and structure of all databases in the XXX-bin.n file, but does not record SELECT and UPDATE statements that are not actually updated.

Of course, we can also use the relevant commands to query whether mysql has enabled binlog logs. As follows:

Mysql > show variables like 'log_%'

3. Generation of binlog log files

When mysql stops or restarts, the server will record the log file in the next log file, and mysql will generate a new log file on restart, and the file sequence number will be incremented in turn.

If the log file exceeds the upper limit of the max_binlog_size (configured in the my.cnf file) system variable configuration, a new log file is also generated.

Note: / etc/my.cnf does not have this option by default and needs to be customized and added:

[root@xin-m mysql] # vim / etc/my.cnf

In addition, if you execute the flush logs command in the mysql command, a new log file will be generated.

Mysql > flush logs

4. View the binlog log in the system

View the binlog logs on the system and use the show master logs command. As follows:

Mysql > show master logs

5. View the binlog log file

Mysql provides the mysqlbinlog command to view the log file, which records the current time as each change log is recorded for database recovery. As follows:

[root@xin-m mysql] # mysqlbinlog mysql-bin.000004 | more

6. Use binlog logs for database recovery

If you encounter a disaster event, you should restore the database with the most recent full backup, and then use the binlog log after the backup to restore the database to the closest available state.

When using binlog logs for recovery, you need to do so in turn, that is, the earliest generated log files should be restored first. The format of the command to restore data is as follows:

Mysqlbinlog xxx-bin.000001 | mysql-uroot-paired password 'dataname

-u specifies the name of the database user name-p user name corresponding to the password of the dataname database

7. Detailed explanation of binlog command line parameters

The my.cnf configuration file for binlog configuration is as follows:

[root@xin-m mysql] # cat / etc/my.cnf | grep-v ^ # | grep-v ^ $

8. Delete binlog log

8.1 Delete some binlog logs

Delete part of the binlog log, we can delete the log name and the time when the log was generated. The delete command is as follows:

Mysql > purge master logs to 'mysql-bin.000009'

Delete according to the log name, delete only the logs before log_name, and log_name itself will not be deleted.

From the image above, we can see that all the logs before mysql-bin.000009 have been deleted.

[root@xin-c mysql] # ll--full-time

Mysql > purge master logs before '2016-05-17 15 purge 2715 53'

According to the time when the log is generated, only the log before date is deleted, and the date itself will not be deleted.

8.2 Delete all binlog logs

Delete all previous binlog logs and regenerate the new binlog, with the suffix starting at 000001. Use the command:

Mysql > reset master

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.