How to configure binlog

binlog There are two most important usage scenarios for logs
1）MySQL Master slave copy ：MySQL Replication stay Master End open binlog,Master Pass its binary log to slaves In order to achieve
master-slave Data consistent purpose .
2） Naturally, it's data recovery , By using mysqlbinlog Tools to recover data .

binlog The log includes two types of files
1） Binary log index file （ The file name suffix is .index） Used to record all binary files
2） Binary log file （ The file name suffix is .00000*） Record everything in the database DDL and DML( In addition to data query statements select) Statement event .

Two 、 Turn on binlog Log function
1） Edit open mysql The configuration file /etc/mys.cnf
[[email protected] ~]# vim /etc/my.cnf
stay [mysqld] Block add
log-bin=mysql-bin Make sure it's on (mysql-bin Is the base name or prefix name of the log )

** Be careful ：** Every time the server （ database ） restart , The server will call flush logs;, Create a new binlog journal ！

2） restart mysqld Service makes configuration effective
[[email protected] ~]# /etc/init.d/mysqld stop
[[email protected] ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

3） see binlog Whether the log is on
mysql> show variables like ‘log_%’;
±--------------------------------±--------------------+
| Variable_name | Value |
±--------------------------------±--------------------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
±--------------------------------±--------------------+
9 rows in set (0.00 sec)

3、 ... and 、 frequently-used binlog Log operation command
1） View all binlog Log list
mysql> show master logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4102 |
±-----------------±----------+
2 rows in set (0.00 sec)

2） see master state , In the end ( newest ) One binlog The number and name of the log , And the last operational event pos The end point (Position) value
mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000002 | 4102 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

3）flush Refresh log journal , Since then, a new number of binlog Log files
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

mysql> show master logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4145 |
| mysql-bin.000003 | 106 |
±-----------------±----------+
3 rows in set (0.00 sec)

Be careful ： whenever mysqld When the service is restarted , This command will be executed automatically , Refresh binlog journal ; stay mysqldump Add... When backing up data -F Options will also refresh binlog journal ;

4） Reset ( Empty ) all binlog journal
mysql> reset master;
Query OK, 0 rows affected (0.12 sec)

mysql> show master logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 106 |
±-----------------±----------+
1 row in set (0.00 sec)
Four 、 utilize binlog Log recovery mysql data

The following for ops Library member Table operation
mysql> use ops;
mysql> CREATE TABLE IF NOT EXISTS member (
-> id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> name varchar(16) NOT NULL,
-> sex enum(‘m’,‘w’) NOT NULL DEFAULT ‘m’,
-> age tinyint(3) unsigned NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
±--------------+
| Tables_in_ops |
±--------------+
| member |
±--------------+
1 row in set (0.00 sec)

mysql> desc member;
±--------±--------------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------±--------------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| sex | enum(‘m’,‘w’) | NO | | m | |
| age | tinyint(3) unsigned | NO | | NULL | |
| classid | char(6) | YES | | NULL | |
±--------±--------------------±-----±----±--------±---------------+
5 rows in set (0.00 sec)

Insert two pieces of data in advance
mysql> insert into member(name,sex,age,classid) values(‘wangshibo’,‘m’,27,‘cls1’),(‘guohuihui’,‘w’,27,‘cls2’);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from member;
±—±----------±----±----±--------+
| id | name | sex | age | classid |
±—±----------±----±----±--------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
±—±----------±----±----±--------+
2 rows in set (0.00 sec)