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.
Comments