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.
$ 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: