MySQL 分布式主从读写分离架构及实战

用户3147702

发布于 2022-06-27 14:01:00

5250

发布于 2022-06-27 14:01:00

1. 概述

随着网站业务的不断发展，用户量不断增加，数据量成倍增长，数据库的负载压力也越来越大，同时，为了防止数据库异常造成的数据丢失，我们常常需要两台或多态数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台。网站可以利用 mysql 提供的特性，实现数据库读写分离，从而改善数据库的负载能力，并提供稳定的服务依托。

如上图，所谓的服务器读写分离就是让 web 服务器在写数据的时候选择主服务器，主数据库通过主从复制机制将数据更新同步到从数据库。这样当 web 服务器读取数据的时候，就可以通过从数据库获取数据。这一方案使得大量度操作的 web 应用可以轻松读取数据，而主数据库也只会承受少量写入操作，并且实现了热备份，收到一举两得的效果。

2. MySQL 数据复制原理

MySQL 主从备份共分为三个步骤：

Master 将改变记录到二进制日志 binlog 中
Slaver 将 Master 的二进制日志事件拷贝到他的中继日志 relay log 中
Slaver 重做中继日志中的事件，将 Master 上的改变反映到自己数据库中

Slaver 服务器中有一个 IO 线程在不断监听 Master 的 binlog 是否有更新，从而在 binlog 更新后进行上述第二步操作。同时，Slaver 中还有一个 SQL 线程，从中继日志中读取事件，完成上述第三步操作。

3. Master 的配置

安装完成 mysql 后，修改 /etc/my.cnf，添加下列配置：

[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin=master-bin
log-bin-index=master.bin.index
server-id=1

同时要记得去掉下面的配置：

bind-address       = 127.0.0.1

否则只能用本机登录 mysql。

然后执行下列命令重启 mysql。

sudo service mysql stop
sudo service mysql start

执行：

sudo netstat -tap | grep mysql

可以看到：

说明 mysql 已启动。

同时，打开 mysql，执行：

SHOW MASTER STATUS;

可以看到：

4. 授权

Slaver 想要访问 Master，必须 Master 授予相应的权限：

create user mysql_msuser;
GRANT REPLICATION SLAVE ON *.* TO 'mysql_msuser'@'192.168.1.141' IDENTIFIED BY 'mysql_slave';

首先创建了用户 mysql_msuser 并为该用户授予了 REPLICATION SLAVE 权限，密码为 mysql_slave，这个用户所在服务器 IP 地址为 192.168.1.141，除此之外的其他权限则无需添加。此时，你可以登录到 Slaver 机器上查看是否可以通过远程登录 Master 上的 mysql。

5. Slaver 配置

在从服务器上，同样要打开 MySQL 的配置文件，添加下列配置：

[client]
port=3306
default-character-set=utf8

[mysqld] port=3306
#character_set_server=utf8 一定要这样写;
character_set_server=utf8

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这个有问题，在创建完新用户登录时报错
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#从服务器的配置
#01.为服务器添加唯一的编号
server-id=2
#02.开启中继日志
relay-log=slave-relay-log-bin
#03.使用中继日志的索引文件
relay-log-index=slave-relay-log-bin.index

与 master 一样，更新配置之后需要重启 mysql 服务。

6. 指定主从关系

使用 root 帐号登录 mysql，执行下列语句配置主从关系：

change master to
master_host="192.168.1.140",
master_port=3306,
master_user="mysql_msuser",
master_password="mysql_slave",
master_log_file="mysql-bin.000001",
master_log_pos=0;

start slave;

到这一步配置工作就已经大功告成了。可以执行：