通俗来讲,如果对数据库的读和写都在同一个数据库服务器中操作,业务系统性能会降低。 为了提升业务系统性能,优化用户体验,可以通过做主从复制(读写分离)来减轻主数据库的负载。 而且如果主数据库宕机,可快速将业务系统切换到从数据库上,可避免数据丢失。源码:https://github.com/limingios/netFuture
下面这个图,就是主从同步的原理。
mysql主从复制存在的问题:
解决
一致性和同步时间本身就是双刃剑,没有完全的通用解决方案,只能通过业务和性能综合考量选择最优解。 master和slave节点享受一致性的时候,比喻下:master是皇帝,下面很多的slave皇子,但是slave中肯定有一名是太子,当master驾崩了,其中一个太子就编程皇帝了。
如何实现,master就负责写,slave就负责读,需要一个负载均衡的解决方案。这里说下atlas。场景如果在商城生成一个订单,立马就要读这个订单,按照传统的方案我应该读slave库,但是这时候slave还没完成同步。这是不是很尴尬,解决方案就是atlas,如果你生成了订单在master,立马需要读的话,只需要在sql语句中加入一个注解,就可以直接去master里面读了。
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
通过源码生成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 |
su -
# 密码
vagrant
#设置 PasswordAuthentication yes
vi /etc/ssh/sshd_config
sudo systemctl restart sshd
实际生产的环境,根据实际开通指定端口
systemctl stop firewalld
systemctl disable firewalld
#查看状态
systemctl status firewalld
docker pull mysql:5.7
mkdir -p /usr/local/mysqlData/master/cnf
mkdir -p /usr/local/mysqlData/master/data
cd /usr/local/mysqlData/master/cnf
vi mysql.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin
server-id = 1
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
docker images
docker run -itd -p 3306:3306 --name master \
-v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d \
-v /usr/local/mysqlData/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=masterpwd ae6b78bedf88
docker container ls
docker container ls
docker exec -it e2ca4d3cd633 /bin/bash
mysql -uroot -pmasterpwd
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'masterpwd' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'readerpwd';
FLUSH PRIVILEGES;
show master status;
mkdir -p /usr/local/mysqlData/slave/cnf
mkdir -p /usr/local/mysqlData/slave/data
cd /usr/local/mysqlData/slave/cnf
vi mysql.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin
server-id = 2
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
docker images
docker run -itd -p 3307:3306 --name slave1 \
-v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d \
-v /usr/local/mysqlData/slave/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=slavepwd ae6b78bedf88
docker container ls
创建远程连接用户,并赋予查询数据库,以及查询的权限,可以用于读写分离。
docker container ls
docker exec -it c380d2b5f6cf /bin/bash
mysql -uroot -pslavepwd
grant SHOW DATABASES,SELECT on *.* to 'slave'@'%' identified by 'slavepwd';
FLUSH PRIVILEGES;
查看主库 show master status
show master status
配置主从,刚才102的status的postion=889 这里也要配置成一致的。 masterlogfile 也需要跟主库保持一致
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;
#启动主从
start slave;
show slave status\G
如果你想使用Atlas代理,就需要102的mysql密码 和103的mysql密码保持一致,原因是360好久不维护了,提问里面有人遇见过用多个pwds的时候直接出现乱码。103(slave)配置(配置目录,配置mysql.cnf 还是保持不变)
cd /usr/local/mysqlData/slave/data
rm -rf *
docker ps
docker rm -f c380d2b5f6cf
#这里的slave103的密码跟master102的密码一致
docker run -itd -p 3307:3306 --name slave1 \
-v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d \
-v /usr/local/mysqlData/slave/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=masterpwd ae6b78bedf88
docker ps
docker ps
docker exec -it e698f50d7ece /bin/bash
mysql -uroot -pmasterpwd
#一定要记牢了,slave同步的时候要用,如果不一致就无法同步啦
show master status;
docker ps
docker exec -it 6djasdad /bin/bash
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'masterpwd' WITH GRANT OPTION;
FLUSH PRIVILEGES;
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;
#启动主从
start slave;
show slave status\G
在slave主机上这2个都为yes才算配置成功。
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
cd /usr/local/mysql-proxy/
cd bin
./encrypt masterpwd
#下面就需要这个密码 test.cnf里面需要
XYx4FmVBYrXmTh762ogNww==
vi /usr/local/mysql-proxy/conf/test.cnf
这里pwds 对应的密码 就是上一步生成的,log-level设置成了debug目的好测试。sql-log=REALTIME可以查看sql的走向。
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses = 192.168.66.102:3306
#Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
proxy-read-only-backend-addresses = 192.168.66.103:3307@1
#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds = root:XYx4FmVBYrXmTh762ogNww==
#设置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
cd /usr/local/mysql-proxy/bin/
# test 配置文件的名称 start 启动,stop 停止
./mysql-proxyd test start
监听端口默认是1234,我没修改,如果需要修改直接改test.cnf
cd /usr/local/mysql-proxy/log
tail -f sql_test.log
终于搞定了这个图
另外说一点,之前遇见的一个坑,当在项目中使用框架mybatis连数据库时,却都直接去主库读写数据了。偏偏添加了事务@Transactional 解决办法在方法上加@Transactional(propagation=Propagation.NOT_SUPPORTED)即可。
PS:其实很多公司都是通过代理的方式来管理主从数据库的。它可以有选择控制从哪个数据库走。感觉挺爽的,102走的是insert,103走的是select。