docker+mysql数据库配置主从复制

docker+mysql数据库主从复制

主库安装

1. 创建配置文件

• 创建目录
  mkdir -p /opt/mysql/master/conf
• 创建my.cnf文件
  vim /opt/mysql/master/conf/my.cnf
• my.cnf文件内容

[client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  [mysqld]
  collation-server = utf8_unicode_ci
  init-connect='SET NAMES utf8'
  character-set-server = utf8
  pid-file= /var/run/mysqld/mysqld.pid
  socket = /var/run/mysqld/mysqld.sock
  datadir = /var/lib/mysql
  secure-file-priv= NULL
  symbolic-links=0
  skip-character-set-client-handshake
  
  binlog_format=ROW
  binlog_rows_query_log_events=1
  server_id = 1
  log-bin= mysql-bin
  gtid_mode=on
  enforce_gtid_consistency=ON
  master_info_repository=TABLE
  relay_log_info_repository=TABLE
  relay_log_recovery=ON
  sync_master_info=1
  binlog_checksum=CRC32
  
  slave-parallel-type=LOGICAL_CLOCK
  slave_parallel_workers=4
  binlog_transaction_dependency_tracking=WRITESET_SESSION
  transaction_write_set_extraction=XXHASH64
  
  transaction-isolation=READ-COMMITTED
  read-only=0
  replicate-ignore-db=mysql
  replicate-ignore-db=sys
  replicate-ignore-db=information_schema
  replicate-ignore-db=performance_schema
  expire_logs_days=30
  
  max_connections=3600
  
  \# Custom config should go here
  !includedir /etc/mysql/conf.d/

2. 启动容器

docker run -d -p 3306:3306 --name mysql-m -v /opt/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/master/data/mysql:/var/lib/mysql -v /opt/mysql/
master/log:/opt/mysql/log -e MYSQL_ROOT_PASSWORD=test1148 --restart=always mysql:5.7.29

3. 主库创建用于同步的账号

• 进入docker容器内
  docker exec -it mysql-m bash
• 登录mysql
  mysql -uroot -p
• 创建backup账号
  GRANT REPLICATION SLAVE ON . to ‘backup’@’%’ identified by ‘123456’;

操作从数据库

“keepalived、heartbeat”暂时没时间搞，有时间补上
主从切换参考博客：https://www.cnblogs.com/zping/p/5283912.html

1. 创建配置文件

• 创建目录
  mkdir -p /opt/mysql/slave/conf
• 创建my.cnf文件
  vim /opt/mysql/slave/conf/my.cnf 内容如下
• my.cnf内容

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir= /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake

binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 2
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32

slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4

binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64

transaction-isolation=READ-COMMITTED
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
expire_logs_days=30

max_connections=3600

\# Custom config should go here
!includedir /etc/mysql/conf.d/

2.启动容器

docker run -d -p 3307:3306 --name mysql-m -v /opt/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/master/data/mysql:/var/lib/mysql -v /opt/mysql/
master/log:/opt/mysql/log -e MYSQL_ROOT_PASSWORD=test123 --restart=always mysql:5.7.29

3.关联主库

• 进入docker容器内
  docker exec -it mysql-s bash

• 登录mysql数据库
  mysql -uroot -p

• 执行关联master语句
  change master to master_host=‘192.168.28.130’,master_port=3306,master_user=‘masterming’,master_password=‘123456’,MASTER_AUTO_POSITION=1;

• 启动slave
  start slave;

• 查看slave的状态
  show slave status\G
  
  
  这俩都是yes证明配置正确。

• 创建从库的普通用户
  read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户（如超级管理员root用户）的数据修改操作，所以需要另外创建普通账号来操作从库。
  GRANT select,insert,update,delete,create,drop,alter ON . to ‘slaveming’@’%’ identified by ‘1q2w3e4r’;

主库查看同步信息

1. 登录主库

• 查看binlog线程，执行以下语句查看正在执行的线程
  show processlist;
  
• 查看所有从库信息
  show slave hosts;