mysql之日志、主从复制

miloyang
0 评论
/ /
759 阅读
/
13581 字
07 2023-10

作为开发，可能很少涉及到运维相关知识的学习，一般都是有运维同学进行部署，但既然要整理mysql全系列，那就一并整理了吧。

日志

在mysql中，主要涉及到的是四个日志，即错误日志、二进制日志、查询日志以及慢查询日志。

错误日志

我们在开发中，遇到问题，都是先看日志。在mysql中也不例外，一旦mysql出现任何故障，建议先查看错误日志。它记录了当前mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

错误日志正常来说都是默认开启的，默认存放路径为 /var/log，默认的日志文件名为 mysqld.log。如果不确定在哪，可通过变量来模糊查询，如: 

mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.00 sec)

出现问题后，直接来该目录下查看[ERROR]标签

binlog 二进制日志

binlog记录了所有的DDL(数据定义语言)语句和DML(数据操作语言)语句，但不包含查询语句(select,show)，也就是包含了全部的数据、表的变更记录。

它的作用，主要是：

  • 灾难时的数据恢复。
  • mysql的主从复制。
  • 其他，如有用到过canal做数据变化的监听从而同步数据到es中。

在mysql8中，默认binlog日志是开启的。可通过如下查看： 

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               | # 是否开启
| log_bin_basename                | /www/server/data/mysql-bin       | # binlog存放的路径，文件名mysql-bin开头，会有很多序号递增文件
| log_bin_index                   | /www/server/data/mysql-bin.index | # 记录了当前数据库的索引信息
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

开启binlog： etc/my.cnf下配置如下，然后重启mysql

[mysqld]
log-bin=mysql-bin            # 启用二进制日志，指定日志文件的前缀
server-id=1                  # 为 MySQL 服务器分配唯一的 ID，每个 MySQL 实例应该有不同的 server-id

mysql服务器中提供了多种格式来记录二进制日志，具体格式和特点如下： 假设执行了一个update语句，影响的行数是5行，则：

日志格式 含义 举例
statement 基于sql语句的日志记录，记录的是sql语句，对数据进行修改的sql都会记录在日志文件中 记录的是update的语句
row 基于行的日志记录，记录的是每一行的数据变更 记录影响的五行数据在变更前以及变更后的具体数据
mixed 混合以上两种格式，默认采用statement，特殊情况下会自动切换为row进行记录

可通过如下查看：

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

设置格式

有两种方式，一种永久的，一种session的。

  • 永久的：在etc/my.cnf中设置/增加:binlog_format=STATEMENT 或者是 ROW/MIXD
  • session: set binlog_format='ROW';

如果通过修改配置文件，则会触发生成新的binlog文件，如果是session则不会

立个flag，出一篇讲解canal的文章

来吧，例子

首先介绍下二进制的日志查看工具：
由于日志是二进制的方式存储，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看，如： 

mysqlbinlog [参数选项] logfilename 

参数选项:
    -d  指定数据库名称，只列出指定的数据相关操作 
    -o  忽略掉日志中的前n行命令
    -v  将行事件重构为sql语句
    -vv 将行事件重构为sql语句，并输出注释信息

ROW格式

我们先执行一条sql，更新下数据。 update tb_user set age=age+1 where id=1;

然后再通过mysqlbinlog工具查看 mysqlbinlog -v -d study mysql-bin.000010

### UPDATE `study`.`tb_user`
### WHERE
###   @1=1
###   @2='ab'
###   @3='226888272@qq.com'
###   @4='+8613884895573'
###   @5=1
###   @6='pwd783012301'
###   @7=13
###   @8=2
###   @9=431028199051500240
###   @10='2023-09-18 17:50:36'
###   @11=1696649138
### SET
###   @1=1
###   @2='ab'
###   @3='226888272@qq.com'
###   @4='+8613884895573'
###   @5=1
###   @6='pwd783012301'
###   @7=14
###   @8=2
###   @9=431028199051500240
###   @10='2023-09-18 17:50:36'
###   @11=1696649760
# at 1617412

