Debian 配置 Mysql 数据库主从,读写分离,负载均衡

MySQL数据库主从复制技术主要有以下一些特点:

  1. 数据分布 (Data distribution )
  2. 负载均衡(load balancing)
  3. 备份(Backups)
  4. 高可用性和容错性 (High availability and failover)

利用这些特点完成能实现多种多样的用途

  1. 实时灾备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。
  2. 读写分离,提供优质的查询服务。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。
  3. 备份,避免影响业务

主从形式也是多种多样:

  1. 一主一从
  2. 互为主从(主主复制)
  3. 一主多从
  4. 多主一从

MySQL主从复制的原理

主从原理

  1. master在执行某sql之后,记录二进制log文件(bin-log)。
  2. 在slave端的IO线程连接上master端后,会请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。
  3. master端在接收到来自slave端的IO线程请求后,通知负责复制进程的IO线程,根据slave端IO线程的请求信息,读取指定 binlog日志指定pos节点位置之后的日志信息,然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外,还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
  4. slave端的IO线程在接收到master端IO返回的信息后,将接收到的binlog日志内容依次写入到slave端的relay log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的master端的binlog文件名和pos节点位置记录到master- info(该文件存在slave端)文件中,以便在下一次读取的时候能够清楚的告诉master,我需要从哪个binlog文件的哪个pos节点位置开 始,请把此节点以后的日志内容发给我。
  5. slave端的SQL线程在检测到relaylog文件中新增内容后,会马上解析该log文件中的内容。然后还原成在master端真实执行的真实SQL语句,并在自身按顺序依次执行这些SQL语句。

MySQL主从复制的缺点

吹了那么多东西,也说说缺点吧!

  1. 主从间的数据库不是实时同步,就算网络连接正常,也存在瞬间主从数据不一致的情况。
  2. 如果主从的网络断开,则从库会在网络恢复正常后,批量进行同步。
  3. 如果对从库进行修改数据,那么如果此时从库正在在执行主库的bin-log时,则会出现错误而停止同步,这个是很危险的操作。所以一般情况下,我们不在从库上修改数据。

注意:

  1. 一个衍生的配置是双主、互为主从配置,只要双方的修改不冲突,则可以工作良好。
  2. 如果需要多主库的话,可以用环形配置,这样任意一个节点的修改都可以同步到所有节点。

项目场景

  1. 主服务器10.10.20.111,其中已经有数据库且库中较多,读写操作频繁。
  2. 从服务器10.10.20.116,空的啥也没有

由于数据库中数据较多且数据库设计不合理,导致很多SQL慢查询,虽然进行了 SQL慢查询优化 ,但是由于读写频繁,服务器压力大,所以提案了两种方案优化。

  1. 互为主从并用nginx实现负载
  2. 读写分离,使用mysql中间件提供负载均衡及连接池

最后选择了配置互为主从并用nginx实现负载均衡。当然我在文章最后也会提供Mysql中间件实现读写分离的方法

mysql主从搭建

准备工作

因为主从服务器需要有相同的初态,所以首先要将主库的数据备份并恢复到从库。

1、将主服务器要同步的数据库加锁,避免同步时数据发生改变

1
2
mysql>use db;
mysql>flush tables with read lock;

2、将主服务器数据库中数据导出

1
mysql>mysqldump -uroot -pxxxx db > db.sql;

这个命令是导出数据库中所有表结构和数据

3、将初始数据导入从服务器数据库

1
mysql>mysqldump -uroot -pxxxx db < db.sql;

主服务器配置

1、修改主MySQL配置

1
2
3
4
5
6
7
8
9
10
11
12
nano /etc/mysql/mariadb.conf.d/50-server.cnf
在[mysqld]中找到server-id,完成以内容的修改

#主数据库端ID号,通常设置为ip的最后一个10进制位,这里我设置为1
server_id = 1
#开启二进制日志,这里是二进制日志的前缀名称及其存放地点
log-bin = /var/log/mysql/mysql-bin
#需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
binlog-do-db = db
##binlog-do-db = db1
#二进制日志自动删除的天数,默认值为0,表示“没有自动删除”,启动时和二进制日志循环时可能删除
expire_logs_days = 7

2、重启MySQL,创建允许从服务器同步数据的账户

1
2
3
4
#创建slave账号account,密码123456
mysql>grant replication slave on *.* to 'account'@'10.10.20.116' identified by '123456';
#更新数据库权限
mysql>flush privileges;

3、查看主服务器状态

1
2
3
4
5
6
mysql>show master status/G;
***************** 1. row ****************
File: mysql-bin.000033 #当前记录的日志
Position: 337523 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:

从服务器配置

1、修改从MySQL配置

1
2
3
4
5
6
7
8
9
10
11
nano /etc/mysql/mariadb.conf.d/50-server.cnf
在[mysqld]中添加

server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#指定slave要复制哪个库
replicate-do-db = db
#MySQL主从复制的时候,当Master和Slave之间的网络中断,但是Master和Slave无法察觉的情况下(比如防火墙或者路由问题)。Slave会等待slave_net_timeout设置的秒数后,才能认为网络出现故障,然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60

2、执行同步命令

1
2
3
4
#执行同步命令,设置主服务器ip,同步账号密码,同步位置
mysql>change master to master_host='10.10.20.111',master_user='account',master_password='123456',master_log_file='mysql-bin.000033',master_log_pos=337523;
#开启同步功能
mysql>start slave;

