Backup MySQL

Some time PHPMyadmin is not enough to restore mysql-backup. you have to many option adn configuration to restore file

- Max upload limit (modify in php.ini)
- Maximum post limit (modify in php.ini)
- Time execution limit
- and so on


This article describe howto restore the bigger file thank common setting permited.

1. Use PHP bigdump

• Download from here http://www.ozerov.de/bigdump.php
• Extact to your web directory
• Open the bigdump.php, dan modify some setting like database name, user name and password
  
• Browse using Firefox or your favorite browser (ie http://localhost/bigump.php
  
• Upload the file
• Then start to restore

note:
Your dump file probably contains a statement like this: SET character_set_client = @saved_cs_client; Bigdump is not able to restore settings saved at the beginning of the import process. However you can skip this error message by adding a comment setting $comment[]='SET character_set_client = @saved_cs_client;'; to the BigDump configuration.


2. Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

3. Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine
• Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

--


taken from : http://www.ozerov.de/bigdump.php and http://www.webcheatsheet.com/SQL/mysql_backup_restore.php

Dikirim dari suwidi.or.id dengan dukungan dev-NET(system)