Maxscale安裝-讀寫分離(1)

前言

關于MySQL中間件的産品也很多,之前用過了360的Atlas、玩過MyCat。這邊我選擇 Maxscale的原因就是功能能滿足需求,也看好他的未來發展。

其實有關于如何安裝 Maxscale的文章百度一下一大把，寫這篇文章主要為了說明配置的某些現象，同時也為之後使用Maxscale的其他配置做下基礎。

我的環境

這邊我的'一主二從'已經是搭建好的了，如何搭建就不再描述了。

注意：這邊我的三個節點都沒有開啟 GTID，具體是為什麼在之後會講到，這邊大家留意一下就行。

192.168.137.11 (Maxscale)192.168.137.21:3306 (Master)

192.168.137.22:3306 (Slave)

192.168.137.23:3306 (Slave)

192.168.137.11 (Maxscale)

192.168.137.21:3306 (Master)

這邊我使用的用于複制的使用者是maxscale,具體權限如下:

CREATE USER maxscale@'%' IDENTIFIED BY "123456";GRANT replication slave, replication client ON *.* TO maxscale@'%';

GRANT SELECT ON mysql.* TO maxscale@'%';

GRANT ALL ON maxscale_schema.* TO maxscale@'%';

GRANT SHOW DATABASES ON *.* TO maxscale@'%';

root@(none) 22:34:15>SELECT VERSION();

+--------------------+

| VERSION() |

| 10.1.8-MariaDB-log |

1 row in set (0.00 sec)

root@(none) 22:34:25>SHOW SLAVE HOSTS;

+------------+----------------+------+------------+

| 3306137022 | 192.168.137.21 | 3306 | 3306137021 |

| 3306137023 | 192.168.137.21 | 3306 | 3306137021 |

2 rows in set (0.00 sec)

CREATE USER maxscale@'%' IDENTIFIED BY "123456";

GRANT replication slave, replication client ON *.* TO maxscale@'%';

| VERSION() |

下載下傳Maxscale

我的版本

[root@normal_11 opt]# pwd/opt

[root@normal_11 opt]# ll

total 149624

-rw-r--r-- 1 root root 3587510 Nov 2 21:07 maxscale-2.0.1.centos.7.tar.gz

[root@normal_11 opt]# pwd

/opt

-rw-r--r-- 1 root root 3587510 Nov 2 21:07 maxscale-2.0.1.centos.7.tar.gz

開始安裝

[root@normal_11 opt]# groupadd maxscale[root@normal_11 opt]# useradd -g maxscale maxscale

[root@normal_11 opt]# cd /opt

[root@normal_11 opt]# tar -zxf maxscale-2.0.1.centos.7.tar.gz

[root@normal_11 opt]# ln -s maxscale-2.0.1.centos.7 /usr/local/maxscale

[root@normal_11 opt]# chown -R maxscale:maxscale /usr/local/maxscale

[root@normal_11 opt]# mkdir -p /u01/maxscale/{data,cache,logs,tmp}

[root@normal_11 opt]# mkdir -p /u01/maxscale/logs/{binlog,trace}

[root@normal_11 opt]# chown -R maxscale:maxscale /u01/maxscale

[root@normal_11 opt]# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/

[root@normal_11 opt]# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456

1D30C1E689410756D7B82C233FCBF8D9

[root@normal_11 opt]# groupadd maxscale

[root@normal_11 opt]# useradd -g maxscale maxscale

Maxscale 配置檔案

[root@normal_11 opt]# cat /etc/maxscale.cnf ###################################################

# CREATE USER maxscale@'%' IDENTIFIED BY "123456";

# GRANT replication slave, replication client ON *.* TO maxscale@'%';

# GRANT SELECT ON mysql.* TO maxscale@'%';

# GRANT ALL ON maxscale_schema.* TO maxscale@'%';

# GRANT SHOW DATABASES ON *.* TO maxscale@'%';

# groupadd maxscale

# useradd -g maxscale maxscale

# cd /opt

# tar -zxf maxscale-2.0.1.rhel.7.tar.gz

# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale

# chown -R maxscale:maxscale /usr/local/maxscale

# mkdir -p /u01/maxscale/{data,cache,logs,tmp}

# mkdir -p /u01/maxscale/logs/{binlog,trace}

# chown -R maxscale:maxscale /u01/maxscale

# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/

# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456

###################################################

[maxscale]

# 開啟線程個數，預設為1.設定為auto會同cpu核數相同

threads=auto

# timestamp精度

ms_timestamp=1

# 将日志寫入到syslog中

syslog=1

# 将日志寫入到maxscale的日志檔案中

maxlog=1

# 不将日志寫入到共享緩存中，開啟debug模式時可打開加快速度

log_to_shm=0

# 記錄告警資訊

log_warning=1

# 記錄notice

log_notice=1

# 記錄info

log_info=1

# 不打開debug模式

log_debug=0

# 日志遞增

log_augmentation=1

# 相關目錄設定

basedir=/usr/local/maxscale/

logdir=/u01/maxscale/logs/trace/

datadir=/u01/maxscale/data/

cachedir=/u01/maxscale/cache/

piddir=/u01/maxscale/tmp/

[server1]

type=server

address=192.168.137.21

port=3306

protocol=MySQLBackend

serv_weight=1

[server2]

address=192.168.137.22

serv_weight=3

[server3]

address=192.168.137.23

[MySQL Monitor]

type=monitor

module=mysqlmon

servers=server1,server2,server3

user=maxscale

passwd=1D30C1E689410756D7B82C233FCBF8D9

# 監控心态為 10s

monitor_interval=10000

# 當複制slave全部斷掉時，maxscale仍然可用，将所有的通路指向master節點

detect_stale_master=true

# 監控主從複制延遲，可用後續指定router service的(配置此參數請求會永遠落在 master)

# detect_replication_lag=true

[Read-Only Service]

type=service

router=readconnroute

router_options=slave

# 允許root使用者登入執行

enable_root_user=1

# 查詢權重

weightby=serv_weight

[Read-Write Service]

router=readwritesplit

max_slave_connections=100%

# sql語句中的存在變量隻指向master中執行

use_sql_variables_in=master

# 允許主從最大間隔(s)

max_slave_replication_lag=3600

[MaxAdmin Service]

router=cli

[Read-Only Listener]

type=listener

service=Read-Only Service

protocol=MySQLClient

port=4008

[Read-Write Listener]

service=Read-Write Service

port=4006

[MaxAdmin Listener]

service=MaxAdmin Service

protocol=maxscaled

socket=/u01/maxscale/tmp/maxadmin.sock

port=6603

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

[root@normal_11 opt]# cat /etc/maxscale.cnf

細心的朋友會注意到, 的我配置檔案最上面就是安裝 Maxscale 的基本步驟，這是本人的一個習慣.

這邊我稍微說明一下配置檔案的意思:

[server1], [server2], [server3] 我配置了三個Maxscale需要連接配接的MySQL服務
[MySQL Monitor] 配置一個監聽服務, 同時監聽着 [server1], [server2], [server3] 的狀态
[Read-Only Service] 配置了隻讀服務, 隻在[server2], [server3]中執行

注意: 雖然是隻讀服務但是同樣可以執行 DML DDL, 說以要限制好使用者的權限.

[Read-Write Listener] 配置了讀寫分離的服務
[MaxAdmin Listener] 配置了使用者管理Maxscale的服務

示範

這邊我們以 [Read-Write Listener] 配置的服務來示範讀寫分離情況

啟動 Maxscale

如果啟動有報錯那就檢視一下日志 /var/log/message 或 /u01/maxscale/logs/trace/maxscale1.log(自定義)

[root@normal_11 opt]# /usr/local/maxscale/bin/maxscale -f /etc/maxscale.cnf[root@normal_11 opt]# netstat -natpl | grep max

tcp 0 0 0.0.0.0:4008 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 0.0.0.0:6603 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 0.0.0.0:4006 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 192.168.137.11:43102 192.168.137.22:3306 ESTABLISHED 5507/maxscale

tcp 0 0 192.168.137.11:54624 192.168.137.21:3306 ESTABLISHED 5507/maxscale

tcp 0 0 192.168.137.11:52989 192.168.137.23:3306 ESTABLISHED 5507/maxscale

[root@normal_11 opt]# /usr/local/maxscale/bin/maxscale -f /etc/maxscale.cnf

[root@normal_11 opt]# netstat -natpl | grep max

tcp 0 0 0.0.0.0:4008 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 0.0.0.0:6603 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 0.0.0.0:4006 0.0.0.0:* LISTEN 5507/maxscale

tcp 0 0 192.168.137.11:43102 192.168.137.22:3306 ESTABLISHED 5507/maxscale

tcp 0 0 192.168.137.11:54624 192.168.137.21:3306 ESTABLISHED 5507/maxscale

tcp 0 0 192.168.137.11:52989 192.168.137.23:3306 ESTABLISHED 5507/maxscale

