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.
$ 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 STATUSG"'
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: