load data from master never worked for innodb. On 2012-10-16 3:52 PM, "Tim Gustafson" <t...@soe.ucsc.edu> wrote:
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 inn... 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. > You can do replication flawlessly for InnoDB tables without stopping > master at all. > > what yo... All total, we have approximately 125GB of MySQL databases. That command would take hours to run. 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. 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: ht...