mysql数据库的主从配置

mysql数据库作为我们程序开发中，最容易遇到瓶颈的一个环节，其有很多可优化的地方，此处我们先从见效最快、技术要求较低的地方 -- 数据库主从优化

主数据库开启二进制日志

1. 修改my.cnf并且将以下参数加入其中,重启mysql实例

2. [mysqld]

   # 其下还有很多其他部分配置，此处不再一一列举，此处只是说明该配置项需要放置到mysqld分组下

   # 由于bug,所以需要设置该参数.否则无法启动mysql实例

   # server-id表示数据库的唯一标识符，主库的server-id值必须比从库大(程序员思路1为最大，常人理解时记住主库一般配置为1~10即可，从库从20往下进行递增)

   server-id=11

   # 其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径

   log-bin = mysql-bin

   # 以下配置项为可选配置项，根据需求进行特殊定制即可

   # 设置二进制的日志格式

   # 二进制日志的格式有三种：基于语句的格式（STATEMENNT）、基于行的格式（ROW）和混合模式（MIXED）

   # 在MySQL 5.7.7之前，默认格式是 STATEMENT。在MySQL 5.7.7及更高版本中，默认值是ROW

   binlog_format=row

   # 选择部分数据库进行记录

   # 如果有多个数据库需要配置，直接复制该条记录重新指定数据库名称即可(默认记录全部数据库)

   binlog-do-db = test

   # 也可以选择不记录某些数据库的二进制日志

   # 如果有多个数据库需要配置，直接复制该条记录重新指定数据库名称即可(默认忽略数据库为空)

   # 此配置项优先级低于binlog-do-db，且为互斥关系，即一旦指定binlog-do-db，则此配置项将不再生效

   binlog-ignore-db = test

   binlog-ignore-db = mysql

   # 设置二进制日志分片大小，即二进制日志大小到达多少后自动创建并启用新的日志进行记录

   max_binlog_size = 500M

   # 设置日志的过期天数，即生成日志后多少天以后，自动删除该二进制日志

   # 此配置项单位为天，默认0表示永不过期

   expire_logs_days = 7

   # 除了二进制日志外，我们还可以记录查询日志，但因为查询太过于吃性能且对原数据并无变更要求，所以一般情况下，我们并未启用查询日志

   # 此处仅为记录查询日志的记录方式，请酌情使用

   # 启用/关闭查询日志，1-开启 0-关闭

   general-log = 1

   # 存储方式，可选值有table、file、none

   # file表示记录到文件中，table表示记录到表中(此选项很少有人选，所以此处暂时忽略)

   log-output = file

   # 查询日志路径

   general-log-file = "/home/chenyubo/www/logs/mysql.general.log"

   # 慢查询日志

   # 相较于查询日志，慢查询日志对于我们来说更加重要

   # 管理语句的慢查询并不会记录其中

   # 管理语句包括：alter table，analyze talbe，check table，create table，create index，drop index，optimize table和repaire table。

   # 慢查询日志开关 0-关闭 1-开启

   slow_query_log = 1

   # 慢查询阀值，单位：秒，默认10秒

   long_query_time = 3

   # 设置慢查询日志路径

   # 如果慢查询日志很多，可使用mysqldumpslow工具对慢查询日志进行分类汇总

   slow_query_log_file = "/var/log/mysql/mysql.slow.log"

3. 查看二进制日志的相关配置

4. -- 查看数据库二进制日志进度

   MariaDB [(none)]> show master status;

   +------------------+----------+--------------+------------------+

   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

   +------------------+----------+--------------+------------------+

   | mysql-bin.000001 | 328 | | |

   +------------------+----------+--------------+------------------+

   1 row in set (0.000 sec)

   -- 查看二进制日志列表

   MariaDB [(none)]> show binary logs;

   +------------------+-----------+

   | Log_name | File_size |

   +------------------+-----------+

   | mysql-bin.000001 | 328 |

   +------------------+-----------+

   1 row in set (0.000 sec)

   -- 查看binlog相关系统参数值

   MariaDB [(none)]> show variables where variable_name in ('log_bin','log_bin_basename','log_bin_index');

   +------------------+--------------------------------+

   | Variable_name | Value |

   +------------------+--------------------------------+

   | log_bin | ON |

   | log_bin_basename | /var/lib/mysql/mysql-bin |

   | log_bin_index | /var/lib/mysql/mysql-bin.index |

   +------------------+--------------------------------+

   3 rows in set (0.001 sec)

   -- 可选SQL，用于管理二进制日志

   -- 这些SQL将不会在复制其执行结果，仅留存SQL用于选择使用

   -- 删除所有二进制日志

   MariaDB [(none)]> reset master;

   -- 删除编号在xxxxxx之前的日志

   MariaDB [(none)]> purge master logs to 'mysql-bin.000006';

   -- 删除日期在'yyyy-mm-dd hh:mm:ss'之前的日志

   MariaDB [(none)]> purge master logs before '2020-07-10 12:12:12';

5. PS：在my.cnf中设置好log-bin之后，log_bin会自动设置为ON，basename设置为mysql-bin，index文件也自动生成。index文件记录的就是所有的二进制文件的名称及位置信息。
6. 在主数据库里创建一个同步账号授权给从数据库使用（grant创建，replication复制）

7. -- 创建同步账号，用于从数据库读取二进制日志

   -- 账户：repl

   -- 登录域：任意

   -- 密码：123456

   MariaDB [(none)]> create user 'repl'@'%' identified by '123456';

   -- 对该账户进行授权

   MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';

   -- 刷新授权

   MariaDB [(none)]> flush privileges;

从库配置

从库配置和主库稍微有些区别，区别为从库需要配置主库的相关信息

[mysqld]
# 其下还有很多其他部分配置，此处不再一一列举，此处只是说明该配置项需要放置到mysqld分组下
# 由于bug,所以需要设置该参数.否则无法启动mysql实例
# server-id表示数据库的唯一标识符，主库的server-id值必须比从库大(程序员思路1为最大，常人理解时记住主库一般配置为1~10即可，从库从20往下进行递增)
server-id = 21
# 启用中继日志
# 一般情况下它在MySQL主从同步读写分离集群的从节点才开启。主节点一般不需要这个日志
# 此处存储的为中继日志的文件名称，生成格式和log-bin相同
relay-log = mysql-relay
# replicate-do-db 设定需要复制的数据库(多数据库逗号，隔开)
# replicate-do-table 设定需要复制的表
# replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
# replicate-ignore-db 设定忽略的复制数据库 (多数据库逗号，隔开)
# replicate-ignore-table 设定需要忽略的复制表
# replicate-wild-ignore-table 同replication-ignore-table功能一样，可以加通配符
比如，忽略mysql库的所有表           

从库配置完成后，进行重启服务 systemctl restart mariadb ，然后进入mysql命令行，进行配置主数据库节点

-- 因配置项只有返回是否OK的结果，所以此处不再复制结果信息
MariaDB [(none)]> change master to \
    master_host='192.168.86.100', -- 地址填主数据库的地址
    master_port=3306, -- 指定主服务器的端口信息
    master_user='repl' , -- 填主数据库的使用者
    master_password='123456' , -- 访问主数据库密码
    master_log_file='mysql_bin_0.000001' , -- 主数据库的文件名
    master_log_pos=134; -- 同步主数据库从什么位置开始，填数字就可以，无需引号，但是最后需要分号
-- 开启主从同步
MariaDB [(none)]> start salve;
-- 查看结果
-- 此结果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示从库配置完成
MariaDB [(none)]> show slave status;           

Docker 从库配置

我们建议将数据库直接以原生的方式搭建的宿主机上，但是从库并没有那么多要求，虽然也建议直接搭建到宿主机上，但是考虑到有时我们只有一台服务器，如果想做主从的话，就要考虑到Docker和宿主机之间的通讯了。

而且因为是主从，Docker服务器需要联通宿主机，而宿主机只考虑到使用端口链接Docker即可

PS：考虑到有些Docker已经启动过了，所以我们建议进入到Docker容器中，执行命令：ip addr进行查看容器IP

PS：如果没有设置专门的网关的话，可以直接在宿主机执行ip addr show docker0查看IP

# Docker 拉取数据库镜像
$ docker image pull mariadb
# 启动容器
# 运行mariadb
# 本地配置：/home/mysql/data MySQL数据存储目录
# 绑定端口：3307                 开放本机端口
# 密码配置：root123              配置MySQL连接密码
# 20.10版本以上
$ docker run --name mysql01 --add-host="host.docker.internal:host-gateway" -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 20.10版本以下
# 然后在宿主机使用docker0的IP进行访问
$ docker run --name mysql01 -v /home/xiaoyutab/Documents/mysqls:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root123 -p 3307:3306 -d mariadb
# 进入Docker虚拟机
$ docker exec -it mysql01 /bin/bash
# 修改配置文件，此处使用cp的形式进行复制-修改
[Docker]$ cd /etc/mysql/mariadb.conf.d
[Docker]$ cp 50-server.cnf /var/lib/mysql/
# 在宿主机修改此文件配置即可【因此文件复制出来为root权限，所以需要使用sodu进行执行】
$ sudo vim /home/xiaoyutab/Documents/mysqls/50-server.cnf
# 复制回来
[Docker]$ rm 50-server.cnf ; cp /var/lib/mysql/50-server.cnf ./
# 重启容器内MySQL
$ docker stop mysql01
$ docker start mysql01
# 再次进入容器，进行数据复制
$ docker exec -it mysql01 /bin/bash
[Docker]$ mysql -uroot -proot123
[Docker]MariaDB [(none)]> change master to master_host='172.17.0.1',master_port=3306,master_user='admin',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=1;
Query OK, 0 rows affected (0.020 sec)

[Docker]MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)

[Docker]MariaDB [(none)]> show slave status\G;
# 此结果中，需要看到【Slave_IO_Running:yes】和【Slave_SQL_Running:yes】才表示从库配置完成           

错误处理

部分时候我们会遇到同步错误，这时候就需要使用以下方法进行解决处理了

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因：主从同步遇到同步日志紊乱

解决办法：

-- 主mysql
-- 查看新的binlog日志
> show binary logs;
-- 从mysql
-- 停止同步
> stop slave;
-- 修改同步信息
-- 如果是服务器变更，也可以重新执行change语句进行配置，类似上文的配置方式
> change master to master_log_file='mysql-bin.000001',master_log_pos=1;
-- 开启同步
> start slave;
-- 检查状态
> show slave status \G;           

Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'grant replication slave on .to 'admin'@'localhost''

原因：主库账户表的数据跟从库不同步导致，主库要更新的记录而从库中不存在

解决方案：忽略掉不需要同步的表

PS：主要忽略的库有：mysql、information_schema、performance_schema，其中后两个为虚拟库，默认忽略，所以主要配置忽略的库为mysql