- 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 |
