Use MySQL Backup Mod as transfer utility?

[rbobzin]

A client's store has a pretty large db (about 20MB). Backing it up (either with cPanel or phpMyAdmin) is no problem, but restoring it on my local box with phpMyAdmin won't work unless I break the file up into 5-6 separate files. (NOTE: I increased php's file size parameters to the max 32MB, but phpMyAdmin still chokes!)

Since one of the advantages of Dr. Byte's Backup MySQL mod is that it handles large databases, I thought I might use it to backup the "live" site, download it to my laptop and "restore" it there. Besides having to change the config files, are there any reasons why this wouldn't work?

Server details:

Production - Server OS: Linux 2.6.9-42.0.10.ELsmp
Database: MySQL 5.0.45-community
PHP Version: 5.2.5 (Zend: 2.2.0)
HTTP Server: Apache/2.2.6 (Unix)

Local: Server OS: Windows NT 5.1 build 2600
Database: MySQL 5.0.22-community-nt
PHP Version: 5.1.4 (Zend: 2.1.0)
HTTP Server: Apache/2.0.58 (Win32) PHP/5.1.4

Thanks!

[yellow1912]

There is php script that works extremely well when restoring huge database file, it's called BigDump, you can google for it.

[DrByte]

Since one of the advantages of Dr. Byte's Backup MySQL mod is that it handles large databases, I thought I might use it to backup the "live" site, download it to my laptop and "restore" it there. Besides having to change the config files, are there any reasons why this wouldn't work?

I use it frequently for this very purpose.

[rbobzin]

Hi, Doc! Based on your reply, I uploaded your mod to the production server, and backup worked beautifully. But when trying to restore the db to the same site on my laptop, got this error (with debug=ON):

Checking Path: c:/5/bin/.exe/
Warning Checking Path: c:/mysql5/bin/
Warning 
Warning COMMAND FILES SELECTED:
Warning mysqlexe="c:/mysql5/bin/mysql.exe"
Warning mysqldumpexe="c:/mysql5/bin/mysqldump.exe"

Warning COMMAND: "c:/mysql5/bin/mysql.exe" "--database=database" "--host=localhost" "--user=user" "--password=password" database < "c:/apache2/htdocs/site/admin/backups/db_database-20080102092929.sql" 2>&1
Error valueA: operable program or batch file.
Error valueB:
Warning Result code: 1
Error Failure: The database may NOT have been restored properly. Please check it carefully.

I did correct the path in the /admin/includes/languages/english/backup_mysql.php file as the readme suggested, but have no clue how to change the security permissions to allow "Internet Guest Account" to run cmd.exe. My laptop is Win XP, so could that be the problem? If yes, then please advise which option sounds better to you:

1. Make the permission changes on the laptop
2. Move the localhost site over to my desktop running Win 2000Pro and Xampp

Thanks!

[DrByte]

If you're running MySQL on your laptop, you should be able to use phpMyAdmin to do the restore.

Or you could run the mysql.exe command from a command prompt ... basically use all the parameters that you quoted earlier:

"c:/mysql5/bin/mysql.exe" "--database=database" "--host=localhost" "--user=user" "--password=password" database < "c:/apache2/htdocs/site/admin/backups/db_database-20080102092929.sql"

[rbobzin]

Thanks for the quick reply! phpMyAdmin always timed out - Fatal error blah blah blah - whenever I tried to restore any sql file over about 10-12 MB, which is why I wanted to use your mod instead. The db I'm trying to restore now is 17MB, and phpMyAdmin choked...again.

In the meantime, I installed the site files and your mod on my desktop's (Win2kPro) localhost (xampp), and got the same error as reported in the above post, so thought maybe the error isn't XP or Internet Guest Account related??? So, searched through the forums again, and found a new thread my earlier search didn't pick up which had this post from you:

To get around Windows' need to use quotes when handling paths that include spaces in the filename/path, simply edit line 23 from this:

 define('OS_DELIM', '');

to this:

 define('OS_DELIM', '"');

And that was the winner - restore worked perfectly!

So, anyone having similar problems as I described above, it seems to be an easy 2-step fix:

1. Correct the path for mysql.exe and mysqldump.exe in the /admin/includes/languages/english/backup_mysql.php file
2. Make the "define('OS_DELIM', '"');" change to /admin/backup_mysql.php file

Bingo!