Mariadb+Galera+MaxScale读写分离集群配置

部署环境：

node-01：10.0.1.10 centos7.6                                                                 

node-02：10.0.1.11 centos7.6                                                                 

maxscale：10.0.1.9 centos7.6                                                                 

mariadb版本：10.3.36

一、安装MariaDB

1、在一台外网机器上配置yum源                                                                 

cat /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

将需要安装的软件包下载到目录，进行离线安装：                                       

yum clean all
yum makecache
yum install -y MariaDB-server MariaDB-client galera --downloadonly --downloaddir=/root/MariaDB-10.3

在node-01上进行yum本地安装

cd /root/MariaDB-10.3
yum localinstall -y *.rpm

启动mariadb数据库

systemctl start mariadb

2、将rpm包scp到另外一台节点，进行同上步骤安装并启动数据库

3、分别在两个节点上创建数据库用户：

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
CREATE USER scalemon@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO scalemon@'%';
CREATE USER maxscale@'%' IDENTIFIED BY '123456';
GRANT SELECT,SHOW DATABASES ON *.* TO maxscale@'%';
CREATE USER exporter@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

4、在2个节点上均需修改配置 vim /etc/my.cnf.d/server.cnf

[mysqld]
max_cnotallow=5000
max_allowed_packet=1024M
explicit_defaults_for_timestamp=1
innodb_file_per_table=1

[galera]
# Mandatory settings
wsrep_notallow=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.1.10,10.0.1.11"
wsrep_node_name=master
wsrep_node_address=10.0.1.10
expire_logs_days=1
log_bin=/var/lib/mysql/binlog/bin
log_slave_updates=ON
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_slave_threads=2
innodb_flush_log_at_trx_commit=0

注意：需要手动创建/var/lib/mysql/binlog/bin并更改用户和属组为mysql

不同的节点需要更改wsrep_node_name和wsrep_node_address

5、在node-01上创建galera集群：

galera_new_cluster && systemctl enable mariadb

6、在node-02上启动mariadb并加入集群

systemctl start mariadb && systemctl enable mariadb

7、连接mariadb,查看galera集群状态

##显示on表示开启
show status like "wsrep_ready";
##查看集群集群数，2表示正常
show status like "wsrep_cluster_size";
##查看集群集群状态
show status like "wsrep%";

在node-01上创建数据库，在node-02上查看到同步创建，集群功能正常。

二、部署MaxScale

下载软件包：​​https://downloads.mariadb.com/MaxScale/2.5.9/centos/7Server/x86_64/maxscale-2.5.9-1.rhel.7.x86_64.rpm​​

安装：

yum localinstall -y maxscale-2.5.9-1.rhel.7.x86_64.rpm

配置/etc/maxscale.cnf

[maxscale]
threads=auto

[server1]
type=server
address=10.0.1.10
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=10.0.1.11
port=3306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2  
user=scalemon           
password=123456
monitor_interval=10000   

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale     
password=123456
enable_root_user=1

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

启动maxscale：

systemctl start maxscale && systemctl enable maxscale

查看4006端口状态，监听即启动成功