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; |
Login to server2.local, then enter the MySQL terminal (this will prompt for the MySQL root password):
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 |
⚠ 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 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.
Comments