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

概述

随着网站业务的不断发展，用户量不断增加，数据量成倍增长，数据库的负载压力也越来越大，同时，为了防止数据库异常造成的数据丢失，我们常常需要两台或多态数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台

网站可以利用 mysql 提供的特性，实现数据库读写分离，从而改善数据库的负载能力，并提供稳定的服务依托

 

如上图，所谓的服务器读写分离就是让 web 服务器在写数据的时候选择主服务器，主数据库通过主从复制机制将数据更新同步到从数据库

这样当 web 服务器读取数据的时候，就可以通过从数据库获取数据

这一方案使得大量度操作的 web 应用可以轻松读取数据，而主数据库也只会承受少量写入操作，并且实现了热备份，收到一举两得的效果

 

MySQL 数据复制原理

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

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

 

Slaver 服务器中有一个 IO 线程在不断监听 Master 的 binlog 是否有更新，从而在 binlog 更新后进行上述第二步操作

同时，Slaver 中还有一个 SQL 线程，从中继日志中读取事件，完成上述第三步操作

 

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;

 

可以看到：

 

 

授权

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

 

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 服务

 

指定主从关系

使用 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;

 

 

到这一步配置工作就已经大功告成了

可以执行：

show slave status \G

 

查看 slave 状态：

 

 

可以看到：

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

说明 slave 的 IO 和 SQL 线程都已经开始运行

 

同步失败处理

千万要注意，从库只读，轻易不可以进行写操作，否则可能造成同步失败

对于同步失败的情况，首先执行 stop slave 停止 slave 服务，然后手动同步到当前状态

然后通过指定 master_log_pos 重新执行 change master to 语句，start slave 即可