How to achieve read and write separation in mysql master-slave based on docker and django-Mysql Tutorial-php.cn

How to achieve read and write separation in mysql master-slave based on docker and django

王林

Release： 2023-06-01 15:07:12

forward

975 people have browsed it

1. Master-slave construction

The process or principle of slave synchronization:

1) The master will record the changes in the binary log ;
2) The master has an I/O thread to send the binary log to the slave;
3) The slave has an I/O thread Write the binary sent by the master into the relay log;
4) The slave has a SQL thread and processes the slave data according to the relay log.

Practical operation

Create two folders:

mkdir  /home/mysql/data/
touch /home/mysql/conf.d
touch /home/mysql/my.cnf
 
mkdir  /home/mysql2/data/
touch /home/mysql/conf.d
touch /home/mysql/my.cnf

Copy after login

Modify the configuration file:

Configuration file of the main library, server-id and enable binlog log

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
server-id=100  
log-bin=mysql-bin
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

Copy after login

Configuration of the slave library:

The following two lines are added

log-bin=mysql-slave-bin #Specify log
relay_log=edu-mysql-relay-bin Specify relay log

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
server-id=101 
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

Copy after login

Pull up two A mysql container:

#启动主库容器（挂载外部目录，端口映射成33307，密码设置为123456）
docker run  -di -v /home/mysql/data/:/var/lib/mysql -v /home/mysql/conf.d:/etc/mysql/conf.d -v /home/mysql/my.cnf:/etc/mysql/my.cnf -p 33307:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
#启动从库容器（挂载外部目录，端口映射成33306，密码设置为123456）
docker run  -di -v /home/mysql2/data/:/var/lib/mysql -v /home/mysql2/conf.d:/etc/mysql/conf.d -v /home/mysql2/my.cnf:/etc/mysql/my.cnf -p 33306:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

Copy after login

Create the test user and authorize

#在主库创建用户并授权
##创建test用户
create user &#39;test&#39;@&#39;%&#39; identified by &#39;123&#39;;
##授权用户
grant all privileges on *.* to &#39;test&#39;@&#39;%&#39; ;
###刷新权限
flush privileges;
#查看主服务器状态(显示如下图)
show master status;
# 可以看到日志文件的名字，和现在处在哪个位置

Copy after login

to connect to the slave library and configure the connection to the main library

Use the command "show slave status\G" to check whether the master_log_file is the same

#连接从库
mysql -h 172.16.209.100 -P 33306 -u root -p123456
#配置详解
/*
change master to 
master_host=&#39;MySQL主服务器IP地址&#39;, 
master_user=&#39;之前在MySQL主服务器上面创建的用户名&#39;， 
master_password=&#39;之前创建的密码&#39;, 
master_log_file=&#39;MySQL主服务器状态中的二进制文件名&#39;, 
master_log_pos=&#39;MySQL主服务器状态中的position值&#39;;
*/
# 输入命令如下
change master to master_host=&#39;101.133.225.166&#39;,master_port=33307,master_user=&#39;test&#39;,master_password=&#39;123&#39;,master_log_file=&#39;mysql-bin.000003&#39;,master_log_pos=0;
# 启用从库
start slave;
# 查看从库状态（如下图）
show slave status\G;
####这两个是yes表示配成功 ####
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

Copy after login

Test:

#在主库上创建数据库test1
create database test1;
use test1;
#创建表
create table tom (id int not null,name varchar(100)not null ,age tinyint);
#插入数据
insert tom (id,name,age) values(1,&#39;xxx&#39;,20),(2,&#39;yyy&#39;,7),(3,&#39;zzz&#39;,23);

Copy after login

2.django implements read and write separation

settings.py configuration

#1  在setting中配置
DATABASES = {
    # 主库
    &#39;default&#39;: {
        &#39;ENGINE&#39;: &#39;django.db.backends.mysql&#39;,
        &#39;NAME&#39;: &#39;lqz1&#39;,
        &#39;USER&#39;: &#39;root&#39;,
        &#39;PASSWORD&#39;: &#39;123456&#39;,
        &#39;HOST&#39;: &#39;101.133.225.166&#39;,
        &#39;PORT&#39;: 33307,
    },
    # 从库
    &#39;db1&#39;: {
        &#39;ENGINE&#39;: &#39;django.db.backends.mysql&#39;,
        &#39;NAME&#39;: &#39;lqz1&#39;,
        &#39;USER&#39;: &#39;root&#39;,
        &#39;PASSWORD&#39;: &#39;123456&#39;,
        &#39;HOST&#39;: &#39;101.133.225.166&#39;,
        &#39;PORT&#39;: 33306,
    },
}

Copy after login

Manually specify read-write separation

####手动来做
    # 向default库写，主库
    res=models.Book.objects.using(&#39;default&#39;).create(name=&#39;金瓶梅&#39;,price=33.4)
    # 去从库查
    res=models.Book.objects.using(&#39;db1&#39;).all().first()
    print(res.name)

Copy after login

Automatically specify (write router and configure setting)

Create a db_router.py# in the root directory ##

class Router1:
    def db_for_read(self, model, **hints):
        return &#39;db1&#39;
    def db_for_write(self, model, **hints):
        return &#39;default&#39;

Copy after login

# 注册一下 # 4 以后只要是写操作，就会用default，只要是读操作自动去db1
	DATABASE_ROUTERS = [&#39;db_router.Router1&#39;,]

Copy after login

More fine-grained (required when sub-database and table sub-database)

class Router1:
    def db_for_read(self, model, **hints):
        if model._meta.model_name == &#39;book&#39;:
            return &#39;db1&#39;
        else:
            return &#39;default&#39;
 
    def db_for_write(self, model, **hints):
        return &#39;default&#39;

Copy after login

When migrating the database, you can specify which app’s table structure to migrate to Which library

# django默认是default
python manage.py migrate app01 --database=default

Copy after login

The above is the detailed content of How to achieve read and write separation in mysql master-slave based on docker and django. For more information, please follow other related articles on the PHP Chinese website!