Atlas架构介绍
Atlas下载链接: https://github.com/Qihoo360/Atlas/releases
所以我们先来配置mysql的主从同步,再配置Atlas
官方截图:
Mysql主从同步配置
准备环境
机器1:192.168.1.10(主)
机器2:192.168.1.11(从)
mysql-5.6.36
注意下面几点:
1)要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。
2)同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。
准备测试库
主库操作:
在master主机器上创建测试库
mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `t_user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1034 DEFAULT CHARSET=utf8;
mysql> INSERT INTO `t_user` VALUES ('1000', '小明', '123456', '13800138000');
INSERT INTO `t_user` VALUES ('1001', '小李', '123456', '13800138000');
INSERT INTO `t_user` VALUES ('1002', '小黄', '123456', '13800138000');
INSERT INTO `t_user` VALUES ('1003', '小陈', '123456', '13800138000');
master主库锁表复制数据
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
主库备份
[root@master ~]# mysqldump -uroot -p -B test > test.sql
说明:-B参数有建库语句。
在当前目录下生成test.sql文件,将该文件拷贝到从库进行导入。
保证双方在同步环境实现前的数据一致。导入成功后,主库解开锁表功能。
主库解开锁表功能
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
设置数据同步权限账号
mysql> grant replication slave,replication client on *.* to 'dev'@'%' identified by "dev";
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
主库的配置
[hsy@master ~]# vim /etc/my.cnf
server-id = 1 #数据库唯一ID,主从的标识号绝对不能重复。
log-bin=mysql-bin #开启bin-log,并指定文件目录和文件名前缀
binlog-ignore-db=information_schema #不同步mysql系统数据库。如果是多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=test #需要同步的数据库。如果是多个同步库,就以此格式另写几行即可。如果不指明对某个具体库同步,就去掉此行,表示同步所有库(除了ignore忽略的库)。
重启数据库
[hsy@master ~]# service mysqld restart
进入主库Mysql控制台,查看主库状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 353 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库的配置
[hsy@slave ~]# vim /etc/my.cnf
server-id = 2
log-bin=mysql-bin
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=test #要同步的数据库
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
重启数据库
[hsy@slave ~]# service mysqld restart
进入slave mysql控制台
mysql> stop slave; #关闭Slave
mysql> change master to master_host='192.168.1.10',master_user='dev',master_password='dev',master_log_file='mysql-bin.000001', master_log_pos=353;
mysql> start slave; #开启Slave
mysql> show slave status \G;
查看配置的信息:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: dev
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 353
Relay_Log_File: VM_128_194_centos-relay-bin.000004
Relay_Log_Pos: 26111
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql
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: 28125
Relay_Log_Space: 26296
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: 2
Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd
Master_Info_File: /data/mysqldb/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)
ERROR:
No query specified
mysql>
如上:当Slave_IO_Running线程和Slave_SQL_Running线程必须都为Yes,则表示主从同步已经实现了,如果有一项不为Yes说明启动失败。
Atlas配置读写分离
准备环境
机器1:192.168.1.10(主) 在master机器上配置Atlas。
Mysql设置
在主/从机器上都建立具有增删改查功能的账号
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'dev'@'%' IDENTIFIED BY 'dev';
FLUSH PRIVILEGES;
安装Atlas
[hsy@master ~] wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[hsy@master ~] rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
通过rpm包安装方式,默认的配置文件在
[hsy@master ~] /usr/local/mysql-proxy/conf/test.cnf
首先可以先获取一下之前步骤中,'dev'@'%'这个用户加密后的密码:
[hsy@master ~] /usr/local/mysql-proxy/bin/encrypt dev
A2OS3vFVUmY= 使用自带脚本对明文面dev进行加密。
test.cnf配置文件参考:
[mysql-proxy]
# 基础设置
# 以守护进程方式启动
daemon = true
# 设置atlas的运行方式,若为true,则表示多一个monitor,该进程会在woker进程挂掉后将其重启。若为false,则只有一个工作进程woker。
keepalive = true
# atlas实例名,便于区分一台机子上的不同atlas
instance = test
# 工作线程数,atlas开发人员推荐将其设置为CPU个数的2~4倍。
event-threads = 4
# SQL日志的开关,共有三个可选项:OFF、ON、REALTIME,分别为:不记录sql日志、记录sql日志,并等待缓冲区填满后,才会落地到磁盘、记录sql日志并实时写入磁盘。
sql-log = REALTIME
# 等同于客户端连接到mysql-server后输入SET names utf8;
charset = utf8
# 如果这样配置,可以通过mysql -h127.0.0.1 -P2345 -uadmin -padmin可以连接到Atlas管理界面
admin-username = admin
admin-password = admin
# 该参数设置Atlas后台管理地址和端口
admin-address = 192.168.1.10:2345
#admin-address = 0.0.0.0:2345
# 设置主库和从库的地址,其中主库为写库,从库为只读库
# 主库地址
proxy-backend-addresses = 192.168.1.10:3306
# 设置读库地址和端口
proxy-read-only-backend-addresses = 192.168.1.11:3306@1
# 若配置为主库也分担读请求,并且设置权重为 (主:从)=(1:3),则可以写作
#proxy-read-only-backend-addresses = 192.168.1.185:3306@1, 192.168.1.186:3306@3
# 设置Atlas工作监听的地址和端口,应用程序将连接到这个地址,既然客户端把Atlas当做一个mysql-server,故此处直接写成3306端口
proxy-address = 192.168.1.10:1234
#proxy-address = 0.0.0.0:1234
# 设置用户的密码(这个密码为MySQL中dev用户通过Atlas加密程序加密后的密码),多个可以用逗号隔开
pwds = dev:A2OS3vFVUmY=
# 设置Atlas日志信息,其中log-level有message、warning、critical、error、debug五个级别
log-level = message
# 日志存放的路径,日志名为$instance_name.log,比如$log-path/test.log
log-path = /usr/local/mysql-proxy/log
# 设置允许连接Atlas的客户端ip,非必须,可以是多个,可以是精准ip也可以是ip段,比如:
#client-ips = 127.0.0.1, 192.168.1
# 挂接lvs的物理网卡ip,若有lvs结构,且设置了client-ips,则必须设置此项,反之可不设置。
#lvs-ips = 192.168.1.1
test.cnf配置中:将sql日志打开
sql-log = REALTIME
配置好后,直接启动
[hsy@master ~] /usr/local/mysql-proxy/bin/mysql-proxyd test start
可用命令检查是否启动成功了
[hsy@master mysql-proxy]# ./bin/mysql-proxyd test
Usage: ./bin/mysql-proxyd instance {start|stop|restart|status}
[hsy@master mysql-proxy]# ./bin/mysql-proxyd test status
MySQL-Proxy of test is running (5176)
MySQL-Proxy of test is running (5177)
连接Atlas后台界面
[hsy@master ~] mysql -h127.0.0.1 -P2345 -udev -pdev
查看当前配置的DBserver和状态
mysql> SELECT * FROM backends;
+-------------+--------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+--------------------+-------+------+
| 1 | 192.168.1.10:3306 | up | rw |
| 2 | 192.168.1.11:3306 | up | ro |
+-------------+--------------------+-------+------+
2 rows in set (0.00 sec)
更多的选项可以通过SELECT * FROM help;来获取:
mysql> SELECT * FROM help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
连接Atlas工作监听界面[hsy@master ~]# mysql -h127.0.0.1 -P1234 -udev -pdev
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MySQL [(none)]>
在/usr/local/mysql-proxy/log目录下可以看到有3个文件:
其中test.log为Atlas的启动日志,test.pid为Atlas的启动pid,sql_test.log为sql日志,在sql日志中可以清楚的看到,读请求是提交给了slave(192.168.1.11),写请求都提交给了master(192.168.1.10)。
提示:在配置文件中如未开启 sql-log = REALTIME,是看不到sql日志的。
参考文档:
MySQL5.6 数据库主从(Master/Slave)同步安装与配置详解:http://blog.csdn.net/xlgen157387/article/details/51331244/
Mysql主从同步(1)-主从/主主环境部署梳理:https://www.cnblogs.com/kevingrace/p/6256603.html
MySQL-proxy 中间件Atlas 实现读写分离:http://blog.itpub.net/29773961/viewspace-2123060/
本人平时喜欢与人交流,若是对文章中有不同意见的,欢迎各位留言指点