Here is a SQL script that you can use to identify Blocking and Blocked Queries in MariaDB.
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx AS r
ON r.trx_id = w.requesting_trx_id
First, we will set up the environment. We will be having 1 master and 2 slaves. On top of those, we will have our MariaDB MaxScale server.
To make it easier to setup the environment, you may use the vagrant file below.
# -*- mode: ruby maxscale222
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
#: adding ansible stuff
config.ssh.insert_key = false
#: maxscale box
config.vm.define :maxscale do |maxscale|
maxscale.vm.hostname="maxscale"
maxscale.vm.box = "domflannery/centos7"
maxscale.vm.network "public_network"
end
######: MASTER / SLAVE SERVERS :######
#: master, async && semisync replication
config.vm.define :mariadb1, autostart: true do |mariadb1|
mariadb1.vm.hostname="mariadb1"
mariadb1.vm.box = "domflannery/centos7"
mariadb1.vm.network "public_network"
end
#: slave01, async && semisync replication
config.vm.define :mariadb2, autostart: true do |mariadb2|
mariadb2.vm.hostname="mariadb2"
mariadb2.vm.box = "domflannery/centos7"
mariadb2.vm.network "public_network"
end
#: slave02, async && semisync replication
config.vm.define :mariadb3, autostart: true do |mariadb3|
mariadb3.vm.hostname="mariadb3"
mariadb3.vm.box = "domflannery/centos7"
mariadb3.vm.network "public_network"
end
end
I chose public networks that is set automatically (through DHCP) for demo purposes. The VMs IP addresses will be in the same range as the host’s IP address.
Once all the virtual boxes are up, we will login to each of them to get the assigned public IP address. First, login to each server using local IP (127.0.0.1) and the respective port numbers (refer to the chart below). The login and password will be both vagrant
Hostname
IP address
Port
maxscale
127.0.0.1
2222
mariadb1
127.0.0.1
2200
mariadb2
127.0.0.1
2201
mariadb3
127.0.0.1
2202
login: vagrant password: vagrant
I got the port numbers above by looking at the logs after I issued vagrant up. Below is an example.
Get the assigned IP address using the command below (ifconfig will not work in this version of centos)
ip a
After getting the public IP addresses of each server, configure them in your putty. Give each connection sensible names. (Also, don’t include the /24 of the IP address. )
Install MariaDB in mariadb1, mariadb2, and mariadb3. I will be using MariaDB 10.4 on Centos7. If you want to use a different flavor of Linux, then refer to this link to get the correct repositories info.
Let’s create a repo file.
vim MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Move the file to /etc/yum.repos.d/
sudo mv -vi MariaDB.repo /etc/yum.repos.d/
Once the file is in place, we are ready to install MariaDB (The capitalization of the package names is important).
sudo yum install MariaDB-server MariaDB-client -y
SETUP MARIADB REPLICATION (USING GTID)
Get the master and replicas ready.
Login to mariadb1 as root, and edit the my.cnf
vi /etc/my.cnf
Delete the existing lines; then enter the following lines below (or just copy paste them).
Do the same (edit config file) in mariadb2 and mariadb3, but change the server_id to a different value for each server.
After editing the my.cnf file on all 3 servers, start mariadb service
systemctl start mariadb
STARTING UP REPLICATION
Create a user in the master, and grant “replication slave” to the user. The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
In a real-world production environment, you would create a user that can only connect from the IP address of your slaves, but for the sake of demonstration, we’ll just use the wild card character (%)
Point your slaves to the master by executed the commands below.
change master to
master_host='192.168.1.220',
master_user='repl',
master_password='password',
master_use_gtid=slave_pos;
Run the command below to start the slave
start slave;
Check slave status
show slave status \G
Both Slave_IO_Running and Slave_SQL_Running parameters should be Yes.
Execute on Master (only) to grant privileges to maxuser. This user will be used by maxscale.
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'localhost' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repl'@'%';
grant select on mysql.user to 'maxuser'@'localhost';
grant select on mysql.db to 'maxuser'@'localhost';
grant select on mysql.tables_priv to 'maxuser'@'localhost';
grant select on mysql.roles_mapping to 'maxuser'@'localhost';
grant select on mysql.columns_priv to 'maxuser'@'localhost';
grant select on mysql.proxies_priv to 'maxuser'@'localhost';
flush privileges;"
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repuser'@'%';
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 select on mysql.columns_priv to 'maxuser'@'';
grant select on mysql.proxies_priv to 'maxuser'@'';
flush privileges;"
In your maxscale server, run the command below to check list of servers being monitored.
maxctrl list servers
FAILOVER TEST
Let’s monitor the servers continuously every second. I suggest you open a new session of your maxscale server, and run the command below.
watch -n1 maxctrl list servers
Go to mariadb1, then stop MariaDB service.
systemctl stop mariadb
After a few seconds, you will see that mariadb2 has been promoted to master.
Let’s bring up mariadb1.
systemctl start mariadb
Monitor the servers, and you will see that they are all up and running.
SWITCHOVER
Let’s make mariadb1 server the master again. We will be doing a switchover, but before we proceed, we have to verify the monitor that we are going to use.
maxctrl list monitors
In our case, it is MariaDB-Monitor.
Now, we are ready to do the switchover. Here is the command to perform it.
call command indicates that it is a module command that is to be invoked. mariadbmon indicates the module whose command we want to invoke. switchover is the command we want to invoke. MariaDB-Monitor is the first argument to the command, the name of the monitor as specified in the configuration file. mariadb1 the name of the server we want to make into master mariadb2 current master.
We now have successfully switched over the master role back to mariadb1
LOAD BALANCING TEST
In this demo, we will be using the maxscale server as our client.
In the previous section, we’ve already installed MariaDB client. Verify that is installed successfully.
rpm -qa | grep -i MariaDB
In mariadb1 (master), create an app user.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' WITH GRANT OPTION;
To connect to our servers via maxscale, we use the connection string below. Essentially, we have to indicate the maxscale IP address. My maxscale IP address happens to be 192.168.1.87. We also have to include the Read/Write listener port that is 4006 as indicated in the maxscale config file (/etc/maxscale.cnf)
Execute the command below in your maxscale server.
mysql -u app_user -p -h192.168.1.87 -P4006
To know which server you are connect to, run the following statement.
SELECT @@hostname;
If we keep on executing this statement repeatedly, we will notice that we will be connected to our slaves: mariadb2 and mariadb3. This is load balancing at work. MaxScale knows that we are executing a read statement; hence it is redirecting the traffic to our read-only replicas.
Once we start a transaction, MaxScale now recognizes that we are about to update a record; hence the connection will be redirected to the master node only.
START TRANSACTION;
The connection will remain in master until we commit the transaction.
COMMIT;
After we commit the transaction, the connection will again be redirected to the read-only replicas.
This is also called the Read Write Splitting.
MaxScale analyze statements, and send where appropriate. Write statements to master; and Read statements to some slaves.
