One of the major contributing factors to the success of MySQL is the ease and simplicity of its replication. Read-slaves for scaleout and backup-slaves for noninterrupting backups are the norm in any MySQL installation I have seen in the last two years.
So how does replication work? And how shall it be expanded in the future?
What is available?
The binlog written by MySQL currently logs all statements changing the tablespace. It is a serialization of all tablespace changes. The binlog position, expressed as (binlog name, offset), is a database global timestamp - a timestamp expressed in seconds.fraction does not work for any precision at all, because on a multi-core machine multiple things can happen concurrently.
If you want to make a consistent full backup of the database, the database must not change during the backup. That is, it must be possible to associate one and exactly one binlog position with the backup.
In fact, if you have such a backup - one associated with a binlog position - and you happen to have the binlogs from that time until now, it is possible to do a point-in-time (PIT) recovery. You'd recover from the full backup and you'd then replay the binlog from the backups binlog position until now. That is why it is important to store the binlog in a filesystem that fails independently from the rest of your MySQL. That's also why you must not filter the binlog that is written by MySQL using binlog-do-db and binlog-ignore-db - if you do, you'll get an incomplete binlog that will fail to be useful in a PIT recovery scenario.
A slave in MySQL is now nothing but a binlog downloader and executor: The slave must be restored from a PIT-capable full backup. It is then being told the current binlog position and where to log in to get the missing binlog. The slaves IO_THREAD will then log into the master server and download the binlog to the local disk as fast as possible, storing it as the relay log. The slaves SQL_THREAD will then start to execute the relay log as fast as possible. Replication can thus be thought of as an ongoing live recovery.
Continue reading "Replication - now and then"
Community (12)
Comments
Sun, 11.05.2008 06:34
In reply to "stuff with no nam es":very informative and succi nct. I am retired and need to learn to build a website [...]
Wed, 30.04.2008 14:08
what is the difference between MySql and PostgreSql?
Wed, 09.04.2008 21:46
What a random fortune, who kno ws what it means. My favorite random fortune cookie note rea ds "you will make a good [...]
Thu, 27.03.2008 19:17
Hi Kristian, I noticed a fe w things that are wrong with y our above article. Overall it is good however, so don [...]
Wed, 19.03.2008 15:01
Sorry, I should mention that t he create transaction simply i nvolves doing: content = Bl ogContent.new(params[:co [...]
Wed, 19.03.2008 14:51
I like to separate text and bl ob columns into their own mode ls and tables, storing and man aging the record content [...]
Wed, 27.02.2008 04:47
IMHO: Rails is being designed and primarily used by people w ith a dedicated DB. So the ide a of constraining in the [...]
Wed, 27.02.2008 04:33
Rails only uses transactions i f you ask it to. If you just d o a People.find(:all), it does a select * from people, [...]