On 12/11/12 00:04, DaB. wrote:
Hello,
At Sunday 11 November 2012 23:54:39 DaB. wrote:
Remember that there is a trick where you dump the
user-databases with
the binlog enabled (and noting its position), leaving the database
writable. You then transfer and import the dump. Finally you make it
readonly, copy the changes from the binlog (which will be much smaller
and thus results in a shorter read-only interval) and point the dns to
the other server.
AFAIK this would need a complete LOCK of all tables during the dumping (to get
a consistent state) which would be the same as switching the server read-only.
Also somebody would have to write a program that read the bin-log and filter
out all non-user-database-entries (enwp, commons and heartbeat in this case).
The 3rd disadvantage would be that you would have to wait for the dump to
complete before you can start the import,
The only advantage I see it that sql-s1-user would be writable between the end
of the dump and the time I awake in the morning.
Sincerely,
DaB.
You are completely right. Still, I suspect it *is* possible to do so
efficiently. Starting a global read transaction and then resuming writes
(I am assuming that all will be InnoDB, which is very likely).
Maybe it needs a special tool.
I am copying Asher in case he can enlighten us.
There are tools for the binlog filtering, but it is not a problem in
this case, since we could just stop replication before the export and
let the lag rise during the backup. In the general case we would
reenable them and let the server pick up after it finishes, but in this
case we are just going to overwrite it :)
How much data is in user tables and how long does it take to import the
(uncompressed, ready) wiki tablespaces?
It might turn out to be faster to switch the tablespaces than moving the
dbs around.