搜公众号
推荐 原创 视频 Java开发 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库
Lambda在线 > 编程坑太多 > 『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

编程坑太多 2019-02-12
举报

通俗来讲,如果对数据库的读和写都在同一个数据库服务器中操作,业务系统性能会降低。 为了提升业务系统性能,优化用户体验,可以通过做主从复制(读写分离)来减轻主数据库的负载。 而且如果主数据库宕机,可快速将业务系统切换到从数据库上,可避免数据丢失。源码:https://github.com/limingios/netFuture

为什么要主从同步

  1. 实时灾备,用于故障切换

  2. 读写负载均衡

  3. 定时任务专用

  4. 开发人员查看

了解原理

下面这个图,就是主从同步的原理。

『互联网架构』软件架构-mysql主从(二)

  1. webapp(就是java的应用程序)往主库mysqld内进行写。 1.1 丢到mysql的data节点 1.2 数据的语句丢到binlogs里(开启主从同步一定要开启binlogs)除了select查询语句都不在记录啦。 1.3 主节点mysqld有个过滤器 ,可以指定那些数据库生成对应的binlogs。那些需要实时同步,那些不需要。 1.4 binlogs 如果一直生成最后硬盘会不会爆掉,不会的可以设置binlogs的有效期,指定什么之前自动失效,它就会自动删除。

  2. 从库生成两个线程,一个I/O线程,一个SQL线程。 2.1 i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中。 2.2 relay log 被sql thread读又生成了binlog 和 data 2.3 主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog。 2.4 slave里面的生成的binlog (可以打开,或者关闭)可以被另外的slave 进行读取。形成一个链式的。

  3. 同步的原理,binlog有个节点(position)当主从不一致的时候,slave 就要去拿了数据了。

问题及解决方法

mysql主从复制存在的问题:

  1. 主库宕机后,数据可能丢失

  2. 从库只有一个sql Thread,主库写压力大,复制很可能延时

解决

  • 半同步复制---解决数据丢失的问题(主节点插入前一条语句的时候,binlogs已经达到了slave的relay binlog)

  1. 5.5集成到mysql,以插件的形式存在,需要单独安装

  2. 确保事务提交后binlog至少传输到一个从库

  3. 不保证从库应用完这个事务的binlog

  4. 性能有一定的降低,响应时间会更长

  5. 网络异常或从库宕机,卡主主库,直到超时或从库恢复

  • 问题:当slave挂了时候,master一直通信,可能导致整个系统都蹦了 解决方案:

  1. master等待通知的超时时间是10秒,如果10秒slave同步的标识还没到,我该继续插入到本地的master的data就插入。

  2. 但是很多公司都是设置1秒,因为很多公司99%的没问题的,但是1秒这1%,就有问题了。这个设置根据业务来。架构师的水平和经验了。

一致性和同步时间本身就是双刃剑,没有完全的通用解决方案,只能通过业务和性能综合考量选择最优解。 master和slave节点享受一致性的时候,比喻下:master是皇帝,下面很多的slave皇子,但是slave中肯定有一名是太子,当master驾崩了,其中一个太子就编程皇帝了。

互联网主流的主从的方案

『互联网架构』软件架构-mysql主从(二)『互联网架构』软件架构-mysql主从(二)

  1. 一主一从,小公司使用比较多,并没有进行读写分离,slave节点主要是热备的。出现问题很快的恢复。记得当初有个同事无意中删除了一个很重要的表的数据,可能删除了20多条,导致半月都没好过,数据一致性的问题一直在解决。手工一条一条的找到。

  2. 一主多从,小公司使用比较多,一般情况下都是一个master节点三到4个slave的节点, 3个slave节点中其中一个是太子,可以保证数据的一致性,

  3. 双主,比较变态,一般用不到,写性能非常高的时候可能会用双主,mysql是允许,北京的写在一个库,上海的写在一个库,说实话不推荐。

  4. 级联同步,一个master一个slave(太子)节点多个slave节点,一个master如果下面不是级联的既要做读又要做写,还要做同步,master的节点压力很大。级联就是让其中一个slave做同步,给master节点的压力减少了,也达到了主从同步的问题。这个也有问题,如果太子挂掉了,后面的皇子没有领头羊了都变成单机了。

  5. 主主从,也就是2个主互访的,用后面的主来同步slave,但是如果后面的master挂掉的话,后面的slave也和级联同步的情况一致也尴尬了。

  6. 环形多主,非常变态,一定不推荐。写的并发是超级超级高的,没办法才这样玩,全国31省,31个master,每个省一个master,如果其中一个master挂掉,整个环形歇菜了。master下面也可以加入slave,估计超大型的才会用到。但是目前也不会这么用了。

介绍主流的一主多从的方案

