MySQL Master-Master Replication
For this tutorial we will install 2 debian LXC Containers on Proxmox:
Instead of setting up another one we will just clone our first LXC and edit it:
So right now we have 2 debian hosts that will run our MySQL databases, the first one is at 10.0.0.161 and the second one is at 10.0.0.162. After we start them, we will enable ssh root login:
nano /etc/ssh/sshd_config
PermitRootLogin yes
CTRL+S CTRL+X
systemctl restart sshd
Do it on both debian hosts, and then ssh into them:
If for some reason you have the following error:
Then set the 'nested' option from inside proxmox:
Then reboot and you get the following result:
Now once that's done we set the hostname of our second debian host correctly:
root@debian-mysql-1:~# hostname debian-mysql-2
root@debian-mysql-1:~# bash
root@debian-mysql-2:~#
Now run mysql's secure installation utility:
And now we're ready to configure MySQL.
Let's configure the first Master Server (10.0.0.161):
root@debian-mysql-1:~# ip a | grep inet
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
inet 10.0.0.161/16 brd 10.0.255.255 scope global eth0
inet6 fe80::9c46:fdff:fe85:2a8a/64 scope link
root@debian-mysql-1:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf
[...]
bind-address = 10.0.0.161 #not 127.0.0.1
[...]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
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
:wq to save and quiti, then reload mysqld since we changed the config:
root@debian-mysql-1:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf
root@debian-mysql-1:~# systemctl restart mysqld
root@debian-mysql-1:~# systemctl status mysqld
* mariadb.service - MariaDB 10.3.27 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-03-27 18:51:40 UTC; 5s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 798 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 799 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 801 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-e
Process: 889 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 891 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 854 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 32 (limit: 7372)
Memory: 63.4M
CGroup: /system.slice/mariadb.service
`-854 /usr/sbin/mysqld
Next we will configure the database itself:
root@debian-mysql-1:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create user 'repl'@'%' identified by 'slavepassword';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.001 sec)
Now we will configure the second Master Server (10.0.0.162) in the exact same way except for the 50-server.conf config file:
root@debian-mysql-2:~# ip a | grep inet
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
inet 10.0.0.162/16 brd 10.0.255.255 scope global eth0
inet6 fe80::101a:11ff:fe2f:8bea/64 scope link
root@debian-mysql-2:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf
Here you can see the difference being the server-id, the auto-increment-offset as well as the bind address.
[...]
bind-address = 10.0.0.162 #not 127.0.0.1
[...]
server-id =2
log_bin = /var/log/mysql/mysql-bin.log
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
:wq to save and quit, then restart mysqld:
root@debian-mysql-2:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf
root@debian-mysql-2:~# systemctl restart mysqld ; systemctl status mysqld
* mariadb.service - MariaDB 10.3.27 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-03-27 19:08:26 UTC; 35ms ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 758 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 759 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 761 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -e
Process: 870 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 872 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 835 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 35 (limit: 7372)
Memory: 61.9M
CGroup: /system.slice/mariadb.service
|-835 /usr/sbin/mysqld
|-873 /bin/bash /etc/mysql/debian-start
|-877 /usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --version-check
|-878 grep -E -v ^(1|@had|ERROR (1054|1060|1061))
`-879 logger -p daemon warn -i -t/etc/mysql/debian-start
After that, we configure the database just like we did previously:
root@debian-mysql-2:~# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create user 'repl'@'%' identified by 'slavepassword';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.001 sec)
Once that's done, we need to make sure both the databases start with the SAME data, to do that we will use mysqldump to make a .sql file out of the first database, and then pipe it into the second debian's mysql:
MariaDB [(none)]> create database mangas;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> create table mangas.title(name varchar(20));
Query OK, 0 rows affected (0.025 sec)
MariaDB [(none)]> insert into mangas.title values ('Baki-Dou');
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> select * from mangas;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> select * from mangas.title;
+----------+
| name |
+----------+
| Baki-Dou |
+----------+
1 row in set (0.001 sec)
MariaDB [(none)]>
So right here we have our database, and let's export it with mysqldump:
root@debian-mysql-1:~# mysqldump -uroot --all-databases --master-data > master1dump.sql
root@debian-mysql-1:~# cat master1dump.sql | grep Baki-Dou
INSERT INTO `title` VALUES ('Baki-Dou');
root@debian-mysql-1:~# cat master1dump.sql | grep mangas
-- Current Database: `mangas`
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mangas` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mangas`;
INSERT INTO `innodb_index_stats` VALUES ('mangas','title','GEN_CLUST_INDEX','2021-03-27 19:15:15','n_diff_pfx01',0,1,'DB_ROW_ID'),('mangas','title','GEN_CLUST_INDEX','2021-03-27 19:15:15','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mangas','title','GEN_CLUST_INDEX','2021-03-27 19:15:15','size',1,NULL,'Number of pages in the index'),('mysql','gtid_slave_pos','PRIMARY','2021-03-27 18:11:21','n_diff_pfx01',0,1,'domain_id'),('mysql','gtid_slave_pos','PRIMARY','2021-03-27 18:11:21','n_diff_pfx02',0,1,'domain_id,sub_id'),('mysql','gtid_slave_pos','PRIMARY','2021-03-27 18:11:21','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','gtid_slave_pos','PRIMARY','2021-03-27 18:11:21','size',1,NULL,'Number of pages in the index');
INSERT INTO `innodb_table_stats` VALUES ('mangas','title','2021-03-27 19:15:15',0,1,0),('mysql','gtid_slave_pos','2021-03-27 18:11:21',0,1,0);
Now that we have this master1dump.sql file, we will move it over to the second debian host:
basically we can use python3's http module to make our files available on the local network, and then we use wget to retrieve the file onto the second debian host. Once that's done we can verify if the file did or didn't get corrupted by checking if the 2 hashes are the same. Next step is to use this master1dump.sql to recreate the database from master1 into master2:
mysql -u root < master1dump.sql
Once that's done, it's time to make the Master-Master connection, check the master status of master1 and 2:
root@debian-mysql-1:~# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000002 | 1136 | | test,information_schema |
+------------------+----------+--------------+-------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
root@debian-mysql-2:~# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.3.27-MariaDB-0+deb10u1-log Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000001 | 484947 | | test,information_schema |
+------------------+----------+--------------+-------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
Now configure master1 accordingly:
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]> change master to master_host = '10.0.0.162', master_user = 'repl', master_password='slavepassword', master_log_file = 'mysql-bin.000001', master_log_pos = 484947;
Query OK, 0 rows affected (0.009 sec)
MariaDB [(none)]>
Now configure master2 accordingly:
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]> change master to master_host = '10.0.0.161', master_user = 'repl', master_password='slavepassword', master_log_file = 'mysql-bin.000002', master_log_pos = 1136;
Query OK, 0 rows affected (0.023 sec)
MariaDB [(none)]>
Once that's done, start both slaves:
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
And now we're going to test if the Master-Master database replication is successful by applying changes on one host, and checking if the changes are made on the other:
Here we see that the replication is being made from Master1 to Master2, which is equivalent to a Master-Slave connection, But the addition here is when we test it the other way around:
And that's it! We have been able to make a Master-Master MySQL Database replication.
Some Address 67120,
Duttlenheim, France.
This cute theme was created to showcase your work in a simple way. Use it wisely.