How to correctly backup Magento database using PHP

Question

The problem is when I use something like that to make a dump:
shell_exec("mysqldump -uuser {$backup_db} > {$sql_name}");

and then use this code when install store:
shell_exec("mysql -u user -ppass -h localhost {$dbName} < {$storeName}.sql");

it doesn't install properly - occured exception when open Magento:

    a:5:{i:0;s:206:"Error in file: "/home/vhosts/username/11300N_test/app/code/core/Mage/Admin/sql/admin_setup/install-1.6.0.0.php" - SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'admin_assert' already exists";i:1;s:1024:"#0 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/Resource/Setup.php(645): Mage::exception('Mage_Core', 'Error in file: ...')
    #1 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/Resource/Setup.php(421): Mage_Core_Model_Resource_Setup->_modifyResourceDb('install', '', '1.6.1.1')
    #2 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/Resource/Setup.php(327): Mage_Core_Model_Resource_Setup->_installResourceDb('1.6.1.1')
    #3 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/Resource/Setup.php(235): Mage_Core_Model_Resource_Setup->applyUpdates()
    #4 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/App.php(417): Mage_Core_Model_Resource_Setup::applyAllUpdates()
    #5 /home/vhosts/username/11300N_test/app/code/core/Mage/Core/Model/App.php(343): Mage_Core_Model_App->_initModules()
    #6 /home/vhosts/username/11300N_test/app/Mage.php(683): Mage_Core_Model_App->run(Array)
    #7 /home/vhosts/username/11300N_test/index.php(87): Mage::run('', 'store')
    #8 {main}";s:3:"url";s:28:"/11300N_test/index.php/admin";s:11:"script_name";s:22:"/11300N_test/index.php";s:4:"skin";s:7:"default";}

Also I should add that this doesn't work for Enterprise Edition only - CE stores will be installed OK using this code.
Also I should note that when I made dump manually (using export via interface of MyAdmin) - everything is OK for Magento EE. Thus it makes me sure that problem is in my mysqldump command.
I think there is some lack of flags or something like that in my linux-commands.

Answer 1

phpMyAdmin (I assume you are referring to it when you say MyAdmin) and other web based MySQL dumps generate complete insert lines in the backup file by passing the --opt flag (and sometimes others like -c to compress) to the dump command.

The command line mysqldump doesn't do this by by default.

If you use use:

shell_exec("mysqldump --opt --default-character-set=utf8 -uuser {$backup_db} > {$sql_name}");

It should work. I've only added the --opt and --default-character-set=utf8 flags.

FYI:

per the MySQL 5.1 Reference on mysqldump

" --opt This option is shorthand. It is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly."

and the --default-character-set=utf8 might not be necessary but won't hurt, and will force the dump to be in utf8 which Magento EE uses.

Answer 2

I think you just need to add drop table if exists...--add-drop-table to mysqldump as option