前期準(zhǔn)備
準(zhǔn)備4臺(tái)機(jī)器葵第，系統(tǒng)為CentOS release 6.6
Ip分別為192.168.20.121桩卵、192.168.20.122怕磨、192.168.20.123演痒、192.168.20.124
4臺(tái)機(jī)器分別作為Atlas代理服務(wù)始花，master MySQL妄讯，slave MySQL 1，slave MySQL 2
下載QiHoo360的Atlas 地址
安裝Atlas
下載得到Atlas-XX.el6.x86_64.rpm安裝文件
sudo rpm –i Atlas-XX.el6.x86_64.rpm安裝
安裝在/usr/local/mysql-proxy
安裝目錄分析
bin
可執(zhí)行文件
encrypt用來(lái)加密密碼酷宵，后面會(huì)用到
mysql-proxy是MySQL自己的讀寫(xiě)分離代理
mysql-proxyd操作Atlas
VERSION
conf
test.cnf配置文件
一個(gè)文件為一個(gè)實(shí)例亥贸，實(shí)例名即文件名，啟動(dòng)需要帶上這個(gè)實(shí)例名
lib依賴包
log記錄日志
啟動(dòng)命令：/usr/local/mysql-proxy/bin/mysql-proxyd [實(shí)例名] start
停止命令：/usr/local/mysql-proxy/bin/mysql-proxyd [實(shí)例名] stop
同理浇垦，restart為重啟炕置，status為查看狀態(tài)
配置文件解釋
請(qǐng)查看官方文檔
數(shù)據(jù)庫(kù)配置
1臺(tái)master2臺(tái)slave，都要配置相同的用戶名密碼男韧，且都要可以遠(yuǎn)程訪問(wèn)分別進(jìn)入3臺(tái)服務(wù)器朴摊，創(chuàng)建相同的用戶名密碼，創(chuàng)建數(shù)據(jù)庫(kù)test此虑，設(shè)置權(quán)限
CREATE USER 'test'@'%' IDENTIFIED BY 'test123';
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test123';
grant all privileges on test.* to 'test'@'%' identified by 'test123';
grant all privileges on test.* to 'test'@'localhost' identified by 'test123';
flush privileges;
主從數(shù)據(jù)庫(kù)配置
配置master服務(wù)器
找到MySQL配置文件my.cnf甚纲，一般在etc目錄下修改配置文件
[mysqld]
一些其他配置
...
主從復(fù)制配置
innodb_flush_log_at_trx_commit=1
sync_binlog=1
需要備份的數(shù)據(jù)庫(kù)
binlog-do-db=test
不需要備份的數(shù)據(jù)庫(kù)
binlog-ignore-db=mysql
啟動(dòng)二進(jìn)制文件 log-bin=mysql-bin
服務(wù)器ID server-id=1
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
重啟數(shù)據(jù)庫(kù)service mysql restart
進(jìn)入數(shù)據(jù)庫(kù)，配置主從復(fù)制的權(quán)限
mysql -uroot -p123456
grant replication slave on . to 'test'@'127.0.0.1' identified by 'test123';
查看主數(shù)據(jù)庫(kù)信息朦前，記住下面的File與Position的信息介杆，它們是用來(lái)配置從數(shù)據(jù)庫(kù)的關(guān)鍵信息讹弯。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 17620976 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置兩臺(tái)salve服務(wù)器
找到配置文件my.cnf修改配置文件如下
[mysqld]
一些其他配置
...
幾臺(tái)服務(wù)器不能一樣 server-id=2
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
進(jìn)入數(shù)據(jù)庫(kù)，配置從數(shù)據(jù)庫(kù)的信息这溅，這里輸入剛才記錄下來(lái)的File與Position的信息组民，并且在從服務(wù)器上執(zhí)行(執(zhí)行的時(shí)候，#行不要復(fù)制進(jìn)去)：
master數(shù)據(jù)庫(kù)的ip
mysql> change master to master_host='192.168.20.122',
# master的用戶名 master_user='buck', # 密碼 master_password='hello', # 端口 master_port=3306, # master數(shù)據(jù)庫(kù)的`File ` master_log_file='mysql-bin.000002', # master數(shù)據(jù)庫(kù)的`Position` master_log_pos=17620976, master_connect_retry=10;
啟動(dòng)進(jìn)程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
檢查主從復(fù)制狀態(tài)悲靴，要看到下列Slave_IO_Running臭胜、Slave_SQL_Running的信息中，兩個(gè)都是Yes癞尚，才說(shuō)明主從連接正確耸三，如果有一個(gè)是No，需要重新確定剛才記錄的日志信息浇揩，停掉“stop slave”重新進(jìn)行配置主從連接仪壮。
mysql> show slave status G;
-
row **
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.246.134 Master_User: buck Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 17620976 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000002 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: 17620976 Relay_Log_Space: 407 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:
1 row in set (0.00 sec)
ERROR:
No query specified
Atlas配置
使用Atlas的加密工具對(duì)上面用戶的密碼進(jìn)行加密
/usr/local/mysql-proxy/bin/encrypt test123
29uENYYsKLo=
配置atlas
這是用來(lái)登錄到Atlas的管理員的賬號(hào)與密碼，與之對(duì)應(yīng)的是Atlas監(jiān)聽(tīng)的管理接口IP和端口胳徽，也就是說(shuō)需要設(shè)置管理員登錄的端口积锅，才能進(jìn)入管理員界面，默認(rèn)端口是2345养盗，也可以指定IP登錄缚陷，指定IP后，其他的IP無(wú)法訪問(wèn)管理員的命令界面往核。方便測(cè)試箫爷，我這里沒(méi)有指定IP和端口登錄。配置主數(shù)據(jù)的地址與從數(shù)據(jù)庫(kù)的地址聂儒，這里配置的主數(shù)據(jù)庫(kù)是122虎锚，從數(shù)據(jù)庫(kù)是123、124
Atlas后端連接的MySQL主庫(kù)的IP和端口衩婚，可設(shè)置多項(xiàng)窜护，用逗號(hào)分隔
proxy-backend-addresses = 192.168.20.122:3306
Atlas后端連接的MySQL從庫(kù)的IP和端口，@后面的數(shù)字代表權(quán)重谅猾，用來(lái)作負(fù)載均衡柄慰，若省略則默認(rèn)為1，可設(shè)置多項(xiàng)税娜，用逗號(hào)分隔
proxy-read-only-backend-addresses = 192.168.20.123:3306@1,192.168.20.124:3306@2
這個(gè)是用來(lái)配置MySQL的賬戶與密碼的坐搔，就是上面創(chuàng)建的用戶，用戶名是test,密碼是test123,剛剛使用Atlas提供的工具生成了對(duì)應(yīng)的加密密碼
pwds = buck:RePBqJ+5gI4=啟動(dòng)Atlas
root[@localhost /usr/local/mysql-proxy/bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started
測(cè)試
進(jìn)入atlas的管理界面
root[@localhost ~]#mysql -h127.0.0.1 -P2345 -uuser -ppwd
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Other names may be trademarks of their respectiveowners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
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)
mysql> 使用工作接口來(lái)訪問(wèn)
[root@localhost ~]#mysql -h127.0.0.1 -P1234 -utest -ptest123
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 34
Server version: 5.0.81-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Other names may be trademarks of their respectiveowners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>
使用可視化管理工具Navicat登錄
著作權(quán)歸作者所有敬矩。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)概行，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處』≡溃互聯(lián)網(wǎng)+時(shí)代凳忙，時(shí)刻要保持學(xué)習(xí)业踏，攜手千鋒PHP,Dream It Possible。
