最近在参加项目开发微信小程序后台，由于用户数量巨大，且后台程序并不是很完美，所以对用户的体验很是不友好（简单说就是很卡）。赶巧最近正在翻阅《大型网站系统与Java中间件实践》。
所以，先是使用Docker swarm构建了负载均衡集群，然后使用Atlas做了数据库的读写分离，尽可能对用户进行分流，降低对单机服务器的负载，提高访问体验。本片随笔仅对数据库的读写分离部分进行介绍。
目标：实现mysql数据库集群，一个主节点负责写入数据，多个从节点负责读取数据
实验环境服务器：
阿里云服务器：centos7（10.0.0.1）
腾讯云服务器：ubuntu:16.04（10.0.0.2)
京东云服务器：ubuntu:16.04 (10.0.0.3)
mysql:5.7
docker:18.03
实现方案mysql 主从数据库 + Atlas读写分离
步骤： 进入Master服务器进行配置使用Docker，在服务器上创建MySQL服务
docker run -p 3306:3306 --name mysql3306 -v /opt/mysql/data/data3306:/var/lib/mysql -v /opt/mysql/logs/logs3306:/logs -e MYSQL_ROOT_PASSWORD=hello -d mysql:5.7进Master服务器的MySQL数据库中，创建用户"niwoo"设置密码为"hello"
mysql -h 127.0.0.1 -uroot -phello mysql> grant all on *.* to ninwoo@'127.0.0.1' identified by "hello"; Query OK, 0 rows affected, 1 warning (0.00 sec)修改niwoo的访问权限
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set host = '%' where user = 'ninwoo'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0确认是否修改成功
mysql> update user set host = '%' where user = 'ninwoo'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select user, host from user; +---------------+-----------+ | user | host | +---------------+-----------+ | ninwoo | % | | root | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 5 rows in set (0.00 sec)更新数据库,是配置生效
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)Master，Slave1的MySQL数据库上创建CampusHire数据库
进入Slave1执行相同1,2,3,4操作
配置主从数据库连接进入Master服务器进行配置
进入mysql容器内部，修改配置文件
docker exec -it mysql3306 bash默认没有vim，无法修改文件
# 更新软件源 apt update # 安装vim apt install vim修改mysql配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf添加如下配置
#主从复制配置 innodb_flush_log_at_trx_commit=1 sync_binlog=1 #需要备份的数据库 binlog-do-db=test #不需要备份的数据库 binlog-ignore-db=mysql #启动二进制文件 log-bin=mysql-bin #服务器ID server-id=1退出容器，重启docker应用
docker restart mysql3306进入数据库，配置主从复制的权限
mysql -h 127.0.0.1 -uroot -phello mysql> grant replication slave on *.* to 'ninwoo'@'127.0.0.1' identified by 'hello'; Query OK, 0 rows affected, 1 warning (0.01 sec)锁定数据库
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)查看主数据库信息，记住下面的“File”与“Position”的信息
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 439 | CampusHire | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)进入slave服务器，进入mysql容器内部
# 更新软件源 apt update # 安装vim apt install vim修改从数据库的配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf添加server-id=2
重启Slave数据库
docker restart mysql3306进入数据库，输入刚刚记录下来的File和Position
MySQL [(none)]> change master to master_host='10.0.0.1', -> -> master_user='ninwoo', -> master_password='hello', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=439, -> master_connect_retry=10;启动Slave进程
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: 139.199.194.49 Master_User: ninwoo Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 439 Relay_Log_File: 2a8a4abdcf68-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 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: 439 Relay_Log_Space: 534 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: 4a0187fa-b0a5-11e8-a7e7-0242ac110003 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 up dates 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: 1 row in set (0.00 sec)