MySQL HA Solution 2019(4)MaxScale

标签: mysql ha solution | 发表时间:2019-08-03 21:19 | 作者:sillycat
出处:https://www.iteye.com
MySQL HA Solution 2019(4)MaxScale

You can find your download from here
https://mariadb.com/downloads/#mariadb_platform-mariadb_maxscale

I choose ubuntu 18.04 for my testing
> wget https://downloads.mariadb.com/MaxScale/2.3.11/ubuntu/dists/bionic/main/binary-amd64/maxscale-2.3.11-1.ubuntu.bionic.x86_64.deb

Here is how I install that
> sudo apt install ./maxscale-2.3.11-1.ubuntu.bionic.x86_64.deb

Go to my MySQL master machine
> mysql -u debian-sys-maint -pG1FEbrOMSORmcaUK
> use mysql;

Create Monitor Account
> create user scalemon@'%' identified by 'kaishi';
> grant replication slave, replication client on *.* to scalemon@'%';

Create Proxy Account
> create user maxscale@'%' identified by 'kaishi';
> grant select on mysql.* to maxscale@'%';

> flush privileges;

Check and Modify the Configuration
> sudo vi /etc/maxscale.cnf
> cat /etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-23/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/
[maxscale]
threads=auto
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[server1]
type=server
address=ubuntu-master
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=ubuntu-dev5
port=3306
protocol=MariaDBBackend
[server3]
type=server
address=ubuntu-dev6
port=3306
protocol=MariaDBBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-monitor/
#[MariaDB-Monitor]
[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=scalemon
password=kaishi
monitor_interval=10000
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readconnroute/
#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=kaishi
max_slave_connections=100%
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/
[MaxAdmin-Service]
type=service
router=cli
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

Start the service
> maxscale --config=/etc/maxscale.cnf

Some error message in the config
Protocol module 'mysqlclient' has been deprecated, use 'mariadbclient' instead.
error  : Invalid value for parameter 'service' for object 'Read-Only-Listener' of type 'listener': Read-Only-Service (was expecting a service name)
THE 'cli' MODULE AND 'maxadmin' ARE DEPRECATED: Use 'maxctrl' instead
Monitor module 'mysqlmon' has been deprecated, use 'mariadbmon' instead.
error  : Failed to open, read or process the MaxScale configuration file /etc/maxscale.cnf.
error  : Unable to find library for module: maxctrl. Module dir: /usr/lib/x86_64-linux-gnu/maxscale
warning: Protocol module 'mysqlbackend' has been deprecated, use 'mariadbbackend' instead.

Check logging file permission
> sudo chmod 777 -R /var/log/maxscale/
> sudo chmod 777 -R /var/lib/maxscale/
> sudo chmod 777 -R /var/run/maxscale/
> sudo chmod 777 -R /var/cache/maxscale/

Checking the logging, it starts
2019-08-03 12:46:04   notice : Selecting new master server.
2019-08-03 12:46:04   notice : Setting 'server1' as master.
2019-08-03 12:46:04   notice : Server changed state: server1[ubuntu-master:3306]: new_master. [Running] -> [Master, Running]
2019-08-03 12:46:04   notice : Server changed state: server2[ubuntu-dev5:3306]: new_slave. [Running] -> [Slave, Running]
2019-08-03 12:46:04   notice : Server changed state: server3[ubuntu-dev6:3306]: new_slave. [Running] -> [Slave, Running]

I used to have an account mycat/mycat when I test mycat, now, I will try that.
> mysql -h ubuntu-dev5 -P 4006 -u mycat -pmycat

> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| ubuntu-dev5 |

> start transaction;
> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| ubuntu-master |

> rollback;
> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| ubuntu-dev5 |

> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mycat              |
| mysql              |

> use mycat;

> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| mycatuser       |


> insert into mycatuser(id, name) values (1, 'carl');

> select * from mycatuser;
+----+------+
| id | name |
+----+------+
|  1 | carl |

Some tools
> sudo maxadmin enable account carl
> maxadmin -S /var/run/maxscale/maxadmin.sock list servers;
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status             
-------------------+-----------------+-------+-------------+--------------------
server1            | ubuntu-master   |  3306 |           1 | Master, Running
server2            | ubuntu-dev5     |  3306 |           1 | Slave, Running
server3            | ubuntu-dev6     |  3306 |           1 | Slave, Running


Open logging on all mysql
> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Restart the service
> sudo /etc/init.d/mysql restart

Or
> sudo service mysql restart

Checking logging
> sudo tail -f /var/log/mysql/mysql.log


References:
https://www.centos.bz/2018/01/mariadb%E4%B8%BB%E4%BB%8E%E9%85%8D%E7%BD%AE%E4%B8%8Emaxscale%E5%AE%9E%E7%8E%B0mysql%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB/
http://www.ttlsa.com/mysql/maxscale-install-read-write-split/
https://www.jianshu.com/p/95e79ae11a20
https://toutiao.io/posts/zwq2k1/preview
https://yq.aliyun.com/articles/515688/






已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql ha solution] 推荐:

