From: Shawn Green Date: October 16 2012 8:13pm Subject: Re: Odd Behavior During Replication Start-Up List-Archive: http://lists.mysql.com/mysql/228419 Message-Id: <507DBFCF.300@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit On 10/16/2012 4:02 PM, spameden wrote: > 2012/10/16 Tim Gustafson > >> 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