MaxScale读写分离配置

  • A+
所属分类：Database

MaxScale读写分离配置

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
[root@DB3 ~]# yum install libaio libaio-devel novacom-server
[root@DB3 ~]# wget https://downloads.mariadb.com/enterprise/fre6-c9jr/mariadb-maxscale/2.1.0/centos/6/x86_64/maxscale-2.1.0-1.centos.6.x86_64.rpm
[root@DB3 ~]# rpm -ivh maxscale-2.1.0-1.centos.6.x86_64.rpm
mysql> create user jiankong@'%' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant replication slave,replication client on *.* to jiankong@'%';
Query OK, 0 rows affected (0.01 sec)
 
mysql> show grants for jiankong@'%';                                          
+----------------------------------------------------------------------+
| Grants for jiankong@%                                                |
+----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'jiankong'@'%' |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create user route@'%' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
 
 
mysql> show grants for route@'%';                            
+------------------------------------------+
| Grants for route@%                       |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'route'@'%'        |
| GRANT SELECT ON `mysql`.* TO 'route'@'%' |
+------------------------------------------+
2 rows in set (0.00 sec)
 
 
mysql> grant select on mysql.* to route@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for jiankong@'%';                                  
+----------------------------------------------------------------------+
| Grants for jiankong@%                                                |
+----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'jiankong'@'%' |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)
[root@DB3 ~]# /etc/init.d/maxscale start              
Starting MaxScale: maxscale (pid 11616) is running...      [  OK  ]
[root@DB3 ~]# maxadmin --user=admin --password=mariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 1.1.1.131       |  3306 |           0 | Master, Running
server2            | 1.1.1.132       |  3306 |           0 | Slave, Running
server3            | 1.1.1.133       |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> show dbusers "Read-Write Service"
User names: jiankong@%, mha@1.1.%.%, repl@1.1.%.%, route@%, mysql.sys@127.0.0.1
[root@DB3 ~]# grep -v "^$\|#" /etc/maxscale.cnf
[maxscale]
threads=auto
[server1]
type=server
address=12.1.10.1
port=3307
protocol=MySQLBackend
serv_weight=2
 
[server2]
type=server
address=12.1.20.1
port=3306
protocol=MySQLBackend
serv_weight=4
 
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=root
passwd=admin
# if slave down master
detect_stale_master=true
monitor_interval=10000
 
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=root
passwd=admin
enable_root_user=1
max_slave_connections=100%
weightby=serv_weight
 
[MaxAdmin Service]
type=service
router=cli
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=3306
 
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=6603

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[root@DB3 ~]# grep -v "^$\|#" /etc/maxscale.cnf
[maxscale]
threads=4
[server1]
type=server
address=1.1.1.131
port=3306
protocol=MySQLBackend
[server2]
type=server
address=1.1.1.132
port=3306
protocol=MySQLBackend
[server3]
type=server
address=1.1.1.133
port=3306
protocol=MySQLBackend
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=jiankong
passwd=admin
monitor_interval=10000
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=route
passwd=admin
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[maxscale]
threads=auto
[server1]
type=server
address=1.1.1.131
port=3306
protocol=MySQLBackend
[server2]
type=server
address=1.1.1.132
port=3306
protocol=MySQLBackend
[server3]
type=server
address=1.1.1.133
port=3306
protocol=MySQLBackend
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=jiankong
passwd=admin
monitor_interval=10000
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=root
passwd=123456
router_options=slave
enable_root_user=1
[Read-Write Service]
type=service
router=readwritesplit
servers=server1
user=route
passwd=admin
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=6603

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
[maxscale]
threads=auto
ms_timestamp=1
syslog=1
maxlog=1
log_to_shm=0
log_warning=1
log_notice=1
log_info=1
log_debug=0
log_augmentation=1
[server1]
type=server
address=1.1.1.131
port=3306
protocol=MySQLBackend
  
[server2]  
type=server  
address=1.1.1.132
port=3306
protocol=MySQLBackend
[server3]
type=server
address=1.1.1.133
port=3306
protocol=MySQLBackend
  
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=jiankong
passwd=admin
monitor_interval=1000
detect_replication_lag=true
detect_stale_master=true
  
  
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=root
passwd=123456
router_options=slave
enable_root_user=1
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=root
passwd=123456
use_sql_variables_in=master
enable_root_user=1
master_accept_reads=true
max_slave_replication_lag=5
[MaxAdmin Service]
type=service
router=cli
  
  
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603