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
    
1st and 2nd exec are commented ?? –  Abdul Manaf Feb 25 at 12:07
    
@Abdul Manaf there is 3 exec commends 3 are giving same result 0kb size file. –  Surendra Feb 25 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 at 15:12

3 Answers 3

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 at 12:15
    
Check answer now –  Abdul Manaf Feb 25 at 12:23
    
@Abduul same result getting 0kb file. –  Surendra Feb 25 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 at 12:44
    
it not throw any error it jest giving 0kb file –  Surendra Feb 25 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 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 at 12:09
    
@UPp_One i tried that but no use.. –  Surendra Feb 25 at 12:13
    
Place a log on the output to see what is going on ! and posted it ! –  Up_One Feb 25 at 12:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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