/ #linux #debian 
3 minutes read | 471 words

How to Replication Mysql Master to Master

Setting up hosts on VM1 and VM2, example:

vim /etc/hosts

Add:

192.168.57.201  saka.id
192.168.57.202  sira.id

Configuration on VM1:

mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

Configuration MySQL, disable config like (expire_logs_days, max_binlog_size dan bind-address). Add new configuration for replication:

vim /etc/mysql/my.cnf
server-id                = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset    = 1
log_bin                  = mysql-bin.log
expire_logs_days         = 10
max_binlog_size          = 1024M
binlog_format            = mixed
sync_binlog              = 1
log_bin                   = /var/log/mysql/mysql-bin.log
log-bin-index             = /var/log/mysql/log-bin.index
log-error                 = /var/log/mysql/error.log
relay-log                 = /var/log/mysql/relay.log
relay-log-info-file       = /var/log/mysql/relay-log.info
relay-log-index           = /var/log/mysql/relay-log.index
slave_skip_errors        = 1007,1008,1050, 1396
bind-address             = 192.168.57.201
binlog_do_db              = consumer
binlog-ignore-db          = test
binlog-ignore-db          = information_schema
binlog-ignore-db          = mysql
replicate-ignore-db       = test
replicate-ignore-db       = information_schema
replicate-ignore-db       = mysql

Restart MySQL serivec:

service mysql restart

Open port 3306.

Check status master MySQL:

mysql
mysql> show master status;
+------------------+----------+---------------+-------------------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB              |
+------------------+----------+---------------+-------------------------------+
| mysql-bin.000001 |      107 | consumer      | test,information_schema,mysql |
+------------------+----------+---------------+-------------------------------+
1 row in set (0.00 sec)

Change permission global access to database, before only allow access localhost. Now allow access to ip VM1:

mysql
mysql> SELECT user, host FROM mysql.user;
mysql> grant all privileges on consumer.* to 'consumer'@'192.168.57.201' identified by 'password' with grant option;

Move to VM2

Konfigurasi VM2:

mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

Configuration MySQL, disable config like (expire_logs_days, max_binlog_size dan bind-address). Add new configuration:

vim /etc/mysql/my.cnf
server-id                = 2
log_bin                  = mysql-bin.log
expire_logs_days         = 10
max_binlog_size          = 1024M
binlog_format            = mixed
sync_binlog              = 1
slave_skip_errors        = 1007,1008,1050, 1396
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset    = 2
bind-address             = 192.168.57.202
log_bin                   = /var/log/mysql/mysql-bin.log
log-bin-index             = /var/log/mysql/log-bin.index
log-error                 = /var/log/mysql/error.log
relay-log                 = /var/log/mysql/relay.log
relay-log-info-file       = /var/log/mysql/relay-log.info
relay-log-index           = /var/log/mysql/relay-log.index
binlog_do_db              = consumer
binlog-ignore-db          = test
binlog-ignore-db          = information_schema
binlog-ignore-db          = mysql
replicate-ignore-db       = test
replicate-ignore-db       = information_schema
replicate-ignore-db       = mysql

Restart MySQL service

service mysql restart

Open port 3306

Chcek status master mysql:

mysql
mysql> show master status;
+------------------+----------+---------------+-------------------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB              |
+------------------+----------+---------------+-------------------------------+
| mysql-bin.000001 |      107 | consumer      | test,information_schema,mysql |
+------------------+----------+---------------+-------------------------------+
1 row in set (0.00 sec)

Change permission global access to database, before only allow access localhost. Now allow access to ip VM2:

mysql
mysql> SELECT user, host FROM mysql.user;
mysql> grant all privileges on consumer.* to 'consumer'@'192.168.57.202' identified by 'password' with grant option; 

Configuration SLAVE:

mysql
mysql> SLAVE STOP;
# change to your own master status MASTER_LOG_FILE is from 'File', MASTER_LOG_POS is from 'Position' of master mail1
mysql> CHANGE MASTER TO MASTER_HOST='192.168.57.201', MASTER_USER='repl_user', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
mysql> SLAVE START;

Back again to VM1 Configuration SLAVE:

mysql
mysql> SLAVE STOP;
# change to your own master status MASTER_LOG_FILE is from 'File', MASTER_LOG_POS is from 'Position' of master mail1
mysql> CHANGE MASTER TO MASTER_HOST='192.168.57.201', MASTER_USER='repl_user', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
mysql> SLAVE START;
Author

Mhy

Jép Kupie Nak Bek Pungoe

Jakarta - Aceh, ID