mysql主从架构分析以及读写分离实践
为什么要使用主从同步
- 实时灾备,用于故障切换
- 读写负载均衡
- 各种独占数据库操作专用
mysql具体内部实现主从原理
master节点主要通过bin-log与slave节点进行交互,每当写master节点,在bin-log中就会记录上相应的操作,bin-log不记录查询操作。slave节点通过复制master的bin-log,解析成relay-bin-log,生成自己的bin-log,最后通过bin-log还原数据,从而达到数据的一致性。
问题
要注意的是,在mysql5.5之前的主从复制容易产生数据不一致的情况,也就是master节点的数据还没来得及同步到slave节点,就开始读取slave节点,这样会产生数据不一致的情况。
解决方式(半同步)
mysql5.5之后引入了一个半同步的方案(也是一个插件),去解决数据没来得及同步的问题,主要原理是master在写入数据库后进行等待,待数据同步到slave节点中的一个才返回,剩下的slave再进行同步。
同样的这种半同步也会引入新的问题,如果slave节点同步数据很慢或者直接断开了,master节点数据库操作超时了,直接出问题,也会有一定的风险。
slave节点同步数据慢:可以配置多个SQL Thread去解决
常用的主从方案
1.一主一从:(大部分拿来做备份)
2.一主多从:(最常见的做法,大部分用的是这种方式,不过要同步slave比较多)
3.双主:(不推荐,多写的情况下用,数据一致性问题严重)
4.环形多主:(和双主类似)
5.级联同步:(比较好,一个master挂了,一个slave马上可以升级为master<可以用Nginx进行自动升级为master>,但会出现一个slave挂了,剩下的全挂了)
mysql主从具体实践
- 配置master节点数据库
#master节点server—id一定要设置为一
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
#binlog_do_db设置要同步的数据库
binlog_do_db = mas_db
#binlog_ignore_db设置要忽略的数据库
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys
- 配置slave节点数据库
#slave节点server—id一定要设置为一
server-id = 3
log_bin = /var/log/mysql/mysql-slave-bin.log
#replicate_do_db设置要同步的数据库
replicate_do_db = mas_db
#replicate_ignore_db设置要忽略的数据库
replicate_ignore_db = mysql
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema
replicate_ignore_db = sys
- 切换slave节点的master到指定的机子上(也就是master节点的机子)
change master to master_host='192.168.244.128',master_user='root',master_password='root';
完成后,再输入命令,启动slave的同步
start slave;
输入命令即可查看状态
show slave status \G;
可看到
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.244.128
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 618
Relay_Log_File: ubuntu-slave1-relay-bin.000005
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mas_db
Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
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: 618
Relay_Log_Space: 701
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: cbc69b0f-0e07-11e8-b8c2-000c2901d559
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
其中
Slave_IO_Running: Yes Slave_SQL_Running: Yes
2个状态表示已经开始同步的操作了
同样的查看master的状态,可到master的机子mysql命令行中输入
show master status \G;
可看到
File: mysql-bin.000001
Position: 875
Binlog_Do_DB: mas_db
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
这样主从同步基本配置就完成了。
mysql主从读写分离具体实践
主要依赖于360的Atlas去实现
下载完安装包后直接安装,安装操作
配置文件如下
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses = 127.0.0.1:3306
#Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
proxy-read-only-backend-addresses = 192.168.244.132:3306@1,192.168.244.133:3306@1
#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds = root:DAJnl8cVzy8=
#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
daemon = true
#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
keepalive = true
#工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
event-threads = 8
#日志级别,分为message、warning、critical、error、debug五个级别
log-level = debug
#日志存放的路径
log-path = /usr/local/mysql-proxy/log
#SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
sql-log = REALTIME
#慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10
#实例名称,用于同一台机器上多个Atlas实例间的区分
#instance = test
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
#分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
#tables = person.mt.id.3
#默认字符集,设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8
#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1
#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
#lvs-ips = 192.168.1.1
最后启动即可
./mysql-proxyd test restart
查看日志文件即可看到读写分离起作用了
[02/12/2018 22:25:40] C:192.168.244.1:23106 S:192.168.244.132:3306 OK 184.631 "SET NAMES utf8"
[02/12/2018 22:25:40] C:192.168.244.1:23106 S:192.168.244.132:3306 OK 1.005 "SHOW FULL TABLES FROM `mas_db` WHERE Table_type != 'VIEW'"
[02/12/2018 22:25:40] C:192.168.244.1:23106 S:192.168.244.133:3306 OK 1.276 "SHOW FULL TABLES FROM `mas_db` WHERE Table_type = 'VIEW'"
[02/12/2018 22:25:40] C:192.168.244.1:23106 S:192.168.244.132:3306 OK 53.995 "SHOW PROCEDURE STATUS WHERE Db='mas_db'"
[02/12/2018 22:25:40] C:192.168.244.1:23106 S:192.168.244.133:3306 OK 22.410 "SHOW FUNCTION STATUS WHERE Db='mas_db'"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 OK 88.819 "SET NAMES utf8"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 OK 0.786 "SET PROFILING=1;"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.133:3306 OK 31.355 "SHOW STATUS"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 OK 2.574 "SHOW STATUS"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.133:3306 OK 2.657 "select * from city"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 OK 1.889 "SHOW STATUS"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.133:3306 OK 2.292 "SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 ERR 1.027 "SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID= GROUP BY STATE ORDER BY SEQ"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.133:3306 OK 0.753 "SELECT * FROM `mas_db`.`city` LIMIT 0"
[02/12/2018 22:25:48] C:192.168.244.1:23107 S:192.168.244.132:3306 OK 1.831 "SHOW COLUMNS FROM `mas_db`.`city`"
[02/12/2018 22:26:26] C:192.168.244.1:23110 S:192.168.244.133:3306 OK 3.708 "SET NAMES utf8"
[02/12/2018 22:26:26] C:192.168.244.1:23110 S:127.0.0.1:3306 OK 71.877 "UPDATE `city` SET `CITY_NAME`='北京(市辖区)2' WHERE (`ID`='1')"
[02/12/2018 22:26:26] C:192.168.244.1:23110 S:192.168.244.133:3306 OK 1.345 "SELECT * FROM `city` WHERE (`ID`='1') LIMIT 1"