mysql主从分离及负载均衡配置

一.说明

1.前置条件

如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负载均衡

  • 已安装mysql
  • 已安装nginx

2.环境介绍

(主)环境 :linux(centos 7.0.2) Mysql(5.7.22) IP:192.168.20.43

(从)环境 :linux(centos 7.0.2) Mysql(5.7.20) IP:192.168.20.44

(均衡)环境 :linux(centos 7.0.2) Mysql(5.7.20) IP:192.168.20.45

二.主环境配置（192.168.20.43）

1.修改mysql的配置文件

mysql通常为my.cnf和my.ini,我的配置文件路径是:/etc/my.cnf

1
2
3
4
5
6
7
8

[mysqld]
log-bin=mysql-bin    (必须配置)   启用二进制日志
server-id=1          (必须配置)    为本台服务器配置唯一的Server ID
auto_increment_offset = 1   #这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
auto_increment_increment = 1   #这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突
log_bin_trust_function_creators = 1  #将函数复制到slave
binlog-do-db=test_sys  （可选配置）要同步的数据库名,要同步多个数据库,就多加几个replicate-db-db=数据库名
binlog-ignore-db=mysql （可选配置）//要忽略的数据库

2.添加从库权限账号

1
2
3
4
5
6

#进入mysql命令行
create user as_test;
#GRANT REPLICATION SLAVE ON *.* TO 'as_test'@'192.168.20.166' IDENTIFIED BY '123456';
grant replication slave,replication client on *.* to 'slave'@'192.168.20.166' identified by 'slpass';

flush privileges;      //刷新权限

3.重启主服务器

1
2

/bin/systemctl restart mysqld.service(centos)
/bin/systemctl restart mysql.service(ubuntu)

4.查看主服务器状态

1
2

#进入mysql命令行
show master status\G;

5.注意事项

  • 如果你不配置server-id或者配置值为0,那么主服务器将拒绝所有从服务器的连接。

  • 在使用InnoDB的事务复制,为了尽可能持久和数据一致,你应该在my.cnf里配置

    1

    nnodb_flush_log_at_trx_commit=1 和 sync_binlog=1

  • 确保主服务器里的skip-networking选项未启用,如果网络被禁用,你的从服务器将不能与主服务器通信并且复制失败

三.从环境配置（192.168.20.44）

1.修改mysql的配置文件

mysql通常为my.cnf和my.ini,我的配置文件路径是:/etc/my.cnf

1
2
3
4
5
6
7
8
9

[mysqld]
log-bin=mysql-bin    (必须配置)   启用二进制日志
server-id=2          (必须配置)    为本台服务器配置唯一的Server ID
replicate-do-db=test_sys  （可选配置）#在master端不指定binlog-do-db,在slave端用replication-do-db来过滤,要同步多个数据库,就多加几个replicate-db-db=数据库名
binlog-ignore-db=mysql （可选配置）//要忽略的数据库
#relay-log=mysqld-relay-bin
relay_log = relay-log  #开启中继日志
read_only = ON  #只读开启,实现读写分离
super_read_only = 1 #root用户只读开启,实现读写分离

2.配置连接主服务器的信息

1
2
3
4
5
6

#注意:MASTER_LOG_FILE和MASTER_LOG_POS的值要根据主数据库show master status中的File和 Position来修改
#change master to master_host='192.168.20.44',master_user='as_test',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=120;
CHANGE MASTER TO MASTER_HOST='192.168.20.44',MASTER_USER='slave',MASTER_PASSWORD='slpass',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1981;

flush privileges;      //刷新权限
start slave;

3.重启从服务器

1
2

/bin/systemctl restart mysqld.service(centos)
/bin/systemctl restart mysql.service(ubuntu)

4.查看从服务器状态

1
2
3
4
5
6
7
8
9

show slave status\G;

Master_Log_File: mysql-bin.000006   #复制的二进制文件
Read_Master_Log_Pos: 4  #从哪个pos位置开始复制
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No    #复制功能还没有启动
Slave_SQL_Running: No   #复制功能还没有启动

5.注意事项

  • 如果有多个从服务器,每个服务器的server-id不能重复,跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器
  • 一般你不需要在从服务器上启用二进制日志,如果你在从服务器上启用二进制日志,那你可用它来做数据备份和崩溃恢复,或者做更复杂的事情（比如这个从服务器用来当作其它从服务器的主服务器）

四.FAQ

在搭建mysql主主复制的时候,创建一个新的具有replicaion权限的新用户,刚开始的操作如下:

1

mysql> GRANT REPLICATION SLAVE ON test.* TO ‘server01’@’%’ IDENTIFIED BY ‘server01’;

会提示错误:ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

错误的原因是因为,replication权限是一个全局权限,不可以指定在某一个库上,所以,把创建用户语句更改为如下即可:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> GRANT REPLICATION SLAVE ON . TO ‘server01’@’192.168.40.129’ IDENTIFIED BY ‘568888’;
Query OK, 0 rows affected (0.00 sec)

ok,一切正常,但是,为了安全起见,在创建新的用户的时候,最好指定登陆地址,不然别人拿着你的账号就要干坏事儿了,虽然这个账号只具备replication权限,但是也是具有危险性的。所以,最后的语句更改为:

mysql> GRANT REPLICATION SLAVE ON . TO ‘server01’@’192.168.40.129’ IDENTIFIED BY ‘568888’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

五.mysql负载均衡（192.168.20.45）

1.在nginx.conf文本最后一行添加下述代码即可:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

stream {
    server {
       listen 3306;
       proxy_pass db;
    }
    upstream db {
       server 192.168.18.130:3305;
       server 192.168.18.129:3305;
    }
}

2.注意事项

  • 读数据时，使用配置后的ip和端口，即读取数据时，从两个数据库读取；

  • 写数据时，只用主库链接

  • mysql端口设置：

    代码需要在nginx1.9版本以上才支持,上述配置中监听了3306端口,即3306端口将被nginx占用,而mysql的默认使用端口为3306,这将导致mysql无法启动,所以需要将mysql的端口号改为其他端口（本文中将其设置成3305,这将会在mysq主主同步的博文中说明）,proxy_pass即为代理地址

六.测试

1.主从复制测试

  • 在主库写入数据，然后查看从库是否有数据

2.负载均衡测试

  • 在主库写入新数据
  • 在从库修改刚刚写入的数据
  • 使用192.168.20.45访问mysql数据库，每次刷新可以看到不同的数据