Guide to Backing Up and Restoring the Database


I recommend you look at using http://sourceforge.net/projects/automysqlbackup, to keep automatic backups of your webERP databases.

Its usage is simple and the documentation is provided.

Here are some notes on its usage:

Note: several automated MySQL backup programs use the 'mysqldump' utility to dump the tables. The web-based phpMyAdmin utility does not use 'mysqldump' and so avoids the following problems.

phpMyAdmin correctly dumps a INNODB database with foreign key constraints and allows easy restore.

As your database dump file gets bigger over time, you may not be able to use phpMyAdmin to restore the tables as you run into memory and timeout problems with Apache and PHP uploads. In this case you will need to use the command-line method as below.

But phpMyAdmin doesnt automate your backups. If you use automysqlbackup, or mysqldump, then you may face a problem.

mysqldump --opt, and automysqlbackup does not correctly dump a INNODB database with foreign key constraints. The restore will fail with an error 150.

Try the following to restore a database dump made in such a way (ie from automysqlbackup etc):

You can run this script from the command line, you do not need to dump/empty your database first.

Its always a good idea to make a quick temporary backup of your DB before you restore, just in case you make a typo error.

(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat your_database_dump.sql
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql --user=your_mysql_username --password=your_mysql_password your_weberp_database_name
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki