通過Atlas實現MySQL讀寫分離
作者：Crazy_Coder
最近公司項目要求MySQL高可用，加上以前公司聽過QiHoo360的Atlas，所以就嘗試搭建了一個MySQL讀寫分離，并且高可用的。
【引自Crazy_Coder的博客】前期準備
- 準備4臺機器，系統為CentOS release 6.6
- Ip分別為192.168.20.121、192.168.20.122、192.168.20.123、192.168.20.124
- 4臺機器分別作為Atlas代理服務，master MySQL，slave MySQL 1，slave MySQL 2
- 下載QiHoo360的Atlas 地址
安裝Atlas
1. 下載得到Atlas-XX.el6.x86_64.rpm安裝文件
2. sudo rpm –i Atlas-XX.el6.x86_64.rpm安裝
3. 安裝在/usr/local/mysql-proxy
4. 安裝目錄分析
- bin
- 可執行文件
- encrypt用來加密密碼，后面會用到
- mysql-proxy是MySQL自己的讀寫分離代理
- mysql-proxyd操作Atlas
- VERSION
- conf
- test.cnf配置文件
- 一個文件為一個實例，實例名即文件名，啟動需要帶上這個實例名
- lib依賴包
- log記錄日志
5. 啟動命令：/usr/local/mysql-proxy/bin/mysql-proxyd [實例名] start
6. 停止命令：/usr/local/mysql-proxy/bin/mysql-proxyd [實例名] stop
7. 同理，restart為重啟，status為查看狀態
8. 配置文件解釋
請查看官方文檔
數據庫配置
1. 1臺master2臺slave，都要配置相同的用戶名密碼，且都要可以遠程訪問
2. 分別進入3臺服務器，創建相同的用戶名密碼，創建數據庫test，設置權限
- 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;
3. 主從數據庫配
1. 配置master服務器
- 找到MySQL配置文件my.cnf，一般在etc目錄下
- 修改配置文件
- [mysqld]
- # 一些其他配置
- ...
- #主從復制配置
- 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
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
- 重啟數據庫service mysql restart
- 進入數據庫，配置主從復制的權限
- mysql -uroot -p123456
- grant replication slave on *.* to 'test'@'127.0.0.1' identified by 'test123';
- 查看主數據庫信息，記住下面的File與Position的信息，它們是用來配置從數據庫的關鍵信息。
- 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)
2. 配置兩臺salve服務器
- 找到配置文件my.cnf
- 修改配置文件如下
- [mysqld]
- # 一些其他配置
- ...
- # 幾臺服務器不能一樣
- server-id=2
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
- 進入數據庫，配置從數據庫的信息，這里輸入剛才記錄下來的File與Position的信息，并且在從服務器上執行(執行的時候，#行不要復制進去)：
- # master數據庫的ip
- mysql> change master to master_host='192.168.20.122',
- # master的用戶名
- master_user='buck',
- # 密碼
- master_password='hello',
- # 端口
- master_port=3306,
- # master數據庫的`File `
- master_log_file='mysql-bin.000002',
- # master數據庫的`Position`
- master_log_pos=17620976,
- master_connect_retry=10;
- 啟動進程
- mysql> start slave;
- Query OK, 0 rows affected (0.00 sec)
- 檢查主從復制狀態，要看到下列Slave_IO_Running、Slave_SQL_Running的信息中，兩個都是Yes，才說明主從連接正確，如果有一個是No，需要重新確定剛才記錄的日志信息，停掉“stop slave”重新進行配置主從連接。
- mysql> show slave status \G;
- *************************** 1. 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配置
1. 使用Atlas的加密工具對上面用戶的密碼進行加密
- /usr/local/mysql-proxy/bin/encrypt test123
- 29uENYYsKLo=
2. 配置atlas
- 這是用來登錄到Atlas的管理員的賬號與密碼，與之對應的是Atlas監聽的管理接口IP和端口，也就是說需要設置管理員登錄的端口，才能進入管理員界面，默認端口是2345，也可以指定IP登錄，指定IP后，其他的IP無法訪問管理員的命令界面。方便測試，我這里沒有指定IP和端口登錄。
- 配置主數據的地址與從數據庫的地址，這里配置的主數據庫是122，從數據庫是123、124
- 主庫的IP和端口，可設置多項，用逗號分隔
- proxy-backend-addresses = 192.168.20.122:3306
- #Atlas后端連接的MySQL從庫的IP和端口，@后面的數字代表權重，用來作負載均衡，若省略則默認為1，可設置多項，用逗號分隔
- proxy-read-only-backend-addresses = 192.168.20.123:3306[@1](https://my.oschina.net/u/1198),192.168.20.124:3306@
- 這個是用來配置MySQL的賬戶與密碼的，就是上面創建的用戶，用戶名是test,密碼是test123,剛剛使用Atlas提供的工具生成了對應的加密密碼
- pwds = buck:RePBqJ+5gI4=
3. 啟動Atlas
- [root[@localhost](https://my.oschina.net/u/570656) /usr/local/mysql-proxy/bin]# ./mysql-proxyd test start
- OK: MySQL-Proxy of test is started
測試
1. 進入atlas的管理界面
- [root[@localhost](https://my.oschina.net/u/570656) ~]#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
- affiliates. Other names may be trademarks of their respective
- owners.
- 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>
2. 使用工作接口來訪問
- [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
- affiliates. Other names may be trademarks of their respective
- owners.
- 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登錄
使用用戶名test、密碼test123、端口1234、地址192.168.20.121正常登錄。注意，這里登錄的是atlas服務器，不再是任何一個MySQL服務器
責任編輯：龐桂玉
來源：
Crazy_Coder的博客
