MySQL读写分离1 - 主从模式搭建
1. 在 Docker 中启动 MySQL
1.1 docker-compose up -d
准备 docker-compose 文件:
version: "3"
services:
mysql-master:
image: mysql:5.6
container_name: mysql_master
ports:
- 6606:3306
volumes:
- /mydata/mysql-ha/config/master:/var/lib/mysql
- /mydata/mysql-ha/config/mysql-master.cnf:/etc/my.cnf
command:
- "--user=root"
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_DATABASE: mydev
MYSQL_USER: admin
MYSQL_PASSWORD: 123456
restart: unless-stopped
networks:
mysql-ms-network:
ipv4_address: 172.25.0.101
mysql-slave1:
image: mysql:5.6
container_name: mysql_slave1
ports:
- 6607:3306
command:
- "--user=root"
volumes:
- /mydata/mysql-ha/config/slave1:/var/lib/mysql
- /mydata/mysql-ha/config/mysql-slave1.cnf:/etc/my.cnf
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_DATABASE: mydev
MYSQL_USER: admin
restart: unless-stopped
networks:
mysql-ms-network:
ipv4_address: 172.25.0.102
mysql-slave2:
image: mysql:5.6
container_name: mysql_slave2
ports:
- 6608:3306
command:
- "--user=root"
volumes:
- /mydata/mysql-ha/config/slave2:/var/lib/mysql
- /mydata/mysql-ha/config/mysql-slave2.cnf:/etc/my.cnf
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_DATABASE: mydev
MYSQL_USER: admin
restart: unless-stopped
networks:
mysql-ms-network:
ipv4_address: 172.25.0.103
# 自定义网络
networks:
mysql-ms-network:
driver: bridge
ipam:
config:
- subnet: 172.25.0.0/24
进入docker环境,运行up命令,启动容器。
上面使用到了三个MySQL的配置文件,将其放在宿主机的位置上,然后挂载到容器中。
master节点配置
[mysqld]
#主数据库端ID号
server_id = 1
#开启二进制日志
log-bin = mysql-bin
#需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
binlog-do-db = mydev
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大,但可减小MySQL崩溃造成的损失)
sync_binlog = 1
#这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
auto_increment_offset = 1
#这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
auto_increment_increment = 1
#二进制日志自动删除的天数,默认值为0,表示“没有自动删除”,启动时和二进制日志循环时可能删除
expire_logs_days = 7
#将函数复制到slave
log_bin_trust_function_creators = 1
#设置日志三种格式:STATEMENT(默认)、ROW、MIXED
binlog_format = ROW
slave1节点配置
[mysqld]
# 从数据库端ID号
server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0
#指定slave要复制哪个库
replicate-do-db = mydev
#MySQL主从复制的时候,当Master和Slave之间的网络中断,但是Master和Slave无法察觉的情况下(比如防火墙或者路由问题)。Slave会等待slave_net_timeout设置的秒数后,才能认为网络出现故障,然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60
log_bin_trust_function_creators = 1
slave2节点配置
[mysqld]
# 从数据库端ID号
server_id = 3
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0
#指定slave要复制哪个库
replicate-do-db = mydev
#MySQL主从复制的时候,当Master和Slave之间的网络中断,但是Master和Slave无法察觉的情况下(比如防火墙或者路由问题)。Slave会等待slave_net_timeout设置的秒数后,才能认为网络出现故障,然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60
log_bin_trust_function_creators = 1
注意:master节点中的binlog-do-db和slave节点的replicate-do-db要保持一致!
1.2 查看容器 docker ps
查看是否正常启动,正常情况下会输出如下:
[root@iZ2ze7eop7f43wmsiq5q9rZ ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
71881ef86ae9 mysql:5.6 "docker-entrypoint.s…" 25 hours ago Up 8 hours 0.0.0.0:6608->3306/tcp mysql_slave2
b6f5b81acfa2 mysql:5.6 "docker-entrypoint.s…" 25 hours ago Up 8 hours 0.0.0.0:6606->3306/tcp mysql_master
88ca5b76a934 mysql:5.6 "docker-entrypoint.s…" 25 hours ago Up 8 hours 0.0.0.0:6607->3306/tcp mysql_slave1
1.3 进入容器
这里有两种方式进入容器:
docker-compose exec mysql-master bash
,注意,要在放置 mysql-master 配置文件的目录
docker exec -it mysql_master /bin/bash
,一般使用这个命令。
由于粗心,第一次启动容器时,将配置文件的名称写错了… 直到进入容器登陆MySQL时,才报错:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
一通检查之后,才发现是…
有报错时,首先去检查MySQL是否正常运行,然后再去检查配置文件。
1.4 查看mysql服务是否启动
root@e45d61b6f721:/# service mysql status
[info] MySQL Community Server 5.6.51 is not running.
root@e45d61b6f721:/# service mysql start
No directory, logging in with HOME=/
......
报错,直接退出了。GRANT ALL PRIVILEGES ON . TO ‘admin’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
原因是,MySQL 没有正常启动,conf文件错误。修改配置文件之后,重新执行 docker-compose up 命令。
# 进入MySQL
mysql -uroot -p
# 开启远程访问:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2. 修改节点信息
2.1 master 节点
获取binlog开始主从复制的地址
[root@iZ2ze7eop7f43wmsiq5q9rZ config]# docker exec -it mysql_master /bin/bash
root@b6f5b81acfa2:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.51-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
在 master 节点查看binlog配置:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 412 | mydev | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
注意,配置Binlog_Do_DB时,使用:
binlog_do_db=test
binlog_do_db=xx
binlog_do_db=xxx
不要使用一行,如:binlog_do_db=test,xx,xxx
2.2 slave 节点
mysql> reset slave;
Query OK, 0 rows affected (0.02 sec)
# 设置订阅的主服务器信息 :
change master to master_host='172.25.0.101',master_user='root',master_password='123456'
,master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=412;
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
# \G 是优化格式,方便阅读
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.0.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004 //备注:I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos: 412 //备注:主服务器二进制日志中,I/O线程已经读取的位置
Relay_Log_File: mysqld-relay-bin.000002 //备注:slave的SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos: 283 //备注:中继日志中,slave的SQL线程已读取和执行的位置
Relay_Master_Log_File: mysql-bin.000004 //备注:跟Master_Log_File比对,Relay_Master_Log_File是否跟得上Master_Log_File
Slave_IO_Running: Yes //备注:I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running: Yes //备注:SQL线程是否被启动
Replicate_Do_DB: mydev //备注:监听的DB
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 412
Relay_Log_Space: 457
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
.... 省略
将salve设置为只读:set global read_only=1;
只读模式对具有super权限的用户来说没有作用。在MySQL5.7版本新增参数,super_read_only,用于设置super权限的用户禁止写操作。
这里我试了下就算设置了只读,root用户确实可以写操作,有兴趣的去试试 😃
另一台salve节点重复同样的操作。
2.3 常见问题
主从没有同步
- 检查master节点:
show master status
,查看 Binlog_Do_DB 是否配置正常。 - 检查slave节点:
show slave status\G
,查看 salve 节点Replicate_Do_DB
是否和master 节点一致Master_Log_File
和Read_Master_Log_Pos
是否和master 节点一致
- 如果中间有差异,没有正常同步,数据不重要的情况下可以重置slave。如果数据重要,需要手动处理。
slave重新同步的方法
# 停止和重置 slave
stop slave;
reset slave;
# 修改到 master 的连接
change master to master_host='xxx',master_port=3306,
master_user='root',master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=3733695,MASTER_DELAY = 3600;
# 启动
start slave;
show slave status;
3. MySQL console 常用语句
3.1 查看binlog是否开启
SHOW VARIABLES LIKE 'log_bin';
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like "binlog%";
+-----------------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
+-----------------------------------------+--------------+
12 rows in set (0.00 sec)
3.2 设置binlog格式
STATEMENT 格式记录DDL和DML语句,可能导致主从不一致情况出现,一般使用row格式:set global binlog_format='row';
service mysql restart 之后还没看到,过了一会儿,重进docker 容器才看到。
3.3 是否半同步复制
show status like 'Rpl_semi_sync_master_status';
4. 验证
以上步骤操作完成之后,可以用工具连接上三台MySQL服务(一主二从),在master节点中操作DDL、DML语句之后,去salve节点查看是否同步过去。
如果有问题的话,按照 2.3
中的方法再去检查一遍,一般是可以正常运行的。:)