使用Docker进行MySQL读写分离/主从复制
以下代码仅供测试学习，请勿用于生产环境。如果要在生产环境使用，请修改其中权限部分的暴力写法。
- 一主二从的配置
- 假设mysql1是主
- mysql2和mysql3为从
停止并删除所有容器
docker kill $(docker ps -aq)
docker rm $(docker ps -aq)
Docker 安装三台MySQL服务器
# 安装第一台MySQL
docker run -d -e MYSQL_ROOT_PASSWORD=123456 -p 3301:3306 --name=mysql1 mysql:5.6
# 安装第二台MySQL
docker run -d -e MYSQL_ROOT_PASSWORD=123456 -p 3302:3306 --name=mysql2 mysql:5.6
# 安装第三台MySQL
docker run -d -e MYSQL_ROOT_PASSWORD=123456 -p 3303:3306 --name=mysql3 mysql:5.6
把三台MySQL的配置文件克隆出来
mkdir -p /home/mysql1 /home/mysql2 /home/mysql3
docker cp mysql1:/etc/mysql/mysql.conf.d/mysqld.cnf /home/mysql1/
docker cp mysql2:/etc/mysql/mysql.conf.d/mysqld.cnf /home/mysql2/
docker cp mysql3:/etc/mysql/mysql.conf.d/mysqld.cnf /home/mysql3/
修改主库配置
# vim /home/mysql1/mysqld.cnf
server-id=1 #任意自然数n，只要保证每台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
修改从库配置
从库1
# vim /home/mysql2/mysqld.cnf
server-id=2 #任意自然数n，只要保证每台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
从库2
# vim /home/mysql3/mysqld.cnf
server-id=3 #任意自然数n，只要保证每台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
把修改后的配置文件放回docker
docker cp /home/mysql1/mysqld.cnf mysql1:/etc/mysql/mysql.conf.d/
docker cp /home/mysql2/mysqld.cnf mysql2:/etc/mysql/mysql.conf.d/
docker cp /home/mysql3/mysqld.cnf mysql3:/etc/mysql/mysql.conf.d/
重启MySQL容器
docker restart mysql1
docker restart mysql2
docker restart mysql3
配置主库
进入主库
docker exec -it mysql1 /bin/bash
mysql -uroot -p123456
查看主库配置是否生效
mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
为从库创建同步账户
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO user@'%' IDENTIFIED BY 'Abc123...';
Query OK, 0 rows affected (0.00 sec)
# user 为创建的同步用户的用户名
# Abc123... 为同步用户的密码
# 验证
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| user | % | *2DE2CA854B5E9444766D39E4533C5C7D0736EF69 |
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
修改从库数据
进入从库1
docker exec -it mysql2 /bin/bash
mysql -uroot -p123456
停止从库同步服务，方便进一步修改
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看主库的ip
docker inspect mysql1
{
...
"Gateway": "172.17.0.1",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"IPAddress": "172.17.0.2", # 此为主库ip
"IPPrefixLen": 16,
...
}
查看主库同步状态
# 在主库中输入如下查看
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001 # 此为日志文件名
Position: 338 # 此为同步位置
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
在从库执行如下代码
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', #主库IP
MASTER_PORT=3306, #主服务器端口
MASTER_USER='user', #主服务器用户名
MASTER_PASSWORD='Abc123...', #主服务器用户密码
MASTER_LOG_FILE='mysql-bin.000001', #日志文件名，获取方法往上看
MASTER_LOG_POS=338; #同步位置，获取方式往上看
# 执行结果
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2',
-> MASTER_PORT=3306,
-> MASTER_USER='user',
-> MASTER_PASSWORD='Abc123...',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=338;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
启动从库1同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
检测同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 556
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes # 必须为Yes
Slave_SQL_Running: Yes # 必须为Yes
Replicate_Do_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: 556
Relay_Log_Space: 457
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 135bae99-48a2-11ed-91d1-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
如果Slave_IO_Running不为Yes
请检查 MASTER_LOG_FILE 的值是否正确，就是mysql-bin.000001和338这两个数据
如果Slave_SQL_Running不为Yes
执行如下代码,记住，要分别一条一条执行。
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;
可能执行一次没反应，多执行几次看看。
直到Slave_IO_Running和Slave_SQL_Running的值都为Yes，说明同步配置完成。
如果Slave_IO_Running一直为连接中
请检查账号是否具有权限或防火墙端口是否放开
检测是否完成
在主库上创建数据库并创建数据表
create database test_mysql charset=utf8;
use test_mysql;
create table user(id int primary key auto_increment);
此时从库出现数据库和数据表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test_mysql |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test_mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| user |
+----------------------+
1 row in set (0.00 sec)
至此，同步完成
Flask中使用读写分离
flask-sqlalchemy本身无法支持读写分离，但是可以通过修改源码来实现这样的功能
数据库配置
class DevConfig(DefaultConfig):
......
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123456@139.224.188.218:3301/20221011db" # 迁移等时候使用
SQLALCHEMY_BINDS = {
"master": "mysql+pymysql://root:123456@139.224.188.218:3301/20221011db", # 主库配置
"slave1": "mysql+pymysql://root:123456@139.224.188.218:3302/20221011db", # 从1
"slave2": "mysql+pymysql://root:123456@139.224.188.218:3303/20221011db", # 从2
}
......
重写SignallingSession类的get_bind方法
class RoutingSession(SignallingSession):
"""
继承SignallingSession， 重写`get_bind` 使其支持读写分离
"""
def __init__(self, *args, **kwargs):
super(RoutingSession, self).__init__(*args, **kwargs)
def get_bind(self, mapper=None, clause=None):
state = get_state(self.app)
if mapper is not None:
try:
persist_selectable = mapper.persist_selectable
except AttributeError:
persist_selectable = mapper.mapped_table
info = getattr(persist_selectable, 'info', {})
bind_key = info.get('bind_key')
if bind_key is not None:
return state.db.get_engine(self.app, bind=bind_key)
# 重点是以下代码↓↓↓↓↓↓
from sqlalchemy.sql.dml import UpdateBase
if self._flushing or isinstance(clause, UpdateBase):
# 此时说明是数据修改类型（增删改）走主库
print("user master DB")
return state.db.get_engine(self.app, bind="master")
else:
# 此时说明是数据查询，走主库或从库
slave_name = random.choice(list(current_app.config.get("SQLALCHEMY_BINDS").keys())) # 随机从SQLALCHEMY_BINDS里面选一个下标
print("user %s DB" % slave_name)
return state.db.get_engine(self.app, bind=slave_name)
重写SQLAlchemy类的create_session方法
class RoutingSQLAlchemy(SQLAlchemy):
"""
重写 `create_session` 使其使用`RoutingSession`
"""
def create_session(self, options):
return orm.sessionmaker(class_=RoutingSession, db=self, **options)
修改db对象
原写法
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
新写法
import random
from flask import current_app
from flask_sqlalchemy import SQLAlchemy, SignallingSession, get_state
from sqlalchemy import orm
db = RoutingSQLAlchemy() # 此类为上面重写后的SQLAlchemy类
393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
