I have previously described how to create a galera cluster using a created libvirt lab. I now describe how to create a master-slave mysql replication lab of the same without utilising Galera and instead using the inbuilt mysql binary-log master and a slave replica synchronising with it’s own user to the master.
Creating the Master and Slave VM’s
As before we create a pair of oneliners, and user-data and meta-data for cloud init to create the two nodes with our provided .ssh/id_rsa.pub key. In this case we only need to recreate the meta-data for the new hostnames and VM instance name. The cloud-init.yaml we created in the previous article is still relevant since that only contains our SSH public key, so we will reuse it.
[root@localhost cloudinit]# cat mysql-master.yaml
instance-id: mysql-master
local-hostname: mysql-master
[root@localhost cloudinit]# cat mysql-slave.yaml
instance-id: mysql-slave
local-hostname: mysql-slave
[root@localhost cloudinit]# cat cloud-init.yaml
#cloud-config
users:
- name: adam
ssh_authorized_keys:
- ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDB1D9CP4jqWxKw4ug+lR2zol7W6oJZ7wuliMt8aqlYekUBk7Pi7apQakc7rqjYD+b4iUDig0/4Zk4u6DC8WPgVr6o60fV7sdpoj0GBuxL+voGE0YV84zmorHoM8TCfLeMN3AdM0EMcT2NI8V/dmZ7uILYLYaXB+RRLv1QoMiL6zLGhLOfhdVKdvmbNqNcrvAEonnzQCVhFjRied2CfhnuH9tNXzGT5Y8wz0E9I8gQQp6GCyU7HnCHW8CLWpymZIrt2y7/Bi4XlKAbvaUFZJ9XLNsAK3gBC/VygIVQkWp9o3Y+KOmOsmsS51xJsigfDI0UMRdehdNEN+6vm7Eft9QZYHOg1xoTyJkgiFs9yCRFSRuXvFSsFLXUq5TFLv73qquKE6e/STORKobF2V7LaOuvbw1BIt2zo4v4c4toyaB5hshojO7bpORzhH8K43vEs0VW2ou9Zo8L3DwmZv6qFAy88BDCAIHoElgc3fmddlZJfCvcN4ZWDuISEP/j2oVuDT40= [email protected]
sudo: ["ALL=(ALL) NOPASSWD:ALL"]
groups: sudo
shell: /bin/bash
Creating a small script to build the Mariadb(mysql) Master-Slave Replica cluster
We create the script as before to build the cluster virtual machines with cloud-init.
[root@localhost cloudinit]# cat create-mysql-master-slave.sh
virt-install --name mysql-master --memory 2048 --vcpus 4 --disk=size=10,backing_store=/var/lib/libvirt/images/CentOS-Stream-GenericCloud-9-latest.x86_64.qcow2 --cloud-init user-data=./cloud-init.yaml,meta-data=./mysql-master.yaml,disable=on --network bridge=virbr0 --osinfo=centos-stream9 --noautoconsole
virt-install --name mysql-slave --memory 2048 --vcpus 4 --disk=size=10,backing_store=/var/lib/libvirt/images/CentOS-Stream-GenericCloud-9-latest.x86_64.qcow2 --cloud-init user-data=./cloud-init.yaml,meta-data=./mysql-slave.yaml,disable=on --network bridge=virbr0 --osinfo=centos-stream9 --noautoconsole
Preparing the Master and Slave VM
[root@localhost cloudinit]# virsh net-dhcp-leases default | grep mysql
2024-02-24 23:54:39 52:54:00:8c:2c:94 ipv4 192.168.122.132/24 mysql-master 01:52:54:00:8c:2c:94
2024-02-24 23:52:54 52:54:00:c8:e7:9f ipv4 192.168.122.117/24 mysql-slave 01:52:54:00:c8:e7:9f
Above we obtain the ip address of the the two nodes with virsh net-dhcp-leases, and a grep for our name tag used in the creation of the VM’s for easy to view output.
We then login and install the mariadb-server package, since we’re at it lets do it properly and install firewalld too and add a firewall rule for mysql port [3306]. We’ll also install vim, because its awesome fast for editing config files and isn’t present in our minimal base image. We also make sure firewalld and mariadb start on boot.
# Login to Master to install packages
ssh [email protected] -i ~adam/.ssh/id_rsa
yum install firewalld mariadb-server vim
[adam@mysql-master ~]$ sudo systemctl start firewalld
[adam@mysql-master ~]$ sudo systemctl enable firewalld
[adam@mysql-master ~]$ sudo systemctl enable mariadb
[adam@mysql-master ~]$ sudo mysql_secure_installation
[adam@mysql-master ~]$ sudo firewall-cmd --add-service=mysql --permanent
[adam@mysql-master ~]$ sudo systemctl restart firewalld
# Login to Slave and perform the same
[adam@mysql-slave ~]$ sudo yum install firewalld mariadb-server vim
[adam@mysql-slave ~]$ sudo systemctl start firewalld
[adam@mysql-slave ~]$ sudo systemctl enable firewalld
[adam@mysql-slave ~]$ sudo systemctl enable mariadb
[adam@mysql-slave ~]$ sudo mysql_secure_installation
[adam@mysql-slave ~]$ sudo firewall-cmd --add-service=mysql --permanent
[adam@mysql-slave ~]$ sudo systemctl restart firewalld
# For good measure we check firewall-cmd open ports were added as the services we need
[adam@mysql-slave ~]$ sudo firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: eth0
sources:
services: cockpit dhcpv6-client mysql ssh
-- truncated output
[adam@mysql-master~]$ sudo firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: eth0
sources:
services: cockpit dhcpv6-client mysql ssh
-- truncated output
We proceed with the masters configuration by giving it id=1 and enabling bin logs for replication.
[adam@mysql-master ~]$ cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
Make sure you add these in the correct section. Note some distributions still use /etc/my.cnf as opposed to /etc/my.cnf.d/ , such as Ubuntu. It’s clear which one you have since there will be a [mysqld] section. Include the two lines we need to add in there. Once done restart mariadb on the master.
[adam@mysql-master ~]$ sudo systemctl restart mariadb
So far so good. Let’s create a user on the master for the slave mysql VM to use for replicating (copying) the data from master node. After creating the new account we flush privileges table for good measure and put the master in a read-only mode to prevent changes for the next step ‘bin file location’ and ‘bin file position’.
[adam@mysql-master ~]$ sudo mysql -u root
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.122.117' IDENTIFIED BY 'makemereallysecureplease'
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> flush tables with read lock;
A word on Secure Passwords if binding in a publicnet
This is only a test lab so it is safe for us to use a low security password. I recommend you use pwgen or similar to create a secure password in a production environment though, especially if you are binding to 0.0.0.0 and are in an open public net.
If you install pwgen install it, by first installing epel-release repo and then pwgen.
Most people will have a private network backend that sits behind a load balancer or master sql server that is only accessible to the service network. Still it’s better in a non lab environment to always get into the habit of using a secure password; notice in my use case I only give access via explicit static ipv4 of the VM in question to prevent unauthorised access to the db. [this is really much more important in a publicnet setting where there is no option for rich rules with firewalld etc]
sudo yum install epel-release
sudo yum install pwgen
$ pwgen --secure 12 -n 1
3L6km4alWQLb
Preparing the Slave VM
Pretty simple, the only important thing is to set the id.
[mysqld]
server-id = 2
Now we’re ready to tell the Slave where the master is. First we need to login to the master sql server to get some important information for the slave we don’t know yet know.
[adam@mysql-master ~]$ sudo mysql -u root
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)
[adam@mysql-slave ~]$ sudo mysql -u root
MariaDB [(none)]> change master to master_host='192.168.122.132', master_user='replication', master_password='test', master_log_file='mysql-bin.000001', master_log_pos=328;
In the legacy mysql master-slave replica’s we need to know 3 things. The credentials for the slave login set on the master (in this case you can see this is set above as the user replication and the password, and also the name and position of the master log file, this is critical for the slave node to know ‘where to sync from’, especially if there is major differences in the two databases. Also, its important to understand later on if you add additional slaves or one of the slaves gets corrupted and needs to be manually resynced with the master.
Checking that the Slave is synching with the replica user using ‘show slave status‘. We use \G for clearer output.
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.122.132
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 342
Relay_Log_File: mariadb-relay-bin.000006
Relay_Log_Pos: 641
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Rewrite_DB:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 342
Relay_Log_Space: 1251
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
As we can see the mysql-slave is correctly configured. Once this is done we can instruct the master to continue processing queries as normal, removing the read lock on the tables;
[adam@mysql-master ~]$ sudo mysql -u root
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> create database testsync;
Query OK, 1 row affected (0.001 sec)
You can see how important it is to properly label servers with cloud-init and virsh, vmware, openstack etc. Since performing maintenance and upgrades of any kind it can be easy to mistake the server otherwise, and can result in some pretty unpleasant confusion for yourself and your clients. We can quickly test that the slave is syncing with the master by creating a new database, too.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| testsync |
+--------------------+
6 rows in set (0.001 sec)
Sure enough our replica is taking output correctly from the master and replicating data.
You may, if the dataset on the master is pre-existing and very large, want to backup the mysql database on the master for good measure with mysqldump
mysqldump -u root –all-databases > all_databases_dump.sql
In our case it was two new servers so this step wasn’t necessary for the lab demonstration.
Another useful command you might want to use is show processlist; which is handy for debugging issues that may arise;
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
| 8 | system user | | NULL | Slave_IO | 12266 | Waiting for master to send event | NULL | 0.000 |
| 9 | system user | | NULL | Slave_SQL | 274 | Slave has read all relay log; waiting for more updates | NULL | 0.000 |
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+
3 rows in set (0.000 sec)
I hope that this is useful to someone, as there are not a lot of really good and simple documentation showing this process in a clear to understand way online.