docker的一大好处是在本地可以很方便快速的搭建负载均衡,主从同步等需要多主机的环境。 可以说是极大方便了运维成本和难度。 本节在本地搭建mysql的一主一从的集群环境。
关于主从同步的流程图,放张网上找的流程图
image.png
以mysql5.7为例
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 以下是新增内容
# 标识不同的数据库服务器,而且唯一
server-id=1
# 启用二进制日志
log-bin=mysql-bin
log-slave-updates=1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip-host-cache
skip-name-resolve
slave 目录底下的 mysqld.cnf 内容为
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 以下是新增内容
server-id=2
log-bin=mysql-bin
log-slave-updates=1
# 多主的话需要注意这个配置,防止自增序列冲突。
auto_increment_increment=2
auto_increment_offset=2
read-only=1
slave-skip-errors = 1062
skip-host-cache
skip-name-resolve
docker run --name mysql_master -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v D:/docker/mysql-master-slave/master/data:/var/lib/mysql -v D:/docker/mysql-master-slave/master/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf mysql:5.7
docker run —name mysql_slave -d -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -v D:/docker/mysql-master-slave/slave/data:/var/lib/mysql -v D:/docker/mysql-master-slave/slave/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf mysql:5.7
这个时候宿主机的 Navicat 应该可以连上容器里的两个数据库了。
docker exec -it mysql_master bash
mysql -u root -p
创建一个同步数据权限的用户
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
查看状态,记住File、Position的值,在 Slave 中将用到
show master status;
image.png
进入slave容器
docker exec -it mysql_slave bash
mysql -u root -p
设置主库链接
change master to master_host='172.17.0.2',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;
启动从库同步
start slave
查看状态,如果 Slave_SQL_Running_State 是 Slave has read all relay log; waiting for more updates 表示正常运行。
show slave status \G
image.png
docker exec mysql_master mysql -uroot -p123456 -e "CREATE DATABASE test"
docker exec mysql_slave mysql -uroot -p123456 -e "SHOW DATABASES"
/etc/mysql/mysql.conf.d/mysqld.cnf
拷贝出来的tail /var/log/mysql/error.log
重新执行同步
stop slave;
change master to master_log_file='mysql-bin.000100,master_log_pos=123'
关于 file 和 pos,需在master上执行show master status
获得。
或者使用 mysqlbinlog
命令分析。change master to ...
。
还需要注意当前master没有写入等操作,最好先锁表,同步设置好后在解锁。参考https://www.cnblogs.com/w2206/p/6963065.html https://github.com/Junnplus/blog/issues/1