本站以分享各种运维经验和运维所需要的技能为主
《python零基础入门》:python零基础入门学习
《python运维脚本》: python运维脚本实践
《shell》:shell学习
《terraform》持续更新中:terraform_Aws学习零基础入门到最佳实战
《k8》从问题中去学习k8s
《docker学习》暂未更新
《ceph学习》ceph日常问题解决分享
《日志收集》ELK+各种中间件
《运维日常》运维日常
《linux》运维面试100问
mysql日志
一、mysql错误日志
1.错误日志默认是关闭的 2.默认路径是 $datadir/,默认的名字是'主机名.err' 3.配置方式(一般场景所有配置) [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=1 basedir=/usr/local/mysql datadir=/usr/local/mysql/data port=3306 socket=/tmp/mysql.sock skip-name-resolve log_err=/usr/local/mysql/data/mysql.err [mysql] socket=/tmp/mysql.sock 4.查看方式 [root@db02 ~]# mysql -e "show variables like '%log_err%'" +---------------------+---------------------------------+ | Variable_name | Value | +---------------------+---------------------------------+ | log_error | /usr/local/mysql/data/mysql.err | +---------------------+---------------------------------+
二、一般查询日志
# 不建议开启,一旦开启,将会存储很多无用数据,也就是只是一个简单的查询,可能系统会自动查询很多数据,数据的操作也同样写入到文件中,容易导致文件过大。 1.查看方式 mysql> show variables like '%general%'; +------------------+--------------------------------+ | Variable_name | Value | +------------------+--------------------------------+ | general_log | OFF | | general_log_file | /usr/local/mysql/data/db02.log | +------------------+--------------------------------+ 2 rows in set (0.00 sec) 2.默认是关闭的 3.默认路径是 $datadir/,默认的名字是'主机名.log' 4.配置方式 [root@db02 ~]# vim /etc/my.cnf [mysqld] log_err=/usr/local/mysql/data/mysql.err general_log=on general_log_file=/usr/local/mysql/data/db02.log [mysql] socket=/tmp/mysql.sock
三、二进制日志
#注意: 1)binlog生成默认大小是120 2)binlog的大小也是binlog的当前位置点 1.查看方式 mysql> show variables like '%log_bin%'; 2.二进制日志默认是关闭的 3.配置binlog [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id #mysql5.7必须配置server_id log_bin=/usr/local/mysql/data/mysql-bin #mysql5.7只支持下划线 #log-bin=/usr/local/mysql/data/mysql-bin #mysql5.6都支持 4.二进制配置路径和名字由配置文件决定,一般保存在$datadir/ 以'mysql-bin.000001'命令
1.二进制日志管理操作
1)开启二进制日志
[root@db02 ~]# vim /etc/my.cnf [mysqld] server_id log_bin=/usr/local/mysql/data/mysql-bin
2)查看二进制日志
#物理查看 [root@db02 ~]# ll /usr/local/mysql/data/ -rw-rw---- 1 mysql mysql 120 Jul 21 19:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 39 Jul 21 19:24 mysql-bin.index #数据库查看 mysql> show variables like '%log_bin%';
3)事件
1.什么是事件 1)在binlog中最小的记录单元为event 2)一个事务会被拆分成多个事件(event) 2.事件(event)特性 1)每个event都有一个开始位置(start position)和结束位置(stop position)。 2)所谓的位置就是event对整个二进制的文件的相对位置。 3)对于一个二进制日志中,前120个position是文件格式信息预留空间。 4)MySQL第一个记录的事件,都是从120开始的。
4)刷新binlog
1)flush logs; 2)重启数据库时会刷新 3)二进制日志上限,默认1G(max_binlog_size)
5)删除binlog
1.根据存在时间删除日志 #临时生效 SET GLOBAL expire_logs_days = 7; #永久生效 [root@db01 data]# vim /etc/my.cnf [mysqld] expire_logs_days = 7 # 删除七天前的日志 2.使用purge命令删除 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; # 删除三天前的数据 3.根据文件名删除,将10之前的,也就是1-9都给删除 PURGE BINARY LOGS TO 'mysql-bin.000010'; 4.使用reset master # 这种比较危险,会直接清空所有的日志文件 mysql> reset master; # 直接回到00000.1
2.二进制日志作用
1.记录已提交的DML事务语句,并拆分为多个事件(event)来进行记录 2.记录所有DDL、DCL等语句,总之,二进制日志会记录所有对数据库发生修改的操作 3.如果我拥有数据库搭建开始所有的二进制日志,那么我可以把数据恢复到任意时刻 4.数据的备份与恢复 5.数据的复制
1)数据库的备份与恢复
1>添加数据
mysql> create database binlog; Query OK, 1 row affected (0.00 sec) mysql> use binlog Database changed mysql> create table binlog(id int); Query OK, 0 rows affected (0.02 sec) mysql> insert binlog values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert binlog values(4); Query OK, 1 row affected (0.00 sec) mysql> insert binlog values(5); Query OK, 1 row affected (0.00 sec)
2>删除数据
#误删除两条数据 mysql> delete from binlog where id=5; Query OK, 1 row affected (0.00 sec) mysql> delete from binlog where id=4; Query OK, 1 row affected (0.00 sec)
3>通过binlog恢复数据
#查看二进制日志找到位置点 [root@db02 data]# mysqlbinlog mysql-bin.000002 #将位置点之间的数据取出 [root@db02 data]# mysqlbinlog --start-position=224 --stop-position=457 mysql-bin.000001 > /tmp/hf.sql #将数据导入回去 [root@db02 data]# mysql < /tmp/45.sql
2)使用binlog配合数据库升级
# 下面方式对数据库升级只是其中一种方式,或者可以使用通过binlog方式做主从,从库同步数据,并且将代码中的数据库地址修改为新的数据库地址,这样就尽可能的保证数据完整性 1.准备一台新的数据库,版本为5.6.38 2.旧数据库备份数据 [root@db03 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B ku linux9 myisam qiudao qiudaodsb tmp world xiangqing >/tmp/full.sql 3.将备份的数据库传到新数据库 [root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/ 4.修改sql中的存储引擎 [root@db02 data]# sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql 5.将修改后的sql文件导入新数据 [root@db02 data]# mysql < /tmp/full.sql 6.将代码中的数据库地址修改为新的数据库地址 7.通过binlog将数据迁移过程中新生成的数据取出 [root@db03 data]# mysqlbinlog -uroot -p123 --start-position=120 --stop-position=465 mysql-bin.000014 > /tmp/bu.sql [root@db03 data]# scp /tmp/bu.sql 172.16.1.52:/tmp/ 8.将新数据导入新库 [root@db02 data]# mysql < /tmp/bu.sql
3.二进制日志工作模式
1)工作模式种类
1.statement 语句模式 2.row 行级模式 3.mixed 混合模式
2)查看工作模式
mysql> show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec)
3)STATEMENT语句模式
#语句模式,mysql5.6默认的模式 记录数据库中操作过得所有sql语句 #查看 [root@db03 data]# mysqlbinlog mysql-bin.000014 #优缺点: 1.易读 2.不安全 3.相对于行级模式占用磁盘空间小
4)row行级模式
#行级模式,mysql5.7默认的模式 记录的是数据的变化过程 #配置行级模式 [root@db03 data]# vim /etc/my.cnf binlog_format=row #查看方式 [root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000016 #优缺点: 1.安全 2.不易读 3.相对于语句模式占用磁盘大
5)mixed混合模式(极少使用)
4.二进制管理操作
1)开启二进制
[root@db03 ~]# vim /etc/my.cnf server_id=1 log_bin=/service/mysql/data/mysql-bin
2)查看二进制日志
#物理查看 [root@db03 data]# ll mysql-bin.* -rw-rw---- 1 mysql mysql 167 7月 14 18:22 mysql-bin.000001 -rw-rw---- 1 mysql mysql 2636 7月 14 19:07 mysql-bin.000002 #数据库查看 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 167 | | mysql-bin.000002 | 2636 | +------------------+-----------+ #查看binlog事件 mysql> show binlog events in 'mysql-bin.000016';
3)刷新
#flush logs; #重启数据库时会刷新 #达到二进制日志上限(max_binlog_size) mysql> show variables like '%max_binlog_size%'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+
4)删除binlog
1.根据存在时间删除日志 #临时生效 SET GLOBAL expire_logs_days = 7; #永久生效 [root@db01 data]# vim /etc/my.cnf [mysqld] expire_logs_days = 7 2.使用purge命令删除 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; 3.根据文件名删除 PURGE BINARY LOGS TO 'mysql-bin.000010'; 4.使用reset master重置binlog mysql> reset master;
5.思考
数据库或表被误删除的是很久之前创建的(一年前,100个binlog) 如果基于binlog全量恢复,成本很高 1.可以用备份恢复+短时间内二进制日志,恢复到故障之前 2.非官方方法,binlog2sql,binlog取反,类似于Oracle的flushback 3.延时从库 如果同一时间内和故障库无关的数据库都有操作,在截取binlog时都会被截取到 其他过滤方案? 1.-d 参数接库名
二、慢日志
1.作用
1.是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件 2.通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的
2.配置
[root@db01 ~]# vim /etc/my.cnf [mysqld] #指定是否开启慢查询日志 slow_query_log = 1 #指定慢日志文件存放位置(默认在data) slow_query_log_file=/service/mysql/data/slow.log #设定慢查询的阀值(默认10s) long_query_time=0.05 #不使用索引的慢查询日志是否记录到日志 log_queries_not_using_indexes #查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 min_examined_row_limit=100(鸡肋) slow_query_log = 1 slow_query_log_file=/service/mysql/data/slow.log long_query_time=3 log_queries_not_using_indexes
3.慢日志测试
#建表 mysql> create table solwlog2 select * from city; Query OK, 4079 rows affected (0.07 sec) Records: 4079 Duplicates: 0 Warnings: 0 #反复插入 mysql> insert solwlog select * from solwlog; Query OK, 2088448 rows affected (9.00 sec) Records: 2088448 Duplicates: 0 Warnings: 0 #查看慢日志 [root@db03 data]# less slow.log
4.使用mysqldumpslow命令来分析慢查询日志
#输出记录次数最多的10条SQL语句 mysqldumpslow -s c -t 10 /database/mysql/slow-log -s: 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙; -t: 是top n的意思,即为返回前面多少条的数据; -g: 后边可以写一个正则匹配模式,大小写不敏感的; #例子: #得到返回记录集最多的10个查询 mysqldumpslow -s r -t 10 /database/mysql/slow-log #得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log
5.扩展
第三方推荐(扩展): yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm 使用percona公司提供的pt-query-digest工具分析慢查询日志 [root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log