How to setup MySQL replication broken between Master Slave MySQL Multi-Master Configuration

1. cd ~/mysql
2. Create one config file (my_backup.cnf) for backup. Sample config files given below:

a. Your original config file contents (my.cnf):

datadir = ~/mysql/data
innodb_data_home_dir = ~/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = ~/mysql/data
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M

b. New backup config file contents (my_backup.cnf):

datadir = ~/mysql/backup
innodb_data_home_dir = ~/mysql/backup
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = ~/mysql/backup
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M

3. Take backup using following script:

mysql]# bin/ibbackup conf/my.cnf conf/my_backup.cnf

4. Backup folder will look something like this:

$ ls -lh ~/mysql/backup
total 38M
-rw-r-----    1 sqladmin    sqladmin           12M Jan 21 18:40 ibbackup_logfile
-rw-r-----    1 sqladmin    sqladmin           14M Jan 21 18:35 ibdata1.ibz
-rw-r-----    1 sqladmin    sqladmin          8.8M Jan 21 18:37 ibdata2.ibz
-rw-r-----    1 sqladmin    sqladmin          2.2M Jan 21 18:40 ibdata3.ibz

5. Now we apply the log to get the backup stable with any changes that happened while we were taking backup.

mysql]# bin/ibbackup --apply-log conf/my_backup.cnf

InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
...
ibbackup: Last MySQL binlog file position 0 11751329, file name ./mysql-bin.000030
ibbackup: The first data file is '~/mysql/backup/ibdata1'
ibbackup: and the new created log files are at '~/mysql/backup/'
081107 15:42:17  ibbackup: Full backup prepared for recovery successfully!

6. Note:

Following line is important. Save it for future reference:

"ibbackup: Last MySQL binlog file position 0 11751329, file name ./mysql-bin.000030"

7. Now copy the backup to the slave machine (preferrably, by tarring all backup files).
8. Stop the slave server, and put these backup files into the mysql/data directory
9. Please make sure they are all `chown sqladmin:sqladmin`.
10. Add the directive `skip-slave-start` into the conf/my.cnf file (to prevent mysql from being slave, keep replication stopped.
11. Save conf/my.cnf file.
12. Start mysql.
13. Connect to mysql prompt:

mysql]# bin/mysql --defaults-file=conf/my.cnf

14. Check the slave is stopped:

sqladmin@localhost [(none)]>stop slave;

15. Update the master config using:

sqladmin@localhost [(none)]>CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000030',MASTER_LOG_POS=11751329;

The two values are the ones which you have noted down before.

16. Now start replication:

sqladmin@localhost [(none)]>start slave;

17. And check replication status:

sqladmin@localhost [(none)]>show slave status\G;


(should look like)

sqladmin@localhost [(none)]>show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: v-cps3.persistent.co.in
                Master_User: sqladmin_repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000030
        Read_Master_Log_Pos: 12855143
             Relay_Log_File: v-cps3-relay-bin.000002
              Relay_Log_Pos: 11726383
      Relay_Master_Log_File: mysql-bin.000030
           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: 12855143
            Relay_Log_Space: 11726383
            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

18. Finally, remove that skip-slave-start directive from the conf/my.cnf file.