MySQL Master Master Replication. Server replication is one way that allows data loss prevention. There are several methods or different ways of doing replication and the right method depends on our way and our needs. Replication is a process where a set of databases are stored in a MySQL database which will later be copied to the MySQL Replication Server. Here we will do master-master replication allowing data to be copied in its entirety from one server to another.
Precondition ( MySQL Master Master Replication )
Creating MySQL Master Master Replication requires the following conditions:
- Two MySQL Server for MySQL Master Master Replication
- OS used Debian 11
- The database that our friend is using is MySQL 8.0 (Repo MySQL 8)
For mysql 8 installation, you can check Install Mysql 8 Debian
In this documentation we simulate using 2 active MySQL Servers,
Server A : 192.168.10.108
Server B : 192.168.10.109
Step 1 – Install and Configure MySQL Server (MySQL Master Master Replication)
Next steps for MySQL Master Master Replication. To follow this documentation, you must have a MySQL server installed on the server. Here’s the MySQL Install Documentation. Log in to the server and follow these commands, For basic mysql 8 commands, you can check at Install Mysql 8 Debian
Update the server first
apt update && apt upgrade -y
Then install MySQL Server and MySQL Client
apt-get install mysql-server mysql-client
Next steps for MySQL Master Master Replication.Wait for the download and install MySQL to finish. When it’s finished, change the MySQL Configuration on the Connection. The default MySQL configuration only accepts localhost (127.0.0.1) connections. You need to change the configuration so that MySQL Replication can connect to MySQL A. To do this configuration you need to edit the file /etc/mysql/mysql.conf.d/mysqld.cnf
nano /etc/mysql/mysql.conf.d/mysqld.cnf
If in the above configuration there is a bind-address setting, then the message is #. follow the following configuration
... default-authentication-plugin=mysql_native_password server-id=1 log-bin="mysql-bin" binlog-ignore-db=test binlog-ignore-db=information_schema replicate-ignore-db=test replicate-ignore-db=information_schema relay-log="mysql-relay-log" auto-increment-increment = 2 auto-increment-offset = 1 ....
Information :
server-id : to uniquely identify the server
log_bin : log storage location
binlog_do_db : the name of the database you want to replicate
bind-address: address or IP address that can accept connections, here you can put a #
if everything is correct, friends, restart the MySQL service
systemctl restart mysql
confirm MySQL status is running properly
systemctl status mysql
Adding User for MySQL A
To add a MySQL user, you must be logged into the MySQL root, and also create permissions for that user, using the following command
mysql -u root -p
Then add the MySQL user and user permissions
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
If successful, it will appear in the MySQL user list, use the following command to view the user
select user, host from mysql.user;
Output
+------------------+-----------+ | user | host | +------------------+-----------+ | replicator | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 5 rows in set (0.00 sec)
That’s the configuration that must be done in the MySQL A configuration, now we see the configuration status, which we will later use on the MySQL B server.
Use the following command to view the status of MySQL A
show master status;Output
+------------------+----------+--------------+-------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------+-------------------+ | mysql-bin.000002 | 1067 | | test,information_schema | | +------------------+----------+--------------+-------------------------+-------------------+ 1 row in set (0.00 sec)
Make sure you remember or record the position information that will be used in the next step, which is MySQL B.
Step 2 – Install and Configure MySQL Server (MySQL Master Master Replication)
For MySQL B the same as the previous configuration, which is different in the MySQL configuration.
apt udpate && apt upgrade -y apt install mysql-server && apt install mysql-client
Then edit the file /etc/mysql/mysql.conf.d/mysqld.cnf follow the following configuration:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
... default-authentication-plugin=mysql_native_password server-id=2 log-bin="mysql-bin" binlog-ignore-db=test binlog-ignore-db=information_schema replicate-ignore-db=test replicate-ignore-db=information_schema relay-log="mysql-relay-log" auto-increment-increment = 2 auto-increment-offset = 2 ...
Don’t forget to restart the MySQL service and confirm the MySQL status
systemctl restart mysql systemctl status mysql
* mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2021-10-21 15:40:52 UTC; 1s ago
Process: 17220 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 17228 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 153976)
Memory: 352.7M
CPU: 758ms
CGroup: /system.slice/mysql.service
`-17228 /usr/sbin/mysqld
Oct 21 15:40:52 MYSQL-B systemd[1]: Starting MySQL Community Server...
Oct 21 15:40:52 MYSQL-B systemd[1]: Started MySQL Community Server.
Adding User For MySQL B
Similar to the previous step, create a user for MySQL B. Go to the root of MySQL B
mysql -u root -p
Then create a user for MySQL B
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' ;
Check position information in MySQL B
show master status;
+------------------+----------+--------------+-------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------+-------------------+ | mysql-bin.000002 | 872 | | test,information_schema | | +------------------+----------+--------------+-------------------------+-------------------+ 1 row in set (0.00 sec)
Note and remember the Position of MySQL which is “872”
Step 3 – Transmitting Information Between MySQL (MySQL Master Master Replication)
The next step is to send information between MySQL, MySQL Information A to B and MySQL B to A. Follow the following command, remember the wrong way to send data, friends, you must be careful, especially on Position.
– Sending information from MySQL B to A
Enter MySQL A and confirm the Status and run the following command.
show master status;
+------------------+----------+--------------+-------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------+-------------------+ | mysql-bin.000002 | 1067 | | test,information_schema | | +------------------+----------+--------------+-------------------------+-------------------+ 1 row in set (0.00 sec)
And log into MySQL B.
STOP SLAVE; CHANGE MASTER TO MASTER_HOST = '192.168.10.108', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 1067; START SLAVE;
Note: The MASTER_LOG_FILE and MASTER_LOG_POS values may be different for each server, so friends must be careful and thorough.
– Sending Data from MySQL A to B
Log back into MySQL B and confirm the Status and follow the following command.
show master status;
+------------------+----------+--------------+-------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------+-------------------+ | mysql-bin.000002 | 872 | | test,information_schema | | +------------------+----------+--------------+-------------------------+-------------------+ 1 row in set (0.00 sec)
Then Enter MySQL A, follow the following command.
STOP SLAVE; CHANGE MASTER TO MASTER_HOST = '192.168.10.109', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 872; START SLAVE;
If friends – friends are correct and confident with the configuration above, we can confirm the Slave Status, if the configuration is correct and sent successfully then, the display will look like the display below:
Use the following command to view Slave Status
SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.20.2.108
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1067
Relay_Log_File: mysql-relay-log.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,information_schema
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: 1067
Relay_Log_Space: 533
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Step 4 – Testing Creating a Database (MySQL Master Master Replication)
Creating a Database in MySQL A
If you have successfully made it to Step 3, now we will try to create a Database. We will try to create a database on MySQL Server A, whether the database will also be duplicated to MySQL Server B, follow the following command to perform the test.
Login to MySQL A
create database dixmata_db;
Then go to MySQL B and then confirm whether the database that was created in MySQL A is also in MySQL B.
Login to MySQL B
show databases;
+--------------------+ | Database | +--------------------+ | dixmata_db | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
In the above test, it can be seen that the database was successfully applied using the MySLQ Master Master Replication method
Membuat Database di MySQL B
Now we will try the opposite, which is to create a Database on MySQL B.
create database dixmata_mysql_B;
Then check on MySQL A
show databases;
+--------------------+ | Database | +--------------------+ | dixmata_db | | dixmata_mysql_B | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec)
When viewed from the MySQL Master Master Replication test above, we have succeeded in making a duplication between MySQL A to MySQL B and vice versa. For further testing of the MySQL Master Replication Master, we will try to test turn off one of the databases, then create a table, whether the table also managed to enter the database that was turned off, whether the database is UP.
Testing Turning Off Database Server A and Creating Tables
Next step for MySQL Master Master Replication, we will test turn off one of the database servers, namely MySQL A and create a table on MySQL B.
Shut down MySQL Server A first and create a table in MySQL B, follow the following command to create a table.
The table that we will create in the “dixmata_db” database.
use dixmata_db;
CREATE TABLE Buku
-> (
-> Nim int,
-> Nama varchar (20),
-> Kode_Jurusan char(10),
-> Alamat text
-> );
Query OK, 0 rows affected (0.03 sec)
DESCRIBE Buku; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | Nim | int | YES | | NULL | | | Nama | varchar(20) | YES | | NULL | | | Kode_Jurusan | char(10) | YES | | NULL | | | Alamat | text | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
Restart MYSQL Server A and see if the table that we created in MySQL B in Database “dixmata_db” , also exists in MySQL A.
Login to MYSQL View tables in Database “dixmata db”
show tables; +----------------------+ | Tables_in_dixmata_db | +----------------------+ | Buku | +----------------------+ 1 row in set (0.00 sec)
DESCRIBE Buku; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | Nim | int | YES | | NULL | | | Nama | varchar(20) | YES | | NULL | | | Kode_Jurusan | char(10) | YES | | NULL | | | Alamat | text | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
It turned out that after the test was done, turning off one of the servers and creating a table, the data would still go to the server if the server was UP again. So by using this method, the data will be preserved and will remain safe in the event of a crash or server crash.
That’s all the documentation we share about MySQL Master Master Replication, suggestions if something goes wrong or does not replicate successfully, look back at the database log, friends will find out where the error is. In this MySQL Master Replication, care needs to be taken in the configuration in Step 3, especially in the File Name, Position, and IP Address. If you have any doubts, you can directly email us or leave a message in the comments column.