如何实现,master就负责写,slave就负责读,需要一个负载均衡的解决方案。这里说下atlas。场景如果在商城生成一个订单,立马就要读这个订单,按照传统的方案我应该读slave库,但是这时候slave还没完成同步。这是不是很尴尬,解决方案就是atlas,如果你生成了订单在master,立马需要读的话,只需要在sql语句中加入一个注解,就可以直接去master里面读了。

『互联网架构』软件架构-mysql主从(二)

Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上,修改了大量bug,添加了很多功能特性。目前该项目在360公司内部得到了广泛应用,很多MySQL业务已经接入了Atlas平台,每天承载的读写请求数达几十亿条。同时,有超过50家公司在生产环境中部署了Atlas,超过800人已加入了我们的开发者交流群,并且这些数字还在不断增加。 Atlas官方链接: https://github.com/Qihoo360/Atlas/blob/master/README_ZH.mdAtlas下载链接: https://github.com/Qihoo360/Atlas/releases

vagrant生成虚拟机

通过源码生成3个虚拟机,准备工作。vagrant已经安装了 对应的docker。用docker安装nexus就是为了避免环境变量,用户赋权等复杂的操作。对于vagrant的如何安装不用的系统不一样可以参看 mac 安装vgarant :https://idig8.com/2018/07/29/docker-zhongji-07/ window安装vgaranthttps://idig8.com/2018/07/29/docker-zhongji-08/

系统类型 IP地址 节点角色 CPU Memory Hostname
Centos7 192.168.66.101 atlas-proxy 2 2G atlas-proxy
Centos7 192.168.66.102 master 2 2G master
Centos7 192.168.66.103 slave 2 2G slave
  • 三台机器window/mac开通远程登录root用户下

 
   
   
 
  1. su -

  2. # 密码

  3. vagrant

  4. #设置 PasswordAuthentication yes

  5. vi /etc/ssh/sshd_config

  6. sudo systemctl restart sshd

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 关闭、禁用防火墙(让所有机器之间都可以通过任意端口建立连接)

实际生产的环境,根据实际开通指定端口

 
   
   
 
  1. systemctl stop firewalld

  2. systemctl disable firewalld

  3. #查看状态

  4. systemctl status firewalld

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

102(master),103(slave)配置

拉取镜像

 
   
   
 
  1. docker pull mysql:5.7

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

102(master)配置

  • 配置目录

 
   
   
 
  1. mkdir -p /usr/local/mysqlData/master/cnf

  2. mkdir -p /usr/local/mysqlData/master/data

  3. cd /usr/local/mysqlData/master/cnf

  4. vi mysql.cnf

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 配置mysql.cnf

 
   
   
 
  1. [mysqld]

  2. pid-file    = /var/run/mysqld/mysqld.pid

  3. socket    = /var/run/mysqld/mysqld.sock

  4. datadir    = /var/lib/mysql


  5. symbolic-links=0


  6. character-set-server = utf8  

  7. #skip-networking  

  8. innodb_print_all_deadlocks = 1

  9. max_connections = 2000  

  10. max_connect_errors = 6000  

  11. open_files_limit = 65535  

  12. table_open_cache = 128  

  13. max_allowed_packet = 4M  

  14. binlog_cache_size = 1M  

  15. max_heap_table_size = 8M  

  16. tmp_table_size = 16M  


  17. read_buffer_size = 2M  

  18. read_rnd_buffer_size = 8M  

  19. sort_buffer_size = 8M  

  20. join_buffer_size = 28M  

  21. key_buffer_size = 4M  


  22. thread_cache_size = 8  


  23. query_cache_type = 1  

  24. query_cache_size = 8M  

  25. query_cache_limit = 2M  


  26. ft_min_word_len = 4  


  27. log-bin = mysql-bin

  28. server-id = 1

  29. binlog_format = mixed  


  30. performance_schema = 0  

  31. explicit_defaults_for_timestamp  


  32. #lower_case_table_names = 1  


  33. interactive_timeout = 28800  

  34. wait_timeout = 28800  


  35. # Recommended in standard MySQL setup  


  36. sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES  


  37. [mysqldump]  

  38. quick  

  39. max_allowed_packet = 16M  


  40. [myisamchk]

  41. key_buffer_size = 8M

  42. sort_buffer_size = 8M

  43. read_buffer = 4M

  44. write_buffer = 4M

  • 启动容器

 
   
   
 
  1. docker images

  2. docker run -itd -p 3306:3306 --name master \

  3. -v  /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d \

  4. -v /usr/local/mysqlData/master/data:/var/lib/mysql  \

  5. -e MYSQL_ROOT_PASSWORD=masterpwd ae6b78bedf88

  6. docker container ls

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 进入容器开启远程访问

 
   
   
 
  1. docker container ls

  2. docker exec -it e2ca4d3cd633 /bin/bash

  3. mysql -uroot -pmasterpwd

 
   
   
 
  1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'masterpwd' WITH GRANT OPTION;

  2. GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'readerpwd';

  3. FLUSH PRIVILEGES;

  4. show master status;

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

