loading...

Ubuntu Server 18.04 – Setting up a slave database server

How to install Ubuntu Server 19.10

Earlier in this chapter, we discussed installing MariaDB on a server. To set up a slave, all you really need to begin the process is set up another database server. If you’ve already set up two database servers, you’re ready to begin. If not, feel free to spin up another VM and follow the process from earlier in this chapter that covered installing MariaDB. Go ahead and set up another server if you haven’t already done so. Of your two servers, one should be designated as the master and the other the slave, so make a note of the IP addresses for each.

To begin, we’ll first start working on the master. We’ll need to edit the /etc/mysql/conf.d/mysql.cnf file. Currently, the file contains just the following line:

[mysql] 

Right underneath that, add a blank line and then the following code:

[mysqld] 
log-bin 
binlog-do-db=mysampledb 
server-id=1 

With this configuration, we’re first enabling bin logging, which is required for a master/slave server to function properly. These binary logs record changes made to a database, which will then be transferred to a slave.

Another configuration file that we’ll need to edit is /etc/mysql/mariadb.conf.d/50-server.cnf. In this file, we have the following line:

bind-address = 127.0.0.1 

With this default setting, the mysql daemon is only listening for connections on localhost (127.0.0.1), which is a problem since we’ll need to connect to it from another machine (the slave). Change this line to the following:

bind-address = 0.0.0.0 

Next, we’ll need to access the MariaDB shell on the master and execute the following commands:

GRANT REPLICATION SLAVE ON *.* to 'replicate'@'192.168.1.204' identified by 'slavepassword'; 

Here, we’re creating a replication user named replicate and allowing it to connect to our primary server from the IP address 192.168.1.204. Be sure to change that IP to match the IP of your slave, but you can also use a hostname identifier such as %.mydomain if you have a domain configured, which is equivalent to allowing any hostname that ends with .mydomain. Also, we’re setting the password for this user to slavepassword, so feel free to customize that as well to fit your password requirements (be sure to make a note of the password).

We should now restart the mariadb daemon so that the changes we’ve made to the mysql.cnf file take effect:

sudo systemctl restart mariadb 

Next, we’ll set up the slave server. But before we do that, there’s a consideration to make now that will possibly make the process easier on us. In a production environment, it’s very possible that data is still being written to the master server. The process of setting up a slave is much easier if we don’t have to worry about the master database changing while we set up the slave. The following command, when executed within the MariaDB shell, will lock the database and prevent additional changes:

FLUSH TABLES WITH READ LOCK; 
If you’re absolutely sure that no data is going to be written to the master, you can disregard that step.

Next, we should utilize mysqldump to make both the master and the slave contain the same data before we start synchronizing them. The process is smoother if we begin with them already synchronized, rather than trying to mirror the databases later. Using mysqldump as we did in the previous section, create a dump of the master server’s database and then import that dump into the slave. The easiest way to transfer the dump file is to use rsync or scp. Then, on the slave, use mariadb to import the file.

The command to back up the database on the master becomes the following:

mysqldump -u admin -p --databases mysampledb > mysampledb.sql 

After transferring the mysampledb.sql file to the slave, you can import the backup into the slave server:

mariadb -u root -p < mysampledb.sql 

Back on the slave, we’ll need to edit the /etc/mysql/conf.d/mysql.cnf and then place the following code at the end (make sure to add a blank line after [mysql]:

[mysqld] 
server-id=2 
Although it’s outside the scope of this tutorial, you can set up multiple slaves. If you do, each will need a unique server-id.

Make sure you restart the mariadb unit on the slave before continuing:

sudo systemctl restart mariadb 

From the root MariaDB shell on your slave, enter the following command. Change the IP address in the command accordingly:

CHANGE MASTER TO MASTER_HOST="192.168.1.184", MASTER_USER='replicate', MASTER_PASSWORD='slavepassword'; 

Now that we’re finished configuring the synchronization, we can unlock the master’s tables. On the master server, execute the following command within the MariaDB shell:

UNLOCK TABLES; 

Now, we can check the status of the slave to see whether or not it is running.
Within the slave’s MariaDB shell, execute the following command:

SHOW SLAVE STATUSG; 
Here, we’re adding G, which changes the output to be displayed vertically instead of horizontally.

Assuming all went well, we should see the following line in the output:

Slave_IO_State: Waiting for master to send event 

If the slave isn’t running (Slave_IO_State is blank), execute the following command:

START SLAVE; 

Next, check the status of the slave again to verify:

SHOW SLAVE STATUSG;

From this point forward, any data you add to your database on the master should be replicated to the slave. To test, add a new record to the Employees table on the mysampledb database on the master server:

USE mysampledb; 
INSERT INTO Employees VALUES ('Optimus Prime', '100', 'Transformer'); 

On the slave, check the same database and table for the new value to appear. It may take a second or two:

USE mysampledb; 
SELECT * FROM Employees; 

If you see any errors in the Slave_IO_State line when you run SHOW SLAVE STATUSG; or your databases aren’t synchronizing properly, here are a few things you can try. First, make sure that the database master is listening for connections on 0.0.0.0 port 3306. To test, run this variation of the netstat command to see which port the mariadb process is listening on (it’s listed as mysqld):

sudo netstat -tulpn |grep mysql 

The output should be similar to the following:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      946/mysqld 

If you see that the service is listening on 127.0.0.1:3306 instead, that means it’s only accepting connections from localhost. Earlier in this section, I mentioned changing the bind address in the /etc/mysql/mariadb.conf.d/50-server.cnf file. Make sure you’ve already done that and restart mariadb. During my tests, I’ve actually had one situation where the mariadb service became locked after I made this change and attempting to restart the process did nothing (I ended up having to reboot the entire server, which is not typically something you’d have to do). Once the server came back up, it was listening for connections from the network.

If you receive errors on the slave when you run SHOW SLAVE STATUSG; with regards to authentication, make sure you’ve run FLUSH PRIVILEGES; on the master. Even if you have, run it again to be sure. Also, double check that you’re synchronizing with the correct username, IP address, and password. For your convenience, here’s the command we ran on the master to grant replication permissions:

GRANT REPLICATION SLAVE ON *.* to 'replicate'@'192.168.1.204' identified by 'slavepassword'; 
FLUSH PRIVILEGES 

Here’s the command that we ran on the slave:

CHANGE MASTER TO MASTER_HOST="192.168.1.184", MASTER_USER='replicate', MASTER_PASSWORD='slavepassword'; 

Finally, make sure that your master database and the slave database both contain the same databases and tables. The master won’t be able to update a database on the slave if it doesn’t exist there. Flip back to my example usage on mysqldump if you need a refresher. You should only need to use mysqldump and import the database onto the slave once, since after you get the replication going, any changes made to the database on the master should follow over to the slave. If you have any difficulty with the mysqldump command, you can manually create the mysampledb and the Employees table on the slave, which is really all it needs for synchronization to start.

Synchronization should then begin within a minute, but you can execute STOP SLAVE; followed by START SLAVE; on the slave server to force it to try to synchronize again without waiting.

And that should be all there is to it. At this point, you should have fully functional master and slave servers at your disposal. To get additional practice, try adding additional databases, tables, users, and insert new rows into your databases. It’s worth mentioning that the users we’ve created here will not be synced to the slave, so you can use the commands we’ve used earlier in this chapter to create users on the slave server if you wish for them to be present there.

Comments are closed.

loading...