其中，where就是条件，也就是更新之前的数据，set是更新之后的数据。 比如第7个字段是年龄，变更前是13，变更后就是14了

STATEMENT格式

修改完格式后，继续执行sql语句 update tb_user set age=age-1 where id=1; 然后再通过mysqlbinlog工具查看mysqlbinlog -v -d study mysql-bin.000010 看最后

SET TIMESTAMP=1696650403/*!*/;
update tb_user set age=age-1 where id=1  #  来来来，这里这里 只是记录了具体的sql语句
/*!*/;
# at 1618288
#231007 11:46:43 server id 1  end_log_pos 1618319 CRC32 0x7c9b83db      Xid = 22982
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

  • reset master

    删除全部binlog日志，删除之后，日志编号将从binlog.0000001重新开始

  • purge master log to 'binlog.***'

    删除 ******编号之前所有的日志

  • purge master logs before 'yyyy-mm-dd hh24:mi:ss'

    删除日志为'yyyy-mm-dd hh24:mi:ss'之前产生的所有日志

当然， mysql肯定不指望手动删除咯，可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
参数为：binlog_expire_logs_seconds

show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的，因为大部分业务都是查询，则涉及到查询语句比较多。如果需要开启查询日志，可以设置一下配置：
同样是在中增加my.cnf general_log=1 general_log_file=....

mysql> show variables like '%general%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | OFF                                |
| general_log_file | /www/server/data/VM-4-9-centos.log |
+------------------+------------------------------------+
2 rows in set (0.00 sec)

慢查询日志

我们之前也是有总结过慢查询日志的，在sql优化那一章节。此次，再次回顾下吧。
慢查询日志记录了所有执行时间超过参数 long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有sql语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以为微秒。

参数：

#慢查询日志
slow_query_log=1

#执行时间参数
long_query_time=2

查询十万条数据 select * from tb_user LIMIT 100000; 在mysql-slow.log下面就会有慢查询的记录了.

Time                 Id Command    Argument
# Time: 2023-09-22T14:27:12.520739Z
# User@Host: milo[milo] @  [119.123.32.87]  Id:     2
# Query_time: 25.903382  Lock_time: 0.005001 Rows_sent: 99606  Rows_examined: 99606
use study;
SET timestamp=1695392832;
select * from tb_user LIMIT 100000;

默认情况下，不会记录管理语句(show/use/create等等)，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes。如：

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

#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

主从复制

概述

主从复制，就是将主库(master)的DDL和DML操作通过binlog日志传到对应的从数据库(slave)中，然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

mysql支持一台主库同时向多台从库进行复制，从库也可以作为其他服务器的主库，实现链状复制。

zhucongfuzhijiagoutu

主从复制主要有以下几个作用：

  • 主库出现问题，可以快速切换到从库提供服务，因为主从数据是一致的。
  • 实现读写分离，主库可以做增、删、改操作，从库可以分流查操作，降低主库的访问压力。
  • 在备份的时候会触发全局锁，此时可以在从库中进行数据备份，以避免备份期间影响主库服务。但从库在备份的时候，同步数据可能会出现延迟。

原理

直接上图吧
zhucongfuzhideyuanli

  • 主库当中一旦发生增、删、改以及发生DDL语句的时候，会把所有的数据变更写入到binlog日志里面
  • 从库中有两个线程，其中IOthread会读取master中的binlog日志，当读取到binlog日志后，iothread会继续把日志写入到slave自身的日志 relay log(中继日志)。
  • 从库中的另外一个线程SQLthread，会读取自身的relay log(中继日志)的数据，把中继日志中的数据变化，反应到当前slave数据库中的数据变化(重做)。从而保证了主从同步。

搭建

准备工作

我本身有一台服务器，就是搭建这个博客的。现在在这个服务器上搭建一个docker容器作为slave。不影响搭建。
有个声音，为什么不用另外一个服务器作为slave？答：还不是因为穷。。。

