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

The role of binary log: it mainly implements three important functions: for replication, for recovery, and for audit.

Parameters related to binary log:

Log_bin

Setting this parameter enables the binlog feature and specifies the path name

Log_bin_index

Set this parameter to specify the path and name of the binary index file

Binlog_format

This parameter controls three binary log formats: STATEMENT,ROW,MIXED

① STATEMENT Mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of every sql statement and every row, which reduces the amount of binlog log, saves IO, and improves performance. The disadvantage is that in some cases (such as indeterminate functions), it can lead to inconsistent data in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf)).

② ROW Mode (RBR)

Instead of recording the context of each sql statement, you only need to record which piece of data has been modified and how it has been modified. And there is no problem that the calls and triggers of stored procedures, or function, or trigger, in certain cases can not be copied correctly. The disadvantage is that a large number of logs will be generated, especially in the case of alter table.

③ MIXED Mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed.

Binlog_row_image

This parameter controls the content of binary logging. There are three choices: full, minimal, and noblob. The default value is full.

Full: record all column values in "before" and "after" images

Minimal: in "before" and "after" images, only column values that are changed and uniquely identify data rows are recorded

Noblob: in the "before" and "after" images, all column values are recorded, except for BLOB and TEXT columns (if not changed).

Binlog_do_db

This parameter indicates that only the binary log of the specified database is recorded.

Binlog_ignore_db

This parameter indicates that the binary log of the specified database is not recorded.

Max_binlog_cache_size

This parameter represents the maximum size of memory used by binlog

Binlog_cache_size

This parameter represents the amount of memory used by binlog and can be tested through the state variables binlog_cache_use and binlog_cache_disk_use.

Binlog_cache_use: number of transactions that use binary log caching

Binlog_cache_disk_use: the number of transactions that use binary log caching but exceed the binlog_cache_ size value and use temporary files to save statements in the transaction

Max_binlog_size

The maximum, maximum and default value of Binlog is 1GB. This setting does not strictly control the size of Binlog, especially when Binlog is close to the maximum and encounters a larger transaction. In order to ensure the integrity of the transaction, it is impossible to switch logs, so all SQL of the transaction can only be recorded in the current log until the end of the transaction.

Sync_binlog

This parameter directly affects the performance and integrity of mysql.

Sync_binlog=0:

When a transaction commits, Mysql simply writes data from binlog_cache to the Binlog file, but does not execute disk synchronization instructions such as fsync to tell the file system to flush the cache to disk, leaving Filesystem to decide when to synchronize, which is the best performance.

Sync_binlog=n, after n transaction commits, Mysql will execute a disk synchronization instruction such as fsync, and the gay file system flushes the Binlog file cache to disk.

The default setting in Mysql is sync_binlog=0, that is, no mandatory disk refresh instructions are made, which is the best performance, but also the greatest risk. Once the system tightens the Crash, all Binlog information in the file system cache will be lost

1. Open binary log

Mysql > show variables like'% log_bin%'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | OFF |-- this parameter is used to set whether to enable binary logging |

| | log_bin_trust_function_creators | OFF |

| | sql_log_bin | ON |

+-+ +

[root@mysql ~] # service mysql stop

Shutting down MySQL.... [OK]

[root@mysql ~] # cp / etc/my.cnf / etc/my.cnf.bak

[root@mysql ~] # vi / etc/my.cnf

Description: add log_bin=/var/lib/mysql/binarylog/binlog to the / etc/my.cnf file

[root@mysql] # mkdir-p / var/lib/mysql/binarylog

[root@mysql] # chown-R mysql:mysql / var/lib/mysql/binarylog

[root@mysql mysql] # service mysql start

Starting MySQL. [OK]

Mysql > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / var/lib/mysql/binarylog/binlog |

| | log_bin_index | / var/lib/mysql/binarylog/binlog.index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

[root@mysql mysql] # ll / var/lib/mysql/binarylog/

Total 8

-rw-rw---- 1 mysql mysql 120 May 30 16:57 binlog.000001

-rw-rw---- 1 mysql mysql 39 May 30 16:57 binlog.index

two。 Toggle binary log

Mysql > flush logs

Query OK, 0 rows affected (0.05 sec)

[root@mysql mysql] # ll / var/lib/mysql/binarylog/

Total 12

-rw-rw---- 1 mysql mysql 164May 30 17:09 binlog.000001

-rw-rw---- 1 mysql mysql 120 May 30 17:09 binlog.000002

-rw-rw---- 1 mysql mysql 78 May 30 17:09 binlog.index

3. Check the number of binary log

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | binlog.000001 | 164|

| | binlog.000002 | 164|

4. View the binary log that is in use

Mysql > show master status

+-+

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

+-+

| | binlog.000010 | 120 | |

+-+

5. View binary log events

5.1

Mysql > show binlog events in 'binlog.000010'

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | binlog.000010 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |

| | binlog.000010 | 120 | Query | 1 | 219 | use `test`; create table andy (id int) |

+-- +

2 rows in set (0.00 sec)

5.2

Mysql > show binlog events in 'binlog.000010' from 120 limit 2

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | binlog.000010 | 120 | Query | 1 | 219 | use `test`; create table andy (id int) |

| | binlog.000010 | 219 | Query | 1 | 298 | BEGIN |

+-- +

2 rows in set (0.00 sec)

6. View binary logs with the mysqlbinlog tool

6.1

[root@mysql ~] # mysqlbinlog / var/lib/mysql/binarylog/binlog.000016

# at 199

# 170530 19:42:52 server id 1 end_log_pos 306 CRC32 0x64d982e4

Query thread_id= exec_time=0

Error_code=0

Use `test` / *! * /

SET timestamp 1496144572 /

Insert into name values ('pottery leaf')

/ *! * /

# at 306

# 170530 19:42:52 server id 1 end_log_pos 337 CRC32 0xf75d46d1

Xid = 36

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

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

Content parsing:

Location > the location in the file. "at 199" indicates that the starting point of" event "begins with 199 bytes;" end_log_pos 306 "indicates that it ends with 306 bytes, and the next event starts at the end of the previous event over and over again.

Timestamp > time stamp of the event: "170530 19:42:52"

Event execution time > event execution time: "exec_time=0"

Error code > error code: "error_code=0"

Server identity > server identity id: "server id 1"

6.2 use the mysqlbinlog tool to view the specified timestamp binlog

[root@mysql] # mysqlbinlog-- start-datetime= "2017-05-30 19:42:52" / var/lib/mysql/binarylog/binlog.000016

6.3 use the mysqlbinlog tool to view the binlog of the specified position

[root@mysql] # mysqlbinlog-- start-position=199-- stop-position=306 / var/lib/mysql/binarylog/binlog.000016

7. Delete binary log

7.1Auto delete, add expire_logs_days to my.cnf

Expire_logs_days = X # X is the specified number of days

7.2Manual deletion (os file is automatically deleted at the operating system level)

Mysql > reset master; / / Delete binlog of master

Mysql > reset slave; / / Delete the relay log of slave

Mysql > purge master logs before '2017-05-30 18 binlog 27displacement; / / Delete the log files in the log index before the specified date

Mysql > purge master logs to 'binlog.000011'; / / before deleting binlog.000011, does not include binlog.000011

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.