• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL系列之十一 日志记录

    系列教程

    MySQL系列之开篇 MySQL关系型数据库基础概念
    MySQL系列之一 MariaDB-server安装
    MySQL系列之二 多实例配置
    MySQL系列之三 基础篇
    MySQL系列之四 SQL语法
    MySQL系列之五 视图、存储函数、存储过程、触发器
    MySQL系列之六 用户与授权
    MySQL系列之七 MySQL存储引擎
    MySQL系列之八 MySQL服务器变量
    MySQL系列之九 mysql查询缓存及索引
    MySQL系列之十 MySQL事务隔离实现并发控制
    MySQL系列之十一 日志记录
    MySQL系列之十二 备份与恢复
    MySQL系列之十三 MySQL的复制
    MySQL系列之十四 MySQL的高可用实现
    MySQL系列之十五 MySQL常用配置和性能压力测试

    一、SQL命令历史

    ~/.mysql_history 记录了在mysql中执行的命令历史

    二、事务日志

    transaction log:事务型存储引擎自行管理和使用

    在一个事务提交后还没有存到磁盘的情况下会记录到事务日志,这个时候如果系统断电,再次开机后会自动将已提交的事务重做(redo log)写入磁盘;如果事务还没有提交的情况下断电重启后会执行撤销操作(undo log)

    相关变量

    MariaDB [school]> SHOW VARIABLES LIKE 'innodb_log%';
    +---------------------------+------------+
    | Variable_name             | Value      |
    +---------------------------+------------+
    | innodb_log_block_size     | 512        | -- 块大小
    | innodb_log_buffer_size    | 8388608    | -- 缓存大小
    | innodb_log_file_size      | 1073741824 | -- 每个日志文件大小
    | innodb_log_files_in_group | 3          | -- 日志组成员个数,也就是有几个文件
    | innodb_log_group_home_dir | ./         | -- 事务文件路径,相对于数据目录
    +---------------------------+------------+

    innodb_log_file_size默认大小为5M,innodb_log_files_in_group个数默认2个;强烈建议调大这俩个值;

    如果事务操作的数据大于事务日志文件总大小,在执行ROLLBACK时:可以撤销,但是数据文件会被占用,需要使用OPTIMIZE TABLE命令来释放数据空间;

    使用TRUNCATE删除表记录可以缩小占用空间,DELETE命令不会释放磁盘空间,如果需要释放需要使用OPTIMIZE命令从新整理碎片来释放空间。

    注意:修改innodb_log_file_size时需要先删除原来的日志文件

    三、错误日志

    错误日志中记录项目:

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

    相关变量

    MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error';  #错误日志文件的路径
    +---------------+------------------------------+
    | Variable_name | Value                        |
    +---------------+------------------------------+
    | log_error     | /var/log/mariadb/mariadb.log |
    +---------------+------------------------------+

    log_warnings=1|0 默认值1(是):是否记录警告信息至错误日志文件

    四、查询日志

    记录用户的操作日志,一般不建议启用

    MariaDB [school]> SHOW VARIABLES LIKE 'general_log%';
    +------------------+-------------+
    | Variable_name    | Value       |
    +------------------+-------------+
    | general_log      | OFF         | -- 开启关闭通用日志,默认关闭
    | general_log_file | centos7.log | -- 通用日志文件的文件名,/var/lib/mysql/HOSTNAME.log
    +------------------+-------------+
    MariaDB [school]> SHOW VARIABLES LIKE 'log_output';  
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_output    | FILE  | -- 以什么方式存放日志,(TABLE|FILE|NONE)
    +---------------+-------+

    mysql.general_log:表存放查询日志,前提是以表方式存放的

    五、慢查询日志

    记录执行查询时长超出指定时长的操作

    1、慢查询相关变量

    MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%';    
    +---------------------+------------------+
    | Variable_name       | Value            |
    +---------------------+------------------+
    | slow_query_log      | OFF              | -- 开启或关闭慢查询日志
    | slow_query_log_file | centos7-slow.log | -- 慢查询日志文件
    +---------------------+------------------+
    MariaDB [school]> SHOW VARIABLES LIKE 'long_query_time';
    +-----------------+-----------+
    | Variable_name   | Value     |
    +-----------------+-----------+
    | long_query_time | 10.000000 | -- 慢查询的阀值,单位秒,查询时间超过10则记录到慢查询日志
    +-----------------+-----------+
    MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | log_slow_rate_limit | 1     | -- 多少次查询才记录,mariadb特有
    | log_slow_verbosity  |       | -- 记录内容的详细程度(Query_plan,explain)
    +---------------------+-------+
    MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | log_queries_not_using_indexes | OFF   | -- 记录没有利用索引查询,而使用全文扫描的语句;默认关闭,建议开启
    +-------------------------------+-------+

    log_slow_filter :根据查询结果过滤

    2、补充:利用 profiling 跟踪查询语句所用的详细时间

    MariaDB [school]> SHOW VARIABLES LIKE 'profiling';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | profiling     | OFF   | -- 开启或关闭,默认关闭
    +---------------+-------+
    MariaDB [school]> SET profiling=ON;  #开启查询语句跟踪功能
    MariaDB [school]> SHOW profiles;  #查询语句执行的时间列表
    +----------+------------+------------------------------------------------------------+
    | Query_ID | Duration   | Query                                                      |
    +----------+------------+------------------------------------------------------------+
    |        1 | 0.00024497 | SELECT * FROM students                                     |
    |        2 | 0.00038528 | SELECT stuid,name,age FROM students WHERE stuid = 2        |
    +----------+------------+------------------------------------------------------------+
    MariaDB [school]> SHOW profile FOR query 2;  #查询指定编号的SQL语句的详细执行过程
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000035 |
    | Opening tables       | 0.000003 |
    | After opening tables | 0.000006 |
    | query end            | 0.000003 |
    | closing tables       | 0.000002 |
    | freeing items        | 0.000011 |
    | updating status      | 0.000006 |
    | cleaning up          | 0.000001 |
    +----------------------+----------+

    六、二进制日志

    记录已提交事务导致数据改变或潜在导致数据改变的SQL语句,通过“重放”日志文件中的事件来生成数据副本,不依赖存储引擎类型。

    开启二进制日志,默认是关闭的,二进制日志和数据分开存放

    开启记录二进制文件的功能:在my.cnf的[mysqld]下加入log_bin[=/path/somefile]

    默认二进制日志在数据库目录下,mariadb-bin.000001 为二进制日志数据文件,mariadb-bin.index 为二进制日志索引文件

    1、记录二进制日志的三种方式:

    MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';  
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | binlog_format | STATEMENT | -- 记录方式,系统默认基于语句方式
    +---------------+-----------+
    SET binlog_format='ROW|STATEMENT|MIXED'; -- 修改二进制日志记录方式

    2、二进制日志的相关变量

    MariaDB [(none)]> SHOW MASTER|BINARY LOGS; -- 查看mariadb自行管理使用中的二进制日志文件列表
    +--------------------+-----------+
    | Log_name           | File_size |
    +--------------------+-----------+
    | mariadb-bin.000002 |       290 |
    | mariadb-bin.000003 |       264 |
    | mariadb-bin.000004 |    529038 |
    | mariadb-bin.000005 |       245 |
    +--------------------+-----------+
    MariaDB [(none)]> SHOW MASTER STATUS; -- 查看使用中的二进制日志文件
    +--------------------+----------+--------------+------------------+
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+----------+--------------+------------------+
    | mariadb-bin.000005 |      245 |              |                  |
    +--------------------+----------+--------------+------------------+
    MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\G -- 查看二进制文件中的指定内容
    MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; -- 是否记录二进制日志,默认ON
    MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; -- 指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可
    MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
    +-----------------+------------+
    | Variable_name   | Value      |
    +-----------------+------------+
    | max_binlog_size | 1073741824 | -- 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G
    +-----------------+------------+
    MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';    
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | sync_binlog   | 0     | -- 设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
    +---------------+-------+
    MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 0     | -- 二进制日志可以自动删除的天数。 默认为0,即不自动删除
    +------------------+-------+

    3、mysqlbinlog命令

    [root@centos7 mysql]# mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v
    # at 528864
    #180611 20:59:46 server id 1  end_log_pos 528992        Query   thread_id=29    exec_time=0     error_code=0
    use `school`/*!*/;
    SET TIMESTAMP=1528721986/*!*/;
    INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M')  #改变数据的SQL语句
    /*!*/;
    
    事件发生的日期和时间:180611 20:59:46
    事件发生的服务器标识:server id 1
    事件的结束位置:end_log_pos 528992
    事件的类型:Query
    事件发生时所在服务器执行此事件的线程的ID:thread_id=29
    语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
    错误代码:error_code=0
    事件内容: 
    GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID

    使用mysqlbinlog mariadb-bin.000001 > file.sql命令重定向到SQL文件中,再使用mysql -uroot -p file.sql命令可以直接导入数据,实现了备份还原的功能

    4、二进制日志的管理

    MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002'; -- 删除到02,注意:02不删除
    MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; -- 删除2018-01-23之前的日志
    MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
    MariaDB [(none)]> RESET MASTER; -- 删除所有二进制日志,index文件重新记数
    MariaDB [(none)]> FLUSH LOGS; -- 手动触发日志滚动

    七、中继日志

    relay log :主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件

    总结

    本篇文章就到这里了,希望可以给你带来一些帮助,也希望您能够多多关注脚本之家的更多内容!

    您可能感兴趣的文章:
    • MySQL启用慢查询日志记录方法
    • mysql 开启慢查询 如何打开mysql的慢查询日志记录
    • MySQL日志设置及查看方法
    • MySQL日志文件详解
    上一篇:MySQL系列之四 SQL语法
    下一篇:MySQL系列之十 MySQL事务隔离实现并发控制
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL系列之十一 日志记录 MySQL,系列,之,十一,日志,