On 10/16/2012 4:02 PM, spameden wrote:
2012/10/16 Tim Gustafson <t...@soe.ucsc.edu>
Thanks for all the responses; I'll respond to each of them in turn below:
you can not simply copy a single database in this state
innodb is much more complex like myisam
I know; that's why I rsync'd the entire /var/db/mysql folder (which
includes the ib_logfile and ibdata files, as well as all other
database and table data), not just individual databases. I also made
sure that "flush tables with read lock" had been executed before
creating the snapshot. The steps I followed were verbatim what the
MySQL documentation said to do. The MySQL documentation even mentions
ZFS snapshots as an effective way to make a backup:
http://dev.mysql.com/doc/refman/5.5/en/flush.html
I have to agree with Harald on this: filesystem snapshots are not an
effective way to clone innodb databases. The rsync-based method
described has worked for me in large scale data situations very
reliably.
I'm confused: in the first sentence, you say snapshots are bad (which
directly contradicts the official MySQL documentation), and in the
second sentence you say rsync is good. Why would an rsync of a file
system snapshot not be good enough? By the way: I forgot to mention
that I also did create a snapshot when the MySQL server on db-01 was
actually shut down, and got the same sort of results.
... snip ...
The part you have all missed here is this:
121016 10:40:20 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
As InnoDB operates, it copies data in the background into the tablespace
file(s). You cannot stop this using FLUSH TABLES WITH READ LOCK. What
you need to do is to wait for the database to quiesce using the
procedure documented at the bottom of this page for using ALTER TABLE
IMPORT TABLESPACE
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
###
In this context, a “clean” .ibd file backup is one for which the
following requirements are satisfied:
There are no uncommitted modifications by transactions in the .ibd
file.
There are no unmerged insert buffer entries in the .ibd file.
Purge has removed all delete-marked index records from the .ibd file.
mysqld has flushed all modified pages of the .ibd file from the
buffer pool to the file.
You can make a clean backup .ibd file using the following method:
Stop all activity from the mysqld server and commit all transactions.
Wait until SHOW ENGINE INNODB STATUS shows that there are no active
transactions in the database, and the main thread status of InnoDB is
Waiting for server activity. Then you can make a copy of the .ibd file.
###
The same rules apply to performing a hot backup of the main tablespace
file(s) as they do to backing up any individual tablespaces.
Of course, if you have achieved a clean shutdown, then the on-disk image
is consistent and, as mentioned in other replies, your rsync will work
just fine. Also, if you are doing the cold-shutdown method, you can
start replication from position 4 of the next binary log created after
you restart your master. The step to save the master's binary log
position can be skipped if you are not trying to do a hot (or warm)
backup.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
