Changes made on the master server are replicated on the slave servers. the replication is asynchronous and automatic. This setup is necessary for situations where you want to scale and 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 any of the other slaves. This brief tutorial shows students and new users how to set up MySQL / MariaDB master and slave replication on Ubuntu 16.04 | 18.04 servers. To get started, follow the steps below:
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 set up two servers. (primary and slave)
Install MariaDB on Both Master / Slave
To install MariaDB on Ubuntu, use the commands below. The setup also works for MySQL servers. To use MySQL instead, change the server’s name below to mysql-server and mysql-client Install MariaDB on both the master and slave servers by running the commands below on each. After installing MariaDB, the commands below can be used to stop, start and enable the MariaDB service to always start up when the server boots. Run these on Ubuntu 16.04 LTS Run these on Ubuntu 18.04 LTS After that, run the commands below to secure the MariaDB server by creating a root password and disallowing remote root access. 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 into the MariaDB server Then type the password you created above to sign on. if successful, you should see MariaDB welcome message
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. Change/add the highlighted line below and save. After making the changes, restart the MariaDB server. After configuring and restarting the master server, log on to it and create a user account that will be used for replication. This account will have a username and password and be used by the slave servers. Run the commands below to log on to the MariaDB master server. 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. Next, run the commands below to grant the replication_user full access to the slave server. Next, run the commands below to show the master server details. It should print out something similar to the content below: Take notes of the File and Position details of the master server. You will need these when configuring the slave server later.
Setup the Slave Server
Now that the master server is set up and configured switch to the slave server and run open its configuration file. Next, change the highlighted lines in the file and save. Restart the slave server. After making the changes above on the slave server, restart the MariaDB server. When you’re done, log on to the slave server. 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: Next, run the following commands to configure the slave to communicate with the master server using the account created for the slave earlier. Remember to use the correct information from above. After running the commands above, run the commands below to start the slave. That should complete the setup. Test by running the SQL commands below: You should see the lines below: If everything is set up correctly, whatever changes are made on the primary master will be replicated to the slave automatically. This is how one setup Master / Slave synchronous replication using the MariaDB server. This should also work with using MySQL servers.
