Backing up a MYSQL Database with PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jballou
Forum Newbie
Posts: 15
Joined: Mon Mar 08, 2004 2:34 pm
Location: San Francisco, CA

Backing up a MYSQL Database with PHP

Post 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!
roders
Forum Commoner
Posts: 68
Joined: Tue Oct 20, 2009 9:29 am

Re: Backing up a MYSQL Database with PHP

Post 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.
User avatar
jballou
Forum Newbie
Posts: 15
Joined: Mon Mar 08, 2004 2:34 pm
Location: San Francisco, CA

Re: Backing up a MYSQL Database with PHP

Post 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.
imadtchmn
Forum Newbie
Posts: 4
Joined: Wed Apr 14, 2010 6:42 am

Re: Backing up a MYSQL Database with PHP

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Backing up a MYSQL Database with PHP

Post by Benjamin »

roders
Forum Commoner
Posts: 68
Joined: Tue Oct 20, 2009 9:29 am

Re: Backing up a MYSQL Database with PHP

Post 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);
Post Reply