MySql运维篇---008：日志：错误日志、二进制日志、查询日志、慢查询日志， ...

1. 日志

1.1 错误日志

错误日志是 MySQL 中最紧张的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常利用时，发起起首查看此日志。
该日志是默认开启的，默认存放目次 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

1. #先登录mysql
2. mysql -uroot -p1234
4. #通过此系统变量查看日志文件的位置
5. show variables like '%log_error%';

复制代码

1. #通过tail指令查看文件尾部的50行日志
2. tail -n 50 /var/log/mysqld.log       

复制代码

1.2 二进制日志

1.2.1 先容

二进制日志（BINLOG）记录了全部的 DDL（数据定义语言：创建数据库…）语句和 DML（数据利用语言：增编削）语句，但不包罗数据查询（SELECT、SHOW）语句。
作用：

• ①. 灾难时的数据规复；
  
  
  
  • 由于二进制日志中记录了数据库、表、以及数据的变更。只需要把这里面的语句再次实行就可以规复数据了。
    
  
  
• ②. MySQL的主从复制。在MySQL8版本中
  
  
  
  • 主从复制底层原理就是基于二进制日志的，详细查看下一章。
    
  
  

mysql8.0版本默认二进制日志是开启着的，涉及到的参数如下：

1. #先登录mysql
2. mysql -uroot -p1234
4. #通过此系统变量来查看二进制日志相关的参数配置
5. show variables like '%log_bin%';

复制代码

参数说明：

• log_bin：on代表二进制日志是开着的。
  
• log_bin_basename：最终天生的二进制日志文件就在/var/lib/mysql目次下，文件名叫做binlog，但是日志文件有可能有许多，binlog只是它的前缀。
  
  
  
  • 当前数据库服务器的binlog日志的基础名称(前缀)，详细的binlog文件名需要再该basename的基础上加上编号(编号从000001开始往上自增)。
    
  • 第一个日志文件写满了大概日志的格式变更了之后，它会再次开启一个新的文件来写日志。
    
  
  
• log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。
  

测试：
进入到/var/lib/mysql目次查看二进制文件到底有没有·

1. #不登录mysql执行
2. cd /var/lib/mysql
4. #可以看到二进制日志文件和索引文件
5. ll
7. #查看索引文件：里面就记录了当前mysql数据库关联的日志文件有哪些
8. cat binlog.index

复制代码

1.2.2 格式

MySQL服务器中提供了多种格式来记录二进制日志，详细格式及特点如下：
日志格式寄义STATEMENT基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。ROW基于行的日志记录，记录的是每一行的数据变更。（默认）MIXED肴杂了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会主动切换为ROW进行记录。 举例：如果我们实行了一条update语句，这条update语句影响的行数是5行

• STATEMENT：记录的就是这条update语句
  
• ROW ：它记录的是update语句所影响的这五行，每一行的数据内容在变更之前怎么样，在变更之后是什么样。
  

1. #先登录mysql
2. mysql -uroot -p1234
4. #通过此系统变量，查看当前mysql的版本中默认的日志格式是那个
5. show variables like '%binlog_format%';

复制代码

如果我们需要配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。

1. vim /etc/my.cnf
3. #在这个文件中添加一行内容
4. binlog_format=STATEMENT
6. #重新启动mysql服务
7. systemctl restart mysqld.service
9. cd /var/lib/mysql
11. #可以看到此时重新生成了一个日志文件binlog.000005，原先是1~4。
12. #因为它的二进制日志格式改了，他不会再往原来的二进制日志文件写入了，而是写到一个新的日志文件中。
13. ll

复制代码

再次查看此体系变量，发现日志格式已经修改为STATEMENT

1. #先登录mysql
2. mysql -uroot -p1234
4. #通过此系统变量，查看当前mysql的版本中默认的日志格式是哪个
5. show variables like '%binlog_format%';

复制代码

1.2.3 查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，详细语法：

1. #logfilename：二进制文件名
2. mysqlbinlog [ 参数选项 ] logfilename
5. 参数选项：
7.         -d                 指定数据库名称，只列出指定的数据库相关操作。
8.         -o                 忽略掉日志中的前n行命令。
9.         -v                 将行事件(数据变更)重构为SQL语句
10.         -vv         将行事件(数据变更)重构为SQL语句，并输出注释信息

