Management of Relational Database Mysql binary Log detailed steps 02/23 Update SLTechnology News&Howtos

Management of Relational Database Mysql binary Log detailed steps

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

The following mainly brings you the detailed steps of Mysql binary log management of relational database. I hope these contents can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

MySQL binary log (Binary Log)

1. It contains the following contents and functions:

Contains all updated data or potentially updated data (such as a DELETE that does not match any rows)

Contains information about the execution time of statements for each update database (DML)

It does not contain statements that have not modified any data. If you need to enable this option, you need to enable the general logging function.

The main goal is to restore the database to the point of failure as much as possible, because the binary log contains all updates made after the backup

Used to record all statements to be sent to the slave cloud server on the master replication cloud server

Enabling this option reduces database performance by 1%, but ensures database integrity. It is worth trading performance for integrity for important databases. Somewhat similar to Oracle turning on archiving mode.

two。 The method and property of opening binary log

When you use the-- log-bin [= file_name] option or specify in the configuration file that log-bin starts, mysqld writes to the log file of the SQL command that contains all the updated data.

For no file_ name value, the default name is the hostname followed by-bin.

When no absolute path is specified, the default location is saved in the data directory.

Each binary log name adds a numeric extension for log aging, so custom extensions are not supported and are dynamically replaced by mysql numeric extensions.

If the current log size reaches max_binlog_size, a new binary log is automatically created.

For large transactions, the binary log exceeds the value set by max_binlog_size. That is, the transaction only writes to a binary log.

Therefore, the binary log file size is similar, and its size is not exactly the same, which is different from oracle.

The binary log file will have a corresponding binary log index file, which contains all binary logs with the same file name as the binary log with the extension .index

The binary index file is specified by the-- log-bin-index [= file_name] option

The RESETMASTER statement deletes all binary log files, which affects the slave library. You can also use PURGE MASTER LOGS to delete only part of the binaries.

3. The binary log records all operations that make changes to the database, and binary has the following two main functions:

1. Restore (recovery) 2, copy (replication)

Startup of binary logs: the configuration parameter log-bin [= name]. If you do not specify name, the default binary log file name is the hostname and suffix is the sequence number of the binary log, and the path is the directory where the database resides.

The file with the suffix index is the index file of the binary log and is used to store the binary log produced by the process.

Parameters related to binary logs:

Max_binlog_size 、 binlog_cache_size 、 sync_binlog 、 binlog-do-db 、 binlog-ignore-db 、 log-slave-update 、 binlog_format

(1) max_binlog_size: this parameter specifies the maximum value of a single binary log file. If this value is exceeded, a new binary log file is generated with the suffix + 1 and recorded to the .index file. The default value starting from Mysql5.0 is 1073741824, which represents 1G.

Take MySQL5.5 as an example:

When using the transaction's storage engine InnoDB, all uncommitted transactions are recorded in a cache. When waiting for the transaction to commit, the binary log in the buffer is written directly to the binary log file. The size of the buffer is determined by binlog_cache_size, and the default size is 32KB. In addition, binlog_cache_size is based on session, that is, when a thread starts a transaction. Mysql automatically allocates a cache with the size of binlog_cache_size, so you need to be very careful in setting this value. You can check the status of binlog_cache_use and binlog_cache_disk_use through show global status to determine whether the current binlog_cache_size setting is appropriate.

(2) the default size of Binlog_cache_size is 32KB.

3. The parameter sync_binlog= [N] indicates that each write cache is synchronized to the disk. If N is set to 1, the binary log is written synchronously to the disk. This parameter is very important and will be mentioned later. It is worth noting that when this parameter is set to 1, innodb_support_xa should also be set to 1 to solve the problem, which ensures that the binary log is synchronized with the InnoDB storage engine data file.

4. Parameters binlog-do-db and binlog-ignore-db indicate which library logs need to be written or ignored. If the default value is empty, the logs of all libraries are synchronized to the binary log.

5. Log-slave-update this parameter needs to be configured when building the architecture of master= > slave= > slave.

6. Binlog_format parameters are also very important. This parameter has been introduced since the mysql5.1 version. The values that can be set are STATEMENT\, ROW, and MIXED.

(1) the STATEMENT format is the same as the previous mysql version, and the binary log file records the logical SQL statements of the log.

