Imscp + Maxscale

**vafecitos** · Mar 11th 2019

Dear, is it possible to use maxscale as a load balancer? between a master / slave in imscp?

I consult them because I try to do it, but I can not configure it because it tries to modify all the time the ips from where the users that the panel uses can connect and ends up producing an error in the installation.

It also conflicts with the forced installation of mysql locally.

Any suggestions on how this could be done ?.

Thank you very much to all.

**Nuxwin** · Mar 11th 2019

vafecitos

Good evening,

What's your intent? Having MaxCale proxy installed on your i-MSCP instance, and one or more database servers installed elsewhere? Then, i-MSCP would send SQL statements to MaxScale which would be forwared to real database server?

Please provide us with more details.

**vafecitos** · Mar 12th 2019

thanks for answering

I am trying to make load balancing between the master and the slave since I have databases that are very large and when the backup runs blocks writes, that is affecting my clients.

my idea is that the select be executed in the slave and that's why I decided to try to put maxscale

Try these two ways, but the problem appears when the installation process attempts to configure user permissions on the database engine

imscp.png

Maxscale config:

Code

[maxscale]
threads=auto
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=master1, slave1
user=maxscale_usr
password=superpassword:D
monitor_interval=2000
detect_stale_master=1
detect_replication_lag=true
[MaxAdmin-Service]
type=service
router=cli
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default
[master1]
type=server
address=master.local
port=3306
protocol=mariadbbackend
[slave1]
type=server
address=slave.local
port=3306
protocol=mariadbbackend
[Splitter-Service]
type=service
router=readwritesplit
servers=master1,slave1
user=maxscale_usr
password=superpassword:D
max_slave_replication_lag=500
master_accept_reads=false
enable_root_user=1
[Splitter-Listener]
type=listener
service=Splitter-Service
protocol=mariadbclient
port=3306

Display More

**Nuxwin** · Mar 12th 2019

Quote from vafecitos

Try these two ways, but the problem appears when the installation process attempts to configure user permissions on the database engine

SQL user permissions? It could be great if you provide us with exact error messages. I will give a try anyway, providing dedicated i-MSCP server adapter if needed.

**vafecitos** · Mar 13th 2019

Thanks por answer

maxscale log:

2019-03-12 23:41:11 error : (260) Invalid authentication message from backend 'master1'. Error code: 1045, Msg : #28000Access denied for user 'imscp_srv_user'@'172.28.250.20' (using password: YES)

2019-03-12 23:41:11 error : Unable to write to backend 'master1' due to authentication failure. Server in state RUNNING MASTER.

IMSCP Message maxscale remote :

Code

[ERROR] Error while performing step: Executing Servers::sqld preinstall tasks... Error was: iMSCP::Stepper::_callback: Couldn't drop the imscp_user@localhost SQL user: DBD::mysql::db do failed: Operation DROP USER failed for 'imscp_user'@'localhost' at /var/www/imscp/engine/setup/../PerlLib/Servers/sqld/mysql.pm line 248. Please have a look at http://i-mscp.net/forum if you need help.

**Nuxwin** · Mar 13th 2019

Quote from vafecitos

2019-03-12 23:41:11 error : (260) Invalid authentication message from backend 'master1'. Error code: 1045, Msg : #28000Access denied for user 'imscp_srv_user'@'172.28.250.20' (using password: YES)

2019-03-12 23:41:11 error : Unable to write to backend 'master1' due to authentication failure. Server in state RUNNING

Basically put, the master i-MSCP SQL user which you configure while i-MSCP installation must have all privileges with GRANT option on master (write server). Is that the case?

**vafecitos** · Mar 13th 2019

Before executing the installation process I run these commands so that the user has the permissions from the host that connects

Code

GRANT ALL PRIVILEGES ON * . * TO 'imscp_srv_user'@'172.28.201.29' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON * . * TO 'imscp_user'@'172.28.201.29' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON * . * TO 'imscp_srv_user'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON * . * TO 'imscp_user'@'%' WITH GRANT OPTION;

Then in the server log (binlogs) I see that the installation process executes these grants:

Code

GRANT USAGE ON mysql.* TO 'imscp_srv_user'@'localhost'
GRANT SELECT ON mysql.db TO 'imscp_srv_user'@'localhost'
TO 'imscp_srv_user'@'localhost'
GRANT SELECT ON mysql.user TO 'imscp_srv_user'@'localhost'
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO'imscp_srv_user'@'localhost'
GRANT ALL PRIVILEGES ON `imscp\_pma`.* TO 'imscp_srv_user'@'localhost'

Imscp + Maxscale

Share

Daily visitors