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/