(2) in ROW format, the binary log records no longer simple SQL statements, but records the row changes of the table. At this time, the transaction isolation of InnoDB can be basically set to READ COMMITTED to obtain better concurrency.

(3) in MIXED format, mysql defaults to STATEMENT format for recording binary log files, but ROW format is used in some cases, including:

1) the storage engine of the table is NDB, and all DML operations on the table will be recorded in ROW format.

2) use uncertain functions such as UUID (), USER (), CURRENT_USER (), FOUND_ROWS (), ROW_COUNT (), etc.

3) INSERT DELAY statement is used

4) user-defined functions are used

5) temporary tables are used

Practical case applications:

(1)。 Binary log location

[root@mysql ~] # cat / etc/my.cnf | grep datadirdatadir = / mydata/data

Or:

View log information:

[root@mysql ~] # ls-l / mydata/data/mysql-bin.*-rw-rw----. 1 mysql mysql 126 May 6 14:39 / mydata/data/mysql-bin.000001-rw-rw----. 1 mysql mysql 2576 May 8 17:02 / mydata/data/mysql-bin.000002-rw-rw----. 1 mysql mysql 126 May 8 17:03 / mydata/data/mysql-bin.000003-rw-rw----. 1 mysql mysql 126 May 8 17:07 / mydata/data/mysql-bin.000004-rw-rw----. 1 mysql mysql 126 May 8 17:08 / mydata/data/mysql-bin.000005-rw-rw----. 1 mysql mysql 126 May 8 17:09 / mydata/data/mysql-bin.000006-rw-rw----. 1 mysql mysql 22481 May 27 18:34 / mydata/data/mysql-bin.000007-rw-rw----. 1 mysql mysql 107 Jun 4 10:48 / mydata/data/mysql-bin.000008-rw-rw----. 1 mysql mysql 152 Jun 4 10:48 / mydata/data/mysql-bin.index

Tips:mysql-bin.index records the index file of MySQL

[root@mysql ~] # cat / mydata/data/mysql-bin.index. / mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004./mysql-bin.000005./mysql-bin.000006./mysql-bin.000007./mysql-bin.000008

(2)。 Switching of binary log files

Execute flush logs to generate a new binary log:

Execute the FLUSH LOGS scroll log to generate a new log file

Mysql > FLUSH LOGS;Query OK, 0 rows affected (0.08 sec)

Query files generated by scrolling logs

[root@mysql ~] # ls-l / mydata/data/mysql-bin.*-rw-rw----. 1 mysql mysql 126 May 6 14:39 / mydata/data/mysql-bin.000001-rw-rw----. 1 mysql mysql 2576 May 8 17:02 / mydata/data/mysql-bin.000002-rw-rw----. 1 mysql mysql 126 May 8 17:03 / mydata/data/mysql-bin.000003-rw-rw----. 1 mysql mysql 126 May 8 17:07 / mydata/data/mysql-bin.000004-rw-rw----. 1 mysql mysql 126 May 8 17:08 / mydata/data/mysql-bin.000005-rw-rw----. 1 mysql mysql 126 May 8 17:09 / mydata/data/mysql-bin.000006-rw-rw----. 1 mysql mysql 22481 May 27 18:34 / mydata/data/mysql-bin.000007-rw-rw----. 1 mysql mysql 150 Jun 4 13:35 / mydata/data/mysql-bin.000008-- > pre-scroll log file-rw-rw----. 1 mysql mysql 107 Jun 4 13:35 / mydata/data/mysql-bin.000009-- > scrolling log file-rw-rw----. 1 mysql mysql 171 Jun 4 13:35 / mydata/data/mysql-bin.index

Query the current log file record status and log files

You can see that the log file has become mysql-bin.000009, which proves that the scrolling has been successful.

Shell mode uses mysqladmin

