Binarylog of mysql _ MySQL

Binarylog of mysql

A preliminary study of mysql binary log

Binary log is an important part of mysql and has two main functions:

Replication: After the binary log is enabled on the master side, the log records all database changes. then, the slave side can obtain the Log file content and perform the same operation on the slave side.

Backup: At a certain time point a makes a backup, and then uses the binary log to record all the database changes after this time point a, and then the next restore, data can be restored by using the backup file a at the time point and the binary log file.

The parameters related to binary log are as follows:

Log-bin and log-bin_index: specifies the location of log files and index files.

Max_binlog_size: maximum binary log size

Binlog_cache_size: the memory size of the current transaction cache.

Binlog_cache_disk_use: number of transactions currently stored on the disk. if this value has a value, you should pay attention to tuning.

Max_binlog_cache_size: maximum number of transaction caches in the memory

Binlog_do_db and binlog_ingore_db: an option for controlling which databases to collect.

Sync_binlog: This value controls how many times the cache data commit is flushed to the disk. The default value is 0, that is, let the database decide the synchronization frequency. For example, if it is set to 1, the cache data is synchronized to the disk every time the commit is performed. this is the most secure, but the performance is the worst.

Log control command:

Manually switch log: flush logs

View log Files: show binary logs, show master logs reset log: reset master delete some logs: purge binary logs to 'log _ name' | 'date'

Automatically delete expired logs. you can set expire_logs_days to control logs deleted several days ago.

In addition, binlog_format has three options: statement, row, and mixed to control the format of binary logs.

You can use the mysqlbinlog command to print the generated binary log information.

mysql> show variables like '%format%';+--------------------------+-------------------+| Variable_name            | Value             |+--------------------------+-------------------+| binlog_format            | ROW               |[root@rhel131 tmp]# mysqlbinlog /tmp/1.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#131018 23:31:26 server id 1  end_log_pos 120 CRC32 0x67b9d21f  Start: binlog v 4, server v 5.6.13-log created 131018 23:31:26 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'TlRhUg8BAAAAdAAAAHgAAAABAAQANS42LjEzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABOVGFSEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR/SuWc='/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

BitsCN.com