True, I hadn't considered that mysql will just create the indexes as it loads the data back in. That increases the time for inserts by a fraction of a second at most, but multipled over a large enough scale, it's unnecessary. Even if I load the data and then create or rebuild the indexes, it's still too long.
That still leaves the problem of the size of the databases themselves and the time that it takes to dump: mysqldump -u <user> --opt <db> <table> > <table>.db This has been running for over 30 minutes now, and it's only one table. Obviously, even doing "bad" math ((16GB / 1.2GB) * .5 = 6.6 hrs) mysqldump is not an option. Surely there's a better backup solution for large databases. This leaves me looking at mysqlhotcopy. Here's what it would do for one table: - begin snip - mkdir /path/to/backup/<dbname>/, 0750 LOCK TABLES `<dbname>`.`<tablename>` READ FLUSH TABLES /*!32323 `<dbname>`.`<tablename>` */ Copying 3 files... cp -p '/usr/local/mysql/var/<dbname>/<tablename>.MYD' '/usr/local/mysql/var/<dbname>/<tablename>.MYI' '/usr/local/mysql/var/<dbname>/<tablename>.frm' '/path/to/backup_test/<dbname>' Copying indices for 0 files... UNLOCK TABLES - end snip - If all mysqlhotcopy does is lock tables and copy the source files to another location on the drive, tar would, on a basic level, do just the same thing by reading the file data in the same manner. In theory, I could just use tar instead of cp, and go straight to tape, bypassing the file IO of copying 16GB of data to another location on the file system (and subsequently deleting it). -J -----Original Message----- From: Mike Hall [mailto:[EMAIL PROTECTED]] Sent: Friday, August 30, 2002 3:14 PM To: Jeremy Tinley; [EMAIL PROTECTED] Subject: Re: Tape backups from live slave > b) I have 200 tables. I don't want to have to recreate indexes for > every table if I have to restore. I'd rather spend an hour restoring > from tape and let replication catch me up. Why would you have to recreate the indexes? The dump will contain this information in the CREATE statements. Mike [sql,query] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