[root@mysql ~] # mysqladmin flush-logs-uroot-predhat [root@mysql ~] # ls-l / mydata/data/mysql-bin.*-rw-rw----. 1 mysql mysql 126 May 6 14:39 / mydata/data/mysql-bin.000001-rw-rw----. 1 mysql mysql 2576 May 8 17:02 / mydata/data/mysql-bin.000002-rw-rw----. 1 mysql mysql 126 May 8 17:03 / mydata/data/mysql-bin.000003-rw-rw----. 1 mysql mysql 126 May 8 17:07 / mydata/data/mysql-bin.000004-rw-rw----. 1 mysql mysql 126 May 8 17:08 / mydata/data/mysql-bin.000005-rw-rw----. 1 mysql mysql 126 May 8 17:09 / mydata/data/mysql-bin.000006-rw-rw----. 1 mysql mysql 22481 May 27 18:34 / mydata/data/mysql-bin.000007-rw-rw----. 1 mysql mysql 150 Jun 4 13:35 / mydata/data/mysql-bin.000008-rw-rw----. 1 mysql mysql 150 Jun 4 13:55 / mydata/data/mysql-bin.000009-rw-rw----. 1 mysql mysql 107 Jun 4 13:55 / mydata/data/mysql-bin.000010-rw-rw----. 1 mysql mysql 190 Jun 4 13:55 / mydata/data/mysql-bin.index

Query the current log file record status and log files

Mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000010 | 107 | +- + 1 row in set (0.00 sec) [root@mysql ~] # mysqladmin refresh-uroot-predhat [root@mysql ~] # ls-l / mydata/data/mysql-bin.*-rw-rw----. 1 mysql mysql 126 May 6 14:39 / mydata/data/mysql-bin.000001-rw-rw----. 1 mysql mysql 2576 May 8 17:02 / mydata/data/mysql-bin.000002-rw-rw----. 1 mysql mysql 126 May 8 17:03 / mydata/data/mysql-bin.000003-rw-rw----. 1 mysql mysql 126 May 8 17:07 / mydata/data/mysql-bin.000004-rw-rw----. 1 mysql mysql 126 May 8 17:08 / mydata/data/mysql-bin.000005-rw-rw----. 1 mysql mysql 126 May 8 17:09 / mydata/data/mysql-bin.000006-rw-rw----. 1 mysql mysql 22481 May 27 18:34 / mydata/data/mysql-bin.000007-rw-rw----. 1 mysql mysql 150 Jun 4 13:35 / mydata/data/mysql-bin.000008-rw-rw----. 1 mysql mysql 150 Jun 4 13:55 / mydata/data/mysql-bin.000009-rw-rw----. 1 mysql mysql 150 Jun 4 13:58 / mydata/data/mysql-bin.000010-rw-rw----. 1 mysql mysql 107 Jun 4 13:58 / mydata/data/mysql-bin.000011-rw-rw----. 1 mysql mysql 209 Jun 4 13:58 / mydata/data/mysql-bin.index

Query the current log file record status and log files

(3) query log-related parameters

(4) binary log generated by transaction operation

Mysql > USE hellodb;Database changedmysql > SHOW TABLES +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in Set (0.00 sec) mysql > SELECT * FROM coc +-+ | ID | ClassID | CourseID | +-+ | 1 | 1 | 2 | 2 | 1 | 5 | 3 | 2 | 2 | 4 | 2 | 6 | 5 | 3 | 1 | | 6 | | 3 | 7 | 7 | 4 | 5 | 8 | 4 | 2 | 9 | 5 | 1 | 10 | 5 | 9 | 11 | 6 | 3 | 12 | 6 | 4 | 13 | 7 | 4 | 14 | 7 | 3 | + | -+-+ 14 rows in set (0.02 sec) mysql > INSERT INTO coc VALUES (15 sec 7 5) Query OK, 1 row affected (0.00 sec) mysql > COMMIT;Query OK, 0 rows affected (0.00 sec)

Query the currently used binary log files

Query the contents of the generated binary log:

