MySQL架构和性能优化2

1.1 并发控制

1.1.1 锁机制

锁类型:

  • 读锁:共享锁,也称为 S 锁,只读不可写(包括当前事务) ,多个读互不阻塞
  • 写锁:独占锁,排它锁,也称为 X 锁,写锁会阻塞其它事务(不包括当前事务)的读和写
  • S 锁和 S 锁是兼容的,X 锁和其它锁都不兼容,举个例子,事务 T1 获取了一个行 r1 的 S 锁,另外事务 T2 可以立即获得行 r1 的 S 锁,此时 T1 和 T2 共同获得行 r1 的 S 锁,此种情况称为锁兼容,但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁,则必须等待 T1 对行 r1 锁的释放,此种情况也称为锁冲突

锁粒度:

  • 表级锁:MyISAM
  • 行级锁:InnoDB

实现

  • 存储引擎:自行实现其锁策略和锁粒度
  • 服务器级:实现了锁,表级锁,用户可显式请求

分类:

  • 隐式锁:由存储引擎自动施加锁
  • 显式锁:用户手动请求

锁策略:在锁粒度及数据安全性寻求的平衡机制

1.1.2 显式使用锁

帮助:https://mariadb.com/kb/en/lock-tables/

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias]
lock_type] ...
lock_type:
READ   #读锁
WRITE  #写锁

解锁

UNLOCK TABLES

关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

范例: 加读锁

mysql> lock tables students read ;
Query OK, 0 rows affected (0.00 sec)
mysql> update students set classid=2 where stuid=24;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be
updated
mysql> unlock tables ;
mysql> update students set classid=2 where stuid=24;
Query OK, 1 row affected (1 min 45.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0

范例: 同时在两个终端对同一行记录修改

#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

1.1.3 事务

事务 Transactions:一组原子性的 SQL语句,或一个独立工作单元
事务日志:记录事务信息,实现undo,redo等故障恢复功能

1.1.3.1 事务特性

ACID特性:

  • A:atomicity 原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
  • C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律
  • I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
  • D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中

Transaction 生命周期
image

1.1.3.2 管理事务

显式启动事务:

BEGIN
BEGIN WORK
START TRANSACTION

结束事务:

#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK

注意:只有事务型存储引擎中的DML语句方能支持此类操作
自动提交:

set autocommit={1|0}

默认为1,为0时设为非自动提交

建议:显式请求和提交事务,而不要使用"自动提交"功能

事务支持保存点:

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

查看事务

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁:
两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态

范例:找到未完成的导致阻塞的事务(支持Mariadb)

#在第一会话中执行
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> update students set classid=10;
#在第二个会话中执行
MariaDB [hellodb]> update students set classid=20;
#在第三个会话中执行
MariaDB [hellodb]> show engine innodb status;
...省略...
---TRANSACTION 120, ACTIVE 673 sec
2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27
MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhost
root
...省略...
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
| lock_id   | lock_trx_id | lock_mode | lock_type | lock_table           |
lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
| 123:9:3:2 | 123         | X         | RECORD    | `hellodb`.`students` |
PRIMARY    |          9 |         3 |        2 | 1         |
| 120:9:3:2 | 120         | X         | RECORD    | `hellodb`.`students` |
PRIMARY    |          9 |         3 |        2 | 1         |
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
2 rows in set (0.001 sec)
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 123               | 123:9:3:2         | 120             | 120:9:3:2        |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.000 sec)
#查看正在进行的事务
MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                   trx_id: 123
                 trx_state: LOCK WAIT
               trx_started: 2019-11-22 19:17:06
     trx_requested_lock_id: 123:9:3:2
         trx_wait_started: 2019-11-22 19:18:50
               trx_weight: 2
       trx_mysql_thread_id: 15 #线程ID
                 trx_query: update students set classid=20
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
         trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
   trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
         trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                   trx_id: 120
                 trx_state: RUNNING
               trx_started: 2019-11-22 19:08:51
     trx_requested_lock_id: NULL
         trx_wait_started: NULL
               trx_weight: 29
       trx_mysql_thread_id: 13                                    #线程ID
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
         trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 28
         trx_rows_modified: 27
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
   trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
         trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.000 sec)

