MariaDB replication setup on CENTOS Linux

It is nice to think that somehow we have fault-tolerance in our database even we only have two servers running on our network. It lessens our worries that in any point of time we do not know when the server crashes and leave us problems in restoring data from the backup. So we need duplicates of our data or something that copies our data from one server to another as a real-time backup which also increases performance and improves reliability and availability. It requires sharing information between resources but it differs from clustering.

So here's what we called Replication, that we will be going to apply on a CENTOS machine. Basically, we only need at least two servers, the Master, and a Slave.

But in the real world application, replication can have one Master and multiple Slaves.

Some of the benefits of replication are the following:

• Scale-Out Solutions
• Spread load among multiple slaves
• All write and updates must occur on master
• Reads can occur on any slave
• Data Security
• Backups can be performed on slaves without affecting master (Ability to save data)
• Analytics
• Data analysis can be performed on any slave without affecting speed of master or other slaves
• Long-Distance Data Distribution
• Allows branch offices to have dedicated, offline server

But for this blog post, I will only configure a Master and a Slave setup.

On the Master server

1.) Connect to the server's ssh using putty

2.) Then vi/nano to /etc/my.cnf
#nano /etc/my.cnf

3.) Append the text below under [mysqld]:
#Binary Logging (For Replication)
log-basename=master
log-bin
binlog-format=row

4.) Then restart mariadb service
#system restart mariadb

5.) Then open MySQL Workbench, and connect to the Master server. And create a replication user then make sure your password you will set must not be longer than 32 characters in length and also not contains a "#" sign same for the user. Follow the sequence below:

6.) Then lock all tables to prevent changes

• Issue the following SQL query on the Master server using MySQL Workbench
• FLUSH TABLES WITH READ LOCK;
• Won't let you update tables
• Does not clear tables, but clears cache
• Lock remains in effect until session is closed
• View the current coordinates to the binary log on the master server
• SHOW MASTER STATUS

7.) Then set global server_id by issuing SQL query again:

set global server_id = 1;

8.) You may check and see the server_id:

show variables where variable_name = 'server_id';

On the Slave server

9.) Then connect to the SQL of your Slave server using MySQL Workbench

set global server_id = 2;

10.) Check server_id of Slave

show variables where variable_name = "server_id";

11.) Make sure slave is stopped first by issuing SQL query to the Slave server

slave stop;

11.) Then issue the following SQL query to configure slave:

change master to

master_host = '[IP address of the master] ',

master_user = '[Replication user]',

master_password = '[The password of the replication user]',

master_log_file = '[ can be seen at /var/lib/mysql (eg. mariadb-bin.000001) and by using "SHOW MASTER STATUS" at Master server SQL query]',

master_log_pos = [Log Position shown at Master server using "SHOW MASTER STATUS"(eg. 2221)],

master_port = 3306,

master_connect_retry = 10;

12.) Then start slave by issuing SQL query to the Slave server

slave start;

13.) Then check the slave status. You may found errors if there's any.

show slave status;

14.) Append to /etc/my.cnf of the Slave server
[mysqld]
server_id=2

Troubleshooting

• Be sure that the firewall on the master allows communication
• #firewall-cmd --list-ports
• TCP Port 3306
• Ping the master from the slave
• Telnet from the slave to the master on port 3306
• Verify binary logging has different server-id
• Verify user account has remote privileges and is configured for replication in "Global Privileges"

14.) There you go, you may see if replication is working, update a record on your Master server and check if the same database record on the Slave server copies the exact data.

Referrences:

mariadb.org

https://linoxide.com/how-tos/configure-mariadb-replication-centos-linux/

https://www.unixmen.com/setup-mariadb-master-slave-replication-in-centos-7/

https://www.youtube.com/channel/UCjCs0BMeJUOLs8j79BP-4Uw