MySQL之MariaDBMaxScale-Oracle&MysqlDBA

wallet01（master）
[root@wallet01 ~]# mysql -uroot -pabcd.1234
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000003 | 251166915 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> create user monitor@'192.168.1.%' identified by "maxscale";
Query OK, 0 rows affected (0.08 sec)

mysql> grant replication slave, replication client on *.* to monitor@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on maxscale_schema.* TO 'monitor'@'192.168.1.%';
Query OK, 0 rows affected (0.02 sec)

mysql> create user router@'192.168.1.%' identified by "maxscale";
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on mysql.user to router@'192.168.130.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on mysql.db to router@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on mysql.tables_priv to router@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant show databases on *.* to router@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

wallet02（slave）
[root@wallet02 ~]# mysql -uroot -pabcd.1234
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.201
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 251188037
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 174038
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
[root@wallet03 ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
[warning] Found existing file at /etc/yum.repos.d/mariadb.repo. Moving to /etc/yum.repos.d/mariadb.repo.old_1.
[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo.
[info] Adding trusted package signing keys...
[info] Succeessfully added trusted package signing keys.

[root@wallet03 ~]# yum install -y maxscale latest

[root@wallet03 ~]# maxkeys
Generating .secrets file in /var/lib/maxscale.

[root@king04 ~]# maxpasswd /var/lib/maxscale maxscale
5A23832649A7C09A5011B99813491542

[root@wallet03 ~]# vi /etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/
# Global parameters 
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-configuration-usage-scenarios/
#maxscale全局参数
[maxscale]
# 线程数量，默认为1.设置为auto与cpu核数相同
threads=auto 
ms_timestamp=1
# Server definitions 
#
# Set the address of the server to the network
# address of a MariaDB server.
#后端数据库服务器
[server1]
type=server
address=192.168.1.201
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.1.202
port=3306
protocol=MariaDBBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
#  
#maxscale的数据库监控模块
[MariaDB-Monitor]
type=monitor
module=mariadbmon
#后端数据库服务器列表
servers=server1,server2
#maxscale监控账户
user=monitor
passwd=5A23832649A7C09A5011B99813491542
每隔2s执行监控检查
monitor_interval=2000
#检查复制延迟
detect_replication_lag=true
#当全部slave都不可用时，select查询请求会转发到master。
detect_stale_master=true
# Service definitions
#
# Service Definition for a read-only service
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readconnroute/
# [Read-Only-Service]
# type=service
# router=readconnroute
# servers=server1
# user=myuser
# passwd=mypwd
# router_options=slave
#  a read/write splitting service.
# ReadWriteSplit documentation:
#  
#maxscale的读写分离服务
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
#maxscale路由账户
user=router
passwd=5A23832649A7C09A5011B99813491542
#全部slave提供select查询服务
max_slave_connections=100%
#slave超时5秒，请求转发到其他slave
max_slave_replication_lag=5
#预先设置会话变量，例如 set character_set_connection=utf8,character_set_results=utf8,character_set_client=binary
默认是all，在master与slave上执行，设置为master,将只会路由到master执行
use_sql_variables_in=all
#允许root用户登录
enable_root_user=1
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
#  
#maxscale的管理服务
[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
#maxscale读写分离服务的监听端口
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006
#maxscale管理服务的监听端口
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

[root@wallet03 ~]# /etc/init.d/maxscale start
Starting MaxScale: maxscale (pid 1500) is running...       [  OK  ]

[root@wallet03 ~]# /etc/init.d/maxscale status
Checking MaxScale status: MaxScale (pid 1500) is running..[  OK  ]

[root@wallet03 ~]# mysql -utpcc -ptpcc -P 4006 -h 192.168.1.200
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| wallet02   |
+------------+

[root@wallet03 ~]# maxadmin -S /tmp/maxadmin.sock 
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.1.201   |  3306 |           4 | Master, Running
server2            | 192.168.1.202   |  3306 |           4 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

延迟检测：maxscale会对master和slave上replication_heartbeat表的master_timestamp时间戳进行对比，
相减得出差异。这个差异就是MySQL主从同步的延迟值。

[root@wallet01 ~]# mysql -uroot -pabcd.1234
mysql> select * from maxscale_schema.replication_heartbeat;
+-------------+------------------+------------------+
| maxscale_id | master_server_id | master_timestamp |
+-------------+------------------+------------------+
|           0 |                1 |       1544771935 |
+-------------+------------------+------------------+