`
sillycat
  • 浏览: 2539465 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

MySQL HA Solution 2019(4)MaxScale

 
阅读更多
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/




分享到:
评论

相关推荐

    mysql router vs maxscale

    4. MySQL Router和MaxScale配置与功能对比: - MySQL Router配置简易,通过配置文件指定master和slave节点,并可以设置不同端口以实现read-write和read-only的路由策略。读写分离不自动,需要程序代码根据端口号...

    Mysql+Maxscale2.0读写分离

    ### MySQL + MaxScale 2.0 读写分离部署与测试 #### 一、概述 在高并发场景下,为了提高数据库系统的响应速度并减轻单个数据库服务器的压力,通常会采用读写分离的方式进行数据库访问优化。MySQL作为一款广泛使用...

    mysql8.0.28版本和vc2019依赖

    MySQL 8.0.28 版本可能包含了一些使用 VC2019 编译的动态链接库,因此在运行时需要这些库的支持。 首先,我们来详细了解一下 MySQL 8.0.28 的主要特性: 1. **性能优化**:此版本对查询执行引擎进行了优化,特别是...

    RoseHA关于linux下mysql配置

    RoseHA是在Linux环境下配置MySQL高可用性解决方案的必备工具。RoseHA可以帮助数据库管理员快速搭建MySQL的高可用集群环境,保证业务连续性,当一台服务器发生故障时,另一台服务器能够立即接管服务,减少或消除系统...

    Mysql HA Scale-Out

    **MySQL HA Scale-Out** MySQL高可用性(High Availability, HA)和扩展性(Scale-Out)是数据库系统设计中的关键组成部分,旨在确保数据的持续可用性和系统性能的提升。在这个主题中,我们将深入探讨MySQL在实现HA...

    VS2019+EF+Mysql生成实体数据模型(解决闪退的坑)VS2019+mysql+vs-mysql.rar

    最近使用VS2019连接数据库生成模型,生成实体,遇到的坑,找了好多资料在这里做个总结。 一、安装环境 mysql-installer-community-5.6.49.0.msi Visual Studio 2019专业版 mysql-for-visualstudio-1.2.9.msi

    MySQL 的 HA、读写分离、均衡负载 中文PDF版.rar

    本书主要讲述MySQL 的 HA、读写分离、均衡负载;感兴趣的朋友可以过来看看 截图: 标签:MySQL 负载均衡 读写分离 MySQLHA 人气书籍...

    mysql8.0.25安装包和依赖vc2019,关注我免费下载!

    在安装 MySQL 8.0.25 时,通常需要依赖 Microsoft Visual C++(VC)运行库,如 VC2019,因为 MySQL 的安装程序可能需要这些组件来正确运行。 **MySQL 8.0.25 的主要特点:** 1. **增强的性能**:MySQL 8.0.25 包含...

    mysql-ha-双主安装

    ### MySQL-HA 双主安装知识点详解 #### 一、MySQL-HA 双主复制原理与实践背景 MySQL-HA(High Availability)双主安装旨在通过构建一个高可用性架构来确保MySQL服务即使在一个节点发生故障的情况下仍能不间断地...

    MYSQL集群+HA高可用【MySQL性能调优】.pdf

    本文详细介绍了如何通过MySQL集群加上高可用性(HA)配置来实现MySQL性能调优的实践案例,文档中涉及到的操作步骤,适用于希望提高数据库性能与稳定性的用户。 知识点1:MySQL Cluster简介 MySQL Cluster是一种多主机...

    MySQL高可用方案之Corosync-Pacemake+MaxScale.docx

    ### MySQL高可用方案之Corosync-Pacemaker+MaxScale #### 一、概述 本文档旨在介绍一种基于Corosync-Pacemaker与MaxScale的MySQL高可用解决方案。该方案结合了Corosync的分布式协调功能与Pacemaker的资源管理能力...

    mysql基于keepalived的HA搭建.pdf

    ### MySQL基于Keepalived的HA搭建知识点 #### 1. MySQL双主架构 在MySQL的高可用性架构中,双主架构可以提供数据的备份和故障转移能力。通常,双主架构意味着两个MySQL服务器相互作为对方的主服务器和从服务器,...

    mysql+heartbeat+共享存储实施方案(mysql高可用集群).doc

    【MySQL+Heartbeat+共享存储实施方案】是一种实现MySQL高可用集群的方法,旨在确保数据库服务的稳定性和连续性。该方案采用HA(High Availability)双机热备软件Heartbeat,配合共享存储来实现实时的数据同步和故障...

    mysql高可用集群部署项目1

    该项目主要涉及到 MySQL 的主从同步、MaxScale 读写分离和 Keepalived+DR 高可用性机制等技术。 一、MySQL 主从同步配置 MySQL 主从同步是指将一个 MySQL 实例(master)中的数据实时同步到另一个 MySQL 实例...

    mysql5.7.29双主HA部署方案.docx

    捣鼓了一个周末--20200920,完整的整理出了这个mysql5.7.29双主HA最清晰最详细最易读的部署方案。本人辛苦的原创,照着我这个文档部署,你肯定可以完美部署。 有需要 的话,加我微信:fage0161

    ha_mysql共享存储实现.txt

    heartbeat mysqld uplooking从最简单用例讲解HA的高可用集群等信息,并结合MYSQL把ISCSI作为共享存存储的SAN服务,整个个流程简单完整的方案

    ha_sphinx for mysql5.5.29

    4. **SQL查询集成**:在MySQL查询中,使用`MATCH()`和`AGAINST()`函数进行全文检索,如同普通的SQL查询一样方便。 5. **性能优化**:调整Sphinx和MySQL的配置参数,比如设置合适的预读块大小,调整索引的分词规则,...

    win10+Qt5.15.2+VS2019_64编译mysql8.0生成的驱动插件

    在本文中,我们将深入探讨如何在Windows 10操作系统上,使用Qt 5.15.2和Visual Studio 2019 64位版本编译MySQL 8.0驱动,以便为Qt应用程序创建所需的数据库连接插件。这个过程对于开发依赖于MySQL数据库的Qt应用至关...

Global site tag (gtag.js) - Google Analytics