MHA +maxscale 实现读写分离高可用架构实战

xiaoxiao2021-02-28  11

MHA 相关原理 http://mp.blog.csdn.net/postedit/79183726

 MHA  http://mp.blog.csdn.net/postedit/79183743 最佳实战

MySQL读写分离原理相关知识体系  http://blog.csdn.net/wjl7813/article/details/79184468

本文以第2种方式实现(基于中间代理层实现)

创建监控复制状态账号 grant replication slave, replication client on *.* to scalemon@'192.168.137.%' identified by 'monitor18'; 创建业务账号  grant all privileges on mycat.* to user_maxscale@'192.168.137.%' identified by 'H2kXvhaJxRSl';  grant select on *.* to user_maxscale@'192.168.137.%'; mkdir -p /data1/maxscale4007/log/ mkdir -p /data1/maxscale4007/data/ mkdir -p /data1/maxscale4007/cache/ mkdir -p /data1/maxscale4007/pid/ chown -R maxscale:maxscale /data1/maxscale4007 2.1.6.maxsacle创建秘钥 [root@mha-manager opt]# maxpasswd /data1/maxscale4007/data/.secrets monitor18 Failed to encode the password [root@mha-manager opt]# maxkeys /data1/maxscale4007/data/ [root@mha-manager opt]# maxpasswd /data1/maxscale4007/data/.secrets monitor18 E2D5388FEC655E04F977AFFE91F08240 [root@mha-manager opt]# maxpasswd /data1/maxscale4007/data/.secrets H2kXvhaJxRSl DAA9A9DD308D222752B398EBC03474EF vim /etc/maxscale4007.cnf  [maxscale] threads=auto    maxlog=1        log_to_shm=0    log_warning=1   log_error=1     log_notice=1    log_info=0      log_debug=0     #log_augmentation=1 LimitNOFILE=65536 logdir=/data1/maxscale4007/log/   datadir=/data1/maxscale4007/data/   cachedir=/data1/maxscale4007/cache/   piddir=/data1/maxscale4007/pid/   libdir=/usr/lib64/maxscale/   execdir=/usr/bin/   [server1] type=server address=192.168.137.141 port=3389 protocol=MySQLBackend [server2] type=server address=192.168.137.142 port=3389 protocol=MySQLBackend [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2 user=scalemon passwd=E2D5388FEC655E04F977AFFE91F08240 monitor_interval=5000      #detect_replication_lag=true  #max_slave_replication_lag  detect_stale_master=true   #[Read-Only Service] #type=service #router=readconnroute #servers=server2 #user=user_maxscale #passwd=A622DB34CBF413527DE9048EBC3FE42E66EF97D8DD167887F781C5108213FD21 #router_options=slave [Read-Write Service] type=service router=readwritesplit servers=server1,server2 user=user_maxscale passwd=DAA9A9DD308D222752B398EBC03474EF max_slave_connections=100% use_sql_variables_in=master  enable_root_user=1   #master_accept_reads=true  [MaxAdmin Service] type=service router=cli #[Read-Only Listener] #type=listener #service=Read-Only Service #protocol=MySQLClient #port=3300 [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4007 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=/data1/maxscale4007/maxadmin.sock port=6604 启动 maxscale [root@mha-manager opt]# maxscale -f /etc/maxscale4007.cnf  maxscale服务状态 [root@mha-manager opt]# maxadmin -S /data1/maxscale4007/maxadmin.sock MaxScale>  list listeners Listeners. ---------------------+--------------------+-----------------+-------+-------- Service Name         | Protocol Module    | Address         | Port  | State ---------------------+--------------------+-----------------+-------+-------- Read-Write Service   | MySQLClient        | *               |  4007 | Running MaxAdmin Service     | maxscaled          | *               |  6604 | Running MaxAdmin Service     | maxscaled          | /data1/maxscale4007/maxadmin.sock |     0 | Running ---------------------+--------------------+-----------------+-------+-------- MaxScale> list servers  Servers. -------------------+-----------------+-------+-------------+-------------------- Server             | Address         | Port  | Connections | Status               -------------------+-----------------+-------+-------------+-------------------- server1            | 192.168.137.141 |  3389 |           0 | Master, Running server2            | 192.168.137.142 |  3389 |           0 | Slave, Running -------------------+-----------------+-------+-------------+-------------------- MaxScale> list services  Services. --------------------------+----------------------+--------+--------------- Service Name              | Router Module        | #Users | Total Sessions --------------------------+----------------------+--------+--------------- Read-Write Service        | readwritesplit       |      1 |     1 MaxAdmin Service          | cli                  |      3 |     4 --------------------------+----------------------+--------+--------------- MaxScale> show services Service 0x1915e10 Service:                             Read-Write Service Router:                              readwritesplit (0x7f7e0a75fd40) State:                               Started Number of router sessions:           0 Current no. of router sessions:       0 Number of queries forwarded:           0 Number of queries forwarded to master: 0 (0.00%) Number of queries forwarded to slave: 0 (0.00%) Number of queries forwarded to all:   0 (0.00%) Started:                             Tue Dec 26 05:02:16 2017 Root user access:                    Enabled Backend databases: 192.168.137.141:3389  Protocol: MySQLBackend 192.168.137.142:3389  Protocol: MySQLBackend Users data:                          0x192b5f0 Total connections:                   1 Currently connected:                 1 Service 0x1914350 Service:                             MaxAdmin Service Router:                              cli (0x7f7e0a969560) State:                               Started Started:                             Tue Dec 26 05:02:16 2017 Root user access:                    Disabled Backend databases: Users data:                          0x193bfa0 Total connections:                   4 Currently connected:                 3 [mysql@mha-manager mha]$ mysql -h 192.168.137.140 -P4007 -uuser_maxscale -pH2kXvhaJxRSl  

mysql> select @@hostname; +--------------+ | @@hostname   | +--------------+ | node1-slave1 | +--------------+ 1 row in set (0.00 sec)

master 创建一个测试库 percona 

mysql> create database percona; Query OK, 1 row affected (2.65 sec) mysql> use percona; Database changed mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); Query OK, 0 rows affected (0.33 sec) mysql>  INSERT INTO percona.example VALUES (1, 'percona1'); Query OK, 1 row affected (0.14 sec) mysql>  mysql> select * from example; +---------+-----------+ | node_id | node_name | +---------+-----------+ |       1 | percona1  | +---------+-----------+ 1 row in set (0.00 sec) mysql> INSERT INTO percona.example VALUES (2, 'percona2'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO percona.example VALUES (3, 'percona3'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from example; +---------+-----------+ | node_id | node_name | +---------+-----------+ |       1 | percona1  | |       2 | percona2  | |       3 | percona3  | +---------+-----------+ 3 rows in set (0.00 sec) mysql> select * from example; +---------+-----------+ | node_id | node_name | +---------+-----------+ |       1 | percona1  | |       2 | percona2  | |       3 | percona3  | +---------+-----------+ 3 rows in set (0.00 sec) mysql> select * from example; +---------+-----------+ | node_id | node_name | +---------+-----------+ |       1 | percona1  | |       2 | percona2  | |       3 | percona3  | +---------+-----------+ 3 rows in set (0.00 sec)

转载请注明原文地址: https://www.6miu.com/read-1600385.html

最新回复(0)