GTID Replication – Differences between MySQL and MariaDB

In this blog post, I’m gonna highlight the differences in GTID replication between MySQL and MariaDB

I will not go thru step-by-step setup, because they are many resources out there that do.

The main reason to use GTID is that it makes it easier to track and compare replicated transaction between master and replica; hence, allowing simpler failover and recovery.

Here are the differences between MySQL and MariaDB

1. Composition of GTID

In MariaDB, it is composed of three separated dashed numbers like x-y-z

  • x: first number – domain ID
  • y: second number – server ID
  • z: third number – sequence number

In MySQL, there are 2 parts:

  • source_id
  • transaction_id

2. Enabling GTIDs

To enable GTID, we have to set the following parameters in my.cnf

In MySQL:

  • gtid_mode
  • enforce_gtid_consistency
gtid-mode=ON
enforce-gtid-consistency

In MariaDB:

  • gtid_strict_mode
gtid_strict_mode=1

enforce_gtid_consistency does not exist in MariaDB

3. CHANGE MASTER TO statement

In order for the Replica to identify its Master (data source), and to use GTID-based auto-positioning, we need execute the CHANGE MASTER TO statement. In MySQL, we use the MASTER_AUTO_POSITION option to tell the replica that transactions will be identified by GTIDs.

Example In MySQL:

change master to
master_host = '192.168.1.120',
master_port=3306, 
master_user = 'repl',
master_password = 'password',
master_auto_position=1;

In MariaDB, A slave is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos. The replication will start at the position of the last GTID replicated to slave when the slave connects to the master. Refer to the official documentation for more info https://mariadb.com/kb/en/gtid/

Example In MariaDB:

change master to 
master_host='192.168.1.120', 
master_port=3306, 
master_user='repl', 
master_password='password', 
master_use_gtid=slave_pos;

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

MariaDB – Identify Blocking/Blocked Queries

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

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

MariaDB – MaxScale Set up, Failover, Switchover, Load Balancing

In this blog post, I will show you how to:

  • Setup MariaDB MaxScale
  • Perform Administration and Monitoring
  • Perform a Failover
  • Perform a Switchover

SETUP ENVIRONMENT

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

HostnameIP addressPort
maxscale127.0.0.12222
mariadb1127.0.0.12200
mariadb2127.0.0.12201
mariadb3127.0.0.12202

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. )

Preview(opens in a new tab)

INSTALL MARIADB

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).

[client-server]
!includedir /etc/my.cnf.d

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid

# Network
port = 3306
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0

# Data directory
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# Connections
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M

# Query Cache
query_cache_limit = 128K
query_cache_size = 64M
query_cache_type = 1
log_warnings = 2

# Slow log
slow_query_log
slow_query_log_file = /var/lib/mysql/mariadb-slow.log
long_query_time = 1
log_slow_verbosity = query_plan,explain

# Replication
server-id=1
gtid_strict_mode=1
log-bin = /var/lib/mysql/bin-mariadb.log
expire-logs-days=8
sync_binlog = 1
slave_compressed_protocol
transaction-isolation = READ-COMMITTED
binlog_format = row


# Error log
log_error = mariadb_error.log

# Deadlocks
innodb_print_all_deadlocks = ON

# Performance Schema
performance_schema=on

# User Statistics
userstat = 1

# Denies naming the connection
skip_name_resolve

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;"

SET UP MAXSCALE

Create maxscale ID

useradd -m -d /home/maxscale -u 6603 -s /bin/bash -c "MaxScale Admin Functional ID" -U maxscale

Set password for maxscale ID

echo 'password' | passwd --stdin maxscale

Execute MariaDB Package Repository Setup

The command below will setup 3 different repositories in a single repository configuration file. The repositories are the following:

  • MariaDB Repository
  • MariaDB MaxScale Repository
  • MariaDB Tools Repository

You may read about the setup and usage of the command from this link.

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

Install MariaDB Client

yum install MariaDB-common MariaDB-compat MariaDB-client -y

Install MaxScale

yum install maxscale -y

Generate Encrypted Password.

You can read about encrypting passwords from this link.

# Generate .secrets file
rm -f /var/lib/maxscale/.secrets
maxkeys /var/lib/maxscale

# Generate encrypted password
ls -altr /var/lib/maxscale/.secrets
chown maxscale:maxscale /var/lib/maxscale/.secrets

maxpasswd  /var/lib/maxscale/ password
42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A

maxpasswd  /var/lib/maxscale/ password2
A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73

Configure maxscale.cnf file

vim /etc/maxscale.cnf.d/maxscale.cnf
[mariadb1]
type=server
address=192.168.1.220
port=3306
protocol=mariadbbackend

[mariadb2]
type=server
address=192.168.1.133
port=3306
protocol=mariadbbackend

[mariadb3]
type=server
address=192.168.1.208
port=3306
protocol=mariadbbackend

[MySQLMonitor]
type=monitor
module=mariadbmon
servers=mariadb1,mariadb2,mariadb3
user=repl
password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
replication_user=repl
replication_password=42535F5E5FBC62D068623CD69F62CC00E231737DFC7D51FBEEC0D81D33D7E99A
monitor_interval=5000
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=1
backend_connect_timeout=10
backend_read_timeout=10
backend_write_timeout=10

[ReadWriteSplitService]
type=service
router=readwritesplit
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=6C620E5D01B63D33023313BCD5DD6EC4F60B619F2B7F4B9D18E034428BEDA178
master_failure_mode=fail_instantly
max_slave_replication_lag=1

[ReadWriteMasterService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=master

[ReadOnlySlaveService]
type=service
router=readconnroute
servers=mariadb1,mariadb2,mariadb3
user=maxuser
password=A650AB2DC74FE3F609FE6D16BED6A38D757184B532B2A54FA9FCF63C36627B73
router_options=slave

[ReadWriteSplitListener]
type=listener
service=ReadWriteSplitService
protocol=mariadbclient
address=0.0.0.0
port=4006

[ReadWriteMasterListener]
type=listener
service=ReadWriteMasterService
protocol=mariadbclient
address=0.0.0.0
port=4007

[ReadOnlySlaveListener]
type=listener
service=ReadOnlySlaveService
protocol=mariadbclient
address=0.0.0.0
port=4008

Change permissions and owner of config files

chown maxscale:maxscale /etc/maxscale.cnf.d/maxscale.cnf
chmod 640 /etc/maxscale.cnf.d/maxscale.cnf

You may read about the MariaDB Monitor, ReadConnRoute, and ReadWriteSplit from MariaDB’s website.

Verify that MaxScale service is enabled.

systemctl is-enabled maxscale.service

Start MaxScale service.

systemctl start maxscale.service

You check the maxscale log at the path below.

tail -100 /var/log/maxscale/maxscale.log

MONITORING MAXSCALE

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.

maxctrl call command mariadbmon switchover MariaDB-Monitor mariadb1 mariadb2

Here is the explanation of the command above.

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.

That is all for this blog post.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter