Re: MySQL Backup advice
Hi Ware Adams, thanks for your answer. Here's another question: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? If yes, where can I find documentation about this? Thansk a lot. Andre On Wed, 3 Mar 2004, Ware Adams wrote: Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Andre MATOS wrote: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? Yes. The basic steps are: 1) Turn on the binary log in mysql: http://www.mysql.com/doc/en/Binary_log.html 2) Take a consistent point in time backup using InnoDB Hot Backup and the perl script ibbackup that InnoDB provides: http://www.innodb.com/manual.php#backup.myisam 3) wait until you need the backup 4) Restore the data set using InnoDB Hot Backup http://www.innodb.com/manual.php#restore 5) The restore will note the position in the binlog at which the backup was taken, use this position to apply the binlogs, also from: http://www.mysql.com/doc/en/Binary_log.html Obviously try this before relying on it. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Hi Andre, I think you are looking for this: http://www.mysql.com/doc/en/Binary_log.html Hope it helps, Ken - Original Message - From: Andre MATOS [EMAIL PROTECTED] To: Ware Adams [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, March 04, 2004 12:09 PM Subject: Re: MySQL Backup advice Hi Ware Adams, thanks for your answer. Here's another question: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? If yes, where can I find documentation about this? Thansk a lot. Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
