maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #04369
Maria db read/write configuration issues
Hi
We have configured maxscale in our environment using the links
http://karlssonondatabases.blogspot.in/2014/02/maxscale-for-rest-of-us-part-1.html,
2, 3 . We have two slaves and one master and the replication between them
is working well. But after configuring read/write split the select queries
are being executed in master itself, not routing to the slaves. Please find
the attached documents of maxscale configuration, maxscale commands
maxscale log and. Please help with maxscale read/write split.
Attached documentsdetails
1. maxsconf.txt from the location /etc/maxscale.cnf
2. maxscale-commands.txt, coomands like maxadmin -pmariadb list
servers, maxadmin -pmariadb list services, maxadmin -pmariadb show service
"Splitter Service",
3. maxscale-log.txt from /var/log/maxscale
Thanks
Chaitu
--
Thanks.
[maxscale]
threads=1
log_info=1
[Splitter Service]
type=service
router=readwritesplit
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
[Splitter Listener]
type=listener
service=Splitter Service
protocol=MySQLClient
port=3307
[srv1]
type=server
address=puppetagent.vidyayug.com
port=3306
protocol=MySQLBackend
[srv2]
type=server
address=sp.test1.com
port=3306
protocol=MySQLBackend
[srv3]
type=server
address=idp.test.com
port=3306
protocol=MySQLBackend
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
[CLI]
type=service
router=cli
[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603
vidyayug@ubuntu:~$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
srv1 | puppetagent.vidyayug.com | 3306 | 0 | Master, Running
srv2 | sp.test1.com | 3306 | 0 | Slave, Running
srv3 | idp.test.com | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
vidyayug@ubuntu:~$ maxadmin -pmariadb list services
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Splitter Service | readwritesplit | 1 | 1
CLI | cli | 2 | 3
--------------------------+----------------------+--------+---------------
vidyayug@ubuntu:~$ maxadmin -pmariadb show service "Splitter Service"
Service 0x1a9f8d0
Service: Splitter Service
Router: readwritesplit (0x7f3c3a0e22e0)
State: Started
Number of router sessions: 0
Current no. of router sessions: 0
Number of queries forwarded: 0
Number of queries forwarded to master: 0 (0.00%)
Number of queries forwarded to slave: 0 (0.00%)
Number of queries forwarded to all: 0 (0.00%)
Started: Thu Mar 9 14:49:02 2017
Root user access: Disabled
Backend databases:
puppetagent.vidyayug.com:3306 Protocol: MySQLBackend
sp.test1.com:3306 Protocol: MySQLBackend
idp.test.com:3306 Protocol: MySQLBackend
Users data: 0x1ab2690
Total connections: 1
Currently connected: 1
vidyayug@ubuntu:~$ maxadmin -pmariadb list sessions
Sessions.
-----------------+-----------------+----------------+--------------------------
Session | Client | Service | State
-----------------+-----------------+----------------+--------------------------
0x1ab2900 | | Splitter Service | Listener Session
0x1ab5f40 | | CLI | Listener Session
0x1ab6eb0 | 127.0.0.1 | CLI | Session ready for routing
-----------------+-----------------+----------------+--------------------------
2017-03-09 14:49:02 notice : Working directory: /var/log/maxscale
2017-03-09 14:49:02 notice : MariaDB MaxScale 2.0.4 started
2017-03-09 14:49:02 notice : MaxScale is running in process 7425
2017-03-09 14:49:02 notice : Configuration file: /etc/maxscale.cnf
2017-03-09 14:49:02 notice : Log directory: /var/log/maxscale
2017-03-09 14:49:02 notice : Data directory: /var/lib/maxscale
2017-03-09 14:49:02 notice : Module directory: /usr/lib/x86_64-linux-gnu/maxscale
2017-03-09 14:49:02 notice : Service cache: /var/cache/maxscale
2017-03-09 14:49:02 notice : The logging of informational messages has been enabled.
2017-03-09 14:49:02 notice : Initialise CLI router module V1.0.0.
2017-03-09 14:49:02 notice : Loaded module cli: V1.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libcli.so
2017-03-09 14:49:02 notice : Initializing statemend-based read/write split router module.
2017-03-09 14:49:02 notice : Loaded module readwritesplit: V1.1.0 from /usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so
2017-03-09 14:49:02 notice : Initialise the MySQL Monitor module V1.4.0.
2017-03-09 14:49:02 notice : Loaded module mysqlmon: V1.4.0 from /usr/lib/x86_64-linux-gnu/maxscale/libmysqlmon.so
2017-03-09 14:49:02 notice : Monitor 'MySQL Monitor' is missing the 'monitor_interval' parameter, using default value of 10000 milliseconds.
2017-03-09 14:49:02 notice : No query classifier specified, using default 'qc_sqlite'.
2017-03-09 14:49:02 notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libqc_sqlite.so
2017-03-09 14:49:02 info : qc_sqlite loaded.
2017-03-09 14:49:02 info : qc_sqlite: In-memory sqlite database successfully opened for thread 139896768935808.
2017-03-09 14:49:02 notice : Encrypted password file /var/lib/maxscale/.secrets can't be accessed (No such file or directory). Password encryption is not used.
2017-03-09 14:49:02 info : Notification service feedback is not enabled.
2017-03-09 14:49:02 info : Splitter Service: User debian-sys-maint@localhost for database ANY added to service user table.
2017-03-09 14:49:02 info : Splitter Service: User repl@xxxxxxxxxxxx for database no db added to service user table.
2017-03-09 14:49:02 info : Splitter Service: User repl@xxxxxxxxxxxx for database no db added to service user table.
2017-03-09 14:49:02 notice : Server changed state: srv1[puppetagent.vidyayug.com:3306]: new_master. [Running] -> [Master, Running]
2017-03-09 14:49:02 notice : Server changed state: srv2[sp.test1.com:3306]: new_slave. [Running] -> [Slave, Running]
2017-03-09 14:49:02 notice : Server changed state: srv3[idp.test.com:3306]: new_slave. [Running] -> [Slave, Running]
2017-03-09 14:49:02 notice : A Master Server is now available: puppetagent.vidyayug.com:3306
2017-03-09 14:49:02 info : Splitter Service: User maxuser@xxxxxxxxxxxxxxxxxxxxxxxxx for database ANY added to service user table.
2017-03-09 14:49:02 info : Splitter Service: User maxuser@xxxxxxxxxxxxxxxxxxxxxxxx for database ANY added to service user table.
2017-03-09 14:49:02 info : Splitter Service: User maxuser@xxxxxxxxxxxx for database ANY added to service user table.
2017-03-09 14:49:02 info : Splitter Service: User maxuser@% for database ANY added to service user table.
2017-03-09 14:49:02 notice : Loaded 7 MySQL Users for service [Splitter Service].
2017-03-09 14:49:02 notice : Loaded module MySQLClient: V1.1.0 from /usr/lib/x86_64-linux-gnu/maxscale/libMySQLClient.so
2017-03-09 14:49:02 notice : Listening connections at 0.0.0.0:3307 with protocol MySQL
2017-03-09 14:49:02 info : Started session [0] for Splitter Service service
2017-03-09 14:49:02 info : Initialise MaxScaled Protocol module.
2017-03-09 14:49:02 notice : Loaded module maxscaled: V2.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libmaxscaled.so
2017-03-09 14:49:02 notice : Listening connections at localhost:6603 with protocol MaxScale Admin
2017-03-09 14:49:02 info : Started session [0] for CLI service
2017-03-09 14:49:02 notice : MaxScale started with 1 server threads.
2017-03-09 14:49:02 notice : Started MaxScale log flusher.
2017-03-09 14:54:29 notice : Loaded module MaxAdminAuth: V2.0.0 from /usr/lib/x86_64-linux-gnu/maxscale/libMaxAdminAuth.so
Follow ups
