Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions?

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname='mydatabase';
$toDay = date('d-m-Y');
    //exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost --single-transaction $dbname > D:\Sql_Backups/".$toDay."_DB.sql");
    //exec('mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname > D:\Sql_Backup/file.sql');
      exec ("mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" >
share|improve this question

closed as off-topic by Jack Douglas Jan 18 at 17:22

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

    
1st and 2nd exec are commented ?? –  Abdul Manaf Feb 25 '14 at 12:07
    
@Abdul Manaf there is 3 exec commends 3 are giving same result 0kb size file. –  Surendra Feb 25 '14 at 12:09
    
Your password ($dbpass) is blank, yet you're using the "-p" parameter. I'm not sure this will work. If there's no password, don't use the "-p" parameter. Also - it's single dashes for short versions of switches (-p, -h, -u) - not double dashes (--). –  Phil Sumner Feb 28 '14 at 15:12

4 Answers 4

up vote 0 down vote accepted

The reason you get a 0KB file for output is becuse of a syntax error in the mysqldump

You have this in the question

exec ("mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" > ...

Remove the extra dashes from -h, -u, -p

exec ("mysqldump --routines -h $dbhost -u $dbuser -p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" > ...

The first two commands you have commented out has double dashes when you spell out the option. That part of the syntax is correct.

OTHER THINGS TO CHECK

  • Make sure the you use the same slashes on the file. Also, check which one of the following works properly in PHP
    • D:/Sql_Backup/db3_backup.sql
    • D:\Sql_Backup\db3_backup.sql
    • D:\\Sql_Backup\\db3_backup.sql
  • Make sure mysqldump.exe is in the %PATH. If if is not, you make have to use the absolute path by doing something like this:

    exec ("D:\Program Files\MySQL\MySQL 5.x Server\bin\mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" > ...

share|improve this answer

I don't know how to write this in php, but you should write mysqldump command like below, there is no need to write three command one is enough

mysqldump --user=root --password=root --host=localhost --single-transaction --routines db_name > db_name.sql

UPDATE

I did it here is the php script

<?php
define("BACKUP_PATH", "/home/abdul/");

$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "world_copy";
$date_string   = date("Ymd");

$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

exec($cmd);
?>

It generate backup file named as 20140225_world_copy.sql in /home/abdul/

For reference have a look at This link.

share|improve this answer
    
i tried this one but no use i dont know where i did wrong.. –  Surendra Feb 25 '14 at 12:15
    
Check answer now –  Abdul Manaf Feb 25 '14 at 12:23
    
@Abduul same result getting 0kb file. –  Surendra Feb 25 '14 at 12:37
    
I can't say what you are trying but it working for me. are you getting any error ? –  Abdul Manaf Feb 25 '14 at 12:44
    
it not throw any error it jest giving 0kb file –  Surendra Feb 25 '14 at 12:52

You need to use the follwoing syntax :

Full dump with routines as well
mysqldump --routines > outputfile.sql


To dump your routines
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt > dumpfile.sql


-the option --routines is available from verion 5.0.13. -remember that the backup user need to have the rights to read the routines.

share|improve this answer
    
I am using mysql server version 5.5.36 and root have all rights But still getting same problem getting 0kb size file. –  Surendra Feb 25 '14 at 12:02
    
Try to do is separately, data dump , and ddl dum, and try to use a text pass, don't pass any params.! –  Up_One Feb 25 '14 at 12:09
    
@UPp_One i tried that but no use.. –  Surendra Feb 25 '14 at 12:13
    
Place a log on the output to see what is going on ! and posted it ! –  Up_One Feb 25 '14 at 12:16

You need find where the mysqldump.exe for backup and mysql.exe for restore is in your computer. Then copy the path to the code: Then create a backup folder to save the backup file.

For backup

$dumpfile"); ?>
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.