dump mysql database via php

[Billy T]

Hey all

I'm looking for a way to dump a mySQL database via php. I found

mysqldump

but this appears to just be a command line thing...I need a query so I can run it via php. I just need to be able to put the data and structure of my db into a text file or something.

I also need to be able to go the other way (ie restore a db's data from a text file containing an previous dump)

Any tips?

Thanks

[CyanBlue]

Well... Simply run that mysqldump command in shell_exec() or exec() to dump the data???I need a query so I can run it via php.I don't think you can run a query to get the whole database structure and data in it... I could be wrong though...I just need to be able to put the data and structure of my db into a text file or something.mysqldump command basically creates the text file that contains sql commands... So, I guess you can run them back when you need to restore the database...

Just my 2 cents from the MySQL dummy... ;)

[freddycodes]

You are both correct. mysqldump is a commandline tool for dumping structure and/or data into a external file. You can run it as CyanBlue said through exec(), but what is the purpose of this script you are writing? Is it for backup purposes? Is it for portability/distribution purposes? Do you have ssh access to your server?

[Billy T]

Thank you both

no I don't have SSH

The purpose is to backup up a whole sites data (db, images etc) put them in a zip and email it to someone. This is so they can restore the backup later on if they **** something up (using my cms thing)

I'll have a play with exec and will let you know how I go

Thanks again

[CyanBlue]

Um... Just my 2 cents here... ;)

I've let the crontab take care of the MySQL database backup every 3 hours, and have it mailed once a day... So, basically I have 8 backup files stored into the server, and I get one copy everyday...

If I ever need to restore the database, I'd find the latest one from the server, and if the database backups in the server are not available at any reason, I'd use the copy that has been sent to me one day earlier...

How does that sound, Billy T???

I know that you do not have SSH which is SHAME(:p), but you should have something like cPanel that you still can do the crontab... Check it out... ;)

[Billy T]

hmm no luck so far

$dir=dirname(__FILE__);
exec("mysqldump -h localhost -u me -p mine -l --all-databases > $dir/mysqlbackup.sql");

creates the file and emails it to me but the file is empty

any obvious mistakes?

Thanks

[Billy T]

Originally posted by CyanBlue
How does that sound, Billy T???


thanks cyan but I dont think it will really be applicable in my case...I need to back up other data too (images etc) so doing that regularly would lead to my server filling up very quickly. Also, I would rather just provide an interface for it and let the client worry about it...

[CyanBlue]

I tried with this line and it was creating the file...exec('mysqldump -uusername -ppassword -l --opt databasename > mysqlbackup.sql');But I wasn't able to get it working with '--all-databases' parameter though...

[Billy T]

damn...doesnt work for me...maybe my host doesnt allow it...I've emailed them

Thanks mate

[CyanBlue]

Hm... I have no clue why it is not working... :(

I guess this has nothing to do with the file permission since you are able to create empty file...

When I tried it with your --all parameter, there was some error message in the text file, but it is not your case either... :(

Yeah... Maybe the shell command is not available to you... Check to see what the host says...

[Billy T]

I can run

exec("tar -zxvf " . $fichier_name)

to unzip a file so I'm not sure...will let you know what I hear from the host

Thanks again

[freddycodes]

Well chances are since tar is a fairly often used tool its in the user who runs the web server's env path. mysqldump may not be and very well could be outside the path of the user.

What does all this mean, well in short the user your hosts drives the web server with may not be able to find or use mysqldump even though it can use tar just fine. Or you may need to specify the complete path to mysqldump, which your host will need to provide.

[Billy T]

yeah i saw something about providing the full path...will see what the host says

thanks mate

[Billy T]

on the money as always freddy

final working code was

exec("/usr/local/mysql/bin/mysqldump $dbname -u $dbUser -p$dbPass > $dir/mysqlbackup.sql") or die('Cannot execute command.');

Thank you both for your help on this

[Billy T]

is it possible to specify a target for an

exec("tar -zxvf

command?

This is driving me nuts...I've uploaded the archive to the parent directory of my script but when I run the exec command it keeps decompressing the archive into the same folder as the script!!

:(

Any ideas

Thanks

[Billy T]

chdir ("../");

did the trick

cheers

[Billy T]

*groan*

ok its decided to stop working

this is my code

$dir=dirname(__FILE__);
exec("/usr/local/mysql/bin/mysqldump $dbname -u $dbUser -p$dbPass > $dir/mysqlbackup.sql") or die('Cannot execute command.');
echo "$dir/mysqlbackup.sql";

It executes without errors and the path that gets echoed looks fine but when go there the 'mysqlbackup.sql' cannot be found (and therefore doesnt get archived and sent in the email that I send)

any ideas? was working yesterday... :(

when I use chdir like in the previous post, it doesnt do anything permanent to php on my server does it? that line is not included in this script but I'm clutching at straws here...

[Billy T]

crazy...

just had to change

>

to

>

and it worked...

This wouldnt be so strange except for the fact that the other day I had to change

>

to ****ing

>

to get it to work...

[Billy T]

for anyone else wanting to do the same thing, my final code was

Dump:

exec("/usr/local/mysql/bin/mysqldump --add-drop-table $dbName -u $dbUser -p$dbPass > $dir/mysqlbackup.sql");

Restore:

exec("/usr/local/mysql/bin/mysql $dbName -u $dbUser -p$dbPass < $dirname/mysqlbackup.sql");

cheers

[buggedcom@work]

Hi.

Apologies for being stupid, but is this a php line of code, or the line of code you enter into the cron job command?

also is the $dir value the path value from the public html folder or the server root?

[Billy T]

exec() is used to excute command line thingies from php

$dir is a var that is holding the directory of the script

$dir=dirname(__FILE__);

cheers

[buggedcom@work]

cheers billy. so how do you get the cron jobs to run the php script? do you just reference the file?

[freddycodes]

Correct me if I am wrong Billy, but that is not run by a cron job, that is just a manually system call.

In order to make it a cronjob, you need to make an entry in your crontab file.

[Billy T]

I dont know anything about cron jobs

thats just a line in a regular php script

cheers

[buggedcom@work]

to putting this in a php file

<php
// with the vars
exec("/usr/local/mysql/bin/mysqldump_--add-drop-table_$dbName_-u_$dbUser_-p$dbPass_>_$dir/mysqlbackup.sql");
?>

then putting the script at

https://www.mySite.com/admin/db_backup.php

i could create the crontab

0 */6 * * * lynx -dump https://www.mySite.com/admin/db_backup.php

to call it every 6 hours? yes :) ? no :( ?

[CyanBlue]

If you've got the crontab, why not just call it directly???
I don't see any reason why you should call the PHP script that calls the shell script??? ;)0 */6 * * * /usr/local/mysql/bin/mysqldump --add-drop-table tableName -uuserName -ppassword > mysqlbackup.sqlAh... Lynx... It's been a long time to hear that... :D I always used wget but I guess you can use lynx to do the same thing, eh???

[freddycodes]

I agree with CyanBlue, for mysqldump there is no reason to involve php.

wget is for retrieving remote files, lynx is a text only browser.

[Billy T]

on another host now - how can I find the path to mysqldump? Do I have to ask them?

Thanks

[CyanBlue]

Do the find from the root directory like this...

find -name mysqldump

and that should yield out the location...

If not, ask the host... :)

[freddycodes]

I would prefer to use locate, but thats just me,


locate mysqldump



Or which


which mysqldump


Or if you can't find it, look for the mysql executable, and look in that folder. If the location databases haven't been updated in a while.

[CyanBlue]

Hehe... I guess locate is not my choice because my host does not have 'locate' command... (Don't know why...)

[Billy T]

hmm seems I dont have telnet access on this account

thanks anyway gents - handy to know

[CyanBlue]

Get a new host, Billy T... :)

[Billy T]

;)

not my host its a clients...and they responded to my query within 18 minutes so that's just as good as telnet to me ;)

[CyanBlue]

Yeah... 18 minutes... Pretty much the same speed as telnet... Can't argue with that... :)

[freddycodes]

telnet is very insecure Billy, did you ask them if they offer any sort of shell access like SSH?

[Billy T]

Yeah... 18 minutes... Pretty much the same speed as telnet... Can't argue with that... :)

ahhh shaddup :p

freddy - I tried connecting via telnet and ssh and when I log in it says something like 'Shell access is not enabled on your account!
If you need shell access please contact support.' and for this job its not worth bothering them.

you dudes need to get your booties over to this thread - http://www.actionscript.org/forums/showthread.php3?p=305265#post305265

;)

[Billy T]

ok I'm seriously close to tears right now so go easy on me

trying to get mysqldump to work on a PC's local server using the code I posted earlier in this thread. The .sql file gets created but its always blank. I've tried every conceivable combination of -h localhost -u myfinguserName etc but cant get the damn data

Please please please put me out of my misery

*sob*

[CyanBlue]

Would that be the problem with the path to the mysqldump??? (It's not if you see the new file getting created unless you are using the pipe when it will create the empty file even though the file did not get executed...)

Anyways... That's the first thing I'd try...

Oh... Can you post the code again??? I saw the code on page 2 or something but that path is set to the Nix format... :)

[Billy T]

when you say 'using the pipe' do you mean am I smoking crack? if so, then 'yes', but I don't think that's the problem

code:

exec("/usr/local/mysql/bin/mysqldump --add-drop-table $dbName -u $dbUser -p$dbPass > $dir/mysqlbackup.sql");

pretty sure the path is correct...when I try other paths it tends to crash the whole comp...

Thanks for your help

[freddycodes]

Billy man, I am afraid its your syntax, and possiblky that file is left over from a long time ago. Or from some other attempt.


exec("/usr/local/mysql/bin/mysqldump -u $dbUser -p$dbPass --add-drop-table $dbName > $dir/mysqlbackup.sql");

[Billy T]

really? what I have works fine on my host just not on a local server (PC)

I tried mixing up the order and I'm sure I tried

exec("/usr/local/mysql/bin/mysqldump -u $dbUser -p$dbPass $dbName > $dir/mysqlbackup.sql");

but that didn't work either...will try it again tomorrow (don't have the comp here now)

Thanks for your help

[Billy T]

hmmm sql file is still blank

any other thoughts? wouldn't be a folder permissions thing would it? I mean, the file IS being created...

Thanks

[Billy T]

hmmm starting to thing the path to the mysqldump is wrong - what else could it be?

Thanks

[Billy T]

got it

C:\www\mysql\bin\mysqldump

thanks gents

[madgett]

Had the same issue...I don't know why relatives paths have so many issues with these things...:(, glad you got it working :)

[freddycodes]

One thing you could do is to add the path to the mysql bin directory to the PATH Env Variable. Then you could just use mysqldump. Like you can on unix platforms normally.

[Billy T]

yeah good idea

I'll do that

Thanks freddy