使用 maxadmin 檢視服務

[root@normal_11 opt]# /usr/local/maxscale/bin/maxadmin -S /u01/maxscale/tmp/maxadmin.sock MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

server1 | 192.168.137.21 | 3306 | 0 | Master, Running

server2 | 192.168.137.22 | 3306 | 0 | Slave, Running

server3 | 192.168.137.23 | 3306 | 0 | Slave, Running

MaxScale> list services

Services.

--------------------------+----------------------+--------+---------------

Service Name | Router Module | #Users | Total Sessions

Read-Only Service | readconnroute | 1 | 1

Read-Write Service | readwritesplit | 1 | 1

MaxAdmin Service | cli | 3 | 3

[root@normal_11 opt]# /usr/local/maxscale/bin/maxadmin -S /u01/maxscale/tmp/maxadmin.sock

MaxScale> list servers

Server | Address | Port | Connections | Status

server1 | 192.168.137.21 | 3306 | 0 | Master, Running

server2 | 192.168.137.22 | 3306 | 0 | Slave, Running

server3 | 192.168.137.23 | 3306 | 0 | Slave, Running

Service Name | Router Module | #Users | Total Sessions

Read-Only Service | readconnroute | 1 | 1

Read-Write Service | readwritesplit | 1 | 1

MaxAdmin Service | cli | 3 | 3

通過登入Maxscale的讀寫分離服務, 來執行sql并且檢視日志，檢視日志路由情況。

注意: 這邊登入的使用者就是普通的MySQL使用者, 不是maxscale使用者

[root@normal_11 opt]# mysql -uHH -p -h192.168.137.11 -P4006Logging to file '/u01/mysql_history/query.log'

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5524

Server version: 5.5.5-10.0.0 2.0.1-maxscale MariaDB Server

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.

[email protected] 11:13:46 [(none)]>SELECT * FROM test.t1;

+----+------+-----+

| id | name | age |

| 1 | HH1 | 1 |

| 2 | HH2 | 2 |

| 3 | HH3 | 3 |

3 rows in set (0.01 sec)

[email protected] 11:15:03 [(none)]>INSERT INTO test.t1 VALUES(NULL, 'HH4', 4);

Query OK, 1 row affected (0.01 sec)

# 使用 HH 登入成的日志

2016-11-03 23:13:46.907 info : (log_server_connections): Servers and router connection counts:

2016-11-03 23:13:46.907 info : (log_server_connections): current operations : 0 in 192.168.137.21:3306 RUNNING MASTER

2016-11-03 23:13:46.907 info : (log_server_connections): current operations : 0 in 192.168.137.22:3306 RUNNING SLAVE

2016-11-03 23:13:46.907 info : (log_server_connections): current operations : 0 in 192.168.137.23:3306 RUNNING SLAVE

2016-11-03 23:13:46.908 info : (select_connect_backend_servers): Selected RUNNING MASTER in 192.168.137.21:3306

2016-11-03 23:13:46.908 info : (select_connect_backend_servers): Selected RUNNING SLAVE in 192.168.137.22:3306

2016-11-03 23:13:46.908 info : (select_connect_backend_servers): Selected RUNNING SLAVE in 192.168.137.23:3306

2016-11-03 23:13:46.908 info : (session_alloc): Started Read-Write Service client session [0] for 'HH' from 192.168.137.11

2016-11-03 23:13:46.909 [9] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1

2016-11-03 23:13:46.909 [9] info : (route_single_stmt): Route query to master 192.168.137.21:3306 <

2016-11-03 23:13:46.922 [9] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select USER()

2016-11-03 23:13:46.922 [9] info : (route_single_stmt): Route query to slave 192.168.137.22:3306 <

# 執行 SELECT * FROM test.t1 語句被路由到 192.168.137.22:3306[server2]中的日志

2016-11-03 23:15:02.618 [9] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * FROM test.t1

2016-11-03 23:15:02.618 [9] info : (route_single_stmt): Route query to slave 192.168.137.22:3306 <

# 執行 INSERT INTO test.t1 VALUES(NULL, 'HH4', 4) 語句被路由到 192.168.137.21:3306[server1]中的日志

2016-11-03 23:17:02.716 [9] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.t1 VALUES(NULL, 'HH4', 4)

2016-11-03 23:17:02.716 [9] info : (route_single_stmt): Route query to master 192.168.137.21:3306 <

[root@normal_11 opt]# mysql -uHH -p -h192.168.137.11 -P4006

Logging to file '/u01/mysql_history/query.log'