Page 1 of 1

Backing up a MYSQL Database with PHP

Posted: Tue Apr 13, 2010 11:31 pm
by jballou
Hey everyone!

I've got a client who wants to redesign his site. I've done most of the hard work but I need to copy over all his files from his current host to his new host, including all his database stuff. The only problem is I have no access to a web panel interface for his database (like phpMyAdmin) that I can use to backup and download his database.

So I'm looking for a php script I can use to backup and download his database, table structure completely intact, preferably as a .sql file.

Any help?

Thanks!

Re: Backing up a MYSQL Database with PHP

Posted: Wed Apr 14, 2010 5:58 am
by roders
on your old database run the following script.

Code: Select all

$filename="dbbackup";
$pathtofile="/path/to/dir/".$filename.".out"; //where you want the backup to stored
$dbuser="username";
$dbpassword="password";
$database="databasename";
$connection="connectiontomysql";

exec("mysqldump -u".$dbuser." -p".$dbpassowrd." -h".$connection." ".$database."  > ".$pathtofile, $output, $res);
Then copy the file create onto you new database and run this script

Code: Select all

$pathtofile="/path/to/dir/".$filename.".out"; //where the old db backup is stored.
$dbuser="username";
$dbpassword="password";
$database="databasename";
$connection="connectiontomysql";
$con=mysql_connect($connection,$dbuser,$dbpassword);
mysql_query("CREATE DATABASE if not exists ".$database,$con);
exec("mysql -u".$dbuser." -p".$dbpassowrd." -h".$connection." < ".$pathtofile, $output, $res);
Just modify the where you need to match the connection and path to file and it will work.

Re: Backing up a MYSQL Database with PHP

Posted: Wed Apr 14, 2010 10:38 am
by jballou
Thanks for responding, but the code didn't work for me.

I noticed you had some undefined variables like $output and $res, I don't know if that has anything to do with it.

I'm not sure why it didn't work, there was no errors generated or anything.

Re: Backing up a MYSQL Database with PHP

Posted: Wed Apr 14, 2010 1:14 pm
by imadtchmn
If you have shell access you can use the following:
mysqldump -u dbuser -p dbname > dbname.sql
you will then be prompted for the password.
To add to your new database, create the database, user and permissions then add the information by using
mysql -u dbuser -p dbname < dbmane.sql

Re: Backing up a MYSQL Database with PHP

Posted: Wed Apr 14, 2010 1:38 pm
by Benjamin

Re: Backing up a MYSQL Database with PHP

Posted: Thu Apr 15, 2010 3:13 am
by roders
jballou wrote:Thanks for responding, but the code didn't work for me.

I noticed you had some undefined variables like $output and $res, I don't know if that has anything to do with it.

I'm not sure why it didn't work, there was no errors generated or anything.
Know this is not needed. This is the code that i use daily to backup my databases. Make sure you change the variable to match you need.
The first script is to be put in a php file then run on the server where the old db is.

Code: Select all

$filename="dbbackup";
$pathtofile="/path/to/dir/".$filename.".out"; //where you want the backup to stored
$dbuser="username";
$dbpassword="password";
$database="databasename";
$connection="connectiontomysql";

exec("mysqldump -u".$dbuser." -p".$dbpassowrd." -h".$connection." ".$database."  > ".$pathtofile, $output, $res);
Then locate the file dbbackup on your server shouldn't be difficult if you set the correct path in the variable $pathtofile. Copy from server 1 and move it to server 2.
After this run the second part of the script

Code: Select all

$filename="dbbackup";
$pathtofile="/path/to/dir/".$filename.".out"; //where the old db backup is stored.
$dbuser="username";
$dbpassword="password";
$database="databasename";
$connection="connectiontomysql";
$con=mysql_connect($connection,$dbuser,$dbpassword);
mysql_query("CREATE DATABASE if not exists ".$database,$con);
exec("mysql -u".$dbuser." -p".$dbpassowrd." -h".$connection." < ".$pathtofile, $output, $res);