In this post we explain how to install MariaDB 10.6 on Ubuntu 22.04 and configure a replication using binary log shipping.
- Setting up a MariaDB replication
- Test MariaDB replication
- Monitor MariaDB replication
What is a MariaDB replication?
A replication means that data is written from a source server to a replica server. The source server works the same way as a standalone MariaDB or MySQL server with one slight difference: The changes to databases (such as INSERT, UPDATE, ALTER TABLE, etc.) are logged in so-called binary logs. These logs are then shipped to the replica server(s) where the changes are committed once again. Another possibility is to use a replication based on GTID setup. In this tutorial we're focusing on the more widely used binary log replication.
What happened to Master-Slave-Replication?
What are the use cases?
Let's present a couple of examples.
For instance if you have a business-critical application, writing and modifying the databases 24/7, you might run into problems when taking a database backup. You could run into a mysqldump error 1412 (table definition has changed) for example. Taking the backup on the replica server allows to take a consistent backup from the data yet the application can continue to write into the source server.
Another example is if you want to speed up your application by scaling up and using multiple read-only database servers. A replication allows not just one but multiple replica servers. They can all be used as read-only database servers, taking off the load and MySQL connections from the source server.
Setting up a MariaDB replication
For this setup we're using two Ubuntu 22.04 machines in the same internal network:
Source Server: 192.168.55.10
Replica Server: 192.168.55.11
Obviously this also works over Internet with public IP addresses, but you should add firewall rules to restrict MySQL traffic to a minimum.
In this tutorial we use the MariaDB packages from the Ubuntu repositories. As an alternative, the MariaDB repositories can also be added as additional APT repositories, which offer the installation of newer MariaDB versions. The MariaDB version on the Ubuntu 22.04 repositories is 10.6.
On both servers install MariaDB:
root@source:~# apt-get install mariadb-server root@replica:~# apt-get install mariadb-server
Make sure to enable MariaDB so the service will automatically start after a system boot:
root@source:~# systemctl enable mariadb root@replica:~# systemctl enable mariadb
You should also run mysql_secure_installation and set a root password. Use the same password on both servers.
root@source:~# mysql_secure_installation root@replica:~# mysql_secure_installation
Configuration on Source Server
We can let the installed configuration files (ending with .cnf) in /etc/mysql/ untouched. It's better to create a new config which is added and read at the end of a MariaDB start.
Use your favourite editor (vim, nano or whatever) to create the new config file:
root@source:~# vi /etc/mysql/mariadb.conf.d/99-replication.cnf
The following configuration options are important/necessary for a replication using log shipping:
- Listener on public interface (default would be localhost)
- Binary Log format set to ROW
- Binary Logs enabled
- Server ID set to a unique number in the replication
This results in the following basic configuration:
root@source:~# cat /etc/mysql/mariadb.conf.d/99-replication.cnf [mysqld] bind-address = 0.0.0.0 binlog_format = ROW max_binlog_size = 128M expire_logs_days = 7 log-error=/var/log/mysql/error.log log-bin-trust-function-creators = true # Replication server_id = 1 log_bin = /var/log/mysql/mysql-bin.log # Only needed on master #read_only = ON # Only needed on slave
Configuration on Replica Server
Similar to the source server, we create a new cnf config for the replica, with slightly different settings:
root@replica:~# cat /etc/mysql/mariadb.conf.d/99-replication.cnf [mysqld] bind-address = 0.0.0.0 binlog_format = ROW max_binlog_size = 128M expire_logs_days = 7 log-error=/var/log/mysql/error.log log-bin-trust-function-creators = true # Replication server_id = 2 #log_bin = /var/log/mysql/mysql-bin.log # Only needed on master read_only = ON # Only needed on slave
Restart MariaDB on both servers:
root@source:~# systemctl restart mariadb root@replica:~# systemctl restart mariadb
Replication User/Privileges on Source Server
On the source server, prepare the replication privileges for a dedicated replication user (repl) with the IP address from the replica server. The repl user requires REPLICATION SLAVE privileges:
root@source:~# mysql MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.55.11' IDENTIFIED BY 'very_secret_password'; Query OK, 0 rows affected (0.001 sec)
Start replication on the Replica Server
On the replica server, start the replication by defining the source server as MASTER, using the credentials of the replication user "repl":
root@replica:~# mysql MariaDB [(none)]> CHANGE MASTER to MASTER_HOST='192.168.55.10', MASTER_USER='repl', MASTER_PASSWORD='very_secret_password'; Query OK, 0 rows affected (0.018 sec)
Then start the replication process (still on the replica server):
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.001 sec)
You can verify the replication status on the replica server:
MariaDB [(none)]> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.55.10 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 537 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 836 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 537 Relay_Log_Space: 1146 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)
The output not only shows the current status of the replication (look at Slave_IO_State, Slave_IO_Running, Slave_SQL_Running) but also at which log position the source and the replica servers are currently at.
Test MariaDB replication
The replication can easily be tested by creating a new database on the source server. Let's verify the current databases on both source and replica servers:
root@source:~# mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ root@replica:~# mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
Now we create a new database "replicationtest" on the source server:
root@source:~# mysql -e "create database replicationtest" root@source:~# mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | replicationtest | | sys | +--------------------+
Almost immediately with a very small delay, the new database is now also created on the replica server:
root@replica:~# mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | replicationtest | | sys | +--------------------+
Monitor MariaDB replication
As mentioned before, the SQL query SHOW SLAVE STATUS\G; shows all necessary information about the status of a MariaDB replication. This can also be integrated into systems and application monitoring tools such as Nagios or Icinga.
check_mysql_slavestatus is a monitoring plugin doing this job and supports both MySQL and MariaDB replications:
$ ./check_mysql_slavestatus.sh -S /run/mysqld/mysqld.sock -u nagios -p secret -w 60 -c 120 OK: Slave SQL running: Yes Slave IO running: Yes / master: 192.168.55.10 / slave is 0 seconds behind master | delay=0s