]]>
]]>

Howto mirror binlog

This howto is intended to describe setup with slave with mirrored binary logs and failover procedure

Purposes

Purpose of mirrored slave is to keep exactly the same binary logs as on master. It is very useful for next goals:

  • Make backups from slave. In this case you can take both full snapshot and binary logs backups only from slave. And these binary logs can be used for point-in-time recovery in case of crash of master, as it is exact copy of master's binary logs.
  • Fast failover procedure. All slaves of master can be easy repointed to mirrored slave in case of master's failure, as they can use the same master_log_file and master_log_pos as on main master.

Terms

db02 - main master

db06 - mirrored slave
db10 - usual slave
db02 --> db06
 |
 +-----> db10

setup mirror slave

:!: Make sure log-bin is disabled when you have rpl_mirror_binlog_enabled

:!: If using an existing regular slave, one needs to run “reset slave;” before the change master. Otherwise mysql will throw an error, “ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log” 1)

It is mandatory to set value for log-bin-index in my.cnf

You need to delete all current binary logs, log-bin-index, and relay-logs, and relay-log-index from new mirror slave before initialization

it is good to have skip-slave-start option also, to be able to configure slave after restart.

my.cnf on db06

# replication
# log-bin=db06-bin
server-id=6

rpl_mirror_binlog_enabled
log-bin-index=db06-bin.index

:!: Note, you can't use log-slave-updates option on mirrored slave, as slave in this case potentially can have different binary logs than on master

Create slave:

db06> CHANGE MASTER TO
  MASTER_HOST='db02',
  MASTER_USER='master_user',
  MASTER_PASSWORD='master_host',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='db02-bin.000016',
  MASTER_LOG_POS=98,
  MASTER_CONNECT_RETRY=10;

db06> START SLAVE:

in error.log on slave db06 you should something like this:

081201 17:30:52 [Note] ReplicationMule: MULE_BEHIND - new(4), old(202)                                                              
081201 17:30:52 [Note] ReplMule::dumpEvent - new status(1) master_log_pos(4), dump_pos(202), file_size(4)                           
081201 17:30:52 [Note] ReplMule::dumpEvent - new status(2) master_log_pos(202), dump_pos(202), file_size(4)                         

in error.log on master db02:

081204 16:32:07 [Note] Start asynchronous binlog_dump to slave_server(6), pos(db02-bin.000016, 4)

Right after start slave mirrored slave is downloading current binary log, and further keeps the mirrors of logs.

setup regular slave

setup db10 as slave is regular procedure to setup replication

Failover.

Let's assume master db02 failed (crashed) on position master_log_file=db02-bin.000200 master_log_pos=4796312 <CRASH_POS>. The position can be obtained in next way: you need to note filename of last binary log file and size of last binary log file. If master server is not available at all (hard drive failure, physical damage of server), you make assume this position from SHOW SLAVE STATUS on db06 and values Master_Log_File / Exec_Master_Log_Pos. :!: Note You may lose several transaction, as not all data may be transferred to db06 and the time of db02 crash.

In case when main master db02 fails we need to perform next actions:

Create master from mirrored slave db06

It can be done by next commands:

 db06> STOP SLAVE;
 db06> MAKE MASTER REVOKE SESSION WITH KILL; <- this command kills all current connections without SUPER privileges;

alternative you may execute:

 db06> MAKE MASTER REVOKE SESSION, it does not kill clients, but you need to be ensure that connections do not touch binary logs.

create master:

 db06> MAKE MASTER MASTER_LOG_FILE='db02-bin', MASTER_SERVER_ID=6,  INDEX='db06-bin.index'  WITH BINLOG;

and enable access:

 db06> MAKE MASTER GRANT SESSION;

:!: MAKE MASTER MASTER_LOG_FILE= … command creates internals structures in mysql, that it now can work as master, that is - write binary-logs, support log-bin-index and assign server-id :!:

This command force to create master infrastructure on this server without restart. basically command is alternative as if you shutdown mysqld, make next changes to my.cnf:

-----
log-bin=db02-bin
server-id=6

#rpl_mirror_binlog_enabled
log-bin-index=db06-bin.index
----

and start mysqld

on db06 new binary log with next name db02-bin.000201 will be created. You should see new created binary & relay logs on db06. Also binary logs should be the same as binary logs on db02 (have the same names and sizes).

repoint regular slave db10 to new master db02

basically on db10 you need only to change master_host and keep master_log_file and master_log_pos the same:

db10> stop slave;

db10> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: 
                Master_Host: db02
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db02-bin.000200  
        Read_Master_Log_Pos: 4796312
             Relay_Log_File: db02-relay-bin.000066
              Relay_Log_Pos: 237
      Relay_Master_Log_File: db02-bin.000200   <--------- <LOG_FILE>
           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: 4796312           <--------- <LOG_POS>
            Relay_Log_Space: 237
            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


db10> change master to master_host='db06', master_log_file='<LOG_FILE>', master_log_pos=<LOG_POS>;

db10> start slave;

Main master (db02) back to life

make changes in my.cnf on db02:

#log-bin
rpl_mirror_binlog_enabled=1
log-bin-index=db02-bin.index
skip-slave-start

start mysql.

:!: you need to know binary log name and position when master was failed. This is <CRASH_POS> you noted before. :!: you need to delete last binary log file from master, as it will be copied from slave automatically.

 db02> change master to master_host='db06', master_log_file='<CRASH_POS_FILE>', master_log_pos=<CRASH_POS_POSITION>;
 db02> start slave;

wait until master is fully synchronized with slave.

Create master db02 again as regular master

:!: at this moment mirrored slave (db06) must start to work in read-only mode; you should kill all connections and execute set global read_only=1; be sure there is no connections with SUPER privileges.

Change db02 from slave to master:

db02> make master revoke session;
db02> MAKE MASTER MASTER_LOG_FILE='db02-bin',  MASTER_SERVER_ID=2, INDEX='db02-bin.index' WITH BINLOG;
db02> make master grant session;

Master is ready to accept connections.

db02> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| db02-bin.000275    |       98 |              |                  | 
+--------------------+----------+--------------+------------------+
1 row in set (0.02 sec)

repoint mirrored slave (db06) to master.

:!: mirrored slave should be restarted, when it down you need to delete log-bin-index file on db06, new file is need to contain fresh copy of mirrored binary logs.

after start:

 db06> change master to master_host='db02', master_log_file='db02-bin.000275', master_log_pos=98;
 db06> start slave;

repoint usual slave (db10) to db02

The procedure is the same as repoint in first case

you need only to change master_host and keep master_log_file and master_log_pos the same:

db10> stop slave;

db10> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: 
                Master_Host: db06
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: db02-bin.000274 
        Read_Master_Log_Pos: 4796312
             Relay_Log_File: db06-relay-bin.000066
              Relay_Log_Pos: 237
      Relay_Master_Log_File: db02-bin.000274  <--------- <LOG_FILE1>
           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: 4796312         <--------- <LOG_POS1>
            Relay_Log_Space: 237
            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


db10> change master to master_host='db02', master_log_file='<LOG_FILE1>', master_log_pos=<LOG_POS1>;

db10> start slave;

slave is ready.

1) Which ends up looking like: May 29 17:49:22 s0239 mysqld[9503]: 090529 17:49:22 [ERROR] Failed to open the relay log '/var/lib/mysql/0/relay-bin.000843' (relay_log_pos 41446934) May 29 17:49:22 s0239 mysqld[9503]: 090529 17:49:22 [ERROR] Could not find target log during relay log initialization
 
howto/mirror_binlog.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>