Introduction
MariaDB replication setup is an essential process for database administrators aiming to ensure data consistency and availability. Setting up replication allows data from one database server, known as the master, to be copied automatically to one or more servers, known as slaves. This guide will walk you through the process of setting up MariaDB replication on an Ubuntu 22.04 system. The following guide will detail each step necessary to ensure a successful MariaDB replication setup. Whether you’re setting it up for backup, scaling, or redundancy, the steps provided will help achieve a smooth replication process.
Prerequisites
Before starting the MariaDB replication setup, ensure that you have the following:
- Two or more servers running Ubuntu 22.04
- MariaDB installed on each server
- SSH access to each server
- Basic understanding of MySQL or MariaDB commands
- Network connectivity between the servers.
Step 1: Install MariaDB
First, ensure MariaDB is installed on your system. Use the following command to install MariaDB:
sudo apt update
sudo apt install mariadb-server
This command updates your package list and installs the MariaDB server package on your Ubuntu system.
Step 2: Secure MariaDB
Once installed, secure the MariaDB installation. Execute the following command:
sudo mysql_secure_installation
This interactive script will help you set a root password, remove anonymous users, disallow root login remotely, and remove test databases.
Step 3: Configure the Master Server
Next, configure the master server. Edit the MariaDB configuration file on the master server to enable binary logging and set a server ID. Open the configuration file with:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Add or modify the following lines under the [mysqld] section:
server-id=1
log_bin=master-bin
binlog_do_db=your_database_name
The server-id must be unique for each server. log_bin enables binary logging, and binlog_do_db specifies which database to replicate.
Step 4: Create a Replication User
Create a dedicated user for replication. Log in to the MariaDB shell and run:
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
Replace 'password' with a strong password. This user will be used by the slave to connect to the master for replication.
Step 5: Obtain the Master Status
Before configuring the slave server, obtain the binary log coordinates from the master server. In the MariaDB shell, execute:
SHOW MASTER STATUS;
Note down the File and Position values. These coordinates are crucial for starting the replication on the slave server.
Step 6: Configure the Slave Server
Now, move on to configuring the slave server. On the slave server, edit the configuration file to set a unique server ID and update the relay log:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Add or modify the following lines:
server-id=2
relay_log=slave-relay-bin
The server-id must be unique and different from the master server’s ID.
Step 7: Start the Slave Process
Log in to the MariaDB shell on the slave server and configure it to start replicating from the master. Execute the following command:
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replicator',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=position;
Replace master_ip, 'password', master-bin.000001, and position with the relevant values you noted earlier from the master server.
Step 8: Start the Slave
Finally, start the slave process with:
START SLAVE;
To confirm that replication is working, check the status with:
SHOW SLAVE STATUS\G
Ensure that there are no errors and that the slave is connected to the master.
Conclusion
Setting up a MariaDB replication involves configuring both master and slave servers properly. This ensures that data flows smoothly and consistently from the master to the slave, maintaining data integrity and availability across your database systems.
