MySQL主从搭建

一 主从配置原理

• master(主库)会将变动记录到二进制日志(binlog)里面；
• slave(从库)有一个I/O线程将二进制日志拉取;
• slave有一个I/O线程把master的二进制写入到relay日志里面；
• slave有一个SQL线程，按照relay日志回放自己的库中。

二 搭建步骤（基于两个docker容器）

首先用docker拉取mysql镜像。

docker pull mysql:5.7
1

配置两台机器环境，创建两个文件夹，做目录映射，一个给主，一个给从。

mkdir /home/mysql
mkdir /home/mysql/conf.d
mkdir /home/mysql/data/
# 创建my.cnf配置文件
touch /home/mysql/my.cnf

mkdir /home/mysql2
mkdir /home/mysql2/conf.d
mkdir /home/mysql2/data
touch /home/mysql2/my.cnf
1
2
3
4
5
6
7
8
9
10

编写mysql 主库的配置文件

vim /home/mysql/my.cnf

# server-id 不能重复，开启bin-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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

编写mysql 从库配置文件

vim /home/mysql2/my.cnf

# server-id不能重复，开启log-bin开启 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

启动主库和从库

# 启动主库容器（挂载外部目录，端口映射成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
1
2
3
4
5

cmd远程连接主库，执行命令。

mysql -h 139.224.115.169 -P 33307 -u root -p

# 创建一个用户
create user 'test'@'%' identified by '123';
# 授予所有表的权限
grant all privileges on *.* to 'test'@'%' ;
# 刷新权限
flush privileges;
# 查看主服务器状态(显示如下图)   bin log日志的位置
show master status; 
1
2
3
4
5
6
7
8
9
10

cmd远程连接从库，执行命令。

mysql -h 139.224.115.169 -P 33306 -u root -p

# 配置详解
'''
        change master to 
        master_host='MySQL主服务器IP地址', 
        master_user='之前在MySQL主服务器上面创建的用户名'， 
        master_password='之前创建的密码', 
        master_log_file='MySQL主服务器状态中的二进制文件名', 
        master_log_pos='MySQL主服务器状态中的position值';
'''

change master to master_host='139.224.115.169',master_port=33307,master_user='test',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=0;

# 启用从库
start slave;

# 查看从库状态（如下图）
show slave status\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试主从同步。

create database test1;
use test1;
create table tab(id int primary key auto_increment,name varchar(32));
insert into tab(name) values('xuxiaoxu'),('jasper');

# 在从库查询
use test1;
select * from tab;
1
2
3
4
5
6
7
8

三 django实现读写分离

在搭建好 MySQL 的主从后，执行下面的步骤：

3.1 配置数据库

在 django 的项目配置文件中：

DATABASES = {
    # 主库，django 默认使用 default 中的数据库
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test1',
        'USER': 'root',
        'PASSWORD': '123456',
        'HOST': '139.224.115.169',
        'PORT': 33307
    },
    # 从库
    'db1': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test1',
        'USER': 'root',
        'PASSWORD': '123456',
        'HOST': '139.224.115.169',
        'PORT': 33306,
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.2 models中创建表

class Books(models.Model):
    name = models.CharField(max_length=32)
    price = models.IntegerField()
1
2
3

3.3 数据库迁移

python manage.py makemigrations
python manage.py migrate    # 默认迁移 default 数据库
python manage.py migrate app01 --database=db1   # 指定要迁移的数据库
1
2
3

3.4 指定要使用的数据库

手动指定

# 向主库写
models.Books.objects.using('default').create(name='三国', price=100)
# 到从库查
res=models.Books.objects.using('db1').all().first()
1
2
3
4

自动指定
通过配置数据库路由，来自动实现读写分离，这样就不需要每次读写都手动指定数据库。

创建一个db_router.py文件，并在该文件中定义一个数据库路由类，并实现2个方法：

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

    def db_for_read(self, model, **hints):
        """用于读的数据库"""
        return "db1"

    def db_for_write(self, model, **hints):
        """用于写的数据库"""
        return "default"
1
2
3
4
5
6
7
8
9
10

在配置文件中配置

DATABASE_ROUTERS = ['db_router.Router', ]
1

四 Pycharm远程连接服务器进行代码的运行与调试

文章超链接

五 Pycharm远程链接docker开发

5.1 操作 docker宿主机

# 1 配置运行远程客户端连接docker
vim /lib/systemd/system/docker.service

#ExecStart=/usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock
# 改成
ExecStart=/usr/bin/dockerd -H tcp://0.0.0.0:2376 -H unix:///var/run/docker.sock

# 2 /etc/profile 写入
执行命令：echo 'export DOCKER_HOST=tcp://0.0.0.0:2376' >> /etc/profile
执行命令：source /etc/profile

# 3 
vim /etc/docker/daemon.json

{
  "hosts": ["tcp://0.0.0.0:2376","unix:///var/run/docker.sock"]
}

# 4 重启docker
systemctl daemon-reload && systemctl restart docker

# 5 测试
	# 虚拟机执行
	curl 127.0.0.1:2376/version
    # win执行
    http://139.224.115.169:2376/version
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

5.2 pycharm配置

1 打开设置，搜索docker
2 选择TCP socket
3 填入：tcp://139.224.115.169:2376
4 Certificates folder暂时不填，Path mappings：暂时删掉
5 点击ok，在下面就能看到图形界面的管理
1
2
3
4
5

添加docker解释器
1