Installing a Web, Email & MySQL Database Cluster on Debian 8.4 Jessie with ISPConfig 3.1 - Page 2
This tutorial exists for these OS versions
- Debian 8 (Jessie)
- Debian 6 (Squeeze)
- Debian 5 (Lenny)
On this page
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:
