O'Reilly logo

Understanding MySQL Internals by Sasha Pachev

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL Commands to Help Understand Replication

A good way to explore and understand how MySQL replication works is to look at the output of some replication monitoring commands. Let us first configure one server as a master by enabling the log-bin option and setting a server ID with the server-id option to some unique number, e.g., the last byte of the IP address. Then we run:

	SHOW MASTER STATUS\G

in the command-line client. We use the \G option to enable vertical display of columns, which makes the output more readable. The command produces an output similar to this:

	*************************** 1. row **************************
	            File: laforge-bin.011
	        Position: 566920603
	    Binlog_do_db:
	Binlog_ignore_db:

The File field is the name of the current binary log to which the master is writing. The Position field shows the offset in the current binary log where the next event is going to be written, or in other words, the size of the current binary log.

The Binlog_do_db and Binlog_ignore_db fields show the values of the corresponding options in the configuration of the master. The master can be instructed to either log updates that were done with only certain default databases selected (Binlog_do_db, the inclusive rule), or to exclude all of the updates that were done in a specified database list (Binlog_ignore_db, the exclusive rule). Note that those rules apply to the default database of the thread (selected via the USE command, or a call to mysql_select_db() in the client API), rather than the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required