Solution from:
https://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/
Successfully implemented on Linux Ubuntu 16.04 in google cloud platform
MySql 5.7 will not load on Debian.
/// Install MySQL on 2 VMs
A. On VMs sql1 & sql2
apt update
apt install mysql-server -y
P@ssW0rd2020 P@ssW0rd2020
B. Comment bind-address to allow global access
cd /etc/mysql/mysql.conf.d/
nano /etc/mysql/mysql.conf.d/mysqld.cnf
C. Restart MySQL Service (cnf changed)
systemctl restart mysql
systemctl status mysql
D. On sql1
nano /etc/mysql/conf.d/mysql.cnf
[mysqld] // note: not [mysql]
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
-------------------------------------
systemctl restart mysql
systemctl status mysql
D. On sql2
nano /etc/mysql/conf.d/mysql.cnf
[mysqld]
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
-------------------------------------
systemctl restart mysql (THIS WILL CREATE BIN LOG)
systemctl status mysql
again, flush privileges; ---supposed to
E. Create the Replicator User(s)
Configure on both sql1 & 2 (replicator password can be same for, or not)
mysql -u root -p
P@ssW0rd2020
CREATE USER 'replicator'@'%' IDENTIFIED BY 'P@ssW0rd2020';
GRANT REPLICATION SLAVE ON . TO 'replicator'@'%' IDENTIFIED BY 'P@ssW0rd2020';
flush privileges;
F. Start with fresh VMs with no databases (better) or mutual import/export databases into sql1 & 2
https://dev.mysql.com/doc/refman/8.0/en/copying-databases.html
G. Configure replication from sql1 to sql2
(make sql2 slave of sql1)
On sql1
SHOW MASTER STATUS;
example output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 448 | example | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- NOTE 'file' and 'position' values
On sql2
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '104.154.225.215', MASTER_USER = 'replicator', MASTER_PASSWORD = 'P@ssW0rd2020', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 448;
START SLAVE;
flush privileges;
H. Repeat for sql2 (make sql1 slave of sql2)
on sql2
SHOW MASTER STATUS;
example output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 448 | example | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- NOTE 'file' and 'position' values
On sql1
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '35.198.195.130', MASTER_USER = 'replicator', MASTER_PASSWORD = 'P@ssW0rd2020', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 448;
START SLAVE;
I. Test the Replication
- Create a database on sql1 it will be replicated on sql2, vice versa.
- Create a database on sql2 it will be replicated on sql1, vice versa.
SUCCESS IS SWEET!
