Page 1 of 1

SOLVED:MySQL Database Backup

Posted: Tue Jul 13, 2004 7:53 am
by bluenote
Hi there,

i am administrating a PHP / MySQL based CMS. Until now, i have done the backup dumps of the database(s) manually - either via command line or with a tuned-up version of phpMyadmin. Yesterday, i was asked to automate this backup procedure. I thought abaout using CRON.

My Problem is: i need a working script for the job, and this script SHOULD NOT have any features like an admin area, thousands of include files or such. It just should dump the structure and the data of all my databases, add a "drop if exists" and store the .sql file on the server after getting

Code: Select all

5 0 * * * /usr/local/bin/php /usr/local/lib/BLUErdb-2.0/include/crontables/root/dump_this_or_not.php
For i dont have the time to code one by myself, i was searching for some code snippets... without luck! So does anyone of you know a ressource, or does anyone has such a script?

T.i.a.,
- bluenote

Posted: Tue Jul 13, 2004 8:21 am
by redmonkey

Code: Select all

<?php

exec('mysqldump --add-drop-table --create-options -u USERNAME -pPASSWORD DATABASENAME > DUMPFILE');

?>
I can't imagine how busy you must be not to be able to write a one line script.

You may need to check with your version of mysqldump for the specific options as they have changed over versions.

Posted: Wed Jul 14, 2004 2:06 am
by bluenote
Hi,

well, you know... sometimes you just don't see the solution, even if it is that simple! Of course i have the time to drop one line, but i was blocked - thinking about a 1000-line PHP script instead of a simple shell command. Thanx!

I have modified your command a little

Code: Select all

/usr/local/mysql/bin/mysqldump --add-drop-table --all --complete-insert -u root DBstaff &gt; /usr/local/lib/BLUErdb-2.0/data/DBstaff/DBstaff_test.sql
The line works absolutely perfect if inserted directly into the command line. Then, i have inserted it into 'dump_db.php':

Code: Select all

<?php

exec('/usr/local/mysql/bin/mysqldump --add-drop-table --all --complete-insert -u root DBstaff > /usr/local/lib/BLUErdb-2.0/data/DBstaff/DBstaff_test.sql'); 

?>
This runs also perfectly. Thanx again!

Greetings,
- bluenote

Posted: Wed Jul 14, 2004 7:17 am
by hawleyjr
How do you determine the following:

Code: Select all

'/usr/local/mysql/bin/mysqldump'

Posted: Wed Jul 14, 2004 8:09 am
by redmonkey
hawleyjr wrote:How do you determine the following:

Code: Select all

'/usr/local/mysql/bin/mysqldump'
That is the path to your 'mysqldump' executable. If the system is setup correctly then you should not have to specify the full path. If for whatever reason you do need to specify the full path you should ask your server admin .
bluenote wrote:I have modified your command a little
Yes, over different versions there have been some changes to the actual option switches passed to the executable. Ideally you need to check the docs specific to your MySQL version.

For what it is worth, I have used this method in the past to perform daily backups via cron and I used something similar to....

Code: Select all

<?php

exec('mysqldump --add-drop-table --create-options -u USERNAME -pPASSWORD DATABASENAME | gzip >  DUMPFILE-' . date('Y-m-d') . '.gz');

?>
.....which automatically dumps the database backup into a compressed file which helps conserve storage space if you are running backups on a regular basis and/or network traffic if you are transferring the backups to a different server. The compressed file's name also contains the date to ensure that I have some ability to roll back if needs be.

Posted: Wed Jul 14, 2004 9:28 am
by hawleyjr
Using the following code I was able to get the backup to work. However the or die() function is still called even though the backup was successful, or was it?

Code: Select all

<?php
exec('mysqldump --add-drop-table --all --complete-insert -h localhost -u USERNAME -pPASSWORD DATABASENAME > FULLROOTPATH/DBstaff_test.sql') or die('Did Not work'); 


//ALSO This caught me up for a few minutes:

//make sure the directory you are saving the file is chmod(777)

?>

Posted: Wed Jul 14, 2004 9:44 am
by redmonkey
hawleyjr wrote:However the or die() function is still called even though the backup was successful, or was it?
One way to tell if it was succesful or not would be to open the file it produced and look at the contents.

You should also look at the exec() function within the manual to see how to properly implement error checking from the command issued through exec. i.e. use the optional return_var within the exec function which will return the termination code from the command issued, this would be the more accurate way of determining if the command was succesful or not.