My activities New request

contact atmail support

PH: +61 (7) 5357-6605

support@atmail.com

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