MariaDB [hellodb]> show processlist;
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
| Id | User        | Host      | db      | Command | Time | State
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
|  1 | system user |           | NULL    | Daemon  | NULL | InnoDB purge
coordinator | NULL             |    0.000 |
|  3 | system user |           | NULL    | Daemon  | NULL | InnoDB purge worker
     | NULL             |    0.000 |
|  4 | system user |           | NULL    | Daemon  | NULL | InnoDB purge worker
     | NULL             |    0.000 |
|  2 | system user |           | NULL    | Daemon  | NULL | InnoDB purge worker
     | NULL             |    0.000 |
|  5 | system user |           | NULL    | Daemon  | NULL | InnoDB shutdown
handler  | NULL             |    0.000 |
| 11 | root        | localhost | hellodb | Query   |    0 | Init                
    | show processlist |    0.000 |
| 13 | root        | localhost | hellodb | Sleep   |   38 |                    
     | NULL             |    0.000 |
| 15 | root        | localhost | hellodb | Query   |   10 | Updating            
    | update students set classid=20 |    0.000
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
7 rows in set (0.000 sec)

#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec)

#查看事务锁的超时时长,默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.001 sec)

1.1.3.3 事务隔离级别

MySQL 支持四种隔离级别,事务隔离级别从上至下更加严格
image

  • READ UNCOMMITTED
    可读取到未提交数据,产生脏读
  • READ COMMITTED
    可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致
  • REPEATABLE READ
    可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置
  • SERIALIZABLE
    可串行化,未提交的读事务阻塞写事务(加读锁,但不阻塞读事务),或者未提交的写事务阻塞读和写事务(加写锁,其它事务的读,写都不可以执行)。会导致并发性能差

MVCC和事务的隔离级别:
MVCC(多版本并发控制机制)只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别:
服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定,默认为REPEATABLEREAD,可在GLOBAL和SESSION级进行设置

#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

服务器选项中指定

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

范例: MySQL8.0 事务隔离级别系统变量tx_isolation已取消

mysql> select @@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

1.2 日志管理

MySQL 支持丰富的日志类型,如下:

  • 事务日志:transaction log
    事务日志的写入类型为"追加",因此其操作为"顺序IO";通常也被称为:预写式日志 write ahead logging
    事务日志文件: ib_logfile0, ib_logfile1
  • 错误日志error log
  • 通用日志general log
  • 慢查询日志 slow query log
  • 二进制日志 binary log
  • 中继日志reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件

1.2.1 事务日志

事务日志:transaction log

  • redo log:记录某数据块被修改后的值,数据更新前先记录redo log( WALWrite Ahead Log ),可以用来恢复未写入data file的已成功事务更新的数据
  • undo log:保存与执行的操作相反的操作,即记录某数据被修改前的值,可以用来在事务失败时进行rollback

事务型存储引擎自行管理和使用,建议和数据文件分开存放

Innodb事务日志相关配置:

show variables like '%innodb_log%';
innodb_log_file_size   50331648 #每个日志文件大小
innodb_log_files_in_group 2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

image

1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践,是使用第三种折衷配置(=2):

1.配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,但毕竟只是内存的数据拷贝,速度很快
2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据

说明:

  • 设置为1,同时sync_binlog = 1表示最高级别的容错
  • innodb_use_global_flush_log_at_trx_commit=0 时,将不能用SET语句重置此变量( MariaDB10.2.6 后废弃)

1.2.2 错误日志

错误日志

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;

范例:

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)

记录哪些警告信息至错误日志文件

#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...         #MySQL5.7之前
log_error_verbosity=0|1|2|3...   #MySQL8.0

范例:

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+
1 row in set (0.001 sec)

范例: MySQL8.0变量变化

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 2     |
+---------------------+-------+
1 row in set (0.00 sec)

1.2.3 通用日志

通用日志:记录对数据库的通用操作,包括:错误的SQL语句

通用日志可以保存在:file(默认值)或 table(mysql.general_log表)

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

范例: 启用通用日志并记录至文件中

#默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

#启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

#默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file         |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)

范例:通用日志记录到表中

#修改通用日志,记录通用日志至mysql.general_log表中
MariaDB [mysql]> set global log_output="table";
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.002 sec)

#general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV
       Version: 10
     Row_format: Dynamic
           Rows: 1
Avg_row_length: 0
   Data_length: 0
Max_data_length: 0
   Index_length: 0
     Data_free: 0
Auto_increment: NULL
   Create_time: 2021-01-30 08:56:13
   Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: General log
1 row in set (0.01 sec)

#general_log表是CSV的文本文件
[root@centos8 ~]#file /var/lib/mysql/mysql/general_log.CSV
/var/lib/mysql/mysql/general_log.CSV: ASCII text

[root@centos8 ~]#head /var/lib/mysql/mysql/general_log.CSV
"2021-02-05 10:01:42.506955","root[root] @ localhost []",8,1,"Query","select
count(*) from general_log"
"2021-02-05 10:01:48.436410","root[root] @ localhost []",8,1,"Query","select *
from general_log"
"2021-02-05 10:01:57.619597","root[root] @ localhost []",8,1,"Query","select *
from students"
"2021-02-05 10:02:03.628124","root[root] @ localhost []",8,1,"Query","SELECT
DATABASE()"
"2021-02-05 10:02:03.628309","root[root] @ localhost []",8,1,"Init DB","hellodb"
"2021-02-05 10:02:03.629031","root[root] @ localhost []",8,1,"Query","show
databases"
"2021-02-05 10:02:03.629901","root[root] @ localhost []",8,1,"Query","show
tables"

MariaDB [mysql]> select * from mysql.general_log\G
...省略...
*************************** 6. row ***************************
 event_time: 2019-11-25 11:03:41.163896
   user_host: root[root] @ localhost []
   thread_id: 9
   server_id: 1
command_type: Query
   argument: xxx
*************************** 7. row ***************************
 event_time: 2019-11-25 11:03:44.549211
   user_host: root[root] @ localhost []
   thread_id: 8
   server_id: 1
command_type: Query
   argument: select * from general_log
7 rows in set (0.000 sec)

范例: 查找执行次数最多的前三条语句

MariaDB [mysql]> select argument,count(argument) num from mysql.general_log
group by argument order by num desc limit 3;
+---------------------------+-----------------+
| argument                  |             num |
+---------------------------+-----------------+
| select * from teachers    |               6 |
| select * from general_log |               4 |
| select * from students    |               3 |
+---------------------------+-----------------+
3 rows in set (0.002 sec)

范例:对访问的语句进行排序

[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' | awk
'{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr

[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' |sort |uniq -c|sort -nr

1.2.4 慢查询日志

慢查询日志:记录执行查询时长超出指定时长的操作

慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值,单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查询类型且查询时长超过long_query_time,则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语
句是否记录日志,默认OFF,即不记录
log_slow_rate_limit = 1 #多少次查询才记录,mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log,MariaDB 10.0/MySQL 5.6.1 版后已删除

范例: 慢查询分析工具mysqldumpslow

[root@centos8 ~]#mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
  --verbose   verbose
  --debug     debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t), 'at' is
default
               aa: average rows affected
               ae: aggregated rows examined
               al: average lock time
               ar: average rows sent
               at: average query time
                 a: rows affected
                 c: count
                 e: rows examined
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME     name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
  
  
[root@centos8 ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/centos8-slow.log
Reading mysql slow query log from /var/lib/mysql/centos8-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=2.0 (2),
Rows_examined=25.0 (25), Rows_affected=0.0 (0), root[root]@localhost
 select * from students where age=N
 
Count: 1  Time=4.00s (4s)  Lock=0.00s (0s)  Rows_sent=4.0 (4), Rows_examined=4.0
(4), Rows_affected=0.0 (0), root[root]@localhost
 select sleep(N) from teachers

1.2.5 二进制日志(备份)

  • 记录导致数据改变或潜在导致数据改变的SQL语句
  • 记录已提交的日志
  • 不依赖于存储引擎类型

事务日志和二进制区别

  • 事务日志在线,二进制离线
  • 事务日志记录事务执行的过程,包括提交和未提交,二进制日志记录只记提交的过程
  • 事务日志只支持innodb,二进制都支持MyiSAM和innoDB

功能:通过"重放"日志文件中的事件来生成数据副本

注意:建议二进制日志和数据文件分开存放

二进制日志记录三种格式

  • 基于"语句"记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
  • 基于"行"记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式
  • 混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上)

格式配置

MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)


#MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.07 sec)

二进制日志文件的构成

有两类文件
1.日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002
2.索引文件:mysql|mariadb-bin.index,文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量:

