-
Mysql-master.cnf
[mysqld] ; 设置server_id，在同一局域网中需要唯一 server_id = 1 ; 指定不需要同步的数据库名称 binlog-ignore-db = mysql ; 指定需要同步的数据库 ; binlog-do-db=db1 ; 开启二进制日志功能 log-bin = mysql-bin ; 数据库时区 default-time_zone='+8:00' ; 设置二进制日志使用内存大小（事务） binlog_cache_size=1M ; 设置使用的二进制日志格式（row-行级别；statement=语句级别；mixed-混合级别） binlog_format=mixed ; 二进制日志过期清理时间。默认为0，表示不自动清理 expire_logs_days=7 log-slave-updates = 1 datadir = /var/lib/mysql pid-file=/var/run/mysqld/mysqld.pid
-
mysql-slave.cnf
[mysqld] ; 设置server_id，在同一局域网中需要唯一 server_id = 2 ; 指定不需要同步的数据库名称 binlog-ignore-db = mysql ; 指定需要同步的数据库 ; binlog-do-db=db1 ; 开启二进制日志功能 log-bin = mysql-bin ; 数据库时区 default-time_zone='+8:00' ; 设置二进制日志使用内存大小（事务） binlog_cache_size=1M ; 设置使用的二进制日志格式（row-行级别；statement=语句级别；mixed-混合级别） binlog_format=mixed ; 二进制日志过期清理时间。默认为0，表示不自动清理 expire_logs_days=7 ; relay_log配置中继日志 relay_log = mysql-relay-bin port = 3307 datadir = /var/lib/mysql pid-file=/var/run/mysqld/mysqld.pid
-
start docker
# 启动mysql master docker run -d -p 3306:3306 --privileged=true -v ~/code/beacon_server/scripts/mysql/mysql-master.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=xxxxx --name mysql-master mysql:5.7 # 启动mysql slave docker run -d -p 3307:3307 --privileged=true -v ~/code/beacon_server/scripts/mysql/mysql-slave.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=xxxx --name mysql-slave mysql:5.7 # 进入master容器内创建数据同步用户 CREATE USER 'slave'@'%'IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%'; show master status # 查看master状态，并记录File跟Position的值 # 进入slave容器内配置主从同步 change master to master_host='宿主机IP',master_user='slave',master_password='123456',master_port=3306, master_log_file='mall-mysql-bin.000001',master_log_pos=154,master_connect_retry=30; start slave show slave status G; # 查看主从同步状态 Slave_IO_Running和Slave_SQL_Running都为Yes即配置成功
-
config.py
SQLALCHEMY_BINDS = { 'master': 'mysql+pymysql://root:xxx@xxx.xxx.xxx.xxx:3306/test?charset=utf8', 'slave': 'mysql+pymysql://root:xxx@xxx.xxxx.xxx.xxx:3307/tedst?charset=utf8', } SQLALCHEMY_TRACK_MODIFICATIONS = False SQLALCHEMY_ECHO = True -
Routing_sqlalchemy.py
# -*- coding: utf-8 -*- from flask_sqlalchemy import SQLAlchemy, SignallingSession, get_state from sqlalchemy import orm from sqlalchemy.sql.dml import UpdateBase class RoutingSession(SignallingSession): """ 自定义Session类 """ def __init__(self, db, autocommit=False, autoflush=True, **options): SignallingSession.__init__(self, db, autocommit=autocommit, autoflush=autoflush, **options) 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) if self._flushing or isinstance(clause, UpdateBase): print("写操作") return state.db.get_engine(self.app, bind='master') else: print("读操作") return state.db.get_engine(self.app, bind='slave') class RoutingSQLAlchemy(SQLAlchemy): """ 自定义数据库路由的sqlalchemy """ def create_session(self, options): return orm.sessionmaker(class_=RoutingSession, db=self, **options) db = RoutingSQLAlchemy()
