Trying to create MariaDB replica server
A common architectural pattern for relational databases is to create an additional replica server. This pattern usually come up due because most applications are usually read heavy - data is usually read to be presented to users.
The whole blog post would be to show how we can quickly get started (naturally - there could be better configuration that we can use here such as limiting which databases which are to be replicated to other replicas.)
Setting up MariaDB on server
We are not utilizing the cloud database solutions provided by the cloud vendors - we won’t learn too much if we simply rely on that mechanism.
First, we would need to create a normal linux/debian server. We would then need to install the mariadb server and its corresponding client.
sudo apt update
sudo apt install -y mariadb-server mariadb-client
We can check that the database is installed the correctly by first going into the MySQL CLI tool.
Then, we can try to list the databases within it by running the following SQL command:
It should respond with the following:
| Database |
| information_schema |
| mysql |
| performance_schema |
Testing the installed Database with an application
Now that we mariadb installed, we would need something to simulate the application which would be inserting the data into the databases. We can utilize the following application - the application would even run a migration step without requiring a separate sql script to do so. https://github.com/hairizuanbinnoorazman/Go_Programming/tree/master/Apps/appwithmysql
In order to use the application mentioned in the github link, we would first need to create the database as well as the corresponding users.
CREATE DATABASE testmysql;
CREATE USER username IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `testmysql`.* TO 'username';
Once we have this in place, we can a
scp of our binary from our application to the server. We should be able to run it with no issue. I’m assume the same binary name was used - which is recordmaker
scp recordmaker <ssh user>@<ip address>:/home/<ssh user>/recordmaker
After which, we can ssh into the server and start the recordmaker binary. If there are permission issues - might need to alter it with chmod etc.
# In /home/<ssh user>/
Alter server to be the primary database server
Now that we have an application to test the entire mechanism. First we need to setup primary server; we would need to also ensure that the primary is accessible by the other replicas. By default, MariaDB is setup to also be binded to 127.0.0.1 - it cannot be accessed from hosts from outside the server it resides in. We need to change this to 0.0.0.0. This is done by changing it in the following file:
... #Other configuration
# bind-address 127.0.0.1 - change this to 0.0.0.0 (similar as the next line)
bind-address = 0.0.0.0
... # Other configuration
We would then need tto restart the database to get these configurations to be used for the mariadb - configuration changes are usually not changed on the fly. We need to make sure that the database is properly binded to 0.0.0.0. We can run the following to check it:
# netstat -tlnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4369/mariadbd
The next step would be to once again to go into MySQL CLI and create the following user:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Create the replica server
We would need to replicate the steps to setup MariaDB on the replica server. We would also need to reconfigure it the
... #Other configuration
server-id = 2
... # Other configuration
We would also need to restart the database after this.
Copy the data over from primary to replicas
This is the important bit here; we would need to “bootstrap” our replica server with the data from our primary server. I tried without it and replicating won’t even work (unless we bootstrap the primary + replica without even putting in the data into the server)
The instructions for this is available in the following section of the replication reference page on MariaDB documentation: https://mariadb.com/kb/en/setting-up-replication/#getting-the-masters-binary-log-co-ordinates. For this blog post, we would just list down the commands that would make this happen.
# In the primary server
# In MySQL tool
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; #make sure we copy the values for log file and log pos. It is needed for later section
# In bash of primary db
We would then copy the data over to the replica server
mariadb < backup-file.sql
This would bootstrap our replica database with the required data.
Then, on the primary server, we can simply run the following command:
Final configuration of replicas MariaDB server
We would need to run the following command on our replica server.
CHANGE MASTER TO
We can then start the slave thread
We can then check if slave is running and replication works as expected.
SHOW SLAVE STATUS \G
If there are any issues, check out the following forum page:
One more round of testing
To make sure that the entire replication process is working, we can utilize our friend, recordmaker that would create database records. As we start running it, we can go to replica server and keep running the following SQL query:
select * from `testmysql`.`users` order by `updated_at` desc limit 10 ;
We will see that there is some slight replication delay but it should be roughly ok for application use. It could be 20-30s delay at times but it could be the amount of data being generated by the
Setting up the above is such a pain - automation is definitely needed. The entire process is pretty much error prone - just one misstep would easily mean bad replication leading to database corruption making it impossible to use.
- Following blog post is heavily references from the following page:
- Need to “expose” mysql to other https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/