103(slave)配置

  • 配置目录

 
   
   
 
  1. mkdir -p /usr/local/mysqlData/slave/cnf

  2. mkdir -p /usr/local/mysqlData/slave/data

  3. cd /usr/local/mysqlData/slave/cnf

  4. vi mysql.cnf

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 配置mysql.cnf

 
   
   
 
  1. [mysqld]

  2. pid-file    = /var/run/mysqld/mysqld.pid

  3. socket    = /var/run/mysqld/mysqld.sock

  4. datadir    = /var/lib/mysql


  5. symbolic-links=0


  6. character-set-server = utf8  

  7. #skip-networking  

  8. innodb_print_all_deadlocks = 1

  9. max_connections = 2000  

  10. max_connect_errors = 6000  

  11. open_files_limit = 65535  

  12. table_open_cache = 128  

  13. max_allowed_packet = 4M  

  14. binlog_cache_size = 1M  

  15. max_heap_table_size = 8M  

  16. tmp_table_size = 16M  


  17. read_buffer_size = 2M  

  18. read_rnd_buffer_size = 8M  

  19. sort_buffer_size = 8M  

  20. join_buffer_size = 28M  

  21. key_buffer_size = 4M  


  22. thread_cache_size = 8  


  23. query_cache_type = 1  

  24. query_cache_size = 8M  

  25. query_cache_limit = 2M  


  26. ft_min_word_len = 4  


  27. log-bin = mysql-bin

  28. server-id = 2

  29. binlog_format = mixed  


  30. performance_schema = 0  

  31. explicit_defaults_for_timestamp  


  32. #lower_case_table_names = 1  


  33. interactive_timeout = 28800  

  34. wait_timeout = 28800  


  35. # Recommended in standard MySQL setup  


  36. sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES  


  37. [mysqldump]  

  38. quick  

  39. max_allowed_packet = 16M  


  40. [myisamchk]

  41. key_buffer_size = 8M

  42. sort_buffer_size = 8M

  43. read_buffer = 4M

  44. write_buffer = 4M

  • 启动容器

 
   
   
 
  1. docker images

  2. docker run -itd -p 3307:3306 --name slave1 \

  3. -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d \

  4. -v /usr/local/mysqlData/slave/data:/var/lib/mysql \

  5. -e MYSQL_ROOT_PASSWORD=slavepwd ae6b78bedf88

  6. docker container ls

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 进入容器开启远程访问

创建远程连接用户,并赋予查询数据库,以及查询的权限,可以用于读写分离。

 
   
   
 
  1. docker container ls

  2. docker exec -it c380d2b5f6cf /bin/bash

  3. mysql -uroot -pslavepwd

 
   
   
 
  1. grant SHOW DATABASES,SELECT on *.* to 'slave'@'%' identified by 'slavepwd';

  2. FLUSH PRIVILEGES;

『互联网架构』软件架构-mysql主从(二)

进入102master的mysql容器

查看主库 show master status

 
   
   
 
  1. show master status


『互联网架构』软件架构-mysql主从(二)

进入103slave的mysql容器

配置主从,刚才102的status的postion=889 这里也要配置成一致的。 masterlogfile 也需要跟主库保持一致

 
   
   
 
  1. change master to master_host='192.168.66.102',master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000003',master_log_pos=889;

『互联网架构』软件架构-mysql主从(二)

 
   
   
 
  1. #启动主从

  2. start slave;

  3. show slave status\G

『互联网架构』软件架构-mysql主从(二)

创建一个数据库自动同步完成

『互联网架构』软件架构-mysql主从(二)

Atlas 代理配置(需要才用,不需要上边就足够了)

如果你想使用Atlas代理,就需要102的mysql密码 和103的mysql密码保持一致,原因是360好久不维护了,提问里面有人遇见过用多个pwds的时候直接出现乱码。103(slave)配置(配置目录,配置mysql.cnf 还是保持不变)

  • 重新删除103的slave的mysql创建新的

 
   
   
 
  1. cd /usr/local/mysqlData/slave/data

  2. rm -rf *

  3. docker ps

  4. docker rm -f c380d2b5f6cf

  5. #这里的slave103的密码跟master102的密码一致

  6. docker run -itd -p 3307:3306 --name slave1 \

  7. -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d \

  8. -v /usr/local/mysqlData/slave/data:/var/lib/mysql \

  9. -e MYSQL_ROOT_PASSWORD=masterpwd ae6b78bedf88  

  10. docker ps      

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 修改数据库权限

 
   
   
 
  1. docker ps

  2. docker exec -it e698f50d7ece /bin/bash

  3. mysql -uroot -pmasterpwd

『互联网架构』软件架构-mysql主从(二)

  • 102 进入mysql查看position

 
   
   
 
  1. #一定要记牢了,slave同步的时候要用,如果不一致就无法同步啦

  2. show master status;

  • 103 进入容器同步

 
   
   
 
  1. docker ps

  2. docker exec -it  6djasdad /bin/bash

  3. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'masterpwd' WITH GRANT OPTION;

  4. FLUSH PRIVILEGES;


  5. change master to master_host='192.168.66.102',master_user='root',master_password='masterpwd',master_log_file='mysql-bin.000003',master_log_pos=1047;

  6. #启动主从

  7. start slave;

  8. show slave status\G

『互联网架构』软件架构-mysql主从(二)

在slave主机上这2个都为yes才算配置成功。『互联网架构』软件架构-mysql主从(二)

  • 进入101 主机开始配置代理

 
   
   
 
  1. wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

  2. rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

『互联网架构』软件架构-mysql主从(二)

『互联网架构』软件架构-mysql主从(二)

  • 密码加密,配置文件需要

 
   
   
 
  1. cd /usr/local/mysql-proxy/

  2. cd bin

  3. ./encrypt masterpwd

  4. #下面就需要这个密码 test.cnf里面需要

  5. XYx4FmVBYrXmTh762ogNww==

『互联网架构』软件架构-mysql主从(二)

  • 配置atlas文件

 
   
   
 
  1. vi /usr/local/mysql-proxy/conf/test.cnf

这里pwds 对应的密码 就是上一步生成的,log-level设置成了debug目的好测试。sql-log=REALTIME可以查看sql的走向。

 
   
   
 
  1. [mysql-proxy]


  2. #带#号的为非必需的配置项目


  3. #管理接口的用户名

  4. admin-username = user


  5. #管理接口的密码

  6. admin-password = pwd


  7. #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔

  8. proxy-backend-addresses = 192.168.66.102:3306


  9. #Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔

  10. proxy-read-only-backend-addresses = 192.168.66.103:3307@1


  11. #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!

  12. pwds = root:XYx4FmVBYrXmTh762ogNww==


  13. #设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。

  14. daemon = true


  15. #设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。

  16. keepalive = true


  17. #工作线程数,对Atlas的性能有很大影响,可根据情况适当设置

  18. event-threads = 8


  19. #日志级别,分为message、warning、critical、error、debug五个级别

  20. log-level = debug


  21. #日志存放的路径

  22. log-path = /usr/local/mysql-proxy/log


  23. #SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF

  24. sql-log = REALTIME


  25. #慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。

  26. #sql-log-slow = 10


  27. #实例名称,用于同一台机器上多个Atlas实例间的区分

  28. #instance = test


  29. #Atlas监听的工作接口IP和端口

  30. proxy-address = 0.0.0.0:1234


  31. #Atlas监听的管理接口IP和端口

  32. admin-address = 0.0.0.0:2345


  33. #分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项

  34. #tables = person.mt.id.3


  35. #默认字符集,设置该项后客户端不再需要执行SET NAMES语句

  36. #charset = utf8


  37. #允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接

  38. #client-ips = 127.0.0.1, 192.168.1


  39. #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置

  40. #lvs-ips = 192.168.1.1

『互联网架构』软件架构-mysql主从(二)

  • 启动atlas

 
   
   
 
  1. cd /usr/local/mysql-proxy/bin/

  2. # test 配置文件的名称 start 启动,stop 停止

  3. ./mysql-proxyd test start

『互联网架构』软件架构-mysql主从(二)

监听端口默认是1234,我没修改,如果需要修改直接改test.cnf

『互联网架构』软件架构-mysql主从(二)

  • 查看日志准备测试

 
   
   
 
  1. cd /usr/local/mysql-proxy/log

  2. tail -f sql_test.log

『互联网架构』软件架构-mysql主从(二)

终于搞定了这个图

另外说一点,之前遇见的一个坑,当在项目中使用框架mybatis连数据库时,却都直接去主库读写数据了。偏偏添加了事务@Transactional 解决办法在方法上加@Transactional(propagation=Propagation.NOT_SUPPORTED)即可。

PS:其实很多公司都是通过代理的方式来管理主从数据库的。它可以有选择控制从哪个数据库走。感觉挺爽的,102走的是insert,103走的是select。


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《『互联网架构』软件架构-mysql主从(二)》的版权归原作者「编程坑太多」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

举报