Hendrik, ----- Original Message ----- From: ""Hendrik Schalekamp"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, June 18, 2002 11:12 AM Subject: Backing up InnoDb
> Hi, > > I'm just trying out the hotbackup utility (http://www.innodb.com/hotbackup.html) > for a MySql InnoDb based database and was hoping somebody could > help me with a few questions about the utility and InnoDb: > > 1. Firstly I'd like to know how mature the hotbackup utility is. The > implementation I require it for could become quite intensive and is a > critical piece of functionality of the system, so I need to know that > the utility is stable. (Has anybody used it and what are your > experiences of it?) about 50 people have tested ibbackup. So far no critical bugs have been reported, but caution is still warranted. You should run ibbackup --restore yourbackupmy.cnf on your backups regularly so that you see they can be restored. Then start mysqld on the restored backup mysqld --defaults-file=yourbackupmy.cnf and run mysql --defaults-file=yourbackupmy.cnf mysql> CHECK TABLE ... on some of the tables so that you see they are ok. You should set [mysqld] port=xxx socket=yyy ... [mysql] port=xxx socket=yyy in yourbackupmy.cnf so that the mysqld started on the backup does not disturb your real MySQL server. > 2. The hotbackup utility only backs up the data of our InnoDb database > and not the .frm files containing the table definitions. Is there any > way that you can lock the table definitions while still allowing for > data insertion, deletion and modification? I basically want to ensure > that the .frm files aren't modified while I back up the data. I think not. An eventual solution to this inconvenience is that also the .frm files will be stored inside ibdata files. That will remove also the other problems with not in-sync .frm files. The fundamental problem here is that .frm files are not managed transactionally. That can be fixed by storing them inside InnoDB. If you make the tar file of the .frm files before the backup and immediately after the backup, usually the tar file taken AFTER the backup is the right one, because it does not take many seconds to back up very small (8 kB) .frm files. You can compare the tar file taken before the backup and after that, and if they are identical, then you know for sure they are up-to-date. If not, you have to use the mysqlbinlog tool to look at the binlog generated immediately after ibbackup finished its work. If ls -l reports that a .frm file was written later than the time ibbackup finished its work, then you may have to reconstruct the right .frm file manually using the binlog and the tar file taken before the backup. Hmm... I will add timestamps to the printout of ibbackup in version 1.03 so that you do not need to look at the binlog to determine the snapshot timepoint of the hot backup. Also, --restore will print the binlog file position of the snapshot so that you do not need to run mysqld to get that information. > 3. If anybody can give me specifics on what kind of queries and actions > will modify the .frm files that would also be useful. (I'm not all that > worried about ALTER TABLE and CREATE TABLE as the system is not dynamic > on that level, but DROP TABLE might be an issue.) ALTER TABLE, CREATE INDEX, DROP INDEX, CREATE TABLE, and DROP TABLE are the SQL commands, I think. > Regards, > -H Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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
