Django 主从复制后的路由表实现读写分离

DB-路由表

通过编写db-router可以灵性连接mysql主从复制等情况下的多数据库

• 假设你已经配置好了类似MYSQL主从复制的服务

配置Django数据库连接

• settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': '192.168.1.101',
        'PORT': 3306,
        'USER': 'root',
        'PASSWORD': '123456',
        'NAME': 'test1'
    },
    'slave': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': '192.168.1.102',
        'PORT': 3306,
        'USER': 'root',
        'PASSWORD': '123456',
        'NAME': 'test1'
    }
}

• 编写db-router

class MasterSlaveDBRouter:
    """数据库主从读写分离路由"""

    def db_for_read(self, model, **hints):
        """读数据库"""
        return "slave"

    def db_for_write(self, model, **hints):
        """写数据库"""
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        """是否运行关联操作"""
        return True

• 编写完成路由表之后，在settings文件中进行注册

# settings.py

DATABASE_ROUTERS = ['pro.utils.MasterSlaveDBRouter'] 

• 如出现以下错误，修改主库binlog日志格式

django.db.utils.InternalError: (1665, 'Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.')

binlog_format=mixed

打开日志用以分析

• 可以通过配置mysql日志记录，用以查看主从在读写情况下的效果

general_log=1
general_log_file=/var/lib/mysql/general.log