help centre
For more info visit status.atmail.com

How can we help?


Search our knowledge base for answers to
common questions and latest updates.



My activities New request

Follow

Repairing MySQL replication

Stewart -

PROBLEM
I need to repair my multiserver installation master-master MySQL setup.

ENVIRONMENT

  • On-Premise Server + WebMail Installations: Version 6.0 > Current Version

CAUSE
Multiserver MySQL replication broken.

RESOLUTION

Introduction

This guide covers the steps required to repair a master-master MySQL setup.

For this guide, we will use the following hostnames for MySQL:

storage1.lan - First MySQL instance, still active
storage2.lan - Second MySQL instance, inactive

We will use the following system variables, which may vary for your system:

MySQL user: atmail-mysql
MySQL password: Password01
MySQL database: atmail_database

We will use the following replication settings, which will be referenced in your /etc/my.cnf files.

MySQL replication user: atmail-replication
MySQL replication password: Replica01

First, stop all services that write to the Atmail MySQL database. You may need to stop the 'atmailserver' and 'httpd' services in your sysvinit directory:

% /etc/init.d/atmailserver stop
% /etc/init.d/httpd stop

First MySQL instance

From the first MySQL instance, make a MySQL dump of the Atmail database:

storage1.lan% mysqldump -u atmail-mysql -p atmail_database > mysqldump-storage1.lan.sql

Once the dump is done, copy this to the second MySQL instance:

storage1.lan% scp mysqldump-storage1.lan.sql root@storage2.lan:/root/mysqldump-storage1.lan.sql

Login to the MySQL terminal:

storage1.lan% mysql -u atmail-mysql -p atmail_database

Once logged in, stop and reset the slave processes:

mysql> stop slave;
mysql> reset slave;

Get the master status of the the first MySQL instance:

mysql> show master status;

This will show results similar to the following:

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

Make a note of the results.

Second MySQL instance:

From the second MySQL instance, make a backup of the inactive MySQL database:

storage2.lan% mysqldump -u atmail-mysql -p atmail_database > mysqldump-storage2.lan.sql

Once the backup's done, login to MySQL terminal:

storage2.lan% mysql -u atmail-mysql -p atmail_database

Inside the MySQL terminal, stop and reset the slave services:

mysql> stop slave;
mysql> reset slave;

Drop the Atmail database:

mysql> drop database atmail_database;

Create the database again:

mysql> create database atmail_database;
mysql> exit;

Import the database copied from the first MySQL instance:

storage2.lan% mysql -u atmail-mysql -p atmail-database < /root/mysqldump-storage1.lan.sql

Login to MySQL again:

storage2.lan% mysql -u atmail-mysql -p atmail_database

Once inside MySQL, execute the following (making a note of your replication settings):

mysql> CHANGE MASTER TO MASTER_HOST='storage1.lan',
mysql> MASTER_USER='atmail-replication',
mysql> MASTER_PASSWORD='Replica01',
mysql> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=55060;

The values correspond as follows:

MASTER_HOST - the hostname of the first MySQL instance
MASTER_USER - your replication username, as per /etc/my.cnf
MASTER_PASSWORD - your replication password, as per /etc/my.cnf
MASTER_LOG_FILE - the File value in the SHOW MASTER STATUS query above from storage1.lan
MASTER_LOG_POS - the Position value in the SHOW MASTER STATUS query above from storage1.lan

Once executed, start the slave process:

mysql> start slave;
mysql> show slave status;

The above should produce a result like:

+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| 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 | storage1.lan | atmail-replication   |        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)

Make sure that Slave_IO_Running and Slave_SQL_Running both point to "Yes". Afterwards, show the master status:

mysql> show master status;

This will show results similar to the following:

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

Make a note of the results.

From the first MySQL instance:

From the first MySQL instance, login to MySQL:

storage1.lan% mysql -u atmail-mysql -p atmail_database

Once inside MySQL, execute the following (making a note of your replication settings):

mysql> CHANGE MASTER TO MASTER_HOST='storage2.lan',
mysql> MASTER_USER='atmail-replication',
mysql> MASTER_PASSWORD='Replica01',
mysql> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=55060;

The values corresond as follows:

MASTER_HOST - the hostname of the first MySQL instance
MASTER_USER - your replication username, as per /etc/my.cnf
MASTER_PASSWORD - your replication password, as per /etc/my.cnf
MASTER_LOG_FILE - the File value in the SHOW MASTER STATUS query above from storage2.lan
MASTER_LOG_POS - the Position value in the SHOW MASTER STATUS query above from storage2.lan

Once executed, start the slave process:

mysql> start slave;
mysql> show slave status;

The above should produce a result like:

+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| 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 | storage2.lan | atmail-replication   |        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)

Make sure that Slave_IO_Running and Slave_SQL_Running both point to "Yes".

Congratulations! Your database should now be synchronized. Now, you can start both Atmail and Apache services again.


 

Have more questions? Submit a request

Comments


Contact our support team


+61 (7) 5357 6605       support@atmail.com