MySQL HA Solution 2019(3)MyCat

- - 企业架构 - ITeye博客
0  . 0  .       .

MySQL HA Solution 2019(4)MaxScale

- - 企业架构 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

MySQL HA Solution 2019(2)ProxySQL

- - 企业架构 - ITeye博客
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |.

MySQL HA Solution 2019(1)Master Slave on MySQL 5.7

- - 企业架构 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

MySQL HA 的選擇…

- - Gea-Suan Lin's BLOG
Percona 把常見的 MySQL High Availability 選擇整理後發表成 Webinar,投影片在這裡可以看到 (以及下載):「 Choosing a MySQL High Availability Solution」. 沒有太多新的東西,主要還是再次描述 MySQL HA 這塊目前沒有萬靈丹,常見的這幾個方案各有自己的優缺點,會依照環境與需求而產生不同的選擇.

MySQL HA 高可用性,MySQL Cluster 叢集

- - SSORC.tw
而 SQL Node (mysqld程序) 只是讓我們建立資料庫、表的地方 (看得到/var/lib/mysql/XXX),只是 SQL Node 這邊是看不到實際空間用量的. manager node 及所有的 node 都要裝 mysql-cluster (到 mysql 官網下載). manager node 設定,它只要 ndb_mgm 與 ndb_mgmd 而已.

利用keepalived构建高可用MySQL-HA(转)

- - 数据库 - ITeye博客
关于MySQL-HA,目前有多种解决方案,比如heartbeat、drbd、mmm、共享存储,但是它们各有优缺点. heartbeat、drbd配置较为复杂,需要自己写脚本才能实现MySQL自动切换,对于不会脚本语言的人来说,这无疑是一种脑裂问题;对于mmm,生产环境中很少有人用,且mmm 管理端需要单独运行一台服务器上,要是想实现高可用,就得对mmm管理端做HA,这样无疑又增加了硬件开支;对于共享存储,个人觉得MySQL数据还是放在本地较为安全,存储设备毕竟存在单点隐患.

HA-JDBC -

- -
The state manager component is responsible for storing the active status of each database in the cluster, as well as any durability state.

nginx + keepalive 实现HA

- - CSDN博客编程语言推荐文章
主nginx负载均衡器 192.168.166.203. 辅nginx负载均衡器 192.168.166.177. VIP地址 192.168.166.178. 二.修改配置文件为以下内容: [master slave].  state MASTER #(主机为MASTER,备用机为BACKUP).

SUSE Linux HA双机搭建

- - CSDN博客数据库推荐文章
原来的数据库服务器运行在HP DL388G7服务器上面,内存32G,由于业务增长,内存吃紧,加上时不时出现服务器硬件故障,由于是单实例单服务器,存在单点发现,于是打算采取一些措施改善一下:. 2)并搭建服务器操作系统级别的双机. 3)迁移数据库数据到新服务器. 前面已经写过数据迁移相关的文章,题目为“ EXP/IMP迁移数据”,链接如下: http://blog.csdn.net/laven54/article/details/8877940.