[root@mysql ~] # mysqlbinlog / mydata/data/mysql-bin.000011 / *! 50530 SET @ SESSION.PSEUDOVLAVESLAVEMODE1 / session. Max.insertdelayedthreads@ @ session.Max.Placement 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / # at 4 "160604 13:58:31 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.33-log created 160604 13 end_log_pos 58 binlog Warning: this binlog is either in use or was not closed properly.BINLOG 'B25SVw8BAAAAZwAAGsAABAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlcards increasing numbers off set @ @ session.auto_increment_increment=1, @ @ session.autovariation increments offsetbacks @ @ session.session .autogenes increments offsetbacks @ @; SET @ @ session.session setbacks clientmakers 3movie accounts session.collationalization connectionsession.collationalization connection3codes session .collationalizations servercards 33Get SET @ @ session.lctimestamp timekeeper namespace namespace SET @ @ session.collationalization databaseset @ @ session.collationalization databaseDeFAULTGER INTO coc VALUES at 178' 160604 14:14:44 server id 1 hellodb` / *! * /; SET timestamp 1465020884Universe lead to insert INTO coc VALUES (155e 7f5) / *! / # at 275 "160604 14:14:44 server id 1 end_log_pos 347 Query thread_id=10 exec_time=0 error_code=0SET timestamp 1465020884Compact / DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET completion TYPENETION OLDMPLETIONSTAMPPLEATION TYPEITER / TYPETPEER Bandard: 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql] # strings / mydata/data/mysql-bin.0000115.5.33-loghellodbBEGINhellodbINSERT INTO coc VALUES

View the generated binary log

View logged events:

Mysql > SHOW BINLOG EVENTS +-+-+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + -+ | mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log Binlog ver: 4 | | mysql-bin.000001 | 107 | Stop | 1 | 126 | | +-+- -- + 2 rows in set (0.02 sec) mysql > SHOW BINLOG EVENTS IN 'mysql-bin.000011' +-+-- + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+- -- + | mysql-bin.000011 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log Binlog ver: 4 | mysql-bin.000011 | Query | 1 | 178 | BEGIN | | mysql-bin.000011 | 178 | Query | 1 | 275 | use `hellodb` INSERT INTO coc VALUES (15pr 7 5) | | mysql-bin.000011 | 275 | Query | 1 | 347 | COMMIT | +-+- -+ 4 rows in set (0.00 sec)

Using binary logs for data recovery applications

Simulated data environment

Mysql > USE hellodb;Database changedmysql > SELECT * FROM coc;+----+ | ID | ClassID | CourseID | +-+ | 15 | 7 | 5 | +-+ 1 rows in set (0.00 sec) mysql > DELETE FROM coc Query OK, 1 rows affected (0.01 sec) mysql > COMMIT;Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM coc;Empty set (0.00 sec)

Use log recovery to analyze the log generation file:

[root@mysql ~] # mysqlbinlog / mydata/data/mysql-bin.000011 > / tmp/log11.txt

[root@mysql ~] # cat / tmp/log11.txt

/ *! 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

# 160604 13:58:31 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.33-log created 160604 13:58:31

# Warning: this binlog is either in use or was not closed properly.

BINLOG'

B25SVw8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/ *! * /

# at 107

# 160604 14:14:44 server id 1 end_log_pos 178 Query thread_id=10 exec_time=0 error_code=0

SET timestamp 1465020884

SET @ @ session.pseudoclinic thread readership idling 10 zipper cycles /

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards, modewords, 0planks / sessions.

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 178

# 160604 14:14:44 server id 1 end_log_pos 275 Query thread_id=10 exec_time=0 error_code=0

Use `hellodb` / *! * /

SET timestamp 1465020884

INSERT INTO coc VALUES (15, 7, 5)

/ *! * /

# at 275

# 160604 14:14:44 server id 1 end_log_pos 347 Query thread_id=10 exec_time=0 error_code=0

SET timestamp 1465020884

COMMIT

/ *! * /

# at 347

# 160604 15:31:40 server id 1 end_log_pos 418 Query thread_id=10 exec_time=0 error_code=0

SET timestamp 1465025500

BEGIN

/ *! * /

# at 418;; the number of operation events recorded in the log. If you want to recover the previous data, you need to stop before DELETE

# 160604 15:31:40 server id 1 end_log_pos 499 Query thread_id=10 exec_time=0 error_code=0

SET timestamp 1465025500

DELETE FROM coc

/ *! * /

# at 499

# 160604 15:31:40 server id 1 end_log_pos 571 Query thread_id=10 exec_time=0 error_code=0

SET timestamp 1465025500

COMMIT

/ *! * /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Recovery using binary Log

[root@mysql ~] # mysqlbinlog / mydata/data/mysql-bin.000011 | mysql- uroot-predhat

View recovered data

Mysql > SELECT * FROM coc;+----+ | ID | ClassID | CourseID | +-+ | 15 | 7 | 5 | +-+ 1 row in set (0.00 sec)

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.