本篇文章介绍一下 mysql 的备份和日志, 由于备份时需要用到日志, 所以在讲备份前, 如果日志内容篇幅过长, 将会把日志和备份分开单独来讲, 先简单介绍一下 mysql 的日志相关内容.
MySQL 日志
日志是 mysql 数据库的重要组成部分. 日志文件中记录着 mysql 数据库运行期间发生的变化; 也就是说用来记录 mysql 数据库的客户端连接状况, SQL 语句的执行情况和错误信息等. 当数据库遭到意外的损坏时, 可以通过日志查看文件出错的原因, 并且可以通过日志文件进行数据恢复.
mysql 主要包含: 错误日志, 查询日志, 慢查询日志, 事务日志, 二进制日志, 中继日志; 比较重要的日志有错误日志, 慢查询日志, 二进制日志. 下面对这几种日志内容做简单介绍:
错误日志
错误日志是 mysql 的重要日志之一, 它记录了 mysql 启动和停止时, 以及服务器在运行过程中发生的任何严重错误时的相关信息. 当数据库出现任何故障导致无法正常使用时, 可以首先查看错误日志.
错误日志的定义在 my.cnf 中定义:
- [mysqld_safe]
- log-error=/var/log/mariadb/mariadb.log
如果没有在 my.cnf 中定义错误日志, 那么 mysqld 会使用 host_name.err(主机名命名), 并默认存放在 DATADIR 定义的目录下.
慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time(单位: 秒)设置的值, long_query_time 的默认时间为 10 秒, 最小为 0, 精度可以到微妙.
- MariaDB [(none)]> show variables like 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
- 1 row in set (0.00 sec)
慢查询日志默认没有开启: 查看慢查询是否启用:
- MariaDB [(none)]> show variables like 'slow%';
- +---------------------+---------------------------------+
- | Variable_name | Value |
- +---------------------+---------------------------------+
- | slow_launch_time | 2 |
- | slow_query_log | ON |
- | slow_query_log_file | /var/log/mariadb/mysql_slow.log |
- +---------------------+---------------------------------+
- 3 rows in set (0.00 sec)
启用慢查询
slow_query_log 是全局变量, 支持命令设置启用或关闭, 要注意的是, 在终端使用指令设置不能永久生效:
MariaDB [(none)]> set global slow_query_log=ON | OFF;
要永久生效需要在配置文件中进行定义:
- [mysqld]
- slow_query_log=on
重启服务后生效. 查看开启状态:
- MariaDB [(none)]> show variables like 'slow_query_log';
- +----------------+-------+
- | Variable_name | Value |
- +----------------+-------+
- | slow_query_log | ON |
- +----------------+-------+
- 1 row in set (0.00 sec)
同时, 开启慢查询后, 会默认在将日志写入 DATADIR 目录下, 命名为 host_name_slow.log; 当然, 可以使用 slow_query_log_file=/path/filename, 的方式来定义慢查询日志存放路径. 下面是自定义日志路径:
- [mysqld]
- slow_query_log=on
- slow_query_log_file=/var/log/mariadb/mysql_slow.log
重启服务查看 / var/log/mariadb/mysql_slow.log 是否生成
- systemctl restart mariadb
- [root@localhost mysql]# ls /var/log/mariadb/
- mariadb.log mariadb.log.rpmsave mysql_slow.log
设置 long_query_time 的阀值
上面提到了只要查询时间超过 long_query_log 设置的值才会被记录到慢查询日志中, 所以这个阀值要根据实际的应用环境来定.
设置 long_query_time 阀值:
- MariaDB [(none)]> set long_query_time=1;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> show variables like 'long_query_time';
- +-----------------+----------+
- | Variable_name | Value |
- +-----------------+----------+
- | long_query_time | 1.000000 |
- +-----------------+----------+
- 1 row in set (0.00 sec)
为了测试一下慢查询日志, 在上将查询时间设置成了一秒
- MariaDB [db4]> select sleep(2), id from tb1 where id = 55;
- +----------+------+
- | sleep(2) | id |
- +----------+------+
- | 0 | 55 |
- +----------+------+
- 1 row in set (3.90 sec) #查询时间 3.9 秒
- MariaDB [db4]> select id from tb1 where id = 55;
- +------+
- | id |
- +------+
- | 55 |
- +------+
- 1 row in set (0.00 sec)
查看慢查询日志
- # User@Host: root[root] @ localhost []
- # Thread_id: 5 Schema: db4 QC_hit: No
- # Query_time: 3.898127 Lock_time: 1.889486 Rows_sent: 1 Rows_examined: 49
- SET timestamp=1528775278;
- select sleep(2), id from tb1 where id = 55;
可以看到, 查询时间超过 1 秒的查询被记录在日志中, 最后的查询则没有被记录.
官方提供的 mysqldumpshow 慢查询分析工具可实现功能如下
统计不同慢 sql 的
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),
其他定义参数
log_slow_filter:
log_slow_filter 是慢查询的一个过滤机制, 在 log_slow_filter 所定义的操作如果执行时间超过慢查询定义的阀值, 操作将会被记录到慢查询日志中
log_queries_not_using_indexes
log_queries_not_using_indexes 是定义是否记录没有使用索引查询语句, 默认为 OFF
- MariaDB [db4]> show variables like 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | OFF |
- +-------------------------------+-------+
- 1 row in set (0.00 sec)
启用该参数:
- MariaDB [db4]> set global log_queries_not_using_indexes=on;
- Query OK, 0 rows affected (0.00 sec)
- log_slow_rate_limit = 1
log_slow_rate_limit 定义多少次查询才会记录到日志中
查看慢查询状态
- MariaDB [db4]> show variables like '%slow%';
- +---------------------+--------------------------------------------------------------------------------------------------------------+
- | Variable_name | Value |
- +---------------------+--------------------------------------------------------------------------------------------------------------+
- | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
- | log_slow_queries | ON |
- | log_slow_rate_limit | 1 |
- | log_slow_verbosity | |
- | slow_launch_time | 2 |
- | slow_query_log | ON |
- | slow_query_log_file | /var/log/mariadb/mysql_slow.log |
- +---------------------+--------------------------------------------------------------------------------------------------------------+
- 7 rows in set (0.00 sec)
二进制日志
二进制日志 (bin-log) 记录了所有 DDL(数据定义语句)和 DML(数据操纵语句), 对数据库结构, 内容产生改变的操作都被记录到二进制日志中.
日志的位置格式
在配置文件中定义 bin-log 选项即可启用二进制日志, mysqld 开始将数据变更情况写入日志文件, 如果没有定义日志文件名, 系统默认会用主机名后面跟'-bin', 如果指定了文件名称没有指定日志存放路径, 默认存放在 DATADIR 的目录下.
- [mysqld]
- log_bin=master-log
查看 bin-log 是否启用
- MariaDB [(none)]> show variables like '%log_bin';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_bin | ON |
- | sql_log_bin | ON |
- +---------------+-------+
- 2 rows in set (0.00 sec)
log_bin 与 sql_log_bin 要同时启用, sql_log_bin 默认开启, log_bin 默认为 OFF, 需要定义存放位置与名称.
bin-log 日志记录格式
binlog_format=STATEMENT|ROW|MIXED: 二进制日志记录的格式, 默认为 STATEMENT
Row :bin-log 会记录成[每一行数据被修改的形式] , 然后在 Slave 端再对相同的数据进行修改.
优点: 在 Row Level 模式下, Binnary Log 可以不记录执行的 Query 语句的上下文相关信息, 只要记录哪一行修改了, 修改成什么样子. Row Level 会详细的记录下每一行数据的修改细节, 而且不会出现某个特定情况下的存储过程, 或 Function, 以及 Trigger 的调用和触发无法被正确复制问题.
缺点: 所有的执行的语句当记录到日志中的时候, 都将以每行记录的修改来记录, 这样可能会产生大量的日志内容, 比如一条 update 语句, 修改多条记录, 则 binlog 中每一条修改都会有记录, 这样造成 binlog 日志量会很大, 特别是当执行 alter table 之类的语句的时候, 由于表结构修改, 每条记录都发生改变, 那么该表每一条记录都会记录到日志中.
Statment :[每一条会修改的 SQL 语句] 都会记录到 Master 的 Binnary 中. Slave 端在复制的时候, SQL 线程会解析成和原来 Master 端执行过相同的 SQL 语句, 并再次执行.
优点: 首先, 解决了 Row Level 下的缺点, 不须要记录每一行的数据变化, 减少了 Binnary Log 日志量, 节约了 IO 成本, 提高了性能.(相比 row 能节约多少性能与日志量, 这个取决于应用的 SQL 情况, 正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量, 但是考虑到如果带条件的 update 操作, 以及整表删除, alter 表等操作, ROW 格式会产生大量日志, 因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况, 其所产生的日志量会增加多少, 以及带来的 IO 性能问题.)
缺点: 由于它是记录的执行语句, 为了让这些语句在 Slave 端也能正确执行. 那么它还必须记录每条语句在执行时的一些相关信息, 即上下文信息, 以保证所有语句在 Slave 端被执行的时候能够得到和在 Master 端执行时相同的结果.
Mixed : 在 Mixed 模式下, 是以上两种 level 的混合使用.
Mixed 混合了 Statment 和 Row 两种日志, 默认情况下才用 Statment 格式来记录, 但是在一些特定的情况下使用 row 来记录更优更合理, mixed, 可以让系统自行判定该基于哪种方式进行, 这正是比较好的地方, 但是在主从环境下, 可能会产生主从之间的数据不一致.
Mysql 默认是使用 Statement 日志格式, 推荐使用 MIXED.
查看默认 bin-log 记录格式
- MariaDB [(none)]> show variables like 'binlog_format';
- +---------------+-----------+
- | Variable_name | Value |
- +---------------+-----------+
- | binlog_format | STATEMENT |
- +---------------+-----------+
- 1 row in set (0.00 sec)
二进制日志相关定义参数
在配置文件中定义好 log_bin=master-log 之后, 在数据库数据存放目录中会生成名为 master-log.000001 的文件, 该文件就是二进制日志文件, 而 master-log.index 文件是索引日志文件.
一些相关参数定义
sql_log_bin= ON | OFF: 是否记录二进制日志, 默认 ON
log_bin=/PATH/BIN_LOG_FILE: 指定文件位置; 默认 OFF, 表示不启用二进制日志功能, 上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED: 二进制日志记录的格式, 默认 STATEMENT
以上参数上面内容已经提到过
max_binlog_size=1073741824; 单个二进制日志文件的最大体积, 到达最大值会自动滚动, 默认为 1G
查看当前二进制日志文件的大小定义
- MariaDB [(none)]> show variables like 'max_binlog_size';
- +-----------------+------------+
- | Variable_name | Value |
- +-----------------+------------+
- | max_binlog_size | 1073741824 | #1G
- +-----------------+------------+
- 1 row in set (0.00 sec)
sync_binlog=1|0; 设定是否启动二进制日志即时同步磁盘功能, 默认 0, 由操
作系统负责同步日志到磁盘
sync_binlog 参数是比较重要的参数, 在 mysql 的复制中, 可以增加 slave 节点的复制可靠性. 在主从复制中应该启用为 1
- MariaDB [(none)]> set global sync_binlog=1;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> show variables like 'sync_binlog';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | sync_binlog | 1 |
- +---------------+-------+
- 1 row in set (0.00 sec)
expire_logs_days=N: 二进制日志可以自动删除的天数. 默认为 0, 即不自动
删除
- MariaDB [(none)]> show variables like 'expire_logs_days';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | expire_logs_days | 0 |
- +------------------+-------+
- 1 row in set (0.00 sec)
Mysql BInlog 日志格式可以通过 mysql 的 my.cnf 指定. 如以下:
- log_bin=master-bin #binlog 日志名
- binlog_format = MIXED # binlog 日志格式
- expire_logs_days = 7 #binlog 过期清理时间
- max_binlog_size 100m #binlog 每个日志文件大小
- sync_binlog=1 #启用 bin-log 同步功能
二进制日志读取 - mysqlbinlog
由于日志是以二进制方式存储的, 不能直接读取, 需要使用 mysqlbinlog 工具来查看.
shell> mysqlbinlog log-file
mysqlbinlog 指令常用选项:
-d dbname: 指定数据库名称
-o #: 忽略掉日志中的前 #个命令
-r : 将输出的文件格式日志输出到指定文件, 重定向
-s : 简单格式显示, 忽略掉一些信息
--set-charset=charname : 在输出文件时加上 set name charname , 装载数据时有用
- --start-datetime= # : 指定从开始时间显示内容
- --stop-datetime= # : 指定内容到结束的时间点
- --start-position= # : 指定开始的 pos 值位置
- --stop-position= # : 指定到结束的 pos 值位置
测试创建一张空白查看二进制日志记录的内容
- MariaDB [db5]> create table tb1 (
- -> id int,
- -> name varchar(20)
- -> ) charset=utf8;
- Query OK, 0 rows affected (0.33 sec)
查看 bin-log
- [root@localhost mysql]# mysqlbinlog master-bin.000006
- # at 245
- #180612 16:26:22 server id 1 end_log_pos 370 Query thread_id=4 exec_time=0 #该行记录了当前最新的 pos 值, 使用 show master status 查看: error_code=0
- use `db5`/*!*/;
- SET TIMESTAMP=1528791982/*!*/;
- SET @@session.pseudo_thread_id=4/*!*/;
- SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
- SET @@session.sql_mode=0/*!*/;
- SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
- /*!\C utf8 *//*!*/;
- SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
- SET @@session.lc_time_names=0/*!*/;
- SET @@session.collation_database=DEFAULT/*!*/;
- create table tb1 (
- id int,
- name varchar(20)
- ) charset=utf8
- /*!*/;
- DELIMITER ;
- # End of log file
- ROLLBACK /* added by mysqlbinlog */;
- /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
- MariaDB [db5]> show master status;
- +-------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +-------------------+----------+--------------+------------------+
- | master-bin.000006 | 370 | | |
- +-------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
也可以使用指定 pos 值的方式来查看
[ mailto:root@localhost mysql]# mysqlbinlog --start-position=245 --stop-position=370 master-bin.000006
二进制日志的删除
在繁忙的系统中, 每天都会生成大量的日志文件, 如果时间长不进行清理会对磁盘空间造成负担, 定义备份好日志文件并清理日志是很有必要的工作.
删除日志的方式
方法 1
执行 reset master; 指令
该命令将删除所有 bin-log 日志, 新的日志编号从 000001 开始.
方法 2
执行 purge master logs to 'master-bin.******' , 执行该命令将会删除指定该编号前的所有的 bin-log 日志文件.
- MariaDB [db5]> purge master logs to 'master-bin.000006';
- Query OK, 0 rows affected (0.81 sec)
只保留了 master-bin.000006 以后的日志
关于一些比较的重要的日志就说这么多, 后面再介绍 mysql 的备份内容.
来源: https://www.cnblogs.com/anay/p/9174510.html