Master-Slave replication with MariaDB

This is how I configured Master-Slave replication with MariaDB. Since MariaDB is fork of MySQL, you should follow the same steps.

My setup

I’m assuming that you already have two Maria/MySQL servers running. Those are the features of mine:

• OS: Debian testing ‘Bookworm’
• MariaDB version: 10.5.12

I’m working with two virtual machines, to create the slave I’ve duplicated the master vm.

• Master: 192.168.122.13
• Slave: 192.168.122.223

If you don’t have a maria or mysql database, here on unixcop we have a couple of tutorials, also here, here or here.

Master

As I’ve said before, I’m assuming that you already have a maria or mysql running. We need to do some configuration on the master, then on the slave host.

Edit /etc/mysql/mariadb.conf.d/50-server.cnf (or the appropriate file in your setup), and change the following lines:

bind-address = <your.master.host.ip>
server-id=1

Restart MariaDB and create a user with replication privileges:

MariaDB [(none)]> CREATE USER 'esclavo'@'192.168.122.223' IDENTIFIED BY 'SlavePass' ;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'esclavo'@'192.168.122.223';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Choose any username and password you want, esclavo means slave on spanish. Set the IP address of your slave MariaDB. Next you could see the status with SHOW MASTER STATUS.

The (almost, we’ll come back later) last step on master is to copy all the data in the master server to the slave. Let’s create a dump with:

root@my1:~# mysqldump -u root --all-databases -p  --master-data > data.sql

Slave

On the slave server, first change the configuration file /etc/mysql/mariadb.conf.d/50-server.cnf in the same lines as the master:

bind-address = <your.slave.host.ip> 
server-id=1

Restart MariaDB and load the dump from the master with:

root@my2:/home/gonz# mysql -uroot < data.sql

Then stop slave, set the master host and start slave with the following commands:

root@my2:/home/gonz# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.5.12-MariaDB-1-log Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.122.13', MASTER_USER='esclavo',MASTER_PASSWORD='SlavePass';
MariaDB [(none)]> START SLAVE;

Don’t forget to change host ip, user and password to your values if you are just copying and pasting those commands ;).

Testing and fixing

That should do it. If everything works as expected, you should have master-slave replication with MariaDB.

You can now create a database on the master and that database should appear automagically in the slave. But didn’t work for me. I’ve created a database on the master with:

MariaDB [(none)]> CREATE DATABASE somedatabase;

And it wasn’t replicated on the slave. To see why, I’ve runned in the slave server:

MariaDB [(none)]> show slave status\G ;

When I’ve googled that error, they suggested to drop the user esclavo and flush privileges. This on the slave server, also stop slave before droping the user and start slave after flush the privileges:

MariaDB [(none)]> stop slave;
MariaDB [(none)]> drop user 'esclavo'@'192.168.122.223';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> start slave;

And this fixed to me, as you can see in the screenshot now there is somedatabase database on the slave server. And it’s empty. I then went back to the master to create a table and populate with data:

The last step is to check on the slave if the table were created and there is data inside:

Just to be sure, you can check the slave status one more time (or any other time in the future if something seems to be wrong).

And finally I’ve setup master-slave replication with MariaDB.