由于Atlas原廠已經(jīng)徹底終止Atlas的維護(hù)，建議采用Atlas的小伙伴放棄這個(gè)產(chǎn)品肛走，鑒于Mycat也是一大堆天坑隧出，目前轉(zhuǎn)戰(zhàn)proxysql中
IP架構(gòu)
主庫：Master01 172.17.100.101
從庫：Slave01? ?172.17.100.103
Atlas：172.17.100.109
Mysql采用二進(jìn)制安裝
配置主庫防火墻（從庫需要讀取主庫的binlog）
#這里的103和104（預(yù)留）都是從庫饵隙，109是Atlas中間件
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.103 -j ACCEPT
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.104 -j ACCEPT
[root@Master01 ~]# iptables -I INPUT -s 172.17.100.109 -j ACCEPT
默認(rèn)開啟binlog贵试，從庫不做級(jí)聯(lián)的情況下最好先關(guān)閉binlog
service mysqld stop
vim /etc/my.cnf
注釋掉log_bin和binlog_format（如下）
#log_bin=/usr/local/mysql/log/binlog
#binlog_format=row
分別配置主從的server_id
主庫server_id=101
從庫server_id=103
在主從庫均給用戶rep授予replication slave和replication client的權(quán)限
mysql> grant replication slave,replication client on *.* to rep@'172.17.100.%' identified by 'beacon';
主庫Master01
執(zhí)行mysqldump將全庫導(dǎo)出
flush tables with read lock;
show master status \G
---------------------------------------
mysql> show master status \G
*************************** 1. row ***************************
? ? ? ? ? ? File: binlog.000004
? ? ? ? Position: 120
? ? Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
將主庫導(dǎo)出的sql導(dǎo)入從庫
從庫Slave01執(zhí)行
mysql> change master to
? ? -> master_host='172.17.100.101',
? ? -> master_user='rep',
? ? -> master_password='beacon',
? ? -> master_log_file='binlog.000004',
? ? -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.18 sec)
從庫Slave01執(zhí)行
start slave；
主庫Master01執(zhí)行
unlock tables;
從庫Slave01通過show slave status \G查看是否成功
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
雙yes代表IO線程和SQL線程均已啟動(dòng)成功攀操，主從配置成功
########################################
IO_Running：connecting/no的原因
1.防火墻策略問題
2.同步用戶的密碼問題院仿、gtid沒關(guān)、serverid一致速和、binlog主庫的位置和同步的位置不一樣
配置Atlas
[root@Atlas01 conf]# pwd
/usr/local/mysql-proxy/conf? #配置的路徑
[root@Atlas01 conf]# ll
total 8
-rw-r--r--. 1 root root 2856 Apr 25 10:33 atlas01.cnf
需要修改的參數(shù)
#通過2345端口連接到管理接口的時(shí)候采用這里的賬號(hào)和密碼
admin-username = root????????#管理接口的用戶名
admin-password = beacon????????#管理接口的密碼
#主從庫的IP以及端口
proxy-backend-addresses = 172.17.100.101:3306? ? ? ? #主庫
proxy-read-only-backend-addresses = 172.17.100.103:3306? ? ? ? #從庫
#在/usr/local/mysql-proxy/bin路徑下執(zhí)行./encrypt beacon得到的結(jié)果就是Oi4W0/3yjGQ=
pwds = rep:Oi4W0/3yjGQ=,
#下面3個(gè)參數(shù)沒有進(jìn)行更改
daemon = true
keepalive = true
event-threads = 8
#日志的路徑和級(jí)別歹垫，日志級(jí)別有5個(gè)選項(xiàng)，分為message颠放、warning排惨、critical、error碰凶、debug
#日志路徑需要進(jìn)行配置暮芭，否則在啟動(dòng)mysql-proxyd的時(shí)候會(huì)因?yàn)檎也坏铰窂蕉鴪?bào)錯(cuò)
log-level = debug
log-path =/usr/local/mysql-proxy/log
#實(shí)例名鹿驼，這里的instance名字需要與配置的cnf文件名（atlas01.cnf）以及啟動(dòng)時(shí)的instance名（mysql-proxyd atlas01 start）一致
instance = atlas01
#1234是監(jiān)聽端口，2345是管理端口辕宏，需要在防火墻里開放這2個(gè)端口
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
#字符默認(rèn)是latin畜晰，我這里并沒有打開utf8，剩下的3個(gè)#開頭的配置亦沒有進(jìn)行更改
#charset = utf8
#tables = person.mt.id.3
#client-ips = 127.0.0.1, 192.168.1
#lvs-ips = 192.168.1.1
啟動(dòng)Atlas
[root@Atlas01 bin]# ./mysql-proxy atlas01 start
2018-04-25 10:06:25: (critical) chassis-frontend.c:122: Failed to get log directory, please set by --log-path
2018-04-25 10:06:25: (message) Initiating shutdown, requested from mysql-proxy-cli.c:381
2018-04-25 10:06:25: (message) shutting down normally, exit code is: 1
從報(bào)錯(cuò)來看瑞筐，需要配置日志路徑
檢查配置凄鼻，發(fā)現(xiàn)已經(jīng)配置了日志路徑，錯(cuò)誤原因是啟動(dòng)時(shí)選錯(cuò)了聚假，應(yīng)該使用mysql-proxyd啟動(dòng)（而不是mysql-proxy）
重新執(zhí)行啟動(dòng)
[root@Atlas01 mysql-proxy]# bin/mysql-proxyd atlas01 start
OK: MySQL-Proxy of atlas01 is started
[root@Atlas01 mysql-proxy]# bin/mysql-proxyd atlas01 status
MySQL-Proxy of atlas01 is running (2950)
MySQL-Proxy of atlas01 is running (2951)
通過ps -ef可以看到對(duì)應(yīng)進(jìn)程? ? ? ? #這2個(gè)進(jìn)程應(yīng)該有一個(gè)是監(jiān)控块蚌，另一個(gè)是管理
[root@Atlas01 mysql-proxy]# ps -ef|grep mysql-proxy
root? ? ? 2950? ? 1? 0 10:33 ?? ? ? ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/atlas01.cnf
root? ? ? 2951? 2950? 0 10:33 ?? ? ? ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/atlas01.cnf
查看日志，已經(jīng)生成
[root@Atlas01 log]# pwd
/usr/local/mysql-proxy/log
[root@Atlas01 log]# ll
total 8
-rw-r-----. 1 root root 686 Apr 25 10:33 atlas01.log
-rw-------. 1 root root? 4 Apr 25 10:33 atlas01.pid
-rw-rw-rw-. 1 root root? 0 Apr 25 10:33 sql_atlas01.log
驗(yàn)證讀寫分離
連接監(jiān)控端
[root@Atlas01 ~]# mysql -h 172.17.100.109 -P 1234 -urep -pbeacon
登陸后可以看到2個(gè)內(nèi)建的庫魔策，test和information
連接管理端
[root@Atlas01 ~]# mysql -h 172.17.100.109 -P 2345 -uroot -pbeacon
登陸后不能采用數(shù)據(jù)庫的常規(guī)操作
管理端采用語句select * from help;可以看到常用的管理類語句匈子，管理端無法通過navicat等客戶端進(jìn)行連接，直接在ssh框里對(duì)庫進(jìn)行管理操作
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? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
通過log路徑下的atlas01.log可以看到atlas參與的所有操作的記錄（之前配置為debug闯袒，信息實(shí)在太少，后來我又把他改成message了）
[root@Atlas01 log]# tail -200f atlas01.log
2018-04-25 10:33:45: (message) chassis-unix-daemon.c:138: [angel] we try to keep PID=2951 alive
2018-04-25 10:33:45: (debug) chassis-unix-daemon.c:159: waiting for 2951
2018-04-25 10:33:45: (debug) chassis-unix-daemon.c:123: we are the child: 2951
2018-04-25 10:33:45: (message) mysql-proxy 0.8.2 started - instance: atlas01
2018-04-25 10:33:45: (debug) max open file-descriptors = 1024
2018-04-25 10:33:45: (message) proxy listening on port 0.0.0.0:1234
2018-04-25 10:33:45: (message) added read/write backend: 172.17.100.101:3306? ? ? ? #主庫信息游岳，讀/寫
2018-04-25 10:33:45: (message) added read-only backend: 172.17.100.103:3306? ? ? ? #從庫信息政敢，只讀
2018-04-25 10:33:45: (message) chassis-event-thread.c:235: starting 8 threads
2018-04-25 14:13:21: (message) chassis-unix-daemon.c:138: [angel] we try to keep PID=3164 alive
2018-04-25 14:13:21: (message) mysql-proxy 0.8.2 started - instance: atlas01
2018-04-25 14:13:21: (message) proxy listening on port 0.0.0.0:1234
2018-04-25 14:13:21: (message) added read/write backend: 172.17.100.101:3306
2018-04-25 14:13:21: (message) added read-only backend: 172.17.100.103:3306
2018-04-25 14:13:21: (message) chassis-event-thread.c:235: starting 8 threads
