mysql构建主从结构，mysql负载均衡和读写分离

一、修改mysql配置

现有两台虚拟机mysql版本都是5.7
master(主)： 192.168.23.133
slave(从)： 192.168.23.134
mysql安装参考：
https://blog.csdn.net/wangyue23com/article/details/121618751

vim /etc/my.cnf

两个mysql数据库都添加如下配置（也可以默认，不做配置）：

#设置utf-8字符格式
character_set_server=utf8
init_connect='SET NAMES utf8'

下面master(主）/etc/my.cnf 增加配置：

#要给从机同步的库（如果不写，默认全部同步）
#binlog-do-db=db01
#不给从机同步的库(多个写多行)
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
binlog-ignore-db=sys
#自动清理 7 天前的log文件,可根据需要修改
expire_logs_days=7
# 启用二进制日志
log-bin=master-bin
# 服务器唯一ID
server-id=1
log_bin_index = master-bin.index

然后修改slave(从）/etc/my.cnf 增加配置：

#服务器唯一ID
server-id = 2
#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log = slave-relay-bin
#主服务器I/O日志读取、记录及存放
relay-log-index = slave-relay-bin.index

上面刚修改完配置，主从数据库都需要重启mysql来使配置生效。

systemctl restart mysqld

二、配置mysql主从连接

1. 主mysql数据库操作

master(主)服务器登录mysql命令行：

mysql -uroot -p

查看master状态信息：

show master status;

下面的File和Position参数，配置从数据库时候要用。

2. 从mysql数据库操作

下面开始配置slave(从)数据库：

#登录slave（从）数据库
mysql -uroot -p

需要修改下面命令：
master_log_file: 上图的File值。
master_log_pos：上图的Position值。

change master to master_host='主服务器ip地址',master_port=3306,master_user='链接主服务器账户',master_password='链接主服务器角色账户密码',master_log_file='主服务器file参数',master_log_pos=主服务器positon参数;

例如我执行的命令如下，dbroot是我自己新建的mysql用户：

#关闭slave，关闭后就可以执行下面master配置了
stop slave;
#执行关联master命令
change master to master_host='192.168.23.133',master_port=3306,master_user='dbroot',master_password='Root123456!',master_log_file='master-bin.000005',master_log_pos=154;
#启动slave，启动后主库有数据DML，则从库也会跟着同步。
start slave;

下图是执行效果：

查看slave（从）数据库跟主库连接状态：
如果Slave_IO_Running和Slave_SQL_Running 都为YES，则说明配置成功

show slave status\G;

下图代表连接成功。

然后就可以自己连主从两个数据库操作了，修改主数据库，从数据库会被同步。

三、出现的一些问题

1. 重启主库后，从库数据不跟着同步了。

主库如果修改配置或者重启后，File和Position参数会发生变化，slave（从）库需要重新执行关联主库命令。

#登录mysql命令行
mysql -uroot -p
#关闭slave，重新执行命令
stop slave;
#执行关联master命令
change master to master_host='192.168.23.133',master_port=3306,master_user='dbroot',master_password='Root123456!',master_log_file='master-bin.000005',master_log_pos=154;
#启动slave，启动后主库有数据DML，则从库也会跟着同步。
start slave;

再次执行从库状态命令查看连接状态：

show slave status\G;

2. 无法连接主库。

查询主库防火墙是否开着，放开端口。

#查看防火墙状态
firewall-cmd --state
#永久放开3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙
firewall-cmd --reload

3. 遇到其他不明错误。

查看mysql日志，根据日志提示再寻找解决线索：

tail -f /var/log/mysqld.log 

假如/var/log/mysqld.log出现：

error connecting to master 'root@123.57.133.22:3307' - retry-time: 60  retries: 7, Error_code: 1130

解决MySQL 1130 错误，无法远程连接
原因：mysql服务器 root 默认只允许 localhost 也就是 127.0.0.1 连接
登录主 mysql:

mysql -u root -p
use mysql;
#将root权限授权给任何ip可以访问
update user set host = '%' where user ='root';
flush privileges;

然后再次操作从mysql，最好主库和从库都修改下，看自己需要。

stop slave;
start slave;

然后问题解决。

四、读写分离

1. Mycat实现读写分离

Mycat是什么？是数据库中间件，介于数据库与应用之间，进行数据处理与交互的中间件服务，可以简单的理解成数据库代理，我们的应用只需要与数据库中间件交互，而无需关注复杂的数据库部署。用户只需要一个Mycat的服务地址和端口来连接数据库，便是实现查询会走slave节点，增删改走master节点，对master节点数据库的增、删、改操作最终会同步到slave节点。

好处：
1.分摊服务器压力，提高机器的系统处理效率
2.增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务

2. MySQL Router方式

Router作为一个流量转发层，位于应用与MySQL服务器之间，其功能类似于LVS。MySQL Servers作为Router的“downstream”（NAT模式），应用不再直连MySQL Servers，而是与Router相连。根据Router的配置，将会把应用程序的读写请求转发给下游的MySQL Servers。 当下游有多个MySQL Servers，无论主、从，Router可以对读写请求进行负载均衡。当下游某个Server失效时，Router可以将其从Active列表中移除，当其online后再次加入Active列表，即提供了Failover特性。

好处：
1.当MySQL Servers集群拓扑变更时，比如增减Slaves节点，只需要修改Router的配置即可，无需修改应用中数据库连接配置。
2.感知MySQL Servers的主从切换、从库增减等集群拓扑变更，而且基于变更能够实现Master自动切换、Slaves列表自动装配等。
3.Master失效后，Cluster将会自动选举一个新的Master，此时Router不需要任何调整、可以自动发现此新Master进而继续为应用服务。
缺点：
1.不支持分库分表；
2.代码层需要配置两个端口来区分 写操作和查询操作，比如7001端口是写操作，7002是读操作。这样就不方便，使用一个数据库连接便能方便的读写。

3. 代码层实现读写分离

项目代码集成多数据源支持，在查询操作时候，标记访问slave数据库，在增删改操作时候，修改Master数据库。