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
| 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
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!
