multi server setup guide - MySQL master-master replication – atmail help centre

PROBLEM
How can I setup MySQL master-master replication for my atmail multiserver setup?

ENVIRONMENT

atmail on-premises mail server installations: 7.0 > 7.8.0.1

CAUSE
atmail has not yet been installed.

RESOLUTION

⚠ Note: There are two sub-tutorials - one for MySQL and one for MariaDB. We highly recommend that you use the MariaDB documentation if running atmail 7.8+ on CentOS 7. The guide for MariaDB setup can be found here.

Using MySQL master-master replication

Preparing MySQL

Should you use the implementation that uses the replication aspect of MySQL, you will need to set MySQL up prior to installing atmail. For the purposes of this guide, we have two example servers - server1.local and server2.local, using the IP addresses 192.168.0.2 and 192.168.0.3, respectively.

On the first server (server1.local, 192.168.0.2), enter the MySQL terminal (this will prompt for the MySQL root password):

mysql -u root -p

Grant replication permissions to the second server (system2.local, 192.168.0.3):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'192.168.0.3' IDENTIFIED BY 'changeme';
mysql> FLUSH PRIVILEGES;

Create the atmail database, and assign a user:

mysql> CREATE DATABASE atmail7;
mysql> GRANT ALL PRIVILEGES ON atmail7.* TO 'atmailuser'@'localhost' IDENTIFIED BY 'changeme';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

mysql -u root -p

Grant replication permissions to the first server (server1.local):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'192.168.0.2' IDENTIFIED BY 'changeme';
mysql> FLUSH PRIVILEGES;

Create the atmail database, and assign a user:

mysql> CREATE DATABASE atmail7;
mysql> GRANT ALL PRIVILEGES ON atmail7.* TO 'atmailuser'@'localhost' IDENTIFIED BY 'changeme';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Create the MySQL replication log directory with proper permissions on BOTH systems:

mkdir /var/log/mysql/
chown mysql:mysql /var/log/mysql

MySQL configuration files

Modify the MySQL configuration file on the second server (server2.local, 192.168.0.3). The file /etc/my.cnf needs to be edited to match the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
  
log=/tmp/mysql.log
  
#
#atmail replication mods
#
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
  
master-host = 192.168.0.2
master-user = replicant
master-password = changeme
master-connect-retry = 60
replicate-do-db = atmail7
  
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = atmail7
  
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index
  
expire_logs_days = 10
max_binlog_size = 500M
#
#End atmail replication mods
#
  
[mysql.server]
user=mysql
basedir=/var/lib
  
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

⚠ Note: Note the value of expire_logs_days. This needs to be set at a value that you are comfortable with. The bin logs contain transaction logs from MySQL, and can be valuable in case of a failure. However, the logs can become quite big. You need to make sure that you have sufficient disk space to allow for the amount of MySQL binary logs that will be accumulated over the set expire period.

Some installations can take up to 10 gigabytes of binary logs a day. Make sure that the partition that the relay-log and log-bin values point to have enough space for this.

Restart the MySQL services on the second server (192.168.0.3):

service mysqld restart

Modify the MySQL configuration file on the first server (server1.local, 192.168.0.2). The file /etc/my.cnf needs to be edited to match the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
  
# Verbose mysql logging
log=/tmp/mysql.log
  
#
#atmail replication mods
#
server-id =2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
  
master-host = 192.168.0.3
master-user = replicant
master-password = changeme
master-connect-retry = 60
replicate-do-db = atmail7
  
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = atmail7
  
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index
  
expire_logs_days = 10
max_binlog_size = 500M
#
#End atmail replication mods
#
  
[mysql.server]
user=mysql
basedir=/var/lib
  
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart the MySQL services on server1.local(192.168.0.2):

service mysqld restart

MySQL Database Replication

Afterwards, enter the MySQL terminal on the second server (server2.local), (this will prompt for the MySQL root password):

mysql -u root -p

Get the master status:

mysql> USE atmail7;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

This will return something similar to this:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    55060 | atmail7      |                  |
+------------------+----------+--------------+------------------+
  
1 row in set (0.00 sec)
  
mysql> UNLOCK TABLES;
mysql> EXIT;

Export the primary MySQL database:

cd /tmp
mysqldump -u root -p  atmail7 > sqldump.sql
scp sqldump.sql root@192.168.0.3:/tmp/mysqldump.sql

Stop the slave on the second server (server2.local):

mysqladmin --user=root -p stop-slave

Import the database into server2.local:

cd /tmp
mysql -u root -p atmail7 < sqldump.sql

Enter the MySQL terminal (this will prompt for the MySQL root password):

mysql -u root -p

Get the master status:

mysql> USE atmail7;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

This will return something similar to this:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   457520 | atmail7      |                  |
+------------------+----------+--------------+------------------+
  
1 row in set (0.00 sec)
  
mysql> UNLOCK TABLES;

Set the primary master of server2.local using the details from the master status of server1.local (these must match the details above from the SHOW MASTER STATUS on server1.local):

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.2',
mysql> MASTER_USER='replicant',
mysql> MASTER_PASSWORD='changeme',
mysql> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=55060;

Restart the slave:

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;
  
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------
-----------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
  
| Waiting for master to send event | 192.168.0.2 | replicant   |        3306 |            60 | mysql-bin.000001 |               55060 | slave-relay.000003 |           235 | mysql-bin.000001      | Yes              | Yes               | atmail7         |                     |                    |                        |                         |                             |          0 |            |            0 |               55060 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
  
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
  
1 row in set (0.00 sec)

The important rows in this output are the Slave_IO_Running and Slave_SQL_Running. They should be set to "Yes".

Set the primary master of server1.local using the details from the master status of server2.local (these must match the details above from the SHOW MASTER STATUS command on server2.local). Enter the MySQL terminal (this will prompt for the MySQL root password):

mysql -u root -p

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.3',
mysql> MASTER_USER='replicant',
mysql> MASTER_PASSWORD='changeme',
mysql> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 457520;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;
  
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.3 | replicant   |        3306 |            60 | mysql-bin.000001 |               55060 | slave-relay.000003 |           235 | mysql-bin.000001      | Yes              | Yes               | atmail          |                     |                    |                        |                         |                             |          0 |            |            0 |               55060 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
  
1 row in set (0.00 sec)

The important rows in this output are the Slave_IO_Running and Slave_SQL_Running. They should be set to "Yes".

Installing atmail's First Instance

Once MySQL master-master replication has been setup move onto the installation and filesystem replication guide.

Using MySQL master-master replication

Preparing MySQL

MySQL configuration files

MySQL Database Replication

Installing atmail's First Instance

Comments