- 浏览: 2539381 次
- 性别:
- 来自: 成都
文章分类
最新评论
-
nation:
你好,在部署Mesos+Spark的运行环境时,出现一个现象, ...
Spark(4)Deal with Mesos -
sillycat:
AMAZON Relatedhttps://www.godad ...
AMAZON API Gateway(2)Client Side SSL with NGINX -
sillycat:
sudo usermod -aG docker ec2-use ...
Docker and VirtualBox(1)Set up Shared Disk for Virtual Box -
sillycat:
Every Half an Hour30 * * * * /u ...
Build Home NAS(3)Data Redundancy -
sillycat:
3 List the Cron Job I Have>c ...
Build Home NAS(3)Data Redundancy
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/
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/
发表评论
-
Stop Update Here
2020-04-28 09:00 310I will stop update here, and mo ... -
NodeJS12 and Zlib
2020-04-01 07:44 465NodeJS12 and Zlib It works as ... -
Docker Swarm 2020(2)Docker Swarm and Portainer
2020-03-31 23:18 361Docker Swarm 2020(2)Docker Swar ... -
Docker Swarm 2020(1)Simply Install and Use Swarm
2020-03-31 07:58 363Docker Swarm 2020(1)Simply Inst ... -
Traefik 2020(1)Introduction and Installation
2020-03-29 13:52 328Traefik 2020(1)Introduction and ... -
Portainer 2020(4)Deploy Nginx and Others
2020-03-20 12:06 419Portainer 2020(4)Deploy Nginx a ... -
Private Registry 2020(1)No auth in registry Nginx AUTH for UI
2020-03-18 00:56 428Private Registry 2020(1)No auth ... -
Docker Compose 2020(1)Installation and Basic
2020-03-15 08:10 364Docker Compose 2020(1)Installat ... -
VPN Server 2020(2)Docker on CentOS in Ubuntu
2020-03-02 08:04 444VPN Server 2020(2)Docker on Cen ... -
Buffer in NodeJS 12 and NodeJS 8
2020-02-25 06:43 376Buffer in NodeJS 12 and NodeJS ... -
NodeJS ENV Similar to JENV and PyENV
2020-02-25 05:14 464NodeJS ENV Similar to JENV and ... -
Prometheus HA 2020(3)AlertManager Cluster
2020-02-24 01:47 413Prometheus HA 2020(3)AlertManag ... -
Serverless with NodeJS and TencentCloud 2020(5)CRON and Settings
2020-02-24 01:46 330Serverless with NodeJS and Tenc ... -
GraphQL 2019(3)Connect to MySQL
2020-02-24 01:48 242GraphQL 2019(3)Connect to MySQL ... -
GraphQL 2019(2)GraphQL and Deploy to Tencent Cloud
2020-02-24 01:48 443GraphQL 2019(2)GraphQL and Depl ... -
GraphQL 2019(1)Apollo Basic
2020-02-19 01:36 320GraphQL 2019(1)Apollo Basic Cl ... -
Serverless with NodeJS and TencentCloud 2020(4)Multiple Handlers and Running wit
2020-02-19 01:19 306Serverless with NodeJS and Tenc ... -
Serverless with NodeJS and TencentCloud 2020(3)Build Tree and Traverse Tree
2020-02-19 01:19 310Serverless with NodeJS and Tenc ... -
Serverless with NodeJS and TencentCloud 2020(2)Trigger SCF in SCF
2020-02-19 01:18 284Serverless with NodeJS and Tenc ... -
Serverless with NodeJS and TencentCloud 2020(1)Running with Component
2020-02-19 01:17 302Serverless with NodeJS and Tenc ...
相关推荐
4. MySQL Router和MaxScale配置与功能对比: - MySQL Router配置简易,通过配置文件指定master和slave节点,并可以设置不同端口以实现read-write和read-only的路由策略。读写分离不自动,需要程序代码根据端口号...
### MySQL + MaxScale 2.0 读写分离部署与测试 #### 一、概述 在高并发场景下,为了提高数据库系统的响应速度并减轻单个数据库服务器的压力,通常会采用读写分离的方式进行数据库访问优化。MySQL作为一款广泛使用...
MySQL 8.0.28 版本可能包含了一些使用 VC2019 编译的动态链接库,因此在运行时需要这些库的支持。 首先,我们来详细了解一下 MySQL 8.0.28 的主要特性: 1. **性能优化**:此版本对查询执行引擎进行了优化,特别是...
RoseHA是在Linux环境下配置MySQL高可用性解决方案的必备工具。RoseHA可以帮助数据库管理员快速搭建MySQL的高可用集群环境,保证业务连续性,当一台服务器发生故障时,另一台服务器能够立即接管服务,减少或消除系统...
**MySQL HA Scale-Out** MySQL高可用性(High Availability, HA)和扩展性(Scale-Out)是数据库系统设计中的关键组成部分,旨在确保数据的持续可用性和系统性能的提升。在这个主题中,我们将深入探讨MySQL在实现HA...
最近使用VS2019连接数据库生成模型,生成实体,遇到的坑,找了好多资料在这里做个总结。 一、安装环境 mysql-installer-community-5.6.49.0.msi Visual Studio 2019专业版 mysql-for-visualstudio-1.2.9.msi
本书主要讲述MySQL 的 HA、读写分离、均衡负载;感兴趣的朋友可以过来看看 截图: 标签:MySQL 负载均衡 读写分离 MySQLHA 人气书籍...
在安装 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(High Availability)双主安装旨在通过构建一个高可用性架构来确保MySQL服务即使在一个节点发生故障的情况下仍能不间断地...
本文详细介绍了如何通过MySQL集群加上高可用性(HA)配置来实现MySQL性能调优的实践案例,文档中涉及到的操作步骤,适用于希望提高数据库性能与稳定性的用户。 知识点1:MySQL Cluster简介 MySQL Cluster是一种多主机...
### MySQL高可用方案之Corosync-Pacemaker+MaxScale #### 一、概述 本文档旨在介绍一种基于Corosync-Pacemaker与MaxScale的MySQL高可用解决方案。该方案结合了Corosync的分布式协调功能与Pacemaker的资源管理能力...
### MySQL基于Keepalived的HA搭建知识点 #### 1. MySQL双主架构 在MySQL的高可用性架构中,双主架构可以提供数据的备份和故障转移能力。通常,双主架构意味着两个MySQL服务器相互作为对方的主服务器和从服务器,...
【MySQL+Heartbeat+共享存储实施方案】是一种实现MySQL高可用集群的方法,旨在确保数据库服务的稳定性和连续性。该方案采用HA(High Availability)双机热备软件Heartbeat,配合共享存储来实现实时的数据同步和故障...
该项目主要涉及到 MySQL 的主从同步、MaxScale 读写分离和 Keepalived+DR 高可用性机制等技术。 一、MySQL 主从同步配置 MySQL 主从同步是指将一个 MySQL 实例(master)中的数据实时同步到另一个 MySQL 实例...
捣鼓了一个周末--20200920,完整的整理出了这个mysql5.7.29双主HA最清晰最详细最易读的部署方案。本人辛苦的原创,照着我这个文档部署,你肯定可以完美部署。 有需要 的话,加我微信:fage0161
heartbeat mysqld uplooking从最简单用例讲解HA的高可用集群等信息,并结合MYSQL把ISCSI作为共享存存储的SAN服务,整个个流程简单完整的方案
4. **SQL查询集成**:在MySQL查询中,使用`MATCH()`和`AGAINST()`函数进行全文检索,如同普通的SQL查询一样方便。 5. **性能优化**:调整Sphinx和MySQL的配置参数,比如设置合适的预读块大小,调整索引的分词规则,...
在本文中,我们将深入探讨如何在Windows 10操作系统上,使用Qt 5.15.2和Visual Studio 2019 64位版本编译MySQL 8.0驱动,以便为Qt应用程序创建所需的数据库连接插件。这个过程对于开发依赖于MySQL数据库的Qt应用至关...