Specification
Linux1: Ubuntu 18.04 LTS
IP: 10.1.1.10
Linux2: Ubuntu 18.04 LTS
IP: 10.1.1.20
MariaDB: 10.1.48-MariaDB
Install MariaDB
sudo apt update
sudo apt-get install software-properties-common
sudo apt-get install mariadb-server
Stop MariaDB
sudo systemctl stop mariadb.service
Edit /etc/mysql/my.cnf parameter file.
sudo vim.tiny /etc/mysql/my.cnf
linux1 (10.1.1.10)
[mysqld]
bind-address = 10.1.1.10
server_id = 10
report_host = linux1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
replicate-do-db = testdb
auto_increment_increment = 5
auto_increment_offset = 1
linux2 (10.1.1.20)
[mysqld]
bind-address = 10.1.1.20
server_id = 20
report_host = linux2
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
replicate-do-db = testdb
auto_increment_increment = 5
auto_increment_offset = 1
Note: replicate-do-db to specify which databases to replicate (optional)
Start MariaDB
sudo systemctl start mariadb.service
Create user in both server that will be used for replicating data between servers and grant privileges.
sudo mysql -u root -p
MariaDB [(none)]> create user 'replusr'@'%' identified by 'replusr';
MariaDB [(none)]> grant replication slave on *.* to 'replusr'@'%';
Verify listener bind-address and port
e.g.: in linux1
sudo netstat -ntpl | grep mysql
tcp 0 0 10.1.1.10:3306 0.0.0.0:* LISTEN 1895/mysqld
Start replication in linux1
sudo mysql -u root -p
For MASTER_LOG_FILE and MASTER_LOG_POS use "show master status" information from in linux2.
Show master status linux2
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000004 | 329 | | |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.1.20', MASTER_USER='replusr', MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=329;
MariaDB [(none)]> START SLAVE;
Check slave status in linux1
MariaDB [(none)]> SHOW SLAVE STATUS\G;
====================
Start replication in linux2
For MASTER_LOG_FILE and MASTER_LOG_POS use "show master status" information from in linux1.
Show master status linux1
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000007 | 329 | | |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.1.10', MASTER_USER='replusr', MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=329;
MariaDB [(none)]> START SLAVE;
Check slave status in linux2
MariaDB [(none)]> SHOW SLAVE STATUS\G;
If everything is OK, it should shown as below
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.20
Master_User: replusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000004
Read_Master_Log_Pos: 329
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 539
Relay_Master_Log_File: mariadb-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 329
Relay_Log_Space: 831
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 20
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
Test Replication
linux1
CREATE DATABASE testdb;
MariaDB [(none)]> create database testdb;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
Create table in testdb
CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(30), datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id));
INSERT INTO users(name) VALUES ('Admin');
MariaDB [testdb]> CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(30), datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id));
Query OK, 0 rows affected (0.07 sec)
MariaDB [testdb]> INSERT INTO users(name) VALUES ('Admin');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum |
+----+-------+---------------------+
| 1 | Admin | 2021-12-26 06:01:31 |
+----+-------+---------------------+
1 row in set (0.00 sec)
If we check on linux2 it should shown the same result and we can also test the same thing on linux2 by add another user on table users
linux2
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
4 rows in set (0.01 sec)
MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [testdb]> INSERT INTO users(name) VALUES ('Guest');
Query OK, 1 row affected (0.01 sec)
linux1
New user Guest that we have added in linux2 replicated in linux1.
MariaDB [testdb]> select * from users;
+----+-------+---------------------+
| id | name | datum |
+----+-------+---------------------+
| 1 | Admin | 2021-12-26 06:01:31 |
| 6 | Guest | 2021-12-26 06:03:23 |
+----+-------+---------------------+
Ref:
http://msutic.blogspot.com/2015/02/mariadbmysql-master-master-replication.html
https://linoxide.com/5-steps-setup-mysql-master-master-replication-ubuntu-16-04/
0 comments:
Post a Comment