How to Setup MariaDB Master and Slave Replication on Ubuntu 16.04

Requirements

Launch Alibaba Cloud ECS Instance

apt-get update -y

Install MariaDB

apt-get install mariadb-server -y
systemctl start mysql
systemctl enable mysql
mysql_secure_installation
Set root password? [Y/n] n
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

Configure Master Node

nano /etc/mysql/my.cnf
[mysqld]
bind-address = 192.168.0.101
server_id=1
log-basename=master
log-bin=/var/log/mysql/mariadb-bin
binlog-format=row
binlog-do-db=masterdb
systemctl restart mysql
mysql -u root -p
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 615 | masterdb | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit;
mysqldump --all-databases --user=root --password --master-data > alldatabase.sql
scp alldatabase.sql root@192.168.0.102:/root/
mysql -u root -pMariaDB [(none)]> UNLOCK TABLES; 
MariaDB [(none)]> exit;

Configure Slave Server

nano /etc/mysql/my.cnf
[mysqld]
bind-address = 192.168.0.102
server-id = 2
replicate-do-db=masterdb
systemctl restart mysql
mysql -u root -p < alldatabase.sql
mysql -u root -p
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=615;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.20.10.6
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 615
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: masterdb

Test Replication

mysql -u root -p
MariaDB [(none)]> create database masterdb;
MariaDB [(none)]> use masterdb;
MariaDB [masterdb]> create table mastertable (c int);
MariaDB [masterdb]> insert into mastertable (c) values (1);
MariaDB [masterdb]> select * from mastertable;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql -u root -p
MariaDB [(none)]> use masterdb;
MariaDB [masterdb]> select * from mastertable;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Related Alibaba Cloud Products

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store