Having all the data in just one server is not the best idea. Especially when talking about a database server. Spreading information in several boxes is a good measure to prevent data loss but also for performance. A MySQL/MariaDB master-slave replication scheme is often used as a good solution for both, data redundancy and speed.
How this works? The most basic cluster configuration is a master-slave replication setup. The master will get all the writes first and those will be replicated elsewhere on a different server configured as a slave. Another way to put it is one server is configured as the primary, and this will get all the new data, while other/s server/s will act as replicas of the primary holding a copy of the original data from the primary.
In heavy traffic sites or databases writes can be directed to one server, the master or primary, and all the queries to extract data can be directed to the other servers often called slaves or replicas. This unloads the burden for the primary to handle both writes and reads and often times writes happen only once while reads can happen again and again, sometimes on the same data set. Handling data is often the purpose of a business or an organization, even a blog. It’s written once but read hundreds, thousands and sometimes hundreds of thousands of times.
Obviously, data must be on both sides, on the primary as well as all over the replica servers. So, writes do also happen on the slaves. However, those writes are not synchronous but asynchronous. The what? Writes happen first on the master/primary server and after that has happened, the primary writes the new arrived data onto the slaves/replica servers.
If you find the articles in Adminbyaccident.com useful to you, please consider making a donation.
Use this link to get $200 credit at DigitalOcean and support Adminbyaccident.com costs.
Get $100 credit for free at Vultr using this link and support Adminbyaccident.com costs.
Mind Vultr supports FreeBSD on their VPS offer.
With this summary we move onto the prerequisites and steps of this how to set up MariaDB/MySQL master-slave replication guide.
Note: I use the terms master-slave and primary-replica interchangeably throughout the guide. Both formulas work just right, both are descriptive and intuitive when used.
Prerequisites:
Two FreeBSD boxes with root access.
A bare bones install of MariaDB or MySQL database on each sever.
Scripts for the install if you want to save time:
Tip: If you happen to use DigitalOcean and they haven’t yet given full support for FreeBSD 13 here there’s a guide to upload your own image, from VirtualBox or similar software into your account there.
Instance’s names in this guide:
Master 1 / Primary
Slave 1 / Replica 1
FreeBSD version on both primary and replica:
Master 1
Slave 1
Network setup on both master and slave
Master 1
Slave 1
Step 1.- Allowing external connections on both Master 1 and Slave 1 nodes
By default, the listener in MariaDB is listening on port 3306 over the localhost interface. If we want to set up communication between different nodes this has to change. This is the first step on this setup of MariaDB master-slave replication.
Because we want Master 1 and Slave 1 nodes to communicate to each other, and those two are in the same network we just need to modify the ‘bind-address‘ directive in the ‘/usr/local/etc/mysql/conf.d/server.cnf’ file. As mentioned above, on FreeBSD, you will see the entry configured with the localhost IP address, 127.0.0.1. We will change this to the node’s own IP or we’ll place an asterisk.
Tip: Don’t set this to a simple asterisk ‘*’ since this means anything on the network can reach this server and interact with it.
Before:
[mysqld]
user = mysql
# port = 3306 # inherited from /usr/local/etc/mysql/my.cnf
# socket = /var/run/mysql/mysql.sock # inherited from /usr/local/etc/mysql/my.cnf
bind-address = 127.0.0.1
After:
[mysqld]
user = mysql
# port = 3306 # inherited from /usr/local/etc/mysql/my.cnf
# socket = /var/run/mysql/mysql.sock # inherited from /usr/local/etc/mysql/my.cnf
bind-address = 192.168.1.83
To secure connections between the two nodes the firewall must restrict connections between the nodes and the specific port. Furthermore, username and password are the second line of defense, however we shouldn’t rely on those and the firewall and other hardening should be in place. In the last step we’ll set TLS communication between the nodes so no traffic can be read in an open network.
Step 2.- Master 1 configuration
A default MariaDB installation is a standalone setup, so if we want to switch it to a MariaDB master-slave replication setup we need to make some changes. First in the Master or Primary and later on the Slave/s or Replica/s. In this step we will configure the Master node as the master in this master-slave relation.
Inside the configuration file ‘/usr/local/etc/mysql/conf.d/server.cnf’ we’ll add the following settings.
server-id=1
log-bin
log-basename=master1
binlog-format=mixed
This above is an important bit. The server has been given an id, in this case the number 1. We must then remember this is the primary or master node. The ‘log-bin’ directive sets up what kind of logging this MariaDB install will have, and it will be in binary format. The ‘log-basename’ directive will set the same name for all the logs related to this instance, in this case all will have a name related to ‘master1’. The ‘binlog-format’ directive obviously sets the logs’ format, in this case ‘mixed’ which is the default. More information in the official documentation.
Tip: Don’t change the binlog-format in a master-slave or master-master configuration without contemplating the consequences and measures to mitigate them first. Check the documentation first.
With this configuration setup in the Master 1 node we fire it up.
[albert@FreeBSD-DB1 ~]$ sudo service mysql-server start
Starting mysql.
[albert@FreeBSD-DB1 ~]$
We check the service is up and running:
[albert@FreeBSD-DB1 ~]$ sudo service mysql-server status
mysql is running as pid 2038.
[albert@FreeBSD-DB1 ~]$
As we can see MariaDB is up and running.
Launch this below to check the log files already been created with the ‘log-basename‘ entry we gave it in the configuration.
ls -la /var/db/mysql/
So, here a sample output:
[albert@FreeBSD-DB1 ~]$ ll /var/db/mysql/
total 183
drwxr-xr-x 4 mysql mysql 13B Dec 28 13:23 .
drwxr-xr-x 14 root wheel 19B Dec 25 17:48 ..
-rw-rw---- 1 mysql mysql 24K Dec 28 13:23 aria_log.00000001
-rw-rw---- 1 mysql mysql 52B Dec 28 13:23 aria_log_control
-rw-r----- 1 mysql mysql 976B Dec 28 13:23 ib_buffer_pool
-rw-rw---- 1 mysql mysql 96M Dec 28 13:23 ib_logfile0
-rw-rw---- 1 mysql mysql 12M Dec 28 13:23 ibdata1
-rw-rw---- 1 mysql mysql 12M Dec 28 13:23 ibtmp1
-rw-rw---- 1 mysql mysql 330B Dec 28 13:23 master1-bin.000001
-rw-rw---- 1 mysql mysql 21B Dec 28 13:23 master1-bin.index
-rw-rw---- 1 mysql mysql 0B Dec 25 17:54 multi-master.info
drwx------ 2 mysql mysql 90B Dec 25 17:54 mysql
drwx------ 2 mysql mysql 3B Dec 25 17:54 performance_schema
[albert@FreeBSD-DB1 ~]$
Mind the files named ‘master1-bin.000001‘ as well as ‘master1-bin.index‘. These have been generated due to the directive ‘log-basename‘ we’ve placed in the main ‘server.cnf‘ file.
Step 3.- Create the replication user on all the nodes.
The second change one needs to make into the Master/Primary node is to set a user able to read one or multiple databases (as needed) so this user replicates the data on the Slave/Replica node/s. We are going to replicate everything on this database.
Tip: You could use any mysql user already present in the Master/Primary node to extract data and replicate it elsewhere. Maybe this is convenient for you and different people can extract their own data at different times.
Inside MySQL prompt one needs to create a user to replicate to another system. This below applies on the master node.
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO replicauser@targetsystemip IDENTIFIED BY 'replicauserpwd';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'replicauserpwd';
MariaDB [(none)]> FLUSH PRIVILEGES;
Note: The expression ‘*.*‘ means that any database in the engine will be replicated. One can choose to declare specific databases to replicate instead of all. Furthermore, a specific user per database or a user per group of databases can be chosen if administration has to be delegated to specific people.
Sample output:
[albert@FreeBSD-DB1 ~]$ sudo mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.13-MariaDB-log FreeBSD Ports
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]> CREATE USER 'replicateme'@'localhost' IDENTIFIED BY 'replicatemepwd';
Query OK, 0 rows affected (0.021 sec)
root@localhost [(none)]>
root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replicateme'@'192.168.1.84' IDENTIFIED BY 'replicatemepwd';
Query OK, 0 rows affected (0.022 sec)
root@localhost [(none)]>
root@localhost [(none)]> SHOW GRANTS FOR 'replicateme'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for replicateme@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `replicateme`@`localhost` IDENTIFIED BY PASSWORD '*9C11336B2C8388535F2F45DB69E2B6F96C6DDD89' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
root@localhost [(none)]>exit
Bye
[albert@FreeBSD-DB1 ~]$
As mentioned above this same must be performed on the Slave/s or replica node/s. In this example there is only one slave node.
With the master configured to act as the primary server and the replica user created on all the nodes in this cluster, we can move on to the next chapter in this how to setup MariaDB master-slave replication guide.
Step 4.- Slave 1 configuration
Similarly to what we’ve done on the Master/Primary node, on the slave node the following configuration needs to be applied
Inside the configuration file ‘/usr/local/etc/mysql/conf.d/server.cnf‘ we’ll add the following settings.
server-id=2
Place the above directive under the ‘[server]’ section.
[albert@FreeBSD-DB2 ~]$ sudo vi /usr/local/etc/mysql/conf.d/server.cnf
……
[server]
server-id=2
……
[albert@FreeBSD-DB2 ~]$
Now that the replica node is ready with the replication user and the proper configuration change it’s time to move onto the next chapter, so we know what is the exact position of both databases and start replicating data.
Step 5.- Get the binary log position (coordinates) for replication
In this chapter of the how to setup MariaDB/MySQL master-slave replication on FreeBSD we now need to do something critical for the success of creating this master-slave cluster.
Before getting down to the command line how MySQL/MariaDB logs events and how this affects replication needs a short explanation.
On every MariaDB/MySQL instance all changes, basically any write event, are logged as an entry on the binary log. Depending on the type of changes the registers are in different formats. Replica/Slave instances are configured to read the logs from their respective Master/Primary nodes and at the same time register and keep track of the changes they make on themselves. Replica nodes receive all the binary log with all the changes made on the Primary node. It is up to the replica which changes to apply on them based on this binary log from the Primary node.
Every replica has a record of the coordinates in the binary log, meaning the file name and the position inside that binary log from the Primary that has been read and processed. This allows more than one Replica node to be connected to the Primary and not to interfere with the tasks on the Primary. This also allows Replica nodes to connect, disconnect and resume operations independently of the Primary node and not affecting how it is working.
Therefore, it is very important to keep track of this coordinates when a MariaDB/MySQL master-slave replication setup is built. We will now take them from the Master/Primary node and then let the Slave/Replica acknowledge them. After that they will both be able to connect and the Replica will absorb data from the Primary node whenever new data or changes are applied to it.
Now, we need to get the last position of the master node. We move on to the master node and issue this next command.
MariaDB [(none)]> SHOW MASTER STATUS;
Before that we’d better lock the database so no writes are made.
root@localhost [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.041 sec)
root@localhost [(none)]>
Now we can check the master node log-bin file and its current position
root@localhost [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000002 | 2888 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
root@localhost [(none)]>
Tip: On a production system it is encouraged to lock the database engine writes so the position does not change. This can be achieved by issuing the following command.
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
To unlock the database engine and get the write capacity we need to issue this command.
MariaDB [(none)]> UNLOCK TABLES;
Back again on the slave node we need to configure the replication from the master.
root@localhost [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.000 sec)
root@localhost [(none)]>
Now the Slave/Replica is stopped we can configure it with the parameters for the host. You can put everything as a one liner:
root@localhost [mysql]> CHANGE MASTER TO MASTER_HOST='192.168.1.83', MASTER_USER='replicateme', MASTER_PASSWORD='replicatemepwd', MASTER_LOG_FILE='master1-bin.000002', MASTER_LOG_POS=2888, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.056 sec)
root@localhost [mysql]>
The first directive, the ‘CHANGE MASTER TO MASTER_HOST='ip_address'‘ sets the Master node not as the localhost but to the real Master/Primary node we want to setup in this cluster. We do also set the replication user with the ‘MASTER_USER’ directive, its password with the ‘MASTER_PASSWORD’ one. Very importantly we set the coordinates we got from the master with the ‘MASTER_LOG_FILE’ and the ‘MASTER_LOG_POS’ directives.
After setting up the configuration bits to read from the Master/Primary node we can start the Slave/Replica node.
root@localhost [(none)]> START SLAVE;
Query OK, 0 rows affected (0.000 sec)
root@localhost [(none)]>
To check if any replication from the master has been performed issue the following command:
MariaDB [(none)]> SHOW SLAVE STATUS\G
One must look for the next two lines in the output. Both must be affirmative to confirm the master-slave replication setup.
Slave_IQ_Running: Yes
Slave_SQL_Running: Yes
This below is the expected output.
root@localhost [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.83
Master_User: replicateme
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master1-bin.000004
Read_Master_Log_Pos: 344
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: master1-bin.000004
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: 344
Relay_Log_Space: 867
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
root@localhost [(none)]>
Step 6.- Testing the master-slave replication
In order to test the master-slave replication we’ll download a set of bulk data from a known resource, import it to the master node and monitor the behavior in the slave node. We should see how data written in the master node is almost instantly replicated in the slave node.
Slave 1
From the system command prompt we can use the watch command to see the transactions between the systems. Firs install gnu-watch. Then use the following command.
root@FreeBSD-DB2# /usr/local/bin/gnu-watch -n1 "mysql classicmodels -e 'show databases;'"
With this you will be able to see in real time any changes made on the master 1 node since replication has been configured for this slave 1 node. In order to apply the above command, you will need the gnu-watch software installed. Do as follows:
root@FreeBSD-DB2:~ # sudo pkg search gnu-watch
gnu-watch-3.3.17 GNU watch command
root@FreeBSD-DB2:~ #
root@FreeBSD-DB2:~ # sudo pkg install -y gnu-watch
......
[1/1] Installing gnu-watch-3.3.17...
[1/1] Extracting gnu-watch-3.3.17: 100%
root@FreeBSD-DB2:~ #
Let’s import now data for the master 1 node. We’ll immediately see it being replicated on the slave node.
Master 1
There’s a site dedicated to MySQL tutorials and information which has kindly published data to pull from and play with. We will add this dataset into the Master/Primary node and check if the replication to the Slave/Replica node works.
First, we will fetch the data on the Primary/Master node.
[albert@FreeBSD-DB1 ~]$ fetch https://www.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip
mysqlsampledatabase.zip 53 kB 2619 kBps 00s
[albert@FreeBSD-DB1 ~]$
We now unzip the content.
[albert@FreeBSD-DB1 ~]$ unzip mysqlsampledatabase.zip
Archive: mysqlsampledatabase.zip
extracting: mysqlsampledatabase.sql
[albert@FreeBSD-DB1 ~]$
Import the resulting ‘.sql’ file into the database. Make sure it’s running.
sudo mysql < mysqlsampledatabase.sql
Sample output:
[albert@FreeBSD-DB1 ~]$ sudo mysql < mysqlsampledatabase.sql
[albert@FreeBSD-DB1 ~]$
Once imported we will set the slave node in a way we can see any changes happening on the master being replicated on the slave.
Slave 1
Now in the Slave/Replica node we should’ve seen how the list of databases has grown with the ‘classimodels’ incorporation.
Every 1.0s: mysql -e 'show databases;' FreeBSD-DB2: Fri Dec 31 18:59:53 2021
Database
classicmodels
information_schema
mysql
performance_schema
Let’s now modify data on the Master 1 node so we can see it replicated on the slave. First, we have to stop the watch command and launch it again with different parameters.
Use this next command, since we will change information in the ‘offices’ table.
/usr/local/bin/gnu-watch -n1 "mysql classicmodels -e 'select * from offices'"
Master 1
Back in the Master/Primary node we now list the databases to see them all. Then we will use the newly uploaded database ‘classicmodels’ and apply a change into it. That change should be automatically replicated to the Slave/Replica node. Look at it carefully!
root@localhost [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| classicmodels |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
root@localhost [(none)]> use classicmodels;
Database changed
root@localhost [classicmodels]>
root@localhost [classicmodels]> UPDATE offices SET city='Londres' WHERE officeCode=7;
Query OK, 1 row affected (0.027 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [classicmodels]>
Notice on the Slave/Replica node you should now read ‘Londres‘ instead of the original ‘London’ on the seventh entry for the officeCode.
Go back to the Master/Primary node again and issue the same command but choosing a different city instead, so you will again read back on the slave 1 node a now different city name. Use ‘Derby‘ for example.
You should see something close to this on the master 1 node:
root@localhost [classicmodels]> UPDATE offices SET city='Derby' WHERE officeCode=7;
Query OK, 1 row affected (0.028 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [classicmodels]>
And you should read something similar to this on the Slave 1 node.
Every 1.0s: mysql classicmodels -e 'select * from offices' FreeBSD-DB2: Fri Dec 31 19:06:17 2021
officeCode city phone addressLine1 addressLine2 state country postalCode territory
1 San Francisco +1 650 219 4782 100 Market Street Suite 300 CA USA 94080NA
2 Boston +1 215 837 0825 1550 Court Place Suite 102 MA USA 02107 NA
3 NYC +1 212 555 3000 523 East 53rd Street apt. 5A NY USA 10022 NA
4 Paris +33 14 723 4404 43 Rue Jouffroy D'abbans NULL NULL France 75017 EMEA
5 Tokyo +81 33 224 5000 4-1 Kioicho NULL Chiyoda-Ku Japan 102-8578 Japan
6 Sydney +61 2 9264 2451 5-11 Wentworth Avenue Floor #2 NULL Australia NSW 2010APAC
7 Derby +44 20 7877 2041 25 Old Broad Street Level 7 NULL UK EC2N 1HNEMEA
At this point we have setup MariaDB master-slave replication on FreeBSD. Any new data or change incorporated in the Master/Primary node is being replicated instantly on the Slave/Replica node.
Conclusion
MariaDB master-slave replication is a great way to always have your data available, unload database servers from many read tasks that can be delivered with other added servers, and have redundancy or even set extra databases as hot backups.
Resources:
Bulk data in the following URL
curl -O https://www.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip
If you find the articles in Adminbyaccident.com useful to you, please consider making a donation.
Use this link to get $200 credit at DigitalOcean and support Adminbyaccident.com costs.
Get $100 credit for free at Vultr using this link and support Adminbyaccident.com costs.
Mind Vultr supports FreeBSD on their VPS offer.