sql_log_bin=ON|OFF#是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项
log_bin=/PATH/BIN_LOG_FILE:#指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED:#二进制日志记录的格式,mariadb5.5默认STATEMENT
max_binlog_size=1073741824#单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G#说明:文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0#设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
expire_logs_days=N:#二进制日志可以自动删除的天数。 默认为0,即不自动删除

二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表,及大小

SHOW {BINARY | MASTER} LOGS

查看使用中的二进制日志文件

SHOW MASTER STATUS

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

范例:

MariaDB [hellodb]> SHOW BINLOG EVENTS;
+------------------+-----+-------------------+-----------+-------------+---------
---------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info  
                                        |
+------------------+-----+-------------------+-----------+-------------+---------
---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc       |         1 |         256 | Server
ver: 10.3.17-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000001 | 256 | Gtid_list         |         1 |         285 | []    
                                        |
| mysql-bin.000001 | 285 | Binlog_checkpoint |         1 |         328 | mysqlbin.000001                               |
| mysql-bin.000001 | 328 | Rotate            |         1 |         375 | mysqlbin.000002;pos=4                         |
+------------------+-----+-------------------+-----------+-------------+---------
---------------------------------------+
4 rows in set (0.000 sec)
MariaDB [hellodb]> SHOW BINLOG EVENTS in 'mysql-bin.000002' from 614 limit 2,3
\G
*************************** 1. row ***************************
  Log_name: mysql-bin.000002
       Pos: 782
 Event_type: Query
 Server_id: 1
End_log_pos: 1098
       Info: use `hellodb`; CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
       Pos: 1098
 Event_type: Gtid
 Server_id: 1
End_log_pos: 1140
       Info: GTID 0-1-4
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
       Pos: 1140
 Event_type: Query
 Server_id: 1
End_log_pos: 1256
       Info: use `hellodb`; /*!40000 ALTER TABLE `classes` DISABLE KEYS */
3 rows in set (0.000 sec)
MariaDB [hellodb]> SHOW BINLOG EVENTS in 'mysql-bin.000002' from 614 limit 2,3\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
       Pos: 782
 Event_type: Query
 Server_id: 1
End_log_pos: 1098
       Info: use `hellodb`; CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
       Pos: 1098
 Event_type: Gtid
 Server_id: 1
End_log_pos: 1140
       Info: GTID 0-1-4
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
       Pos: 1140
 Event_type: Query
 Server_id: 1
End_log_pos: 1256
       Info: use `hellodb`; /*!40000 ALTER TABLE `classes` DISABLE KEYS */
3 rows in set (0.000 sec)

mysqlbinlog:二进制日志的客户端命令工具,支持离线查看二进制日志
命令格式:

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式:YYYY-MM-DD hh:mm:ss
 --stop-datetime=
 --base64-output[=name]
 -v -vvv

范例:

mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v
mysqlbinlog  --start-datetime="2018-01-30 20:30:10"   --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

范例: 同步远程主机的二进制日志

#从10.0.0.8远程主机实时同步从指定的二进制日志binlog.000002开始向后进行同步到当前目录
#先在10.0.0.8的MySQL创建用户并授权
[root@centos8 ~]#mysql -uroot -p123456
mysql> create user test@'10.0.0.%' identified by '123456';
mysql> grant REPLICATION SLAVE on *.* to test@'10.0.0.%' ;

#安装和MySQL服务器相同版本的客户端
[root@centos8 ~]#yum -y install mysql
#前台执行,自动同步二进制日志
[root@centos8 data]#mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456 --raw --stop-never binlog.000002

二进制日志事件的格式:

# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1    
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间:151105 16:31:40
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 431
事件的类型:Query
事件发生时所在服务器执行此事件的线程的ID:thread_id=1
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
错误代码:error_code=0
事件内容:
GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

范例:

PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志,index文件重新记数

RESET MASTER [TO #]; #删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB 10.1.6开始支持TO #

切换日志文件:

FLUSH LOGS;

范例:

[root@master ~]#file /var/lib/mysql/mariadb-bin.000001
/var/lib/mysql/mariadb-bin.000001: MySQL replication log, server id 18 MySQL 5+, server version 10.3.17-MariaDB-log

范例: 切换二进制日志

[root@centos8 ~]#mysqladmin   flush-binary-log
[root@centos8 ~]#mysqladmin   flush-logs
[root@centos8 ~]#mysql
MariaDB [hellodb]> flush logs;
posted @   Shine、  阅读(11)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示