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.
>
I think you can do this too, I did that once. But make sure you have
changed server-id in the my.cnf and use change master to to select proper
master, you can ofc reset master too if you want on the slave. Basically
reset master means you're flushing all logs and resetting to the first log
entry and position.
>
> > You can do replication flawlessly for InnoDB tables without stopping
> > master at all.
> >
> > what you need is issue on master:
> >
> > mysqldump --single-transaction -A > ALL.databases.dump.sql &&
> > mysql -e 'SHOW MASTER STATUS\G" > ALL.binlog
>
> All total, we have approximately 125GB of MySQL databases. That
> command would take hours to run.
True, it's not fast.
> During that time, no new
> transactions could be committed to any of our databases, and
> performance for read-only queries would be seriously affected.
> Further, we have a combination of MyISAM and InnoDB databases and
> tables, and the --single-transaction parameter to mysqldump does not
> lock MyISAM tables.
Yes, it's true, the thing I mentioned only relates to InnoDB, but for
MyISAM you can issue for specific tables:
LOCK TABLES for writing
There used to be a MySQL command that basically
> did all that in one statement ("LOAD DATA FROM MASTER"), but they
> dropped it because of the difficulties in getting all the master data
> that way.
> --
>
> Tim Gustafson
> t...@soe.ucsc.edu
> 831-459-5354
> Baskin Engineering, Room 313A
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>
>
