MySQL HA Solution 2019(4)MaxScale

MySQLHASolution2019(4)MaxScale

Youcanfindyourdownloadfromhere

https://mariadb.com/downloads/#mariadb_platform-mariadb_maxscale

Ichooseubuntu18.04formytesting

>wgethttps://downloads.mariadb.com/MaxScale/2.3.11/ubuntu/dists/bionic/main/binary-amd64/maxscale-2.3.11-1.ubuntu.bionic.x86_64.deb

HereishowIinstallthat

>sudoaptinstall./maxscale-2.3.11-1.ubuntu.bionic.x86_64.deb

GotomyMySQLmastermachine

>mysql-udebian-sys-maint-pG1FEbrOMSORmcaUK

>usemysql;

CreateMonitorAccount

>createuserscalemon@'%'identifiedby'kaishi';

>grantreplicationslave,replicationclienton*.*toscalemon@'%';

CreateProxyAccount

>createusermaxscale@'%'identifiedby'kaishi';

>grantselectonmysql.*tomaxscale@'%';

>flushprivileges;

CheckandModifytheConfiguration

>sudovi/etc/maxscale.cnf

>cat/etc/maxscale.cnf

#MaxScaledocumentation:

#https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-23/

#Globalparameters

#

#Completelistofconfigurationoptions:

#https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/

[maxscale]

threads=auto

#Serverdefinitions

#

#Settheaddressoftheservertothenetwork

#addressofaMariaDBserver.

#

[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

#Monitorfortheservers

#

#ThiswillkeepMaxScaleawareofthestateoftheservers.

#MariaDBMonitordocumentation:

#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

#Servicedefinitions

#

#ServiceDefinitionforaread-onlyserviceand

#aread/writesplittingservice.

#

#ReadConnRoutedocumentation:

#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

#ReadWriteSplitdocumentation:

#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%

#ThisserviceenablestheuseoftheMaxAdmininterface

#MaxScaleadministrationguide:

#https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/

[MaxAdmin-Service]

type=service

router=cli

#Listenerdefinitionsfortheservices

#

#Theselistenersrepresenttheportsthe

#serviceswilllistenon.

#

#[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

Starttheservice

>maxscale--config=/etc/maxscale.cnf

Someerrormessageintheconfig

Protocolmodule'mysqlclient'hasbeendeprecated,use'mariadbclient'instead.

error:Invalidvalueforparameter'service'forobject'Read-Only-Listener'oftype'listener':Read-Only-Service(wasexpectingaservicename)

THE'cli'MODULEAND'maxadmin'AREDEPRECATED:Use'maxctrl'instead

Monitormodule'mysqlmon'hasbeendeprecated,use'mariadbmon'instead.

error:Failedtoopen,readorprocesstheMaxScaleconfigurationfile/etc/maxscale.cnf.

error:Unabletofindlibraryformodule:maxctrl.Moduledir:/usr/lib/x86_64-linux-gnu/maxscale

warning:Protocolmodule'mysqlbackend'hasbeendeprecated,use'mariadbbackend'instead.

Checkloggingfilepermission

>sudochmod777-R/var/log/maxscale/

>sudochmod777-R/var/lib/maxscale/

>sudochmod777-R/var/run/maxscale/

>sudochmod777-R/var/cache/maxscale/

Checkingthelogging,itstarts

2019-08-0312:46:04notice:Selectingnewmasterserver.

2019-08-0312:46:04notice:Setting'server1'asmaster.

2019-08-0312:46:04notice:Serverchangedstate:server1[ubuntu-master:3306]:new_master.[Running]->[Master,Running]

2019-08-0312:46:04notice:Serverchangedstate:server2[ubuntu-dev5:3306]:new_slave.[Running]->[Slave,Running]

2019-08-0312:46:04notice:Serverchangedstate:server3[ubuntu-dev6:3306]:new_slave.[Running]->[Slave,Running]

Iusedtohaveanaccountmycat/mycatwhenItestmycat,now,Iwilltrythat.

>mysql-hubuntu-dev5-P4006-umycat-pmycat

>select@@hostname;

+-------------+

|@@hostname|

+-------------+

|ubuntu-dev5|

>starttransaction;

>select@@hostname;

+---------------+

|@@hostname|

+---------------+

|ubuntu-master|

>rollback;

>select@@hostname;

+-------------+

|@@hostname|

+-------------+

|ubuntu-dev5|

>showdatabases;

+--------------------+

|Database|

+--------------------+

|information_schema|

|mycat|

|mysql|

>usemycat;

>showtables;

+-----------------+

|Tables_in_mycat|

+-----------------+

|mycatuser|

>insertintomycatuser(id,name)values(1,'carl');

>select*frommycatuser;

+----+------+

|id|name|

+----+------+

|1|carl|

Sometools

>sudomaxadminenableaccountcarl

>maxadmin-S/var/run/maxscale/maxadmin.socklistservers;

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

Openloggingonallmysql

>sudovi/etc/mysql/mysql.conf.d/mysqld.cnf

general_log_file=/var/log/mysql/mysql.log

general_log=1

Restarttheservice

>sudo/etc/init.d/mysqlrestart

Or

>sudoservicemysqlrestart

Checkinglogging

>sudotail-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/