在高并发流量下,数据库往往是服务端的瓶颈,由于数据库数据需要确保落地,同时保证数据同步,数据即时性,有效性的问题,导致数据库不能像平常后端程序一样负载均衡.
那么在大并发下,该如何缓解数据库的压力呢?
mysql读写分离
我们可以分析,程序对于mysql的操作无非就2种,写入数据/更新数据(数据变更),读取数据.
数据变更,因为要保证数据可靠以及数据同步问题,无法直接通过开多台服务器解决.
但是读取数据库,因为不涉及数据库变更,所以我们可以在程序中,将程序中涉及数据库数据变更的,和数据库查询的数据库区分,实现读写分离
数据变更时操作A服务器,A服务器数据变更后,立即传输给B服务器,使得B服务器进行更新数据.同时由于读数据库完全不涉及数据变更,可以开启多台读数据库,进行分散数据库压力
读写分离需要程序代码进行实现,而非数据库的功能,通过读写分离,能够极大的缓解数据库的压力.(虽然还是需要数据更新,并且还需要数据同步,但是写服务器只需要负责写入数据,读取的压力将分散到了读服务器上).
mysql集群
mysql集群除了为了解决数据库压力分散问题坏,同时为了实现数据库的高可用性,在一台数据库宕机的情况下,尽可能的降低业务的影响.
mysql集群有着以下几种方式:
1:mysql一主一从,mysql读写分离,使数据库压力分散,提高服务器性能
2:mysql一主多从,当主服务器出问题后,可以选择一台从服务器变更为主服务器,继续提供服务
3:mysql多主多从,一台主服务器出问题了,可立即切换另一台主服务器提供服务.
同时,mysql集群将带来相关的一些问题,例如:
1:主从同步数据延迟问题
2:一主多从虽然可以提高可用性,但在主服务器宕机的时候,可能会出现一些数据同步未完成,数据丢失的问题,需要在主服务器恢复后增量恢复
3:多主多从需要考虑主服务器都在使用时,id自增,主键冲突的问题,以及其中一台主服务器宕机时间至恢复时间内的数据丢失,增量同步的问题.
mysql一主一从搭建
当我们了解了mysql集群的实现原理,应用场景之后,就可以开始搭建主从集群环境了,我们需要准备:
1:2台服务器(虚拟机)
2:2台都需要安装mysql环境
目前我使用的是宝塔安装的mysql 5.6,可以自行安装mysql用于测试.
主服务器:192.168.192.131
从服务器:192.168.192.130
配置项
主服务器主要配置项:
log-bin = mysql-bin ##binlog文件存储路径,相对路径=datadir+log-bin.xxxxx 例如宝塔的binlog路径为;/www/server/data/mysql-bin.000001 server-id = 1 服务器标识id,通常主服务器id比从服务器小.
从服务器配置项:
log-bin = mysql-bin server-id = 2
检查配置项命令:
\[root@localhost www\]# egrep "log-bin|server-id" /etc/my.cnf log-bin=mysql-bin server-id = 1 ###上面是主服务器 \[root@localhost ~\]# egrep "log-bin|server-id" /etc/my.cnf log-bin=mysql-bin server-id = 2 ###这里是从服务器
查看binlog开启情况:
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) ### 上面是主服务器 mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) ### 这里是从服务器
主库新建从库访问账号,并给予相关的权限
## 新增主库访问账号rep,密码为123456,只允许通过192.168.*.* ip连接 mysql> grant replication slave on *.* to 'rep'@'192.168.%.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) ## 刷新系统用户权限 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) ## 查询当前数据库账号 mysql> select user,host from mysql.user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | rep | 192.168.%.% | | root | ::1 | | | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | +------+-----------------------+ 7 rows in set (0.00 sec)
服务器数据同步,保证2台服务器数据一致
主服务器加只读锁,防止在数据同步时,主服务器新增数据
## 数据库加锁,只允许读取数据 mysql> flush table with read lock; Query OK, 0 rows affected (0.01 sec) ## 这个时候新建数据库,或者数据更新都会报错 mysql> create database test; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
备份数据
## 记录当前binlog位置, mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000013 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
使用mysqldump命令进行备份数据库
mysqldump -uroot -p -A -F --master-data=2|gzip > /root/rep.sql.gz -A 备份全部数据库 -F 刷新二进制日志 --master-data=2 追加二进制位置和文件输出到sql中 gzip 将mysqldump的数据压缩,节省存储空间
将压缩文件上传到从服务器.并导入到从库中
gunzip < /root/rep.sql.gz |mysql -uroot -p
配置从库与服务器通信参数:
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.192.131', -> MASTER_PORT=3306, -> MASTER_USER='rep', -> MASTER_PASSWORD='123456', -> MASTER\_LOG\_FILE='mysql-bin.000013', -> MASTER\_LOG\_POS=120; Query OK, 0 rows affected, 2 warnings (0.01 sec)
验证修改的参数:
\[root@localhost ~\]# cd /www/server/data/ \[root@localhost data\]# cat master.info 23 mysql-bin.000012 120 192.168.192.131 rep 123456 3306 60 0 0 1800.000 0 86400 0 \[root@localhost data\]#
启动从服务器节点,正式开始同步主服务器:
## 启动从服务器 mysql> start slave; Query OK, 0 rows affected (0.00 sec) ## 查看从服务器运行状态 mysql> show slave status\\G *************************** 1. row *************************** Slave\_IO\_State: Waiting for master to send event Master_Host: 192.168.192.131 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master\_Log\_File: mysql-bin.000013 Read\_Master\_Log_Pos: 120 Relay\_Log\_File: localhost-relay-bin.000003 Relay\_Log\_Pos: 283 Relay\_Master\_Log_File: mysql-bin.000013 Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes Replicate\_Do\_DB: Replicate\_Ignore\_DB: Replicate\_Do\_Table: Replicate\_Ignore\_Table: Replicate\_Wild\_Do_Table: Replicate\_Wild\_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec\_Master\_Log_Pos: 120 Relay\_Log\_Space: 460 Until_Condition: None Until\_Log\_File: Until\_Log\_Pos: 0 Master\_SSL\_Allowed: No Master\_SSL\_CA_File: Master\_SSL\_CA_Path: Master\_SSL\_Cert: Master\_SSL\_Cipher: Master\_SSL\_Key: Seconds\_Behind\_Master: 0 Master\_SSL\_Verify\_Server\_Cert: No Last\_IO\_Errno: 0 Last\_IO\_Error: Last\_SQL\_Errno: 0 Last\_SQL\_Error: Replicate\_Ignore\_Server_Ids: Master\_Server\_Id: 1 Master_UUID: f730887e-1e5c-11ea-ae2e-000c29fc65d1 Master\_Info\_File: /www/server/data/master.info SQL_Delay: 0 SQL\_Remaining\_Delay: NULL Slave\_SQL\_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master\_Retry\_Count: 86400 Master_Bind: Last\_IO\_Error_Timestamp: Last\_SQL\_Error_Timestamp: Master\_SSL\_Crl: Master\_SSL\_Crlpath: Retrieved\_Gtid\_Set: Executed\_Gtid\_Set: Auto_Position: 0 1 row in set (0.00 sec)
注意,只有当 slave_io_running和slave_sql_running 都为yes时,才算是启动成功.
如果你的mysql服务器是直接克隆的,需要注意删除mysql数据目录下的auto.cnf文件,并重启一次服务器,改文件记录了数据库的uuid,如果重复则会出错.
主服务器恢复可写:
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
这步可以直接放到备份数据,并记录binlog文件名和位置时去做.
测试
主服务器当前数据库:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
从服务器当前数据库:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
主服务器新增数据库:
mysql> create database test666; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test666 | +--------------------+ 5 rows in set (0.00 sec) mysql>
从服务器查看:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test666 | +--------------------+ 5 rows in set (0.00 sec) mysql>
主从服务器搭建成功!
