主从数据库搭建

参考博文

环境

Centos 7
Docker

1.基于docker内部无vim/vi，在本地创建配置文件，再映射进容器

cat /opt/mysql/mysql-master/conf/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin

cat /opt/mysql/mysql-slave/conf/my.cnf 
[mysqld]
server-id=2

2.启动两台MySQL

docker run -p 33306:3306 --name mysql-master -v /opt/mysql/mysql-master/conf:/etc/mysql/conf.d -v /opt/mysql/mysql-master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d docker.io/mysql
docker run -p 33307:3306 --name mysql-slave -v /opt/mysql/mysql-slave/conf:/etc/mysql/conf.d -v /opt/mysql/mysql-slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d docker.io/mysql

3.进入master主数据库,执行sql语句

create user 'replication'@'10.8.15.70' identified with mysql_native_password by 'root';
 
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.8.15.70';
 
flush privileges;

# 查看主机状态
show master status\G;

这里记录下画框的两个参数

4.进入slave从数据库

注意：LOG_FILE和LOG_POS即master上的两个参数

CHANGE MASTER TO
MASTER_HOST='10.8.15.70',
MASTER_PORT=33306,
MASTER_USER='replication',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1124;

# 开启主从同步
start slave;

# 查看主从同步状态
show slave status\G;

框起两项都为YES即成功

注意：重启Slave_SQL_Running可能变为NO，则主从复制不再进行，执行以下命令

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

效果

master创建，slave同步