基于本地测试,部署MySQL的主从复制环境
数据库的配置文件和数据文件存在本地,文件夹和目录结构如下:
mysql
├── master
│ ├── conf
│ │ └── my.cnf
│ └── data
└── slaver
├── conf
│ └── my.cnf
└── data
Master配置: my.cnf[mysqld]
# 用于标识不同的数据库服务器,而且唯一
server_id = 1
# 需要启用二进制日志
log-bin= mysql-bin
read-only=0
# 指定同步的数据库
binlog-do-db=test_db
# 忽略记录二进制日志的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
Slave配置: my.cnf[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=1
# 指定同步的数据库
binlog-do-db=test_db
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
启动创建主从容器
docker pull mysql:5.6
// 创建master
docker run --name mastermysql -d -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root -v ~/docker/mysql/master/data:/var/lib/mysql -v ~/docker/mysql/master/conf/my.cnf:/etc/mysql/my.cnf mysql:5.6
// 创建slave
docker run --name slavermysql -d -p 3326:3306 -e MYSQL_ROOT_PASSWORD=root -v ~/docker/mysql/slaver/data:/var/lib/mysql -v ~/docker/mysql/slaver/conf/my.cnf:/etc/mysql/my.cnf mysql:5.6
主数据库配置
docker exec -it mastermysql bash
mysql -u root -p
// 创建一个用户来同步数据
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
// 查看状态,记住File、Position的值,在Slaver中将用到
show master status;
从数据库配置
// 进入slaver容器
docker exec -it slavermysql bash
mysql -u root -p
//设置主库链接
mysql> change master to master_host='192.168.10.103',master_user='backup',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=675,master_port=3316;
// 启动主从同步
mysql> start slave;
// 查看状态
mysql> show slave status;
说明:
master_host:主库地址
master_user:主库创建的同步账号
master_password:主库创建的同步密码
master_log_file:主库产生的日志
master_log_pos:主库日志记录偏移量
master_port:主库使用的端口,默认为3306