Verifying MySQL Replication in action

There is a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.

If you would like to try this, you can use which can launch a MySQL Master/Slave configuration in seconds. You can get started with MySQL Sandbox and .

Sandbox setup

$ make_replication_sandbox ~/mysql/mysql-5.1.35-osx10.5-x86.tar.gz
$ cd ~/sandboxes/rsandbox_5_1_35/

On the master

We will use a modified version of the numbers procedure found at Filling test tables quickly as our test program.

./m
create schema if not exists test;
use test
drop table if exists numbers;
create table numbers (id int unsigned not null primary key);

delimiter $$

drop procedure if exists fill_numbers $$
create procedure fill_numbers(in p_max int)
deterministic
begin
  declare counter int default 1;
  truncate table numbers;
  insert into numbers values (1);
  while counter < p_max
  do
      insert into numbers (id)
          select id + counter
          from numbers;
      select count(*) into counter from numbers;
      select counter;
  end while;
end $$
delimiter ;

call fill_numbers(2000000);

On the slave

$ watch -n 1 --differences './s2 -e "SHOW SLAVE STATUS\G"'

This simple command monitors the replication process dynamically and gives a highlighted output during the process. You will notice different highlighted sections during various stages of the replication process.

The output will look similar to:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 23150
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4586
               Relay_Log_File: mysql_sandbox23152-relay-bin.000029
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             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: 4586
              Relay_Log_Space: 564
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: