1. Add into the MaxScale server MaxScale server the MariaDB repositories
[root@maxscale data]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.8 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /data
/data
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb-main mariadb-maxscale mariadb-tools updates
Cleaning up list of fastest mirrors
[root@maxscale data]#
2. Install mysql /mariadb client
[root@maxscale data]# yum install mariadb -y
3. Install maxscale
[root@maxscale data]# yum install maxscale -y
4. Enable automatic booting & start
[root@maxscale data]# systemctl status maxscale
5. Setting Node
As MaxScale perform tasks like authentication, monitoring, and more, you need to create a database user with some specific privileges:
CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword';
GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';
[root@maxscale data]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.8 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /data
/data
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb-main mariadb-maxscale mariadb-tools updates
Cleaning up list of fastest mirrors
[root@maxscale data]#
2. Install mysql /mariadb client
[root@maxscale data]# yum install mariadb -y
3. Install maxscale
[root@maxscale data]# yum install maxscale -y
4. Enable automatic booting & start
[root@maxscale data]# systemctl status maxscale
5. Setting Node
As MaxScale perform tasks like authentication, monitoring, and more, you need to create a database user with some specific privileges:
CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword';
GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';
other lab
CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword';
GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.procs_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.proxies_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.procs_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.proxies_priv TO 'maxscaleuser'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
GRANT ALL ON *.* TO 'maxscaleuser'@'%';
--optional
GRANT SELECT, INSERT ON *.* TO 'maxscaleuser'@'%';
FLUSH PRIVILEGES;
sample log
MariaDB [(none)]> CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.* TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
6. How to Configure MaxScale (for mylab status active active & automatic failover) & restart maxscale
$ cat /etc/maxscale.cnf
# Global parameters
[maxscale]
threads = auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
--optional
GRANT SELECT, INSERT ON *.* TO 'maxscaleuser'@'%';
FLUSH PRIVILEGES;
sample log
MariaDB [(none)]> CREATE USER 'maxscaleuser'@'%' IDENTIFIED BY 'maxscalepassword';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION CLIENT on *.* to 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT ON mysql.* TO 'maxscaleuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
6. How to Configure MaxScale (for mylab status active active & automatic failover) & restart maxscale
$ cat /etc/maxscale.cnf
# Global parameters
[maxscale]
threads = auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# https://mariadb.com/kb/en/mariadb-maxscale-2302-mariadb-maxscale-configuration-guide/
max_auth_errors_until_block=0
admin_host=10.10.10.15
admin_port=8989
admin_enabled=1
admin_secure_gui=false
# run this command to administration
max_auth_errors_until_block=0
admin_host=10.10.10.15
admin_port=8989
admin_enabled=1
admin_secure_gui=false
# run this command to administration
# maxctrl -u admin -p mariadb -h 10.10.10.15:8989
# Server definitions
[server1]
type=server
address=10.10.10.51
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=10.10.10.52
port=3306
protocol=MariaDBBackend
# Monitor for the servers
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
monitor_interval=2000
# Service definitions
[Read-Only-Service]
type=service
router=readconnroute
servers=server2
user=maxscaleuser
password=maxscalepassword
router_options=slave
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1
user=maxscaleuser
password=maxscalepassword
# Listener definitions for the services
[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
# Server definitions
[server1]
type=server
address=10.10.10.51
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=10.10.10.52
port=3306
protocol=MariaDBBackend
# Monitor for the servers
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
monitor_interval=2000
# Service definitions
[Read-Only-Service]
type=service
router=readconnroute
servers=server2
user=maxscaleuser
password=maxscalepassword
router_options=slave
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1
user=maxscaleuser
password=maxscalepassword
# Listener definitions for the services
[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
sample
[root@maxscale data]# cat /etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/
[maxscale]
#threads=auto
threads = auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[server1]
type=server
address=10.10.10.51
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=10.10.10.52
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/maxscale-6-monitors/
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
monitor_interval=2000
#monitor_interval=2s
auto_failover=true
auto_rejoin=true
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service]
type=service
router=readconnroute
auto_failover=true
auto_rejoin=true
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service]
type=service
router=readconnroute
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
router_options=master,slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
# 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
[root@maxscale data]#
[root@maxscale data]# systemctl restart maxscale
7. Check status maxscale
# maxctrl list services
# maxctrl list servers
Commands:Commands:
maxctrl list servers
maxctrl list services
maxctrl list listeners
maxctrl list monitors
maxctrl list sessions
maxctrl list filters
maxctrl list modules
maxctrl list users
maxctrl --version
maxctrl list commands
user=maxscaleuser
password=maxscalepassword
router_options=master,slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
# 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
[root@maxscale data]#
[root@maxscale data]# systemctl restart maxscale
7. Check status maxscale
# maxctrl list services
# maxctrl list servers
Commands:Commands:
maxctrl list servers
maxctrl list services
maxctrl list listeners
maxctrl list monitors
maxctrl list sessions
maxctrl list filters
maxctrl list modules
maxctrl list users
maxctrl --version
maxctrl list commands
sample
[root@maxscale data]# systemctl restart maxscale
[root@maxscale data]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬─────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 0 │ server2 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴─────────┘
[root@maxscale data]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬─────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 0 │ server2 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴─────────┘
[root@maxscale data]#
[root@maxscale data]# maxctrl list servers
┌─────────┬─────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server1 │ 10.10.10.51 │ 3306 │ 0 │ Master, Running │ │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server2 │ 10.10.10.52 │ 3306 │ 0 │ Running │ │
└─────────┴─────────────┴──────┴─────────────┴─────────────────┴──────┘
[root@maxscale data]#
8. Test login using max connection
mysql -h 10.10.10.60 -u maxscaleuser -p maxscalepassword -P4006 -e 'SELECT @@hostname;'
mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
[root@maxscale data]# mysql -h 10.10.10.60 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
[root@maxscale data]# mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
[root@maxscale data]#
9. testing restart mariadb01 & check result
[root@maxscale data]# mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
[root@maxscale data]#
[root@maxscale data]# mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
ERROR 1815 (HY000): Internal error: Session creation failed
[root@maxscale data]# mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb02 |
+------------+
[root@maxscale data]#
[root@maxscale data]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 7 │ server1, server2 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 0 │ │
└────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
[root@maxscale data]# maxctrl list servers
┌─────────┬─────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server1 │ 10.10.10.51 │ 3306 │ 0 │ Running │ │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server2 │ 10.10.10.52 │ 3306 │ 0 │ Master, Running │ │
└─────────┴─────────────┴──────┴─────────────┴─────────────────┴──────┘
[root@maxscale data]#
10. testing start mariadb01 , restart mariadb & check result
[root@maxscale data]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 7 │ server1, server2 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 0 │ │
└────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
[root@maxscale data]# maxctrl list servers
┌─────────┬─────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server1 │ 10.10.10.51 │ 3306 │ 0 │ Master, Running │ │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server2 │ 10.10.10.52 │ 3306 │ 0 │ Running │ │
└─────────┴─────────────┴──────┴─────────────┴─────────────────┴──────┘
[root@maxscale data]#
11. login again using maxscale
[root@maxscale data]# mysql -h 127.0.0.1 -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
[root@maxscale data]# mysql -h maxscale -umaxscaleuser -pmaxscalepassword -P4006 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| mariadb01 |
+------------+
[root@maxscale data]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 9 │ server1, server2 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 0 │ │
└────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
[root@maxscale data]# maxctrl list servers
┌─────────┬─────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server1 │ 10.10.10.51 │ 3306 │ 0 │ Master, Running │ │
├─────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
│ server2 │ 10.10.10.52 │ 3306 │ 0 │ Running │ │
└─────────┴─────────────┴──────┴─────────────┴─────────────────┴──────┘
[root@maxscale data]# echo "Teguh Triharto Lab for MariaDB Administration"
Teguh Triharto Lab for MariaDB Administration
[root@maxscale data]#
mariadbmon │ async-restore-from-backup, async-create-backup, async-rebuild-server, async-cs-set-readwrite, async-cs-set-readonly, async-cs-stop-cluster, async-cs-start-cluster, async-cs-get-status, cs-get-status, async-cs-remove-node, async-cs-add-node, cancel-cmd, fetch-cmd-result, async-release-locks, release-locks, async-reset-replication, reset-replication, async-rejoin, rejoin, async-failover, failover, async-switchover, switchover
other config
[root@maxscale data]# cat /etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/
[maxscale]
#threads=auto
threads = auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[server1]
type=server
address=10.10.10.51
port=3306
protocol=MariaDBBackend
priority=1
[server2]
type=server
address=10.10.10.52
port=3306
protocol=MariaDBBackend
priority=2
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-6-monitors/
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
monitor_interval=2000
#monitor_interval=2s
auto_failover=true
auto_rejoin=true
detect_stale_master=true
failover_timeout=30
auto_rejoin=true
failcount=5
master_failure_timeout=30
verify_master_failure=true
switchover_timeout=90
detect_stale_slave=true
servers_no_promotion=server1
replication_user=maxscaleuser
replication_password=maxscalepassword
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service]
type=service
router=readconnroute
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
router_options=master,slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=20
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36000
filters=Hint
# 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
# service=Read-Write-Servicexx nama service
protocol=MariaDBClient
port=4006
authenticator=MariaDBAuth
authenticator_options=lower_case_table_names=true
[root@maxscale data]#
[root@maxscale data]# systemctl restart maxscale
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/
[maxscale]
#threads=auto
threads = auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[server1]
type=server
address=10.10.10.51
port=3306
protocol=MariaDBBackend
priority=1
[server2]
type=server
address=10.10.10.52
port=3306
protocol=MariaDBBackend
priority=2
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-6-monitors/
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
monitor_interval=2000
#monitor_interval=2s
auto_failover=true
auto_rejoin=true
detect_stale_master=true
failover_timeout=30
auto_rejoin=true
failcount=5
master_failure_timeout=30
verify_master_failure=true
switchover_timeout=90
detect_stale_slave=true
servers_no_promotion=server1
replication_user=maxscaleuser
replication_password=maxscalepassword
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service]
type=service
router=readconnroute
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
router_options=master,slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscaleuser
password=maxscalepassword
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=20
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36000
filters=Hint
# 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
# service=Read-Write-Servicexx nama service
protocol=MariaDBClient
port=4006
authenticator=MariaDBAuth
authenticator_options=lower_case_table_names=true
[root@maxscale data]#
[root@maxscale data]# systemctl restart maxscale
special for my lab< maxscale combine Galera & GTID>
[root@ha data]# cat /etc/maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/
[maxscale]
threads=auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
### Galera HA
[galera01-v11]
type=server
address=10.10.10.11
port=3306
protocol=MariaDBBackend
[galera02-v12]
type=server
address=10.10.10.12
port=3306
protocol=MariaDBBackend
[galera03-v13]
type=server
address=10.10.10.13
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/maxscale-6-monitors/
#
# Create the monitor user with:
#
# CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_pw';
# GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'monitor_user'@'%';
#
[MariaDB-Monitor-galera]
type=monitor
module=mariadbmon
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
monitor_interval=2s
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# Create the service user with:
#
# CREATE USER 'service_user'@'%' IDENTIFIED BY 'service_pw';
# GRANT SELECT ON mysql.user TO 'service_user'@'%';
# GRANT SELECT ON mysql.db TO 'service_user'@'%';
# GRANT SELECT ON mysql.tables_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.columns_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.procs_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.proxies_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.roles_mapping TO 'service_user'@'%';
# GRANT SHOW DATABASES ON *.* TO 'service_user'@'%';
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service-galera]
type=service
router=readconnroute
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
router_options=slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service-galera]
type=service
router=readwritesplit
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Read-Only-Listener-galera]
type=listener
service=Read-Only-Service-galera
protocol=MariaDBClient
port=4008
[Read-Write-Listener-galera]
type=listener
service=Read-Write-Service-galera
protocol=MariaDBClient
port=4006
### GTID MariaDB Replication
[gtid01-v31]
type=server
address=10.10.10.31
port=3306
protocol=MariaDBBackend
[gtid02-v32]
type=server
address=10.10.10.32
port=3306
protocol=MariaDBBackend
[gtid03-v33]
type=server
address=10.10.10.33
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor-gtid]
type=monitor
module=mariadbmon
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
monitor_interval=2s
[Read-Only-Service-gtid]
type=service
router=readconnroute
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
router_options=slave
[Read-Write-Service-gtid]
type=service
router=readwritesplit
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
[Read-Only-Listener-gtid]
type=listener
service=Read-Only-Service-gtid
protocol=MariaDBClient
port=4038
[Read-Write-Listener-gtid]
type=listener
service=Read-Write-Service-gtid
protocol=MariaDBClient
port=4036
[root@ha data]#
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-6-mariadb-maxscale-configuration-guide/
[maxscale]
threads=auto
log_augmentation = 1
ms_timestamp = 1
syslog = 1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
### Galera HA
[galera01-v11]
type=server
address=10.10.10.11
port=3306
protocol=MariaDBBackend
[galera02-v12]
type=server
address=10.10.10.12
port=3306
protocol=MariaDBBackend
[galera03-v13]
type=server
address=10.10.10.13
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/maxscale-6-monitors/
#
# Create the monitor user with:
#
# CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_pw';
# GRANT REPLICATION CLIENT, FILE, SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'monitor_user'@'%';
#
[MariaDB-Monitor-galera]
type=monitor
module=mariadbmon
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
monitor_interval=2s
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# Create the service user with:
#
# CREATE USER 'service_user'@'%' IDENTIFIED BY 'service_pw';
# GRANT SELECT ON mysql.user TO 'service_user'@'%';
# GRANT SELECT ON mysql.db TO 'service_user'@'%';
# GRANT SELECT ON mysql.tables_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.columns_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.procs_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.proxies_priv TO 'service_user'@'%';
# GRANT SELECT ON mysql.roles_mapping TO 'service_user'@'%';
# GRANT SHOW DATABASES ON *.* TO 'service_user'@'%';
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readconnroute/
[Read-Only-Service-galera]
type=service
router=readconnroute
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
router_options=slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[Read-Write-Service-galera]
type=service
router=readwritesplit
servers=galera01-v11,galera02-v12,galera03-v13
user=maxscaleuser
password=maxscalepassword
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Read-Only-Listener-galera]
type=listener
service=Read-Only-Service-galera
protocol=MariaDBClient
port=4008
[Read-Write-Listener-galera]
type=listener
service=Read-Write-Service-galera
protocol=MariaDBClient
port=4006
### GTID MariaDB Replication
[gtid01-v31]
type=server
address=10.10.10.31
port=3306
protocol=MariaDBBackend
[gtid02-v32]
type=server
address=10.10.10.32
port=3306
protocol=MariaDBBackend
[gtid03-v33]
type=server
address=10.10.10.33
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor-gtid]
type=monitor
module=mariadbmon
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
monitor_interval=2s
[Read-Only-Service-gtid]
type=service
router=readconnroute
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
router_options=slave
[Read-Write-Service-gtid]
type=service
router=readwritesplit
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
[Read-Only-Listener-gtid]
type=listener
service=Read-Only-Service-gtid
protocol=MariaDBClient
port=4038
[Read-Write-Listener-gtid]
type=listener
service=Read-Write-Service-gtid
protocol=MariaDBClient
port=4036
[root@ha data]#
sample log maxscale
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser1 -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
ERROR 1045 (28000): Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -uabcdef -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
ERROR 1045 (28000): Access denied for user 'abcdef'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser1 -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
ERROR 1045 (28000): Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| teguhth02 |
+------------+
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| teguhth02 |
+------------+
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P411111111111 -e 'SELECT @@hostname;'
Warning: option 'port': unsigned value 411111111111 adjusted to 4294967295
ERROR 2005 (HY000): Unknown MySQL server host '10.10.10.15' (-8)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4112 -e 'SELECT @@hostname;'
ERROR 2002 (HY000): Can't connect to MySQL server on '10.10.10.15' (115)
[root@teguhth02 ~]#
ERROR 1045 (28000): Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -uabcdef -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
ERROR 1045 (28000): Access denied for user 'abcdef'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser1 -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
ERROR 1045 (28000): Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| teguhth02 |
+------------+
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4036 -e 'SELECT @@hostname;'
+------------+
| @@hostname |
+------------+
| teguhth02 |
+------------+
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P411111111111 -e 'SELECT @@hostname;'
Warning: option 'port': unsigned value 411111111111 adjusted to 4294967295
ERROR 2005 (HY000): Unknown MySQL server host '10.10.10.15' (-8)
[root@teguhth02 ~]# mysql -h 10.10.10.15 -umaxscaleuser -pmaxscalepassword -P4112 -e 'SELECT @@hostname;'
ERROR 2002 (HY000): Can't connect to MySQL server on '10.10.10.15' (115)
[root@teguhth02 ~]#
[root@ha01 ~]# tail -f /var/log/maxscale/maxscale.log
2023-07-27 10:20:50.823 notice : (Read-Write-Listener-galera); (listen): Listening for connections at [::]:4006
2023-07-27 10:20:50.823 notice : (launch_all): Service 'Read-Write-Service-galera' started (4/4)
2023-07-27 10:20:50.825 notice : (operator()): MaxScale started with 1 worker threads.
2023-07-27 10:20:56.916 notice : (set_version): 'gtid01-v31' sent version string '10.5.21-MariaDB-log'. Detected type: 'MariaDB', version: 10.5.21.
2023-07-27 10:20:56.925 notice : (mxs_update_server_charset): Server 'gtid01-v31' charset: latin1_swedish_ci
2023-07-27 10:20:59.821 notice : (log_state_change): Server changed state: gtid01-v31[10.10.10.31:3306]: master_up. [Down] -> [Master, Running]
2023-07-27 10:20:59.928 notice : (set_version): 'gtid02-v32' sent version string '10.5.21-MariaDB-log'. Detected type: 'MariaDB', version: 10.5.21.
2023-07-27 10:20:59.940 notice : (mxs_update_server_charset): Server 'gtid02-v32' charset: latin1_swedish_ci
2023-07-27 10:21:02.826 notice : (log_state_change): Server changed state: gtid02-v32[10.10.10.32:3306]: slave_up. [Down] -> [Slave, Running]
2023-07-27 10:21:05.836 warning: [mariadbmon] (update_master): The current primary server 'galera01-v11' is no longer valid because it has been down over 5 (failcount) monitor updates and it does not have any running slaves, but there is no valid alternative to swap to.
2023-07-27 10:23:12.152 error : (load_users_from_backends): No valid servers from which to query MariaDB user accounts found.
2023-07-27 10:23:12.153 warning: (1) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.15] to service 'Read-Write-Service-galera'. Originating listener: 'Read-Write-Listener-galera'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.15' (using password: YES)'.
2023-07-27 10:23:54.610 notice : (update_users): Read 16 user@host entries from 'gtid01-v31' for service 'Read-Only-Service-gtid'.
2023-07-27 10:24:03.620 notice : (update_users): Read 16 user@host entries from 'gtid01-v31' for service 'Read-Write-Service-gtid'.
2023-07-27 10:24:15.923 warning: (4) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.15] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.15' (using password: YES)'.
2023-07-27 10:25:36.107 warning: (6) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:26:53.554 warning: (8) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser1'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:27:11.427 warning: (9) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'abcdef'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'abcdef'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:28:12.850 warning: (10) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser1'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)'.
maxctrl -u admin -p mariadb -h 10.10.10.15:8989
2023-07-27 10:20:50.823 notice : (Read-Write-Listener-galera); (listen): Listening for connections at [::]:4006
2023-07-27 10:20:50.823 notice : (launch_all): Service 'Read-Write-Service-galera' started (4/4)
2023-07-27 10:20:50.825 notice : (operator()): MaxScale started with 1 worker threads.
2023-07-27 10:20:56.916 notice : (set_version): 'gtid01-v31' sent version string '10.5.21-MariaDB-log'. Detected type: 'MariaDB', version: 10.5.21.
2023-07-27 10:20:56.925 notice : (mxs_update_server_charset): Server 'gtid01-v31' charset: latin1_swedish_ci
2023-07-27 10:20:59.821 notice : (log_state_change): Server changed state: gtid01-v31[10.10.10.31:3306]: master_up. [Down] -> [Master, Running]
2023-07-27 10:20:59.928 notice : (set_version): 'gtid02-v32' sent version string '10.5.21-MariaDB-log'. Detected type: 'MariaDB', version: 10.5.21.
2023-07-27 10:20:59.940 notice : (mxs_update_server_charset): Server 'gtid02-v32' charset: latin1_swedish_ci
2023-07-27 10:21:02.826 notice : (log_state_change): Server changed state: gtid02-v32[10.10.10.32:3306]: slave_up. [Down] -> [Slave, Running]
2023-07-27 10:21:05.836 warning: [mariadbmon] (update_master): The current primary server 'galera01-v11' is no longer valid because it has been down over 5 (failcount) monitor updates and it does not have any running slaves, but there is no valid alternative to swap to.
2023-07-27 10:23:12.152 error : (load_users_from_backends): No valid servers from which to query MariaDB user accounts found.
2023-07-27 10:23:12.153 warning: (1) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.15] to service 'Read-Write-Service-galera'. Originating listener: 'Read-Write-Listener-galera'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.15' (using password: YES)'.
2023-07-27 10:23:54.610 notice : (update_users): Read 16 user@host entries from 'gtid01-v31' for service 'Read-Only-Service-gtid'.
2023-07-27 10:24:03.620 notice : (update_users): Read 16 user@host entries from 'gtid01-v31' for service 'Read-Write-Service-gtid'.
2023-07-27 10:24:15.923 warning: (4) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.15] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.15' (using password: YES)'.
2023-07-27 10:25:36.107 warning: (6) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:26:53.554 warning: (8) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser1'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:27:11.427 warning: (9) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'abcdef'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'abcdef'@'::ffff:10.10.10.32' (using password: YES)'.
2023-07-27 10:28:12.850 warning: (10) [MariaDBProtocol] (send_authentication_error): Authentication failed for user 'maxscaleuser1'@[::ffff:10.10.10.32] to service 'Read-Write-Service-gtid'. Originating listener: 'Read-Write-Listener-gtid'. MariaDB error: 'Access denied for user 'maxscaleuser1'@'::ffff:10.10.10.32' (using password: YES)'.
maxctrl -u admin -p mariadb -h 10.10.10.15:8989
12. Additional for test
### GTID MariaDB Replication ###
[gtid01-v31]
type=server
address=10.10.10.31
port=3306
protocol=MariaDBBackend
[gtid02-v32]
type=server
address=10.10.10.32
port=3306
protocol=MariaDBBackend
[gtid03-v33]
type=server
address=10.10.10.33
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor-gtid]
type=monitor
module=mariadbmon
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
#monitor_interval=1000
monitor_interval=1s
auto_failover=false
#detect_stale_master=true
failover_timeout=30s
auto_rejoin=true
failcount=5
master_failure_timeout=30s
verify_master_failure=true
switchover_timeout=90s
replication_user=maxscaleuser
replication_password=maxscalepassword
enforce_writable_master=1
enforce_read_only_slaves=1
#detect_stale_slave=true
servers_no_promotion=gtid03-v33
[Read-Write-Service-gtid]
type=service
router=readwritesplit
#servers=gtid01-v31,gtid02-v32,gtid03-v33
servers=gtid01-v31,gtid02-v32
user=maxscaleuser
password=maxscalepassword
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=2s
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36s
filters=Hint
[Read-Write-Listener-gtid]
type=listener
service=Read-Write-Service-gtid
protocol=MariadbClient
port=4036
authenticator=MariaDBAuth
authenticator_options=lower_case_table_names=true
## optional
[Read-Only-Service-gtid]
type=service
router=readwritesplit
#servers=gtid01-v31,gtid02-v32,gtid03-v33
servers=gtid01-v31
user=maxscale
password=F33D9A6E1376BD25313EB4EF0733ED43
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=2s
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36s
filters=Hint
[Read-Only-Listener-gtid]
type=listener
service=Read-Only-Service-gtid
protocol=MariaDBClient
port=4038
##optional
[Hint]
type=filter
module=hintfilter
[gtid01-v31]
type=server
address=10.10.10.31
port=3306
protocol=MariaDBBackend
[gtid02-v32]
type=server
address=10.10.10.32
port=3306
protocol=MariaDBBackend
[gtid03-v33]
type=server
address=10.10.10.33
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor-gtid]
type=monitor
module=mariadbmon
servers=gtid01-v31,gtid02-v32,gtid03-v33
user=maxscaleuser
password=maxscalepassword
#monitor_interval=1000
monitor_interval=1s
auto_failover=false
#detect_stale_master=true
failover_timeout=30s
auto_rejoin=true
failcount=5
master_failure_timeout=30s
verify_master_failure=true
switchover_timeout=90s
replication_user=maxscaleuser
replication_password=maxscalepassword
enforce_writable_master=1
enforce_read_only_slaves=1
#detect_stale_slave=true
servers_no_promotion=gtid03-v33
[Read-Write-Service-gtid]
type=service
router=readwritesplit
#servers=gtid01-v31,gtid02-v32,gtid03-v33
servers=gtid01-v31,gtid02-v32
user=maxscaleuser
password=maxscalepassword
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=2s
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36s
filters=Hint
[Read-Write-Listener-gtid]
type=listener
service=Read-Write-Service-gtid
protocol=MariadbClient
port=4036
authenticator=MariaDBAuth
authenticator_options=lower_case_table_names=true
## optional
[Read-Only-Service-gtid]
type=service
router=readwritesplit
#servers=gtid01-v31,gtid02-v32,gtid03-v33
servers=gtid01-v31
user=maxscale
password=F33D9A6E1376BD25313EB4EF0733ED43
master_failure_mode=fail_on_write
strict_multi_stmt=false
use_sql_variables_in=master
master_reconnection=true
delayed_retry=true
delayed_retry_timeout=2s
transaction_replay=true
max_sescmd_history=60
prune_sescmd_history=true
connection_timeout=36s
filters=Hint
[Read-Only-Listener-gtid]
type=listener
service=Read-Only-Service-gtid
protocol=MariaDBClient
port=4038
##optional
[Hint]
type=filter
module=hintfilter
### End GTID MariaDB Replication ###
https://severalnines.com/database-blog/how-install-and-configure-maxscale-mariadb
https://mariadb.com/kb/en/mariadb-maxscale-22-maxctrl/
No comments:
Post a Comment