MySQL运维篇（日志、主从复制）

日志

四种日志需要关注：错误日志、 二进制日志、查询日志、慢查询日志

错误日志

它记录了当mysqld启动和停止时 ，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议先查看此日志。

该日志默认开启，默认存放于/var/log/，默认名为mysqld.log。

#查看日志位置

show variables like '%log_error%'

二进制日志

二进制日志（BINLOG）记录了所有的DDL语句（库表结构的操作）和DML语句（增、删、改），但不包括select、show语句。

作用：灾难时的数据恢复；MySQL的主从复制；（MySQL8版本二进制日志默认是开启的）

#查看日志位置

show variables '%log_bin%'

文件名形如 binlog.000001、binglog.0000002 ...    

每个文件大小有限，一个用完就会自增

binlog.index文件中记录了上述所有关联的二进制日志文件。

MySQL服务器提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式	含义
STATEMENT	记录的是SQL语句，对数据进行修改的SQL都会被记录
ROW	记录的是每一行的数据变更（默认）
MIXED	混合了上面两种模式，默认采用STATEMENT，某些特殊情况自动切换为ROW

#查看日志记录格式

show variables like '%binlog_format%'

如何查看二进制日志呢？ 

#删除全部binlog日志，删除后日志将重新编号

reset master

#删除******编号之前的所有日志（不包括自己）

purge master logs to 'binlog.******'

 也可以在mysql配置文件中配置过期时间设置之后，二进制日志过期会自动删除

#查看过期时间（单位s）

show variables like '%binlog_expire_logs_seconds%';

查询日志 

记录所有的增删改查语句（包括库表级别），默认情况日志没有开启

 #查询是否开启查询日志

show variables like '%general%'

 如何开启？修改MySQL配置文件，添加如下内容：

#该选项用来开启查询日志，可选值：0或者1；0关闭，1开启

general_log=1

#设置日志文件名，如果不指定，默认为host_name.log

heneral_log_file = my_query.log

 慢查询日志

记录sql语句执行时间超过预设时间（默认10s）的sql语句 ，并且默认情况下不会记录管理语句和未使用索引的查询语句。

配置：

#开启慢查询日志

slow_query_log=1

#设置时间阈值

long_query_time=2 

#记录执行较慢的管理语句

log_slow_admin_statements=1

#记录执行较慢的未使用索引的查询语句

log_queries_not_using_indexes=1

---

主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中然后在从库上对这些日志重新执行（也叫重做）从而使得从库和主库的数据保持同步 。（可以是链状）

优点

1. 主库出现问题，可以快速切换到从库提供服务。

2. 实现读写分离，缓解主库的访问压力。

3. 可以在从库中执行备份，避免备份期间影响主库服务。

原理

 

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

1. master主库在事务提交时会把数据变更记录在二进制日志文件binlog中

2. 从库的IO线程读取主库的二进制日志文件BinLog并写入到从库的中继日志Relay Log

3. 从库的SQL线程读取中继日志并改变它自己的数据  

搭建

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

 2. 重启MySQL服务

 systemctl restart mysqld

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

#创建Jack用户，并设置密码，该用户可在任意主机连接改mysql服务

create user 'Jack'@'%' identified with mysql_native_password by 'Root@123456';

#为'Jack'@'%'用户分配主从复制权限

grant replication slave on *.* to 'Jack'@'%';

接下来配置从库 

 1. 修改配置文件

 注意这里的只读对超级管理员没有限制，想要让超级管理员也是只读权限需要配置：

super-read-only=1

 2. 重启mysql服务

3. 登录mysql，配置关联的主库

mysql8.0.23语法：

 change replication source to source_host='192.168.174.134', source_user='Jack', source_password='Root@123456', source_log_file='binlog.000003', source_log_pos=659;

 其中最后两项需要在主机通过命令  show master status;  查看，我的信息如图：

8.0.23之前：

8.0.23	含义	8.0.23之前
SOURCE_HOST	主库ip	MASTER_HOST
SOURCE_USER	连接主库的用户名	MASTER_USER
SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
SOURCE_LOG_FILE	binlog日志文件名	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日志文件位置	MASTER_LOG_POS

4. 开启同步

start replica   #8.0.22之后

start slave     #8.0.22之前

查看同步状态 

show replica status\G   #8.0.22之后

show slave status\G    #8.0.22之前

 主从复制只要Replica_IO_Running和Replica_SQL_Running为yes就正常