1：获取镜像：docker pull mysql:5.7
2：启动镜像并挂载数据卷：docker run -d -p 33061:3306 -v /home/mysql01:/etc/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql01 mysql:5.7

则登录进来后为全新的：

[root@VM-4-9-centos mysql01]# docker exec -it 02c6d435dd41 /bin/bash
bash-4.2# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

主库配置

  • 修改配置文件 /etc/my.cnf

    [mysql]
#mysql服务id，保证整个集群环境中唯一，取值范围：1-2^32-1，默认为1
server-id=1
#是否只读，1表示只读，0表示读写
read-only=0
#忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

    配置完后重启 systemctl restart mysqld

  • 创建远程远程连接的账号，并授予主从复制的权限，后面从库就使用该账号。 

    #创建milo01用户，设置密码
CREATE USER 'milo01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

#为milo01用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'milo01'@'%';

  • 查看二进制日志坐标 

    mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    file：从哪个日志文件开始推送日志文件
    position：从哪个位置开始推送日志
    binlog_ignore_db：指定不需要同步的数据库

从库配置

  • 修改配置文件

    [mysql]
#mysql服务id，保证整个集群环境中唯一，取值范围：1-2^32-1，默认为1
server-id=2
#是否只读，1表示只读，仅仅是普通用户，但是超级管理员是可以读写的，除非设置super-read-only=1
read-only=1

    修改完后重启 docker restart fb53f6fa1b77

  • 在从库中设置相关配置

    版本高于8.0.23执行：
    CHANGE REPLICATION SOURCE TO SOURCE_HOST='XXX.XXX.XXX.XXX',SOURCE_USER='XXX',SOURCE_PASSWORD='XXX',SOURCE_LOG_FILE='XXX',SOURCE_LOG_POS=XXX;

    CHANGE REPLICATION SOURCE TO SOURCE_HOST='124.223.47.250',SOURCE_USER='milo01',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000002',SOURCE_LOG_POS=599;

    版本低于8.0.23执行：
    CHANGE MASTER TO MASTER_HOST='主库IP',MASTER_USER='主库用户名',MASTER_PASSWORD="主库密码",MASTER_LOG_FILE='binlog文件名',MASTER_LOG_POS=binlog文件位置;

    参数名 含义
    SOURCE_HOST 主库ip地址
    SOURCE_USER 链接主库的用户名
    SOURCE_PASSWORD 链接主库的密码
    SOURCE_LOG_FILE binlog日志文件名
    SOURCE_LOG_POS binlog日志文件位置

  • 启动主从复制 

    start replica; # 8.0.23之后
start slave; # 8.0.23之前

  • 查看从库状态 

        show replica status\G; # 8.0.23之后
    show slave status\G; # 8.0.23之前

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 124.223.47.250
                  Master_User: milo01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 29704
               Relay_Log_File: fb53f6fa1b77-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes

    只是需要关注Slave_IO_Running 和 Slave_SQL_Running这两个为yes就可以，如果为no的话就去排查吧。可能是主库的log_file或者是pos不对，需要停掉slave再去看看。 排错相关命令：

    STOP SLAVE SQL_THREAD; #停止重做线程 
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #  跳过一个错误信息
START SLAVE; # 开启同步

验证

我们在主库中创建一个db01的库，创建一个tb_user的表，并且insert一条数据后。我们在从库中： 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db01;
Database changed
mysql> select * from tb_user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | milo |   32 |
+----+------+------+
1 row in set (0.00 sec)

那么问题来了，我之前的数据如何同步呢？
由于我们的主从是通过binlog来的，而binlog日志会定时清理，所以没法通过binlog进行全量的数据导入。我们可以先从主库dump一份出来后，再导入到从库中，这样两边数据一直后，才启用主从复制。

转载请注明来源： miloyang
本文链接： boxiaoyang.cn/article/134
<<分库分表之mycat
mysql进阶之锁、INNODB引擎详解>>
人未眠
工作数十年
脚步未曾歇，学习未曾停
乍回首
路程虽丰富，知识未记录
   借此博客，与之共进步

分类