mysql主从复制读写分离

mysql主从复制

• mysql提高性能方法之一 [读写分离]

由于资源有限，我们用docker来演示多个mysql间实现主从复制

docker-compose配置参考

 mysql:
  image: mysql:5.7.35
  container_name: mysql
  ports:
   - 3406:3306
  environment:
   - MYSQL_ROOT_PASSWORD=lican520
   - TZ=Asia/Shanghai
  volumes:
   - ./mysql/lib/mysql:/var/lib/mysql
   - ./mysql/conf.d:/etc/mysql/conf.d
  logging:
   driver: json-file
   options:
    max-size: "10M"
    max-file: "10"
  restart: always


 mysql_slave:
  image: mysql:5.7.35
  container_name: mysql_slave
  ports:
   - 3407:3306
  environment:
   - MYSQL_ROOT_PASSWORD=lican520
   - TZ=Asia/Shanghai
  volumes:
   - ./mysql_slave/lib/mysql:/var/lib/mysql
   - ./mysql_slave/conf.d:/etc/mysql/conf.d
  logging:
   driver: json-file
   options:
    max-size: "10M"
    max-file: "10"
  restart: always

 mysql_slave2:
  image: mysql:5.7.35
  container_name: mysql_slave2
  ports:
   - 3408:3306
  environment:
   - MYSQL_ROOT_PASSWORD=lican520
   - TZ=Asia/Shanghai
  volumes:
   - ./mysql_slave2/lib/mysql:/var/lib/mysql
   - ./mysql_slave2/conf.d:/etc/mysql/conf.d
  logging:
   driver: json-file
   options:
    max-size: "10M"
    max-file: "10"
  restart: always

1.修改docker-compose.yaml文件，新增一主两从

2.重启docker-compose

docker-compose down
docker-compose up -d 

3.查看三个mysql容器启动状态

docker ps

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aRZKrOmw-1679968813969)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1679966330845.png)]

4.修改主mysql配置文件

cd /home/work/dockerProject/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=device //同步到从服务器的数据库
expire_logs_days=7

5.进主mysql查看master-log-file和position

docker exec -it mysql /bin/bash
mysql -uroot -p lican520
show master status

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iPVo25IR-1679968813970)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1679966935160.png)]

6.新增同步用户

GRANT REPLICATION SLAVE ON *.* to 'master001'@'%' identified by 'lican520'

7.修改从mysql配置文件

cd /home/work/dockerProject/mysql_slave
vim my.cnf
[mysqld]
server-id=2
replicate_wild_do_table=device.%

cd /home/work/dockerProject/mysql_slave2
vim my.cnf
[mysqld]
server-id=3
replicate_wild_do_table=device.%

8.登录从服务器[这里演示mysql_slave2]

docker exec -it mysql_slave2 /bin/bash
mysql -uroot -p lican520
//设置同步指令
change master to master_host='175.24.207.175',master_port=3406,master_user='master001',master_password='lican520',master_log_file='mysql-bin.000003',master_log_pos=154;

9.开启同步

start slave

10.查看同步状态

show slave status\G;

11.验证

//1.在主服务器创建数据库，创建表，插入数据
insert into device value (2, 'device2');
查看从服务器的同步情况

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XaxWHnQf-1679968813971)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1679968634448.png)]