基于Docker的MYSQL配置简单的读写分离

下载aarch64的MYSQL

docker pull --platform=arm64 mysql/mysql-server:5.7

创建映射的文件夹

mkdir -p \
mysql/master/data \
mysql/master/conf \
mysql/slave/data \
mysql/slave/conf \

创建MYSQL配置文件

Master

[mysqld]
log-bin=mysql-bin #开启日志
server-id=1 #这里必须唯一
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
binlog-do-db = nacos #指定需要复制的数据库
port		= 3306
socket		= /var/lib/mysql/mysql.sock #这里需要修改一下
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 8M
max_allowed_packet = 100G
table_open_cache = 32
sort_buffer_size = 256K
net_buffer_length = 4K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 4M
thread_cache_size = 4
#query_cache_size = 4M
tmp_table_size = 8M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

binlog_format=mixed
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/usr/local/mysql/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""


innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Slave

[mysqld]
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db = nacos #指定需要复制的数据库
port		= 3306
socket		= /var/lib/mysql/mysql.sock
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 8M
max_allowed_packet = 100G
table_open_cache = 32
sort_buffer_size = 256K
net_buffer_length = 4K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 4M
thread_cache_size = 4
#query_cache_size = 4M
tmp_table_size = 8M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

binlog_format=mixed
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/usr/local/mysql/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""


innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

创建docker前的必备操作 

安装扩展源

yum -y install epel-release

安装python-pip模块

sudo yum install python-pip

以命令进行安装

cd /usr/local/bin/

#下面方法二选一

#看你自己的架构是什么再下载 https://github.com/docker/compose/

wget \
https://github.com/docker/compose/releases/download/v2.5.0/docker-compose-darwin-aarch64

#建议在GitHub下载到本地然后上传

#重命名
mv docker-compose-linux-aarch64 docker-compose
#更改权限
chmod +x /usr/local/bin/docker-compose

 创建docker-compose.yml

version: '3.7'
services:
  mysql_master:
    image: mysql/mysql-server
    container_name: mysql_master
    ports:
      - 8806:3306
    volumes:
      - /home/mysql/master/data:/var/lib/mysql
      - /home/mysql/master/conf/master-my.cnf:/etc/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: nacos
      MYSQL_USER: admin
      MYSQL_PASSWORD: password
    restart: always
    networks:
      mysql-network:
        ipv4_address: 172.25.0.101

  mysql_slave:
    image: mysql/mysql-server
    container_name: mysql_slave
    ports:
      - 8807:3306
    volumes:
      - /home/mysql/slave/data:/var/lib/mysql
      - /home/mysql/slave/conf/slave-my.cnf:/etc/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: nacos
      MYSQL_USER: admin
      MYSQL_PASSWORD: password
    restart: always
    networks:
      mysql-network:
        ipv4_address: 172.25.0.102
networks:
  mysql-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.25.0.0/24

然后运行文件 

docker-compose up -d

配置主从

进入主MYSQL

进入MYSQL界面输入 

配置远程访问角色并刷新 

GRANT ALL PRIVILEGES ON *.* TO 'copy'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

flush privileges;

 查询复制主库复制地址

show master status;
#输入后会出现这些东西，记住File Position
>
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      604 | nacos        |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置从MYSQL

进入MYSQL界面输入

reset slave;

 设置订阅主服务器地址

#master_host:在docker-compose文件中位置的地址
#master_user/master_password:在主库配置远程访问角色
#master_log_file/master_log_pos:上面查询出来的主库信息

change master to master_host='172.25.0.101',master_user='copy',master_password='password',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=604;  

启动订阅线程

start slave;

查看订阅状态

show slave status\G;

#当返回的信息出现这两个yes，就完成配置
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes

安装过程中可能出现的问题

1. 没有编写my.cnf配置文件，docker如果需要映射文件的话需要提前在宿主机的指定目录创建好映射的文件，否则docker-compose启动会报错
2. 出现Slave_IO_Running: No请检查你的配置文件中server-id是否唯一
3. 建议安装一个docker可视化工具