复制代码

测试：接下来呢我们就来设置一下这两种日志格式，来看一下它们之间的区别是什么样子的。
情况1：当前的日志格式是row
第一步：以db01数据库下的stu表为例进行演示。

1. 客户端1：就是登录进mysql执行的命令
3. mysql -uroot -p1234
5. #当前的日志格式是row
6. show variables like '%binlog_format%';
8. use db01;
10. #查看db01数据库下面有哪些表
11. show tables;
13. #查看stu表下面有哪些数据
14. select * from stu;

复制代码

第二步：实行更新语句

1. 客户端1：
3. update stu set age = age +1 where id =1;

复制代码

第三步：查看二进制日志表记录的是什么内容

1. 客户端2：就是没有登录进mysql执行的命令
3. cd /var/lib/mysql
5. ll
7. #因为二进制日志是第一个日志文件写满了之后会开启一个新的日志文件，所以只需要看最后一个日志文件即可。
8. #二进制文件不能直接查看使用cat显示的是乱码，需要通过mysqlbinlog 指令来查看
9. #日志里面是以行的格式显示的，所以看不到sql语句，我们还需要使用-v把它重构为sql语句才能看到
10. #效果：在日志的最后部分可以看到数据执行前后的变化
11. mysqlbinlog  -v binlog.000004;

复制代码

可以看出日志格式是row，记录的是记录的是每一行的数据变更，在变更之前怎么样，在变更之后是什么样。

情况2：当前的日志格式是STATEMENT
第一步：修改日志格式为STATEMENT，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可

1. vim /etc/my.cnf
3. #在这个文件中添加一行内容
4. binlog_format=STATEMENT
6. #重新启动mysql服务
7. systemctl restart mysqld.service
9. cd /var/lib/mysql
11. #可以看到此时重新生成了一个日志文件binlog.000005，原先是1~4。
12. #因为它的二进制日志格式改了，他不会再往原来的二进制日志文件写入了，而是写到一个新的日志文件中。
13. ll

复制代码

第二步：再次实行之前的更新语句

1. mysql -uroot -p1234
3. use db01;
5. update stu set age = age +1 where id =1;

复制代码

第三步：再次查看这个新天生的二进制日志表的内容

1. #进入到二进制日志文件存放的位置
2. cd /var/lib/mysql
4. #可以看到此目录下有这个日志文件
5. ll
7. #查看此二进制日志文件
8. #不需要加-v，因为是STATEMENT它本身记录的就是sql语句
9. #效果：可以看到此时记录的就是sql语句而不是每一行的数据变化
10. mysqlbinlog  binlog.000005;

复制代码

1.2.4 删除

对于比较繁忙的业务体系，每每天生的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：
指令寄义reset master 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始purge master logs to 'binlog.*****'删除 ***** 编号之前的全部日志purge master logs before 'yyyy-mm-dd hh24:mi:ss'删除日志为 “yyyy-mm-dd hh24:mi:ss” 时间点之前产生的全部日志 也可以在mysql的配置文件中配置二进制日志的逾期时间，设置了之后，二进制日志逾期会主动删除。

1. mysql -uroot -p1234
3. #查看系统变量，在mysql命令行中执行
4. #单位是秒，默认过期时间为30天，到期之后会自动删除
5. show variables like '%binlog_expire_logs_seconds%';

复制代码

测试：
客户端1：

1. mysql -uroot -p1234
3. #删除000002之前的日志文件,不包含000002
4. purge master logs to 'binlog.000002';

复制代码

客户端2：

1. cd /var/lib/mysql
3. #可以看到二进制日志文件和索引文件
4. ll

复制代码

1.3 查询日志

查询日志中记录了客户端的全部操纵语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。

1. mysql -uroot -p1234
3. #检查参数查看开关是否开启
4. #可以看到默认是关闭的以及日志文件所处位置和文件名
5. show variables like '%general%';

复制代码

如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

1. vim  /etc/my.cnf
3. #该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
4. general_log=1
6. #设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log
7. general_log_file=mysql_query.log
9. #重启mysql服务
10. systemctl restart mysqld.service
12. #查看这个目录下是否会生成此日志文件
13. cd /var/lib/mysql/
15. ll

复制代码

开启了查询日志之后，在MySQL的数据存放目次，也就是 /var/lib/mysql/ 目次下就会出现mysql_query.log 文件。之后全部的客户端的增编削查操纵都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。所以用不上此日志文件，我们可以把它关上。
测试：
客户端1：

2. mysql -uroot -p1234
4. use db01;
6. #执行查询操作（前提是已经登录）
7. select * from stu;
9. #执行更新操作
10. update stu set age=100 where id=7;

复制代码

客户端2：

1. cd /var/lib/mysql/
3. #前提是已经进入到了这个目录，并且目录下有这个文件(上面已经配置过了)
4. #实时刷新此日志文件尾部的内容（tail查看文件尾部，-f表示实时刷新）
5. tail -f mysql_query.log

复制代码

可以看到全部的DDL和DML操纵都会在日志表当中记录。

1.4 慢查询日志

慢查询日志记录了全部实行时间凌驾参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的全部的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。
解释：

• 慢查询日志记录了实行效率比较低，实行速度比较慢的sql语句。
  
• 之前在索引的sql性能分析中解说过。
  

如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

1. #慢查询日志：1代表开启
2. slow_query_log=1
4. #执行时间参数：表示执行时间超过2秒就是慢查询日志，此时慢查询日志文件就会记录这条sql.
5. long_query_time=2

复制代码

默认情况下，不会记录管理语句，也不会记录不利用索引进行查找的查询。可以利用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。
解释：

• 通过在vim /etc/my.cnf 配置文件中配置这2个参数，可以改变它的默认行为。
  
• 如果添加了log_slow_admin_statements =1：表示当我们实行比较慢的管理语句的时间，也会记录在慢查询日志当中。
  
• 如果添加了log_queries_not_using_indexes = 1：表示如果某一条sql语句，它没有利用索引而造成实行效率比较慢的话，也会记录在慢查询日志当中。
  
• 通过慢查询日志就可以定位出那些sql实行效率低，从而对这类的sql进行优化。
  

1. #记录执行较慢的管理语句
2. log_slow_admin_statements =1
4. #记录执行较慢的未使用索引的语句
5. log_queries_not_using_indexes = 1

复制代码

  上述全部的参数配置完成之后，都需要重新启动MySQL服务器才可以见效。
  测试：
客户端1：

1. mysql -uroot -p1234
3. #db01数据库下有tb_sku表，存放了1000万条记录
4. #电脑太卡，所以我没有创建tb_sku表,这里不在演示，只显示最终结果
5. use db01;
7. #不会记录
8. select * from tb_user limit 0,10; -- 这条SQL执行效率比较高, 执行耗时 0.01sec
10. #前面学习过SQL优化，分页查询越向后效率越低，此时超过2秒，会记录在慢查询日志中
11. select * from tb_user limit 1000000,10; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时4.71sec（秒）

复制代码

客户端2：

1. #配置慢查询日志
2. vim /etc/my.cnf
4. #配置的内容
5. slow_query_log=1
7. long_query_time=2
9. # 重启Mysql服务器
10. systemctl restart mysqld
12. # 进入到此目录，发现会有一个后缀是-slow.log的日志文件
13. cd /var/lib/mysql/
15. ll
17. #实时刷新文件尾部的位置发现：
18. #记录了什么时间哪一个用户在哪一个主机上执行了什么样的sql语句
19. tail -f mysql8-slow.log

复制代码

2.主从复制

2.1 概述

主从复制是指将主数据库的 DDL 和 DML 操纵通过二进制日志传到从库服务器中，然后在从库上对这些日志重新实行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的优点重要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。
  
• 实现读写分离，降低主库的访问压力。
  
  
  
  • 增编削操纵主库，查询操纵从库。
    
  
  
• 可以在从库中实行备份，以克制备份期间影响主库服务。
  
  
  
  • 数据备份的时间加上全局锁以防止备份的数据不完备，此时数据库处于只读状态，其它的客户端只能查询不能做增编削。
    
  • 有了主从复制后，可以在从库当中进行备份只需要锁从库就行，主库仍然可以进行增编削等操纵。从库加了全局锁后仍然可以查询，只不过在数据备份期间可能存在一定的数据延迟，由于在备份期间从库是不可以大概实行从主库同步过来的二进制日志的。
    
  • 办理：可以利用single-transaction参数代替加全局锁的方式进行备份，来包管数据的同等性备份------详情查看全局锁。
    
  
  

