If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds. |
The feature is documented in WL#344. (There was a manual online as well
together with the binaries for MySQL 5.6.0, but they were removed
after a few days for a good reason. I am confident that both the
manual and some binaries will eventually show up soon).
Since as of today there are no binaries for MySQL 5.6.x, you need
to get the code and compile it yourself. Just get the code from
https://code.launchpad.net/mysql-server and
compile it using the instructions in building MySQL 5.5 with cmake.
To get a taste of this new feature, the quickest way is to set up
replication using the binaries that you have built and MySQL
Sandbox.
make_replication_sandbox mysql-5.6.2-m5-osx10.6-.tar.gz
# the file name may change, depending on the operating system you are using
Soon you will have one master and two slaves in
$HOME/sandboxes/rsandbox_5_6_2.
What you have to do is connect to one of the slaves and enter
these commands:
STOP SLAVE;
change master to master_delay=60;
START SLAVE;
Let's say that you did this to slave #2.
Now whatever you do in the master will be replicated immediately
in slave #1, but it will executed with 60 seconds delay in slave
#2.
To be clear, the IO_THREADs of both slaves keep getting data from
the master as fast as they can, same as they did until version
5.5, but slave #2 will hold the SQL_THREAD for the defined amount
of seconds.
This new state is visible in the output of the SHOW SLAVE STATUS
command, which lists this information after you do something in
the master like creating a table or inserting data:
SQL_Delay: 60
SQL_Remaining_Delay: 43
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
The main purpose of delayed replication is to protect the server
against human mistakes. If I accidentally drop a table, the
statement is instantly replicated to all the slaves, but it is
not executed to the delayed slaves.
$ ./m -e 'drop table test.t1 '
$ ./use_all 'show tables from test'
# master
# server: 1:
# server: 2:
Tables_in_test
t1
The table is gone in the master, and it is gone in the regular
slave, but it is still there in the delayed slave. And if I
detect the problem before the delayed statement gets executed (a
delay time longer than 60 seconds would be advisable in this
case, 3600=1 hour, seems healthier), then I may be able to
recover the data.
I notice en passant that there is much more than delayed
replication going on in MySQL 5.6. For example, the
information_schema tables related to InnoDB have increased from 7
to 18:
show tables from information_schema like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMPMEM |
| INNODB_TRX |
| INNODB_BUFFER_PAGE | *
| INNODB_LOCK_WAITS |
| INNODB_SYS_TABLESTATS | *
| INNODB_CMP |
| INNODB_SYS_COLUMNS | *
| INNODB_CMPMEM_RESET |
| INNODB_SYS_FOREIGN_COLS | *
| INNODB_BUFFER_PAGE_LRU | *
| INNODB_BUFFER_POOL_STATS | *
| INNODB_CMP_RESET |
| INNODB_SYS_FOREIGN | *
| INNODB_METRICS | *
| INNODB_SYS_INDEXES | *
| INNODB_LOCKS |
| INNODB_SYS_FIELDS | *
| INNODB_SYS_TABLES | *
+----------------------------------------+
18 rows in set (0.00 sec)
# (*) new tables marked with a star
What they do and how to play with them will be matter for some more investigation.