Hello. Great job on a very well throughout and helpful tutorial. I am working with one of my clients and they have a particular use case that requires "all" writes/reads be routed to the master through MaxScale. In your tutorial at 39:54, you mentioned a parameter in MaxScale that I believe could be of use to handle this specific use case. I played the video several times at this point and was unable to understand what parameter you mentioned. I believe it ended with _reads. Do you know of a way to force all reads/writes to the master? We came up with a solution; however it is not a good one. For the router, I am using "router=readwritesplit". Before defining the replication splitter service, I created a filter named "rtmfilter" of type "filter". The name of this module is "ccrfilter" with a time of "1000". Then, when defining the read/write splitter, I am just including "filters=rtmfilter". This works since we will never hit a time of 1000. Again, I believe the solution we came up with is terrible. ~jeff
Hey Jeffrey, apologies for the long delay in the reply. Somehow I missed this comment. In the MaxScale Read Write Splitter section set "master_accepts_reads=true" mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/#master_accept_reads, this will use the Master node for reads as well. BUT if you want all the reads and writes to go to Master regardless, then you can configure the Read/Write Split section to use "readconnroute" filter instead of "readwritesplit" router. In the "readconrout" router section, configure as follows [Read-Service] type=service router=readconnroute servers=server1, server2, server3 router_options=master This will ensure all the traffic goes to "master" whichever it is out of the three servers defined. Similarly, if you want to send the traffic to all the slaves and never to the master, sue the "router_options=slave", if you want to distribute. Remember the readconroute is not very smart, it will just send anything coming from the client regardless of its type "READ/WRITE", will be sent to the node defined through "roouter_options". For your case, the above example will be perfect as it will totally ignore all the slaves and just route everything to the Master node. Hope this helps in your future projects :)
Hey Zaky, For MaxScale R/W service you will need to grant the following asuming the user name is maxuser@'%', once done on the Master DB node, restart MaxScale service :) GRANT SELECT ON mysql.user TO 'maxuser'@'%'; GRANT SELECT ON mysql.db TO 'maxuser'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxuser'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxuser'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxuser'@'%';
Yes, that is one of the drawbacks of using a Galera Cluster because it uses full synchronous replication. All the nodes must acknowledge the transaction write before the end user can continue with next transaction. Should always make sure that all the nodes are identical in terms of hardware and network specs. 👍
@@periklhsvasilakis8115 Not really the case! Galera cluster has its benefits of providing very high availability and durability. Because all the nodes are equal in terms of data and are all masters, the client can switch to any node without any worry. MaxScale can switch to any other master if a node goes down without any delays, data loss is zero because of full synchronous replication. Whereas a standard master/slave can have replication lag and inconsistent slave reads because of async behavior but provides much higher TPS. There are positives and negatives of each architecture just like with any solution, chose the one that fits your requirements :)
Hi I have a Galera Cluster with 3 node and I configured maxscale like your instruction then I created a user with all privileges. this user can connect to any of my nodes but it gets `ERROR 1045 (28000): Access denied` from MaxScale node. do you have any idea about problem? ------------------------------------------------------------------------------------------------------- [maxscale] threads=1 [Galera-Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=maxscale password=qwe123 monitor_interval=1000 [Read-Write-Service] type=service router=readwritesplit servers=server1, server2, server3 user=maxscale password=qwe123 [Read-Only-Service] type=service router=readconnroute servers=server1, server2, server3 user=maxscale password=qwe123 router_options=slave [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient port=4006 [Read-Only-Listener] type=listener service=Read-Only-Service protocol=MariaDBClient port=4008 [server1] type=server address=192.168.122.93 port=3306 protocol=MariaDBBackend [server2] type=server address=192.168.122.17 port=3306 protocol=MariaDBBackend [server3] type=server address=192.168.122.13 port=3306 protocol=MariaDBBackend ------------------------------------------------------------------------------------------------------- reza@sky:~$ mysql -ureza -preza -h 192.168.122.222 -P4006 ERROR 1045 (28000): Access denied for user 'reza'@'::ffff:192.168.122.1' (using password: YES)
I think at first you must select only one type of router config, between readwritesplit or readconnroute. About the access, do you set a maxscale user in mysql with his privileges? I have the same config at work and I has doing some research of that.
My problem was about privileges. maxscale user need some privileges but I didn't grant all of them. Problem solved after I granted permissions. thanks to you for tutorial.
Thank you guys for the positive response
it is very useful session. thanks
I am glad it helped 🙂
Hello. Great job on a very well throughout and helpful tutorial. I am working with one of my clients and they have a particular use case that requires "all" writes/reads be routed to the master through MaxScale. In your tutorial at 39:54, you mentioned a parameter in MaxScale that I believe could be of use to handle this specific use case. I played the video several times at this point and was unable to understand what parameter you mentioned. I believe it ended with _reads. Do you know of a way to force all reads/writes to the master? We came up with a solution; however it is not a good one. For the router, I am using "router=readwritesplit". Before defining the replication splitter service, I created a filter named "rtmfilter" of type "filter". The name of this module is "ccrfilter" with a time of "1000". Then, when defining the read/write splitter, I am just including "filters=rtmfilter". This works since we will never hit a time of 1000. Again, I believe the solution we came up with is terrible. ~jeff
Hey Jeffrey, apologies for the long delay in the reply. Somehow I missed this comment. In the MaxScale Read Write Splitter section set "master_accepts_reads=true" mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/#master_accept_reads, this will use the Master node for reads as well. BUT if you want all the reads and writes to go to Master regardless, then you can configure the Read/Write Split section to use "readconnroute" filter instead of "readwritesplit" router.
In the "readconrout" router section, configure as follows
[Read-Service]
type=service
router=readconnroute
servers=server1, server2, server3
router_options=master
This will ensure all the traffic goes to "master" whichever it is out of the three servers defined.
Similarly, if you want to send the traffic to all the slaves and never to the master, sue the "router_options=slave", if you want to distribute. Remember the readconroute is not very smart, it will just send anything coming from the client regardless of its type "READ/WRITE", will be sent to the node defined through "roouter_options".
For your case, the above example will be perfect as it will totally ignore all the slaves and just route everything to the Master node.
Hope this helps in your future projects :)
Very useful. keep it up.
I have made this video available again, just thought of keeping it alive while I record a updated version :)
hi thanks for video
i stack on maxscale can't read the master server can you provide the maxscale.cnf, example for me ? thanks
What user i must write in [Read-Write-service]
Because maxscale status telling me no user were loaded in READ WRITE SERVICE
Hey Zaky, For MaxScale R/W service you will need to grant the following asuming the user name is maxuser@'%', once done on the Master DB node, restart MaxScale service :)
GRANT SELECT ON mysql.user TO 'maxuser'@'%';
GRANT SELECT ON mysql.db TO 'maxuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxuser'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxuser'@'%';
Hello, can you make me totorial master slave configuration and how to connect it to maxscale
Thanks..
Uploading a mini series "tutorial" now! check out the channel in 12 hours time :)
If a slave server has bad performance (bad cpu, low memory, bad network) or a big delay for a reason, it will affect the hall cluster ?
Yes, that is one of the drawbacks of using a Galera Cluster because it uses full synchronous replication. All the nodes must acknowledge the transaction write before the end user can continue with next transaction.
Should always make sure that all the nodes are identical in terms of hardware and network specs. 👍
@@faisalee So is it better to use Master to slaves setup?
@@periklhsvasilakis8115 Not really the case! Galera cluster has its benefits of providing very high availability and durability. Because all the nodes are equal in terms of data and are all masters, the client can switch to any node without any worry. MaxScale can switch to any other master if a node goes down without any delays, data loss is zero because of full synchronous replication. Whereas a standard master/slave can have replication lag and inconsistent slave reads because of async behavior but provides much higher TPS. There are positives and negatives of each architecture just like with any solution, chose the one that fits your requirements :)
Hi
I have a Galera Cluster with 3 node and I configured maxscale like your instruction then I created a user with all privileges. this user can connect to any of my nodes but it gets `ERROR 1045 (28000): Access denied` from MaxScale node. do you have any idea about problem?
-------------------------------------------------------------------------------------------------------
[maxscale]
threads=1
[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
password=qwe123
monitor_interval=1000
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1, server2, server3
user=maxscale
password=qwe123
[Read-Only-Service]
type=service
router=readconnroute
servers=server1, server2, server3
user=maxscale
password=qwe123
router_options=slave
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006
[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=4008
[server1]
type=server
address=192.168.122.93
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.122.17
port=3306
protocol=MariaDBBackend
[server3]
type=server
address=192.168.122.13
port=3306
protocol=MariaDBBackend
-------------------------------------------------------------------------------------------------------
reza@sky:~$ mysql -ureza -preza -h 192.168.122.222 -P4006
ERROR 1045 (28000): Access denied for user 'reza'@'::ffff:192.168.122.1' (using password: YES)
I think at first you must select only one type of router config, between readwritesplit or readconnroute.
About the access, do you set a maxscale user in mysql with his privileges?
I have the same config at work and I has doing some research of that.
My problem was about privileges. maxscale user need some privileges but I didn't grant all of them. Problem solved after I granted permissions.
thanks to you for tutorial.