2.2 原理

MySQL主从复制的焦点就是 二进制日志，详细的过程如下：

从上图来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  
• 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
  
  
  
  • 从库中的IOthread线程：发起一个请求连接主数据库，然后读取主数据库中的 Binlog日志，读取完并返回从库之后，此线程会把Binlog日志写入到从库自身的一份日志（中继日志 Relay Log）中。
    
  
  
• slave从库重做中继日志中的事件，将改变反映它本身的数据。
  
  
  
  • 从库中的SQLthread线程：读取中继日志当中的数据，然后把中继日志当中所记录的数据变化在反映到自身数据库的数据变化，从而包管主从数据的同等。
    
  
  

举例：主库实行insert语句之后写入到二进制日志中，然后被IOthread线程读取过来之后写入到中继日志，那么SQLthread线程读取中继日志就会读取到这条insert语句，那么接下来在从库当中再去实行这条insert，此时就包管了主从数据的同等。
2.3 搭建

2.3.1 环境预备

预备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化预备(安装、 密码配置等操纵)工作（注意要关闭防火墙）。 其中：

• 192.168.10.200作为主服务器master
  
  
  
  • 主机名：master
    
  
  
• 192.168.10.201作为从服务器slave
  
  
  
  • 主机名：slave
    
    
    
    
  
  

注意事项：

• 起首配置的ip所在要和虚拟机配置的域名解析要在同一个网段，只有最后一个可以不同。
  
  
  
  
• 如果重启虚拟机后不显示ens33网卡，需要重启网络服务，固然启动服务时有可能会报错，需要关闭NetworkManger 服务。
  
  
  
  • ifconfig异常情况不显示ens33：
    
    
    
    
  • ifconfig正常情况显示ens33：
    
    
    
    
  
  

1. #重启网络服务，可能会报错
2. service network restart
4. #如果报错：可能是和 NetworkManager 服务有冲突
5. #NetworkManager 是一个为系统提供检测和配置功能以便自动连接到网络的程序。包含一个守护程序、一个命令行界面（nmcli）和一个基于 curses 的界面（nmtui）。
7. #解决：直接关闭 NetworkManger 服务就好了，并且禁止开机启动，之后在重启网络服务
8. #关闭NetworkManger 服务
9. service NetworkManager stop
11. #禁止开机启动
12. chkconfig NetworkManager off
14. #此时再次启动网络服务就会成功了
15. service network restart

复制代码

最后查看2台mysql服务器的运行状态：

1. systemctl status mysqld

复制代码

2.3.2 主库配置

1.修改配置文件  vim /etc/my.cnf

1. #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
2. server-id=1
4. #是否只读,1 代表只读, 0 代表读写
5. read-only=0
7. #以下2个不需要配置，表示创建的所有数据库都需要进行同步
8. #忽略的数据, 指不需要同步的数据库
9. #binlog-ignore-db=mysql
11. #指定同步的数据库
12. #binlog-do-db=db01

复制代码

2.重启MySQL服务器

1. #如果没有报错代表配置文件中的配置成功
2. systemctl restart mysqld

复制代码

3.登录mysql，创建远程连接的账号，并授予主从复制权限
解释

• ‘itcast’@‘%’：其中itcast是用户名，@'%代表这个用户可以在恣意主机上来访问当前服务器
  
• 密码是：Root@123456
  

1. #需要先登录mysql
2. mysql -uroot -p1234
4. #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
5. #作用：在从库当中连接主库时的账号和密码。
6. CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
9. #为 'itcast'@'%' 用户分配主从复制权限
10. GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

复制代码

4.通过指令，查看二进制日志坐标

1. show master status ;

复制代码

字段寄义说明：

• file : 从哪个日志文件开始推送日志文件（写到谁人日志文件了）
  
• position ： 从哪个位置开始推送日志
  
• binlog_ignore_db : 指定不需要同步的数据库
  

   

• 主库配置完后就不要在实行DML增编削以及DDL语句了。
  

  2.3.3 从库配置

1.修改配置文件 vim /etc/my.cnf

