How to configure master-slave replication in MariaDB

In our previous tutorial, we have learned how to install and configure MariaDB[1], and also learned some basic commands for managing MariaDB [2]. Now let's learn how to configure a master-slave replication on a MariaDB server.

Replication is used to create multiple copies of our database, these copies can be used to run queries on other databases, like some very heavy queries may affect the performance of the main database server, or we can use it to do Data redundancy, or both. We can automate this process, that is, the replication process from the master server to the slave server is automatically performed. Perform backups without affecting writes on the primary server.

So, let’s now configure our master-slave replication, which requires two machines with MariaDB installed. Their IP addresses are as follows:

• Master Server - 192.168.1.120 Host Name - master.ltechlab.com
• Slave server - 192.168.1.130 Host name - slave.ltechlab.com

With MariaDB installed on these machines, we continue with the tutorial. If you need a tutorial on installing and configuring MariaDB, check out this tutorial[3].

We now enter a database named important in MariaDB, which will be copied to our slave server. To start the process, we edit the file named /etc/my.cnf, which is the configuration file for MariaDB.

$ vi /etc/my.cnf

Find the [mysqld] section in this file, and then enter the following content:

[mysqld]
log-bin
server_id=1
replicate-do-db=important
bind-address=192.168.1.120

Save and exit this file. After completion, you need to restart the MariaDB service.

$ systemctl restart mariadb

Next, we log into the Mariadb instance on our main server.

$ mysql -u root -p

Create a new user named slaveuser on it for master-slave replication, and then run the following command to assign it the required permissions:

STOP SLAVE;
GRANT REPLICATION SLAVE ON *.* TO  'slaveuser'@'%' IDENTIFIED BY 'iamslave';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note: We need the values ​​of MASTER_LOG_FILE and MASTER_LOG_POS to configure master-slave replication, which can be obtained by show master status, Therefore, you must make sure you write down their values.

After running these commands, enter exit to exit this session.

Now, we need to create a backup for our database important. You can use the mysqldump command to back up.

$ mysqldump -u root -p important > important_backup.sql

After the backup is complete, we need to log back into the MariaDB database and unlock our tables.

$ mysql -u root -p
$ UNLOCK TABLES;

Then exit this session. Now, we move our backup to the slave server, whose IP address is: 192.168.1.130.

The configuration on the master server has been completed. Now, we start configuring the slave server.

我们再次去编辑（从服务器上的） /etc/my.cnf 文件，找到配置文件中的 [mysqld] 节，然后输入如下内容：

[mysqld]
server-id = 2
replicate-do-db=important
[ …]

现在，我们恢复我们主数据库的备份到从服务器的 MariaDB 上，运行如下命令：

$ mysql -u root -p < /data/ important_backup.sql

当这个恢复过程结束之后，我们将通过登入到从服务器上的 MariaDB，为数据库 important 上的用户 'slaveuser' 授权。

$ mysql -u root -p

GRANT ALL PRIVILEGES ON important.* TO 'slaveuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

接下来，为了这个变化生效，重启 MariaDB。

$ systemctl restart mariadb

记住，我们需要 MASTER_LOG_FILE 和 MASTER_LOG_POS 变量的值，它可以通过在主服务器上运行 SHOW MASTER STATUS 获得。现在登入到从服务器上的 MariaDB，然后通过运行下列命令，告诉我们的从服务器它应该去哪里找主服务器。

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST= '192.168.1.110′, MASTER_USER='slaveuser', MASTER_PASSWORD='iamslave', MASTER_LOG_FILE='mariadb-bin.000001′, MASTER_LOG_POS=460;
SLAVE START;
SHOW SLAVE STATUS\G;

注意： 请根据你的机器的具体情况来改变主服务器的配置。

我们将在我们的主服务器上创建一个新表来测试主从复制是否正常工作。因此，登入到主服务器上的 MariaDB。

$ mysql -u root -p

选择数据库为 important：

use important;

在这个数据库上创建一个名为 test 的表：

create table test (c int);

然后在这个表中插入一些数据：

insert into test (c) value (1);

检索刚才插入的值是否存在：

select * from test;

你将会看到刚才你插入的值已经在这个新建的表中了。

现在，我们登入到从服务器的数据库中，查看主从复制是否正常工作。

$ mysql -u root -p
$ use important;
$ select * from test;

你可以看到与前面在主服务器上的命令输出是一样的。因此，说明我们的主从服务工作正常，没有发生任何问题。

我们的教程结束了，请在下面的评论框中留下你的查询/问题。

The above is the detailed content of How to configure master-slave replication in MariaDB. For more information, please follow other related articles on the PHP Chinese website!