MySql Dump Using a PHP Script

When you need to take a backup of a large MySql database, then “mysqldump” command come most handy and does the task in few seconds. But for that we need to access the web server or database through SSH. And database access in 99% cases is not allowed from remote location for security reasons.

I found myself in such situation recently and PhpMyAdmin export feature was not working due to script timeout or memory issues. So I googled for this and after 15-20 minutes of searching I found this very useful and working small script for the job.

$User = ""; // Put New user -- CPanel user or MySQL user with All permissions is fine.
$Password = ""; // Put New Password
$DatabaseName = ""; // Put Database name
$File = ""; // Put the complete path here -- /home/user/database.sql for example
$Results = shell_exec( "mysqldump --allow-keywords --opt -u$User -p$Password $DatabaseName > $File");

Just upload the file on the web server and execute it. Do remember to specify the path of the file so that it is accessible from the web e.g. provide any path which is inside public_html or www folder on most servers.

It will take just few seconds for 300-400MB database and then you can download the file and use it as you like.

2 thoughts on “MySql Dump Using a PHP Script

  • Above mentioned command is working but create blank file on remote server.
    Written code is as follows:

    $host = “localhost”;
    $user = “user”;
    $password = “password”;
    $backup_path = “path/test.sql”;
    $db_name = “test”;
    //system(‘which mysqldump’);
    $Results = shell_exec(“/usr/bin/mysqldump –allow-keywords –opt -u$user -p$password $db_name > $backup_path”);
    echo “table backed up successfully”;

    • If file is being created, then the command is being executed and path details are correct. Please double check if the user and passwords are correct and the user has the permission to access the database. Also please check that the parameters are followed by two dashes (–), as given below:

      mysqldump –allow-keywords –opt -u$User -p$Password $DatabaseName > $File

      And check that the path to mysqldump is correct.

      To confirm if the command is working or not – you can try executing the above command at UNIX shell prompt. Let me know if this is still not working.

Leave a Reply

Your email address will not be published. Required fields are marked *