Restarting replication from scratch on master-master cluster

[Michael R Kirkpatrick]

My First post here ... hope you MySQL gurus can help,
 
   We have a master-master MySQL 5.0 cluster (Linux) and replication
broke about 2 months ago. Replication stopped because we have no log
rotation automated for our bin logs and we ran out of disk space (must
be common). After trying to restart replication with no success we have
decided to restart replication from scratch. I have outlined the process
for my boss and he still has misgivings about restarting according to my
plans because he thinks my plan fails to address the master-master
aspect of the cluster; this is on a production server. I will enclose
here how I think this will work and I will include some of my MySQL
output. I also have some questions at the close of this post.
 
  If I can't get replication restarted soon I may be looking for a job!!
 
  Please help if you can!
 
 
Node 1: "slave" that ran out of space:
 
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 192.168.0.91
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000033
        Read_Master_Log_Pos: 96906912
             Relay_Log_File: mysql-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000033
           Slave_IO_Running: No
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 96906912
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

mysql> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000031 | 1073833660 |
| mysql-bin.000032 | 1073812143 |
| mysql-bin.000033 |   97237312 |
+------------------+------------+
3 rows in set (0.00 sec)

 
Node 2: current "master" with all of the current logs
 
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.92
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000033
        Read_Master_Log_Pos: 97237242
             Relay_Log_File: mysql-relay-bin.000203
              Relay_Log_Pos: 2195
      Relay_Master_Log_File: mysql-bin.000033
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 97237242
            Relay_Log_Space: 2195
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000051 |      9196 |
+------------------+-----------+
1 row in set (0.00 sec)
 
 
 
 
This is my plan:
 
*** backup databases on node 2 (master)
 
mysqldump --all-databases
 
 
***remove all binlogs on node 1
 
PURGE BINARY LOGS TO 'mysql-bin.000033'
 
 
***remove bin-logs on node 2  (?) (master)
 
is this correct? --seems counter-intuitive
 
 
***lock tables on node 2 (master)
 
FLUSH TABLES WITH READ LOCK;
 
 
***copy bin-logs on node 2 (master)
 
mysqldump myDBname -uroot -p > /home/myusername/database.sql;
 
(do I need to do a --ADD-DROP-DATABASE for active transactions? syntax?)
 
(should I be doing a mysqldump --master-data ) so that replication can
pick up correctly?
 
 
***create new DB on node 1 
 
CREATE DATABASE 'myDBname';
 
gunzip /home/myusername/database.sql.gz
 
 
***show status and keep up on the terminal for node 2 (master)
 
SHOW MASTER STATUS;
 
e.g.,     mysql-bin.000047     700775872
 
 
 
***restart slave from node 1 (slave)
 
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='node2',
mASTER_LOG_FILE='mysql-bin.000047', MASTER_LOG_POS=97237242;
START SLAVE;
 
 
***unlock tables 
 
SHOW SLAVE STATUS \G
 
to confirm 
 
Slave IO Running: YES
Slave SQL Running: YES
 
 
 
 
Q: My restart replication process must be master-master specific -
please advise if possible.
 
 
I'll keep reading the posts -- please reply to me either directly or to
the forum.
 
THANKS!
 
 
 
Michael Kirkpatrick
 
Michael Kirkpatrick
Systems Administrator
MicroElectronics, Inc.
mkirkpatrick@stripped
614-777-2746
 
P consider the environment before printing