mysql binlog初步介绍

mysql
6 minutes read (About 840 words)

binlog 即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；
它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

mysql binlog解析

binlog有三种格式:

  • Statement 基于SQL语句的复制(statement-based replication,SBR)，
  • Row 基于行的复制(row-based replication,RBR)，
  • Mixed 混合模式复制(mixed-based replication,MBR)。

在我这边mysql 5.7.20版本中默认是使用Row的, 而且默认情况下没有开启binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mac环境下开启mysql的binlog

编辑my.cnf文件，对于我来说就是/usr/local/etc/my.cnf文件，在其中增加下面的内容：

1
2
3
log-bin = /Users/rollenholt/Downloads/mysql/binlog
binlog-format = ROW
server_id = 1

然后brew services restart mysql重启mysql，接下来我们就可以验证mysql的binlog已经开启了：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> show variables like 'log_bin'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       177 |
| binlog.000003 |       177 |
| binlog.000004 |       154 |
+---------------+-----------+
4 rows in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       177 |
| binlog.000003 |       177 |
| binlog.000004 |       154 |
+---------------+-----------+
4 rows in set (0.00 sec)

同时查看我们配置的log-bin属性对于的目录下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
~/Downloads/mysql » ls
binlog.000001 binlog.000002 binlog.000003 binlog.000004 binlog.index
------------------------------------------------------------
~/Downloads/mysql » cat binlog.index
/Users/rollenholt/Downloads/mysql/binlog.000001
/Users/rollenholt/Downloads/mysql/binlog.000002
/Users/rollenholt/Downloads/mysql/binlog.000003
/Users/rollenholt/Downloads/mysql/binlog.000004
------------------------------------------------------------
~/Downloads/mysql » cat binlog.000001
_binR �[w{5.7.20-logR �[8


**4SVѶR �[#��_]�hu �[����%                                                                        ------------------------------------------------------------
~/Downloads/mysql »

binlog相关的基本命令：

  • 查看是否开启binlog show variables like 'log_bin'
  • 获取binlog文件列表 show binary logs
  • 查看master上的binlog show master logs

  • 只查看第一个binlog文件的内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> show binlog events;
    +---------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +---------------+-----+----------------+-----------+-------------+---------------------------------------+
    | binlog.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
    | binlog.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
    | binlog.000001 | 154 | Stop           |         1 |         177 |                                       |
    +---------------+-----+----------------+-----------+-------------+---------------------------------------+
    3 rows in set (0.01 sec)

  • 查看指定binlog文件的内容 show binlog events in 'binlog.000001'

  • 删除binlog
    • 使用linux命令删除binlog文件
    • 设置binlog的过期时间 使用variable expire_logs_days
    • 手动删除binlog
      • reset master;//删除master的binlog
      • reset slave; //删除slave的中继日志
      • purge master logs before ‘2012-03-30 17:20:00’; //删除指定日期以前的日志索引中binlog日志文件
      • purge master logs to ‘mysql-bin.000002’; //删除指定日志文件的日志索引中binlog日志文件
1
2
3
4
5
6
7
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)
  • flush logs 刷新日志
    • 当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增；此外，如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）；日志被刷新时，新生成一个日志文件。

ERROR 1728 (HY000): Cannot load from mysql.procs_priv. The table is probably corrupted

mysql
2 minutes read (About 352 words)

今天在搞mysql binlog收集时，需要创建一个mysql用户，结果出现了：

ERROR 1728 (HY000): Cannot load from mysql.procs_priv. The table is probably corrupted异常

解决办法:

sudo mysql_upgrade -u root -p

注意后面的用户名和密码自己修改为自己的哈。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
~ » sudo mysql_upgrade -u root -p
Password:
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
sys.sys_config                                     OK
temp.hsc_biz_system_customer                       OK
temp.hsc_settlement_info                           OK
temp.hsc_settlement_invoice                        OK
temp.hsc_settlement_object                         OK
temp.invoice_config                                OK
test.worker_node                                   OK
test.a                                             OK
test.application                                   OK
test.application_acl                               OK
test.b                                             OK
test.big_table                                     OK
test.certificate_info_tab                          OK
test.company_tab                                   OK
test.connection_config                             OK
test.leader_election                               OK
test.test                                          OK
test.test1                                         OK
test.test_emoij                                    OK
test.user                                          OK
Upgrade process completed successfully.
Checking if update is needed.
------------------------------------------------------------

然后重启mysql以后，在尝试创建用户，发现完美解决。

mysql sleep

mysql
2 minutes read (About 343 words)

今天看到了一个sql：

1
select count(*), sleep(5) from test

第一次看到这个sleep函数，所以专门研究了一波。这个函数的语法是：SLEEP(duration), 其中duration的单位是秒

Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. The duration may have a fractional part. If the argument is NULL or negative, SLEEP() produces a warning, or an error in strict SQL mode.

When sleep returns normally (without interruption), it returns 0:

简单的说他可以让sql在执行的时候sleep一段时间。

1
2
3
4
5
6
mysql> SELECT SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           0 |
+-------------+

When SLEEP() is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error. This is true whether the query is killed or times out:

  • This statement is interrupted using KILL QUERY from another session:
1
2
3
4
5
6
mysql> SELECT SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           1 |
+-------------+
  • This statement is interrupted by timing out:
1
2
3
4
5
6
mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           1 |
+-------------+

这里解释一下上面的语法，那个语法是Mysql Optimizer Hints

When SLEEP() is only part of a query that is interrupted, the query returns an error:

  • This statement is interrupted using KILL QUERY from another session:
1
2
mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
ERROR 1317 (70100): Query execution was interrupted
  • This statement is interrupted by timing out:
1
2
3
mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 FROM t1 WHERE SLEEP(1000);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement
execution time exceeded

This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

参考资料

我的技术随笔

Tag Cloud

cache concurrent dubbo elasticsearch git java kafka linux mac mysql nginx python spark spring tomcat trace zookeeper 中间件 杂谈 系统设计

Categories

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×