For security and fault tolerance reasons, setting up MySQL / MariaDB master and slave replication is a way to go. In a master and slave setup, one database server serves are the primary or master and the other(s) as slave in this topology.
Changes made on the master server are replicated to the slave servers. the replication is asynchronous and automatic.
This setup is necessary in situations where you want to scale, provide live backups for disaster recovery and fault-tolerance.
Each replication slave must have a unique server ID. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from any of the other slaves.
This brief tutorial shows students and new users how to setup MySQL / MariaDB master and slave replication on Ubuntu 16.04 | 18.04 servers.To get started, follow the steps below:
Step 0: Server Preparation
For replication to work, you’ll need two or more servers. One server will serve as the primary and the others as slaves. For this tutorial, we’ll setup two servers. (primary and slave)
Primary = 192.168.1.1 Slave = 192.168.1.2
Step 1: Install MariaDB on Both Master / Slave
To install MariaDB on Ubuntu, use the commands below. The setup also works for MySQL server. To use MySQL instead, change the server name below to mysql-server and mysql-client
Install MariaDB on both master and slave server by running the commands below on each.
sudo apt update sudo apt-get install mariadb-server mariadb-client
After installing MariaDB, the commands below can be used to stop, start and enable MariaDB service to always start up when the server boots.
Run these on Ubuntu 16.04 LTS
sudo systemctl stop mysql.service sudo systemctl start mysql.service sudo systemctl enable mysql.service
Run these on Ubuntu 18.04 LTS
sudo systemctl stop mariadb.service sudo systemctl start mariadb.service sudo systemctl enable mariadb.service
After that, run the commands below to secure MariaDB server by creating a root password and disallowing remote root access.
sudo mysql_secure_installation
When prompted, answer the questions below by following the guide.
- Enter current password for root (enter for none): Just press the Enter
- Set root password? [Y/n]: Y
- New password: Enter password
- Re-enter new password: Repeat password
- Remove anonymous users? [Y/n]: Y
- Disallow root login remotely? [Y/n]: Y
- Remove test database and access to it? [Y/n]: Y
- Reload privilege tables now? [Y/n]: Y
Restart MariaDB server
To test if MariaDB is installed, type the commands below to logon to MariaDB server
sudo mysql -u root -p
Then type the password you created above to sign on. if successful, you should see MariaDB welcome message
Step 3: Configure MariaDB Master Server
First, we want to create our master server in this topology. This is the primary server and all changes on this server will be replicated onto the slave(s).
To configure the master server, open the configuration file below and make the highlighted changes. then save the file and exit.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Change / add the highlighted line below and save.
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. log-bin bind-address = 192.168.1.1 server_id = 1 log-basename = master1
After making the changes, restart MariaDB server.
sudo systemctl restart mariadb.service sudo systemctl restart mysql.service
After configuring and restarting the master server, logon to it and create a user account that will be used for replication. These account will have a username and password and use by the slave servers.
Run the commands below to logon to MariaDB master server.
sudo mysql -u root -p
After logging on, run the commands below to create a new account for replication. this account name will be replication_user with a new password.
CREATE USER 'replication_user'@'192.168.1.2' IDENTIFIED BY 'new_password_here';
Next, run the commands below to grant the replication_user full access to the slave server.
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.2';
Next, run the commands below to show the master server details.
SHOW MASTER STATUS;
It should print out something similar to the content below:
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000001 | 315 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
Take notes of the File and Position details of the master server. You will need these when configuring the slave server later.
Step 4: Setup the Slave Server
Now that the master server is setup and configured switch to the slave server and run open its configuration file.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Next, change the highlighted lines in the file and save.
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
log-bin
bind-address = 192.168.1.2
server_id = 2
Restart the slave server.
After making the changes above on the slave server, restart MariaDB server.
sudo systemctl restart mariadb.service sudo systemctl restart mysql.service
When you’re done, logon to the slave server.
sudo mysql -u root -p
We want to configure the slave server to communicate with the primary server. To do that, stop the slave server by running the commands below:
STOP SLAVE;
Next, run the following commands to configure the slave to communication with the master server using the account created for the slave earlier.
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication_user', MASTER_PASSWORD='type_replication_user_password', MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=315;
Remember to use the correct information from above. After running the commands above, run the commands below to start the slave.
START SLAVE;
That should complete the setup.
Test by running the SQL commands below:
SHOW SLAVE STATUS \G
You should see the lines below:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
If everything is set up correctly, whatever change made on the primary master will be replicated to the slave automatically.
This is how one setup Master / Slave synchronous replication using MariaDB server. This should also work with using MySQL servers.
You may also like the post below: