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

First, set the / etc/my.cnf parameter to open binary log

Log-bin=mysql-bin

Server-id=1

Second, about binary log parameters

Mysql > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / var/lib/mysql/mysql-bin |

| | log_bin_index | / var/lib/mysql/mysql-bin.index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.01 sec)

Mysql >

Third, check the current binary log

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 154 | |

+-+ +

1 row in set (0.00 sec)

Mysql >

Mysql >

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 154 | |

+-+ +

1 row in set (0.00 sec)

Mysql >

Fourth, binary log index

[root@node1 mysql] # more / var/lib/mysql/mysql-bin.index

. / mysql-bin.000001

[root@node1 mysql] #

Fifth, set the maximum log size

Mysql > show variables like'% max_binlog%'

+-+

| | Variable_name | Value |

+-+

| | max_binlog_cache_size | 18446744073709547520 | |

| | max_binlog_size | 1073741824 | |

| | max_binlog_stmt_cache_size | 18446744073709547520 | |

+-+

3 rows in set (0.00 sec)

Mysql >

Sixth, switch logs

Mysql > flush logs

Query OK, 0 rows affected (0.01 sec)

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 201 | |

| | mysql-bin.000002 | 201 | |

| | mysql-bin.000003 | 201 | |

| | mysql-bin.000004 | 154 | |

+-+ +

4 rows in set (0.00 sec)

Mysql >

Seventh, set up a new log, delete all logs, and generate a new log.

Mysql > reset master

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql >

Mysql >

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 154 | |

+-+ +

1 row in set (0.00 sec)

Mysql >

Eight, delete the log

Mysql > help purge

Name: 'PURGE BINARY LOGS'

Description:

Syntax:

PURGE {BINARY | MASTER} LOGS

{TO 'log_name' | BEFORE datetime_expr}

The binary log is a set of files that contain information about data

Modifications made by the MySQL server. The log consists of a set of

Binary log files, plus an index file (see

Http://dev.mysql.com/doc/refman/5.7/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed

In the log index file prior to the specified log file name or date.

BINARY and MASTER are synonyms. Deleted log files also are removed from

The list recorded in the index file, so that the given log file becomes

The first in the list.

This statement has no effect if the server was not started with the

-- log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010'

PURGE BINARY LOGS BEFORE '2008-04-02 22 purl 46purl 26'

Mysql >

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 201 | |

| | mysql-bin.000002 | 201 | |

| | mysql-bin.000003 | 201 | |

| | mysql-bin.000004 | 201 | |

| | mysql-bin.000005 | 154 | |

+-+ +

5 rows in set (0.00 sec)

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000003'

Query OK, 0 rows affected (0.01 sec)

Mysql >

Mysql >

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000003 | 201 | |

| | mysql-bin.000004 | 201 | |

| | mysql-bin.000005 | 154 | |

+-+ +

3 rows in set (0.01sec)

Mysql >

Nine, binlog_format sets statment,row,mixed

Mysql > show variables like'% format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

| | date_format |% Y-%m-%d |

| | datetime_format |% Y-%m-%d% H:%i:%s |

| | default_week_format | 0 | |

| | innodb_default_row_format | dynamic |

| | innodb_file_format | Barracuda |

| | innodb_file_format_check | ON |

| | innodb_file_format_max | Barracuda |

| | time_format |% H:%i:%s |

+-+ +

9 rows in set (0.01 sec)

Mysql >

Unable to read dml statement when set to row

Mysql > desc test1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | YES | MUL | NULL |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into test1 values (1)

Query OK, 1 row affected (0.05sec)

[root@node1 mysql] # mysqlbinlog mysql-bin.000005

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

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 160417 22:23:55 server id 1 end_log_pos 123 CRC32 0xf2f394d3 Start: binlog v 4, server v 5.7.11-log created 160417 22:23:55

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

BINLOG'

E5wTVw8BAAAAdwAAAHsAAAABAAQANS43LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AdOU8/I=

'/ *! * /

# at 123

# 160417 22:23:55 server id 1 end_log_pos 154 CRC32 0xc32a5dce Previous-GTIDs

# [empty]

# at 154

# 160417 22:35:04 server id 1 end_log_pos 219 CRC32 0x97a78d76 Anonymous_GTID last_committed=sequence_number=1

SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com

# at 219

# 160417 22:35:04 server id 1 end_log_pos 293 CRC32 0x907830ed Query thread_id=3 exec_time=0 error_code=0

SET timestamp 1460903704

SET @ @ session.pseudoclinic thread readership idler 3max session /

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

SET @ @ session.sqlhammer modewords 1436549152

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

/ *!\ C utf8 * /! * /

SET @ @ session.session. Session setting setting clientmakers 33 minutes session. Collationalization connections33 camera sessions session. Collationalization serverals 8 packs.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 293

# 160417 22:35:04 server id 1 end_log_pos 343 CRC32 0x06337336 Table_map: `testdb`.`test1` mapped to number 112

# at 343

# 160417 22:35:04 server id 1 end_log_pos 383 CRC32 0x8934c1da Write_rows: table id 112 flags: STMT_END_F

BINLOG'

GJ8TVxMBAAAAMgAAAFcBAAAAAHAAAAAAAAEABnRlc3RkYgAFdGVzdDEAAQMAATZzMwY=

GJ8TVx4BAAAAKAAAAH8BAAAAAHAAAAAAAAEAAgAB//4BAAAA2sE0iQ==

'/ *! * /

# at 383

# 160417 22:35:04 server id 1 end_log_pos 414 CRC32 0x2dd7f541 Xid = 41

Com _ MIT _ blank /

SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * /

DELIMITER

# End of log file

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

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

[root@node1 mysql] #

[root@node1 mysql] #

But the dcl statement can still be understood.

Mysql > use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > GRANT ALL PRIVILEGES ON *. * TO user1

ERROR 1133 (42000): Unknown error 1133

Mysql >

Mysql >

Mysql >

Mysql >

Mysql > create user test1

Query OK, 0 rows affected (0.00 sec)

Mysql >

Com _ MIT _ blank /

# at 414

# 160417 22:41:45 server id 1 end_log_pos 479 CRC32 0x01464b0a Anonymous_GTID last_committed=sequence_number=2

SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com

# at 479

# 160417 22:41:45 server id 1 end_log_pos 618 CRC32 0x450cd4fe Query thread_id=3 exec_time=0 error_code=0

Use `mysql` / *! * /

SET timestamp 1460904105

CREATE USER 'test1'@'%' IDENTIFIED WITH' mysql_native_password'

/ *! * /

SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * /

DELIMITER

# End of log file

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

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

[root@node1 mysql] #

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.