前因:为提升数据库性能,实现读写分离。本文主要记录了Mysql主从复制集群的搭建过程(一主一从)。
1.预先准备
- liunx服务器一台,或者两台 。只为演示就在一台机操作了,目录分别为
mysql-master
和master-slave
- docker容器化技术,docker-compose,不熟悉的可先了解mysql第一话 - mysql基于docker的安装及使用
2.Master节点配置
2.1 docker-compose.yaml文件
版本可自由选择,数据仅供测试
version: '3.7'
services:
mysql-master:
image: mysql:5.7
container_name: mysql-master
restart: always
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=123456
- TZ=Asia/Shanghai
volumes:
- /root/local/mysql-master/data:/var/lib/mysql
- /root/local/mysql-master/my.cnf:/etc/mysql/my.cnf
networks:
- my-net
networks:
my-net:
external: true
2.2 my.cnf配置
[mysqld]
# binlog 配置 开启二进制日志功能
log-bin=mysql-bin
# 同一局域网id要唯一
server-id=10
#二进制日志格式 row/基于行的复制 statement(默认)/根据修改数据的sql语句 mixed/混合使用
binlog-format=ROW
#binlog过期清理时间
expire_logs_days=30
2.3 运行master节点
启动成功后进入master节点容器内部,创建slave用户
#启动mysql
[root@m mysql-master]# docker-compose up -d
#进入master容器内部
[root@m mysql-master]# docker exec -it mysql-master /bin/bash
#进入mysql
root@b65d84c29ce3:# mysql -uroot -p123456
#创建slave用户
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
#赋予权限
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
2.4 查看master节点的binlog文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 617 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
记住这个名字!
3.Slave节点配置
3.1 docker-compose.yaml配置
就目录和端口不一样
version: '3.7'
services:
mysql-slave:
image: mysql:5.7
container_name: mysql-slave
restart: always
ports:
- "3307:3306"
environment:
- MYSQL_ROOT_PASSWORD=123456
- TZ=Asia/Shanghai
volumes:
- /root/local/mysql-slave/data:/var/lib/mysql
- /root/local/mysql-slave/my.cnf:/etc/mysql/my.cnf
networks:
- my-net
networks:
my-net:
external: true
3.2 my.cnf配置
[mysqld]
# binlog 配置 开启二进制日志功能,此slave也可当做其他slave的master来用
log-bin=mysql-slave-bin
# 同一局域网id要唯一
server-id=11
#relay_log配置中继日志
relay_log=mysql=relay-bin
#设置为只读 不设置表示slave可写
read_only=1
#binlog过期清理时间
expire_logs_days=30
3.3 运行slave节点
[root@m mysql-slave]# docker-compose up -d
[root@m mysql-slave]# docker exec -it mysql-slave /bin/bash
root@6ec22dd2595d:/# mysql -uroot -p123456
#停止slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#连接到master
mysql> change master to master_host='192.168.0.221',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=617;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#开启同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
3.4 查看slave运行状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.221
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 617
Relay_Log_File: mysql=relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
#这两行为yes说明slave开启成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
## 。。。省略后续
4.测试
- 在master节点上添加test_db库,并在该库添加test表
root@b65d84c29ce3:/# mysql -uroot -p123456
mysql> CREATE DATABASE `test_db` /*!40100 COLLATE 'utf8_general_ci' */;
Query OK, 1 row affected (0.00 sec)
mysql> use `test_db`;
Database changed
mysql> CREATE TABLE `test` (
-> `id` BIGINT NOT NULL,
-> `age` INT NULL,
-> `name` VARCHAR(50) NULL,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='utf8_general_ci'
-> ;
Query OK, 0 rows affected (0.01 sec)
- 在slave节点上查看
root@6ec22dd2595d:/# mysql -uroot -p123456
mysql> show DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.00 sec)
mysql> use `test_db`;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
可以看到在slave节点上也有对应的库和表,接下来就可以快乐的读写分离了。
以上就是本章的全部内容了。
上一篇:mysql第八话 - mysql的其它懒人用法
下一篇:mysql第十话 - mysql+springboot之sharding-JDBC分表分库实战
书到用时方恨少,事非经过不知难