docker Mysql主从配置
docker Mysql主从配置
- 启动Master
准备配置文件mysqld.cnf
[mysqld]
#开启 binlog
log-bin=mysql-bin
# 当前server在cluster中的id,必须保证在cluster中唯一
server-id=1
#只保留7天的二进制日志,以防磁盘被日志占满(可选)
# expire-logs-days=7
#不备份的数据库 (可选)
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
docker run -d \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /etc/localtime:/etc/localtime \
-v /mnt/d/wslwork/mysql/master314/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /mnt/d/wslwork/mysql/master314/datas:/var/lib/mysql \
-p 3309:3306 \
--name mysql-master314 \
mysql:5.7.16
docker logs mysql-master314
如果出现这种情况,就需要降低配置文件权限,否在会被忽略,那么bin-log是没有开启成功的.
mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.
mysqld: [Warning] World-writable config file '/etc/mysql/mysql.conf.d/mysqld.cnf' is ignored.
那是因为权限太高了,文件被忽略了,
chmod 0444 my.cnf
chmod 0444 mysqld.cnf
创建用于同步bin-log的用户
CREATE USER 'slave' @'%' IDENTIFIED BY '123456';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave' @'%';
#刷新权限
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
- 启动salve节点
准备配置文件mysqld.cnf
[mysqld]
server-id=2
relay_log=relay-log
#开启只读 意味着当前的数据库用作读,当然这也只会影响到非root的用户,如果使用root用户操作本库是不会有影响的
read_only=ON
docker run -d \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /etc/localtime:/etc/localtime \
-v /mnt/d/wslwork/mysql/salve314/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /mnt/d/wslwork/mysql/salve314/datas:/var/lib/mysql \
-p 3310:3306 \
--name mysql-salve314 \
mysql:5.7.16
在salve节点中,加入到master中
CHANGE MASTER TO
MASTER_HOST='192.168.1.9',
master_port = 3309,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
# 根据SHOW MASTER STATUS;查看然后赋予对应的值
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=786;
# 启动slave同步(可以在连接工具中执行)
START SLAVE;
# 查看同步状态(不能在连接工具中执行)
SHOW SLAVE STATUS\G
验证是否成功,在master中创建一个数据库,可以看到同步到salve中,这就标识成功.