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 MySQL SandBox which can launch a MySQL Master/Slave configuration in seconds. You can get started with MySQL Sandbox and Download 3.0.04.

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: