Installing a Web, Email & MySQL Database Cluster on Debian 8.4 Jessie with ISPConfig 3.1 - Page 2

5. MySQL Master-Master Replication

5.1 Prepare Server1

Login into MySQL and create an account specifically for replication in MySQL. I use a separate user for the replication to minimize the possibility of compromise to other accounts (username and password are stored in plain text in the master info repository file or table):

CREATE USER 'slaveuser2'@'server2.example.tld' IDENTIFIED BY 'slave_user_password';
CREATE USER 'slaveuser2'@'192.168.0.106' IDENTIFIED BY 'slave_user_password';
CREATE USER 'slaveuser2'@'2001:db8::2' IDENTIFIED BY 'slave_user_password';

and grant the REPLICATION SLAVE privilege:

GRANT REPLICATION SLAVE ON *.* TO 'slaveuser2'@'server2.example.tld';
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser2'@'192.168.0.106';
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser2'@'2001:db8::2';
QUIT;

Make some changes for the replication to your MySQL-Config:

vi /etc/mysql/my.cnf

Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

[...]
[mysqld]
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 = 100M
binlog_format = mixed
sync_binlog = 1
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index
slave_skip_errors = 1007,1008,1050, 1396
bind-address = ::

and restart MySQL afterwards:

service mysql restart

I simply skip some slave-errors. I explained this on my blog:

  • 1007 = Can't create database '%s'; database exists
  • 1008 = Can't drop database '%s'; database doesn't exist  
  • 1050 = Table '%s' already exists  
  • 1396 = Operation %s failed for %s  - Can not create user

you can find the list of MySQL error codes here: Server Error Codes and Messages

5.2 Prepare Server2

Make some changes for the replication to your MySQL-Config:

vi /etc/mysql/my.cnf

Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

[...]
[mysqld]
server-id = 2
log_bin = mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed
sync_binlog = 1
slave_skip_errors = 1007,1008,1050, 1396

5.3 Create a snapshot of the existing databases on server1

Dump the databases on server1 and enter the MySQL root password:

mysqldump -p --all-databases --allow-keywords --master-data --events --single-transaction > /root/mysqldump.sql

Copy the dump to server2:

scp /root/mysqldump.sql [email protected]:/root

5.4 Import the dump on server2

mysql -u root -p < /root/mysqldump.sql

Shutdown mysql on server2:

service mysql stop

Copy the defaults-file for MySQL from server1 to server2. Switch to server1 and run

scp /etc/mysql/debian.cnf [email protected]:/etc/mysql/debian.cnf

Start MySQL on server2:

service mysql start

and login into MySQL to set the master-server with:

CHANGE MASTER TO MASTER_HOST="server1.example.tld", MASTER_USER="slaveuser2", MASTER_PASSWORD="slave_user_password";

Start the slave:

START SLAVE;

and check the slave-status with

SHOW SLAVE STATUS \G

The result will be something like

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: server1.example.tld
                  Master_User: slaveuser2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 109989
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 110276
        Relay_Master_Log_File: mysql-bin.000001
             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: 109989
              Relay_Log_Space: 110574
              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: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:

If "Seconds Behind Master" is not 0, wait until the Slave has read all data from the master (just repeat the command to get the current value). Ensure, that server2 uses the right master-log and log position.

On server1 run in MySQL:

SHOW MASTER STATUS \G

*************************** 1. row ***************************
            File: mysql-bin.000991
        Position: 69185362
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

And compare the Replication Master Binary Log Coordinates.

We are running a MySQL Master-Slave-Replication where server1 is the master and server2 the slave.

5.5 MySQL Master-Master-Replication

Create the MySQL-User for the replication and grant the privileg in MySQL:

CREATE USER 'slaveuser1'@'server1.example.tld' IDENTIFIED BY 'slave_user_password';
CREATE USER 'slaveuser1'@'192.168.0.105' IDENTIFIED BY 'slave_user_password';
CREATE USER 'slaveuser1'@'2001:db8::1' IDENTIFIED BY 'slave_user_password';
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser1'@'server1.example.tld';
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser1'@'192.168.0.105';
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser1'@'2001:db8::1';
QUIT;

Make some changes for the replication to your MySQL-Config on server2:

vi /etc/mysql/my.cnf

Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

[...]
[mysqld]
[...]
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
relay-log = slave-relay.log
relay-log-index = slave-relay-log.inde

and restart MySQL:

service mysql restart

Login into MySQL and get the Master Binary Log Coordinates:

SHOW MASTER STATUS \G

*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 326
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Login into MySQL on server1 and set the master-server with

CHANGE MASTER TO MASTER_HOST="server2.example.tld", MASTER_USER="slaveuser1", MASTER_PASSWORD="slave_user_password", MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=326;

Start the slave:

START SLAVE;

and check the slave-status with

SHOW SLAVE STATUS \G

Check, that the slave is running:

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

If your replication stops working, you may find additional information here:

Share this page:

2 Comment(s)

Add comment

Comments

From: Jorge Gustavo Rocha

Hi Florian,

Great guide!

On the last CHANGE MASTER TO ... (back on server1) the MASTER_LOG_FILE should use be mysql-bin.000001, I think.

Regards,

Gustavo

From: Jorge Gustavo Rocha

Hi Florian,

Forget the previous comment. I've just realized that your SHOW SLAVE STATUS \G on server2 returned mysql-bin.000002. 

Regards,

Gustavo