Backing Up Innodb table with individual tablespaces
If I use innodb_file_per_table, how do I properly back-up that table so it could be imported to another server should something happen to my main server if I do not have the hot-back-up utility? If I lock then copy the table, can I simply use ALTER TABLE tbl_name IMPORT TABLESPACE for it to work with another mysql server? Thanks for any help. Best Regards, Andrew
Re: Backing up InnoDB MySQL DB
Victoria Reznichenko wrote: ColdFusion Lists [EMAIL PROTECTED] wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? You can use mysqldump utility that comes with MySQL distribution: http://dev.mysql.com/doc/mysql/en/mysqldump.html There is also non-free InnoDB Hot Backup program: http://www.innodb.com/order.php Some info about backing up InnoDB database find at: http://dev.mysql.com/doc/mysql/en/Backing_up.html Also, as has been suggested on this list before, you can create a replication slave of your database servr, and do the backups on that slave. This requires extra disk space, and probably an extra box, but it means you can try different backup schemes without putting much extra starin on your main database server. Quite useful in high-load production environments. http://dev.mysql.com/doc/mysql/en/Replication.html We are using this with a script that just stops the slave DB and makes a copy of the data directory. It makes a rollback very fast. Additionally, this second DB machine can be a hot backup for your main DB machine, should something go wrong. MySQL requires master servers to have log-bin enabled. You will have to figure out how to get rid of the bin-logs from the master machine, or else they will keep growing indefinately. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing up InnoDB MySQL DB
Hi all we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? Thanx for your time. Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
Re: Backing up InnoDB MySQL DB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 May 2004 02:34 pm, ColdFusion Lists wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? If you can afford to shut it down, just stop the db, and copy (and zip) the db files.. If you can;t, you have to buy the mysql innodb backup tool from mysql.com.. (I think its from there.. ) The hotbackup tool it pretty automatted from what I hear. Jeff - -- Mercifully free of the ravages of intelligence. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtPLuld4MRA3gEwYRAviTAKDbVUPLWpKPRVavRL62o1LBnFIGiQCfYxJm TFerdkznCkX3vo4Qqetk+Oc= =l0ew -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up InnoDB MySQL DB
ColdFusion Lists [EMAIL PROTECTED] wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? You can use mysqldump utility that comes with MySQL distribution: http://dev.mysql.com/doc/mysql/en/mysqldump.html There is also non-free InnoDB Hot Backup program: http://www.innodb.com/order.php Some info about backing up InnoDB database find at: http://dev.mysql.com/doc/mysql/en/Backing_up.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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?) 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. 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.) Regards, -H Existentialism lead to nihilism. Nihilism lead to dancing. - Alara Rogers - 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
Re: Backing up InnoDb
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
