MySql5.7 主从配置

摘要：本文主要介绍了基础mysql主从配置，性能的优化等等都没有经过详尽的测试，会给出主要的性能影响参数设置和配置步骤，大家按照自己的实际情况进行配置即可。文章是以docker为基础容器化部署的数据库。

准备

• 为什么需要主从

数据容灾、减少主库的读写压力提高服务的吞吐量。

• 环境

系统：centos7
docker：Docker version 20.10.7, build f0df350
docker-compose：docker-compose version 1.29.2, build 5becea4c
服务器IP：192.168.137.100

主库配置

由于为了兼容表情的存储，所以数据的编码格式都改成了utf8mb4，为保持字符集编码统一则都用utf8mb4_unicode_ci

• 编辑配置文件my.cnf

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
default-storage-engine=INNODB
character-set-client-handshake = FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=2000
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=2
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

• 编辑docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:5.7.16
    networks:
      - default
    container_name: mysql5.7-master
    volumes:
      - ./data:/var/lib/mysql
      - ./conf/my.cnf:/etc/my.cnf
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "TZ=Asia/Shanghai"
    ports:
      - 3316:3306
networks:
  default:
    external:
      name: huzhihui

启动服务

• 创建/alidata/docker/mysql-master-slave文件夹
• 在mysql-master-slave文件夹下创建master文件夹
• 在master文件夹下创建conf文件夹
• 复制my.cnf文件到conf文件夹下
• 复制docker-compose.yml到master文件夹下

[root@localhost master]# ls
conf  docker-compose.yml

• 运行docker-compose up -d
• 查询服务docker ps |grep mysql5.7-

[root@localhost master]# docker ps |grep mysql5.7-
6b89f18fc2de   mysql:5.7.16                "docker-entrypoint.s…"   46 minutes ago   Up 46 minutes      0.0.0.0:3317->3306/tcp, :::3317->3306/tcp                                              mysql5.7-slave
542a560b73e2   mysql:5.7.16                "docker-entrypoint.s…"   55 minutes ago   Up 55 minutes      0.0.0.0:3316->3306/tcp, :::3316->3306/tcp                                              mysql5.7-master

• 查询主库的bin-log节点

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 |     2950 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

• 新增从库同步的用户

如果要指定ip能访问就更改一下即可

CREATE USER `slave`@`%` IDENTIFIED BY 'slave@123';

GRANT File, Replication Slave ON *.* TO `slave`@`%`;

从库配置

• 编辑配置文件my.cnf

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
default-storage-engine=INNODB
character-set-client-handshake = FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=2000
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=3
log-bin=mysql-bin
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
innodb_flush_log_at_trx_commit=2
sync_binlog=1
expire_logs_days=7
log_slave_updates=1
slave_parallel_workers=4
slave_parallel_type=logical_clock
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
read_only=1

• 编辑docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:5.7.16
    networks:
      - default
    container_name: mysql5.7-slave
    volumes:
      - ./data:/var/lib/mysql
      - ./conf/my.cnf:/etc/my.cnf
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "TZ=Asia/Shanghai"
    ports:
      - 3317:3306
networks:
  default:
    external:
      name: huzhihui

• 创建启动步骤和主库的方式一致
• 从库同步数据配置

我们之前主库查询到了bin-log的日志节点,我们则需要在从库设置同步节点信息

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.137.100',
MASTER_PORT = 3316,
MASTER_USER = 'slave',
MASTER_PASSWORD = 'slave@123',
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 2687;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

• 查看状态show slave status;显示如下就表示成功了

Slave_IO_State
Waiting for master to send event

• 然后就可以愉快的同步了

优化参数详解

server-id=110 #设置主服务器的ID(不能和别的服务器重复)
innodb_flush_log_at_trx_commit=2  #可以配置的值：0/1/2; 0、效率最高，最不安全；1、最安全，但是效率最低；2、安全和效率平衡的取舍，在服务器系统挂掉的情况下会丢失数据；
sync_binlog=1  #，值可设置 0（性能最高，但是可能丢失数据而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘）、1、500、1000；当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
log-bin=mysql-bin  #binlog日志文件名
binlog-ignore-db=mysql # 表示不同步mysql库
binlog-ignore-db=information_schema # 表示不同步information_schema库
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=xxxx  # 这个表示只同步某个库 （如果没有此项，表示同步所有的库）


replicate-ignore-db=mysql  #配置不需要复制的库mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
replicate_do_db=python #标记出需要同步的数据库名,在多从配置时可以按需配置，不配置同步所有
innodb_flush_log_at_trx_commit=2 # 可以配置的值：0/1/2; 0、效率最高，最不安全；1、最安全，但是效率最低；2、安全和效率平衡的取舍，在服务器系统挂掉的情况下会丢失数据；
sync_binlog=1000 # 每进行n次事务提交之后，MySQL将binlog_cache中的数据强制写入磁盘。
slave_parallel_workers=4 #根据实际情况决定开启多少个线程用于主从复制
slave_parallel_type=logical_clock #基于组提交的并行复制方式
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON