MariaDB is well known for being a drop in replacement for MySQL. It was a community fork of MySQL that has become default in Centos 7.
In MariaDB master-slave replication the data that gets created on the master server, gets replicated in real time to the slaves. This allows you to create highly available solutions or it can be used as a form of backup.
Before we can setup the replication, we actually need to install MariaDB. On both your server you are using as the master and the server you are using as the slave run the following commands to install MariaDB:
yum install mariadb-server mariadb -y
Once installed we need to start the software and make sure it starts automatically on boot.
systemctl start mariadb systemctl enable mariadb
By default MariaDB comes without a root password set. To make sure not just anyone can login to it we need to set the root password. Run the command:
It will then ask you a series of questions to help secure your server.
Set root password? [Y/n]
For this option we do want to set the root password so type
Y and then hit enter.
New password: Re-enter new password:
It will then prompt you to enter a password. Make sure this is secure but you should also be able to remember it.
Remove anonymous users? [Y/n]
It then asks if you want to remove anonymous users. Type
Y for this one, just like you did when choosing the root password option. This removes the ability for people to connect without entering a password.
Disallow root login remotely? [Y/n]
Next it will ask if you want to disallow root to login from an external location. Type
Y for this one and do not allow this to be done. This is typically a secure risk to allow it.
Remove test database and access to it? [Y/n]
Since this is going to be a production system there should not be any testing users or databases. Type
Y and have them removed.
Reload privilege tables now? [Y/n]
Finally the system will ask you the last option, if you want to reload the privilege tables. Since we made changes to password and users select
Y for this to have it done. Once you do this the basic setup is complete.
Now that the sql server is setup, we need to configure the master server. Login to that server for this next section.
First we should make sure that the firewall allows the mysql ports to be sent through the firewall. Run:
firewall-cmd --permanent --add-port=3306/tcp firewall-cmd --reload
After that we need to make a small modification to the MariaDB configuration file:
Look for the section with the [mysqld] header and put these lines:
[mysqld] server_id=1 log-basename=master log-bin binlog-format=row binlog-do-db=<databasename>
Make sure to replace <databasename> with the name of the database you want to replicate. Save the file with
ctrl + o then
ctrl + x.
Once you save and close the file we need to restart the server to apply the changes.
systemctl restart mariadb
Now login to MariaDB as we setup the replication.
mysql -u root -p
It will prompt you for your root password that you set earlier for mysql.
Then follow these steps to create a user for the replication.
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec)
This command will stop the current running slave to make sure it is stopped.
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'securepassword'; Query OK, 0 rows affected (0.00 sec)
This command above will create a new user named “slaveuser” with a password of “securepassword”. Make sure to replace the password with a more secure one.
MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
Then flush the tables to make sure the changes have taken effect.
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000001 | 460 | <db_name> | | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
MariaDB [(none)]> exit
Note down the file name and position from the “show master status” command as you will likely need it later.
Now that you have done this we need to backup all the databases on the master to transfer them to the new slave. Run the command:
mysqldump --all-databases --user=root --password --master-data > masterdatabases.sql
This will dump all the data into a file called “masterdatabases.sql”. From there we need to make sure that the databases are unlocked.
Log back into MariaDB and run:
mysql -u root -p MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> quit Bye
Now upload the masterdatabases.sql file you created earlier using scp,sftp,ftp or some other method of your choosing to your slave server.
If you have not done it already, make sure you have installed MariaDB, run the secure setup and allowed it through the firewall like you did on the master.
Once this is done open up the MariaDB configuration file:
Just like on the master find the [mysqld] section and put these lines below it:
[mysqld] server-id = 2 replicate-do-db=<db to replicate>
Make sure to replace “db to replicate” with the name of the database that is being replicated. The server id should also be unique. It should be different from the master and also any other slaves. When you have done this save and close the file with:
ctrl + o and then
ctrl + x.
Now we need to import the databases that we uploaded to the slave earlier.
mysql -u root -p < masterdatabases.sql
Then we need to apply the configuration file changes we made before.
systemctl restart mariadb
From there we to now login to MariaDB to setup the slave.
mysql -u root -p
Once there you need to stop the slave to make changes
MariaDB [(none)]> STOP SLAVE; Query OK, 0 rows affected (0.01 sec)
Once done with that you need to configure the slave to use the master we setup earlier. Make sure to replace the values with the ones we told you to save from before
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='<your master's ip>', MASTER_USER='slaveuser', MASTER_PASSWORD='securepassword', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=460; Query OK, 0 rows affected (0.03 sec)
After that is done the slave needs to be started.
MariaDB [(none)]> SLAVE START; Query OK, 0 rows affected (0.01 sec)
You can then view the status of the slave and it should look similar to this:
MariaDB [(none)]> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: <master's ip address> Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 460 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: <replicated 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: 460 Relay_Log_Space: 827 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 1 row in set (0.00 sec)
Now that your system is setup for the replicate you need to test that it works. Login to your master and make changes to the database that you setup replication for. Then login back into the slave and see if the changes are also there. If they are then you have successfully setup master-slave replication.
This work is licensed under a Creative Commons Attribution 4.0 International License.