1. #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
2. server-id=2
5. #是否只读,1 代表只读, 0 代表读写
6. #从库只需要做查询操作不需要做修改操作，所以设置为1也可以。
7. #这个选项仅仅代表是普通用户只读，如果这个用户具有超级管理员super的权限，那么他也是可以进行读写的。
8. read-only=1
10. #如果想要禁用超级管理员的读写功能，让它也变为只有读的功能，可以设置以下参数
11. super-read-only=1

复制代码

2.重新启动MySQL服务

1. systemctl restart mysqld

复制代码

3.登录mysql，设置主库配置
现在主库和从库是没有关系的，并没有产生关联，所以接下来还要在从库中去设置主库的相关配置。

• SOURCE_HOST=‘192.168.200.200’：原主机所在是多少，也就是主库的ip
  
• SOURCE_USER=‘itcast’：连接这个ip所在对应的mysql，那么我的用户名是多少
  
• SOURCE_PASSWORD=‘Root@123456’：密码是多少
  
• SOURCE_LOG_FILE=‘binlog.000004’：从哪个二进制日志文件开始同步
  
• SOURCE_LOG_POS=663：表示从这份日志文件中的哪个位置开始同步。
  

1. mysql -uroot -p1234
3. CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.10.200', SOURCE_USER='itcast',SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000012',SOURCE_LOG_POS=663;

复制代码

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，实行如下SQL：

1. CHANGE MASTER TO MASTER_HOST='192.168.10.201', MASTER_USER='itcast',MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000012',MASTER_LOG_POS=663;

复制代码

2个版本的区别是参数名不同：当前利用的8.0.26版本兼容之前的语法，所以实行那一条都可以
参数名寄义8.0.23之前SOURCE_HOST主库IP所在MASTER_HOSTSOURCE_USER连接主库的用户名MASTER_USERSOURCE_PASSWORD连接主库的密码MASTER_PASSWORDSOURCE_LOG_FILEbinlog日志文件名MASTER_LOG_FILESOURCE_LOG_POSbinlog日志文件位置MASTER_LOG_POS 4.开启同步操纵

1. start replica ; #8.0.22之后
3. start slave ; #8.0.22之前

复制代码

5.查看主从同步状态

1. show replica status ; #8.0.22之后
2. #表中的数据比较大展示出来的效果比较混乱，可以加上\G把每一列数据转化为每一行显示。
3. show replica status\G;
5. show slave status ; #8.0.22之前

复制代码

效果：只需要以下2个选项为yes就代表主从复制正常，IO-Running代表那一组io线程运行是否正常，SQL-Running代表那一组sql线程运行是否正常。
错误情况：如果是克隆的虚拟机，mysql的uuid值是一样的，必须修改从库虚拟机的mysql服务器uuid的值，和主库不能一样

办理：

1. #不需要登录mysql
2. #修改此文件中的uuid值，随便修改一个字符
3. vim  /var/lib/mysql/auto.cnf
5. #重启mysql服务
6. systemctl restart mysqld

复制代码

再次查询主从同步状态，此时都为yes（注意在从库实行）

1. #登录进mysql后执行，可以开启多个会话窗口这样就不需要多次登陆了
2. show replica status\G;

复制代码

2.3.4 测试

先查询此时的数据库状态：

1. show databases;

复制代码

1.在主库 192.168.10.200 上创建数据库、表，并插入数据

1. create database db02;
3. use db02;
5. create table tb_user(
7.         id int(11) primary key not null auto_increment,
8.         name varchar(50) not null,
9.         sex varchar(1)
10.        
11. )engine=innodb default charset=utf8mb4;
13. insert into tb_user(id,name,sex) values(null,'Tom', '1'),(null,'Trigger','0'),(null,'Dawn','1');

复制代码

2.在从库 192.168.10.201 中查询数据，验证主从是否同步

1. show datables;
3. use db02;
5. show tables;
7. select * from tb_user;

复制代码

注意：

• 我们刚才所演示的这种主从复制，它是从二进制日志的当前位置今后进行主从复制的，如果我们要把之前的数据也需要同步到从库，那么这个时间我们可以先把主库的数据导出到一个sql脚本当中，然后在从库当中把sql脚本实行，先包管主库和从库的初始数据是同等的，然后再从当前位置今后再进行同步。
  

2.4 总结

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。