Main menu

MariaDB Database replication in 10 min

There are lot of ways to prepare for disaster and MariaDB database replication is one of them. Database replication provides a way to have multiple copies of databases in different location. It can be use as database real-time backup or scaling out application. In this article we just configure one master and one slave database server replication.

We assume that you already have database server up and running in both systems.

Master server configuration

Open MySQL server configuration file and change the configuration according your environment.

[root@localhost ~]# vi /etc/my.cnf
bind-address      =       192.168.53.19
server-id         =       19
log_bin           =       /var/log/mariadb/mysql-repl.log
binlog_do_db      =       zabbix
  • bind-address: By default you server is bind with local address <127.0.0.1>. For replication purpose you need to bind database server with IP which can be reachable from slave server.
  • server-id: Server id is an unique ID of database server in replication.
  • log_bin: This is where server keep replication information. Slave read those changes form log file and apply then into databse.
  • binlog_do_db: Which database we want to replication. If we want to replicate more then one database we can copy thse parameter multiple times.

Restart the MariaDB Server

[root@localhost ~]# systemctl restart mariadb

Login into your database server and create a user for slave server. We will use this user for replication.

[root@localhost ~]# mysql -u root -p
MariaDB> GRANT REPLICATION SLAVE ON master_db.* TO 'username'@'slave_ip' IDENTIFIED BY 'your_password';
 FLUSH PRIVILEGES;

Make your database Read Only until we get backup of master database.

MariaDB> USE master_db;
MariaDB> FLUSH TABLES WITH READ LOCK;
MariaDB> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    6523  | master_db    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

It will show the position form which master server will start the replication. We will use this number in slave databse to specify start replication from this position.

After taking the database backup we will unlock the database.

MariaDB> UNLOCK TABLES;
MariaDB> QUIT;

Master server configuration is finished.

Slave server configuration

Restore the databse from master server

Open /etc/my.cnf and configure following setting.

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
 server-id = 12
 relay-log = /var/log/mariadb/relay.log
 log-bin = /var/log/mariadb/bin.log
 binlog_do_db = slave_db

Resart MariaDB service to apply these configuraiton.

[root@localhost ~]# systemctl restart mariadb

Login into MariaDB and specify the master server and credentials.

CHANGE MASTER TO MASTER_HOST='master_server_ip',MASTER_USER='user_name', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  position_number;

Activate the slave server.

MariaDB> START SLAVE;

Checking the slave status if is there any issues.

SHOW SLAVE STATUS\G

 

 

te

FacebookTwitterGoogle+RSS