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. <\/p>\n\n\n\n
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.<\/p>\n\n\n
\n[root@localhost cloudinit]# cat mysql-master.yaml\ninstance-id: mysql-master\nlocal-hostname: mysql-master\n\n[root@localhost cloudinit]# cat mysql-slave.yaml\ninstance-id: mysql-slave\nlocal-hostname: mysql-slave\n\n[root@localhost cloudinit]# cat cloud-init.yaml\n#cloud-config\n\nusers:\n - name: adam\n ssh_authorized_keys:\n - ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDB1D9CP4jqWxKw4ug+lR2zol7W6oJZ7wuliMt8aqlYekUBk7Pi7apQakc7rqjYD+b4iUDig0\/4Zk4u6DC8WPgVr6o60fV7sdpoj0GBuxL+voGE0YV84zmorHoM8TCfLeMN3AdM0EMcT2NI8V\/dmZ7uILYLYaXB+RRLv1QoMiL6zLGhLOfhdVKdvmbNqNcrvAEonnzQCVhFjRied2CfhnuH9tNXzGT5Y8wz0E9I8gQQp6GCyU7HnCHW8CLWpymZIrt2y7\/Bi4XlKAbvaUFZJ9XLNsAK3gBC\/VygIVQkWp9o3Y+KOmOsmsS51xJsigfDI0UMRdehdNEN+6vm7Eft9QZYHOg1xoTyJkgiFs9yCRFSRuXvFSsFLXUq5TFLv73qquKE6e\/STORKobF2V7LaOuvbw1BIt2zo4v4c4toyaB5hshojO7bpORzhH8K43vEs0VW2ou9Zo8L3DwmZv6qFAy88BDCAIHoElgc3fmddlZJfCvcN4ZWDuISEP\/j2oVuDT40= adam@localhost.localdomain\n sudo: ["ALL=(ALL) NOPASSWD:ALL"]\n groups: sudo\n shell: \/bin\/bash\n\n<\/pre><\/div>\n\n\nCreating a small script to build the Mariadb(mysql) Master-Slave Replica cluster<\/h2>\n\n\n\n
We create the script as before to build the cluster virtual machines with cloud-init.<\/p>\n\n\n
\n[root@localhost cloudinit]# cat create-mysql-master-slave.sh\nvirt-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\n\n\nvirt-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\n\n<\/pre><\/div>\n\n\nPreparing the Master and Slave VM<\/h2>\n\n\n\n[root@localhost cloudinit]# virsh net-dhcp-leases default | grep mysql\n 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\n 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\n\n<\/pre><\/div>\n\n\nAbove 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.<\/p>\n\n\n
\n# Login to Master to install packages\nssh adam@192.168.122.132 -i ~adam\/.ssh\/id_rsa \n\nyum install firewalld mariadb-server vim\n[adam@mysql-master ~]$ sudo systemctl start firewalld\n[adam@mysql-master ~]$ sudo systemctl enable firewalld\n[adam@mysql-master ~]$ sudo systemctl enable mariadb\n[adam@mysql-master ~]$ sudo mysql_secure_installation\n[adam@mysql-master ~]$ sudo firewall-cmd --add-service=mysql --permanent\n[adam@mysql-master ~]$ sudo systemctl restart firewalld\n\n# Login to Slave and perform the same\n\n[adam@mysql-slave ~]$ sudo yum install firewalld mariadb-server vim\n[adam@mysql-slave ~]$ sudo systemctl start firewalld\n[adam@mysql-slave ~]$ sudo systemctl enable firewalld\n[adam@mysql-slave ~]$ sudo systemctl enable mariadb\n[adam@mysql-slave ~]$ sudo mysql_secure_installation\n[adam@mysql-slave ~]$ sudo firewall-cmd --add-service=mysql --permanent\n[adam@mysql-slave ~]$ sudo systemctl restart firewalld\n\n# For good measure we check firewall-cmd open ports were added as the services we need\n\n[adam@mysql-slave ~]$ sudo firewall-cmd --list-all\npublic (active)\n target: default\n icmp-block-inversion: no\n interfaces: eth0\n sources:\n services: cockpit dhcpv6-client mysql ssh\n -- truncated output\n\n[adam@mysql-master~]$ sudo firewall-cmd --list-all\npublic (active)\n target: default\n icmp-block-inversion: no\n interfaces: eth0\n sources:\n services: cockpit dhcpv6-client mysql ssh\n -- truncated output\n\n<\/pre><\/div>\n\n\nWe proceed with the masters configuration by giving it id=1 and enabling bin logs for replication.<\/p>\n\n\n
\n[adam@mysql-master ~]$ cat \/etc\/my.cnf.d\/mariadb-server.cnf\n[mysqld]\nserver-id=1\nlog-bin=mysql-bin\n\n<\/pre><\/div>\n\n\nMake 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.<\/p>\n\n\n
\n[adam@mysql-master ~]$ sudo systemctl restart mariadb\n<\/pre><\/div>\n\n\nSo 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’.<\/p>\n\n\n
\n[adam@mysql-master ~]$ sudo mysql -u root\n\nMariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.122.117' IDENTIFIED BY 'makemereallysecureplease'\n\nMariaDB [(none)]> flush privileges;\nMariaDB [(none)]> flush tables with read lock;\n<\/pre><\/div>\n\n\nA word on Secure Passwords if binding in a publicnet<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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]\n\n\n
\nsudo yum install epel-release\nsudo yum install pwgen\n\n$ pwgen --secure 12 -n 1\n3L6km4alWQLb\n\n<\/pre><\/div>\n\n\nPreparing the Slave VM <\/h2>\n\n\n\n
Pretty simple, the only important thing is to set the id.<\/p>\n\n\n
\n[mysqld]\nserver-id = 2\n\n<\/pre><\/div>\n\n\nNow 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.<\/p>\n\n\n
\n[adam@mysql-master ~]$ sudo mysql -u root\n\nMariaDB [(none)]> show master status;\n+------------------+----------+--------------+------------------+\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+------------------+----------+--------------+------------------+\n| mysql-bin.000001 | 328 | | |\n+------------------+----------+--------------+------------------+\n1 row in set (0.001 sec)\n\n<\/pre><\/div>\n\n\n[adam@mysql-slave ~]$ sudo mysql -u root\nMariaDB [(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;\n\n<\/pre><\/div>\n\n\nIn the legacy mysql master-slave replica’s we need to know 3 things. The credentials <\/strong>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 <\/strong>and position of the master log file<\/strong>, 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.<\/p>\n\n\n\nChecking that the Slave is synching with the replica user using ‘show slave status<\/strong>‘. We use \\G for clearer output.<\/p>\n\n\n\nMariaDB [(none)]> show slave status\\G;\n*************************** 1. row ***************************\n Slave_IO_State: Waiting for master to send event\n Master_Host: 192.168.122.132\n Master_User: replication\n Master_Port: 3306\n Connect_Retry: 60\n Master_Log_File: mysql-bin.000003\n Read_Master_Log_Pos: 342\n Relay_Log_File: mariadb-relay-bin.000006\n Relay_Log_Pos: 641\n Relay_Master_Log_File: mysql-bin.000003\n Slave_IO_Running: Yes\n Slave_SQL_Running: Yes\n Replicate_Rewrite_DB:\n Replicate_Do_DB:\n Replicate_Ignore_DB:\n Replicate_Do_Table:\n Replicate_Ignore_Table:\n Replicate_Wild_Do_Table:\n Replicate_Wild_Ignore_Table:\n Last_Errno: 0\n Last_Error:\n Skip_Counter: 0\n Exec_Master_Log_Pos: 342\n Relay_Log_Space: 1251\n Until_Condition: None\n Until_Log_File:\n Until_Log_Pos: 0\n Master_SSL_Allowed: No\n Master_SSL_CA_File:\n Master_SSL_CA_Path:\n Master_SSL_Cert:\n Master_SSL_Cipher:\n Master_SSL_Key:\n Seconds_Behind_Master: 0\n Master_SSL_Verify_Server_Cert: No\n Last_IO_Errno: 0\n Last_IO_Error:\n Last_SQL_Errno: 0\n Last_SQL_Error:\n Replicate_Ignore_Server_Ids:\n Master_Server_Id: 1\n Master_SSL_Crl:\n Master_SSL_Crlpath:\n Using_Gtid: No\n Gtid_IO_Pos:\n Replicate_Do_Domain_Ids:\n Replicate_Ignore_Domain_Ids:\n Parallel_Mode: optimistic\n SQL_Delay: 0\n SQL_Remaining_Delay: NULL\n Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates\n Slave_DDL_Groups: 1\nSlave_Non_Transactional_Groups: 0\n Slave_Transactional_Groups: 0\n1 row in set (0.000 sec)\n\nERROR: No query specified\n\n<\/pre><\/div>\n\n\nAs 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;<\/p>\n\n\n
\n[adam@mysql-master ~]$ sudo mysql -u root\nMariaDB [(none)]> unlock tables;\nQuery OK, 0 rows affected (0.000 sec)\n\nMariaDB [(none)]> create database testsync;\nQuery OK, 1 row affected (0.001 sec)\n\n<\/pre><\/div>\n\n\nYou 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.<\/p>\n\n\n
\nMariaDB [(none)]> show databases;\n+--------------------+\n| Database |\n+--------------------+\n| information_schema |\n| mysql |\n| performance_schema |\n| sys |\n| test |\n| testsync |\n+--------------------+\n6 rows in set (0.001 sec)\n\n<\/pre><\/div>\n\n\nSure 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<\/p>\n\n\n
\nmysqldump -u root \u2013all-databases > all_databases_dump.sql\n<\/pre><\/div>\n\n\nIn our case it was two new servers so this step wasn’t necessary for the lab demonstration.<\/p>\n\n\n\n
Another useful command you might want to use is show processlist; which is handy for debugging issues that may arise;<\/p>\n\n\n
\nMariaDB [(none)]> show processlist;\n+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+\n| Id | User | Host | db | Command | Time | State | Info | Progress |\n+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+\n| 8 | system user | | NULL | Slave_IO | 12266 | Waiting for master to send event | NULL | 0.000 |\n| 9 | system user | | NULL | Slave_SQL | 274 | Slave has read all relay log; waiting for more updates | NULL | 0.000 |\n| 10 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |\n+----+-------------+-----------+------+-----------+-------+--------------------------------------------------------+------------------+----------+\n3 rows in set (0.000 sec)\n\n<\/pre><\/div>\n\n\n 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. <\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading