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)
