MariaDB MaxScale — Replica Rebuild – Part 3

Part 3 — Create MaxScale Database Users

This multi-part series breaks down each section into easy logical steps.

If you have not completed part 1, start here.

Creation of Database Users for MaxScale

On one database server only, we will use ‘Server 1’, you will need to create some database users that will be used by MaxScale. 

MaxScale itself requires various access to your database servers, to be able to monitor and manipulate the underlying topology. 

Some of the users that we create are against localhost or the subnet. In my instance, the subnet is ‘10.106.0.%’. This means that any server on this subnet can connect to the database server, and this might not be secure enough for a production environment.

Connect to mariadb using the command line tool mariadb, and execute the SQL statements. The first set of commands is related to the maxscale_user, and this will be used by the service within MaxScale:

CREATE USER IF NOT EXISTS 'maxscale_user'@'10.106.0.%' IDENTIFIED BY 'aBcd123_';
GRANT SELECT ON mysql.user TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.db TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.columns_priv TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.procs_priv TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale_user'@'10.106.0.%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale_user'@'10.106.0.%';
GRANT SHOW DATABASES ON *.* TO 'maxscale_user'@'10.106.0.%';

Create a user for the Replication User:

CREATE USER IF NOT EXISTS 'replication_user'@'10.106.0.%' IDENTIFIED BY 'aBcd123_';

Create a monitor user, the localhost one is used for the backup:

CREATE USER IF NOT EXISTS 'monitor_user'@'10.106.0.%' IDENTIFIED BY 'aBcd123_';
CREATE USER IF NOT EXISTS 'monitor_user'@'localhost' IDENTIFIED BY 'aBcd123_';

Create a config sync user:

CREATE USER IF NOT EXISTS 'config_sync_user'@'10.106.0.%' IDENTIFIED BY 'aBcd123_';

Setup the required permissions for the config sync user:

GRANT SELECT, INSERT, UPDATE, CREATE ON mysql.maxscale_config TO 'config_sync_user'@'10.106.0.%';

The following Grants are required for the MaxScale Monitor User. (This is for MariaDB versions => 10.5.9):

GRANT REPLICATION CLIENT, REPLICA MONITOR, FILE, CONNECTION ADMIN ON *.* TO 'monitor_user'@'10.106.0.%';

The following are required for the backup user, it connects via the local socket. The user configured in the monitor is used for taking the backup. (This is for MariaDB versions => 10.5): 

GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, SLAVE MONITOR, REPLICATION SLAVE ADMIN ON *.* TO 'monitor_user'@'localhost';

** The Grants SLAVE MONITOR and REPLICATION SLAVE ADMIN are not listed in the documentation.

The following are required for the Cluster Manipulation Grants (This is for MariaDB versions => 10.5 and < 11.0.1):

GRANT SUPER, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'monitor_user'@'10.106.0.%';
GRANT SELECT ON mysql.user TO 'monitor_user'@'10.106.0.%';
GRANT SELECT ON mysql.global_priv TO 'monitor_user'@'10.106.0.%';

The following allows the ‘replication_user’ to be used:

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'10.106.0.%';

The next step is to configure SSH access between the MaxScale and Database servers. This is done in Part 4.

Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 | Part 8 | Part 9