3、查看从服务器状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.20.111
Master_User: account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 337523
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 337686
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes ##这两行很重要都是YES表示成功
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...

测试主从是否配置成功

1、解锁主服务器数据库

首先解锁主库。

mysql>unlock tables;

然后在主库上创建一张空表,然后在从数据库上查看是否成功同步。

mysql互为主从搭建

0.创建授权用户

1
2
3
4
5
6
7
#A服务器
mysql> grant replication slave on *.* to 'account'@'10.10.20.116' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

#B服务器
mysql> grant replication slave on *.* to 'account'@'10.10.20.111' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

1.编辑主库配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#主库配置文件
nano /etc/mysql/mariadb.conf.d/50-server.cnf
log-bin=mysql-bin #开启二进制日志
server-id=1
relay-log=mysql-relay-bin #开启中继日志
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中,
#从库做为其他从库的主库时 log-slave-updates参数是必须要添加
log_slave_updates = on
#这个参数一般用在主主同步中,用来错开自增值, 防止键值在相互同步时冲突,导致数据丢失
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=1 #自动增长的起始数值
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大,但可减小MySQL崩溃造成的损失)
sync_binlog = 1
修改配置完成以后重启

2.编辑从库配置文件

1
2
3
4
5
6
7
8
#从库配置文件,先关闭二进制日志
nano /etc/mysql/mariadb.conf.d/50-server.cnf
server-id=2
#log-bin=mysql-bin
#log_slave_updates = on
auto_increment_increment=2
auto_increment_offset=2
修改配置完成以后重启

3.将表导入到从服务器上

1
2
3
mysqldump --databases db --lock-all-tables --master-data=2 > /root/db.sql
scp /root/db.sql [email protected]:/root/
mysql -uroot -p db < dump.sql #从服务器上执行

为了防止数据库内容变化,在主数据库导出数据前和从数据库导入数据后,应该对数据库进行加锁。

4.让从库先成为slave

首先查看主库的状态

1
2
3
4
5
6
mysql>show master status/G;
***************** 1. row ****************
File: mysql-bin.000033 #当前记录的日志
Position: 337523 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:

然后让从库先成为slave

1
mysql> change master to master_host='10.10.20.111',master_user='account',master_password='123456',master_port=3306,MASTER_LOG_FILE='mysql-bin.000033', MASTER_LOG_POS=337523;

5.让主库成为slave

1
2
3
4
5
#修改从库的配置
nano /etc/mysql/mariadb.conf.d/50-server.cnf
#将刚刚注释掉的参数生效
log-bin=mysql-bin
log_slave_updates = on

修改配置完成以后重启从库,然后查看从库的状态

1
2
3
4
5
6
mysql> show master status/G;
***************** 1. row ****************
File: mysql-bin.000001 #当前记录的日志
Position: 120 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:

然后让主库成为slave

1
2
mysql> change master to master_host='10.10.20.116',master_user='account',master_password='123456',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
mysql> start slave;

6.查看主从数据库的状态

主库的状态

1
2
3
4
5
6
7
8
9
10
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.20.116
Master_User: account
Master_Port: 3306
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

从库的状态

1
2
3
4
5
6
7
8
9
10
11
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host:10.10.20.111
Master_User: account
Master_Port: 3306
Connect_Retry: 60
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

7.测试互为主从是否成功

首先解锁主库。

1
mysql>unlock tables;

然后在主库上创建一张空表,然后在从数据库上查看是否成功同步。
然后在从库上删除这张空表,然后在主数据库上查看是否成功同步。

Nginx实现Mysql负载均衡

读写分离方案(mysql-proxy)

读写分离方案主要解决的是“数据库读性能瓶颈”问题
水平拆分方案主要解决的是“数据库数据量大”问题
垂直拆分方案主要解决的是“数据库多字段引起的表IO”问题

查看Mysql binlog日志

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
show binlog events\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 249
Info: Server ver: 10.1.37-MariaDB-1, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 249
Event_type: Gtid_list
Server_id: 1
End_log_pos: 274
Info: []
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 274
Event_type: Binlog_checkpoint
Server_id: 1
End_log_pos: 313
Info: mysql-bin.000001
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 313
Event_type: Gtid
Server_id: 1
End_log_pos: 351
Info: BEGIN GTID 0-1-1
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 351
Event_type: Query
Server_id: 1
End_log_pos: 476
Info: use `mysql`; update user set Host='192.168..42.28' where User='slave_user'
*************************** 6. row ***************************
Log_name: mysql-bin.000001
Pos: 476
Event_type: Query
Server_id: 1
End_log_pos: 546
Info: COMMIT
*************************** 7. row ***************************
Log_name: mysql-bin.000001
Pos: 546
Event_type: Gtid
Server_id: 1
End_log_pos: 584
Info: BEGIN GTID 0-1-2
*************************** 8. row ***************************
Log_name: mysql-bin.000001
Pos: 584
Event_type: Query
Server_id: 1
End_log_pos: 708
Info: use `mysql`; update user set Host='192.168.42.28' where User='slave_user'
*************************** 9. row ***************************
Log_name: mysql-bin.000001
Pos: 708
Event_type: Query
Server_id: 1
End_log_pos: 778
Info: COMMIT
*************************** 10. row ***************************
Log_name: mysql-bin.000001
Pos: 778
Event_type: Stop
Server_id: 1
End_log_pos: 797
Info:
10 rows in set (0.02 sec)