restore MySQL using PHP and a cron job

I'm backing up a largish MySQL database (50 meg and growing) everynight using a php script and cron job. the resulting file is ftp'd to another server and I would like to setup another php script/cron job to load the dump file into a database on the new server.

Any tips / pointers / scripts ??

It has to be automatic, no intervention from me

Who is Participating?
here's a tutorial on this, using the same general approach
I assume that you are using mysqldump for backing-up. In any case, if you've a schema with INSERT statements, I suggest that you use 'mysqlimport', something like

$commandline = mysqlimport -u <db_user> -p<password> -h <hostname> -c <column list> --fields-terminated-by=<Terminator> --fields-enclosed-by=<Enclosure> --fields-escaped-by=<Escape> -i -L <databasename> <path_infile><inFile>
and then use the 'exec' function to execute 'commandline' in your CRON

You may want to be embedding the 'datestamp' in the file name for <inFile> and then verify that the 'new' file is available before you invoke the 'exec' function. In any case, ' -i ' option ensures that 'duplicates' are ignored, should the table have some 'primary' or 'unique' key.

Hope this helps!

abenbowAuthor Commented:

daft question time, I haven't got much hair left

the scond part of the tutorial gives sample PHP code for the import part.

Am I right in saying that I can modify it with my db details and then point my browser at it and it should work?

I ask because it's not, I get no errors even if I deliberately put in the wrong password for the db.

the php file is in the same directory as the database file and the file definitely exists
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

First, make sure that your dump file and the php script are in the same directory. Add your details to the second script (user, pass, actual database name and actual dump file name). If you're running php in safe mode, you will not be allowed to use exec, although you may still be abl to cron the script using

php /path/to/your/script.php

as the command

Exec does not automatically show the output. you could change it to


instead. But you may not see much output anyway...the real uestion is: did the database get imported? remember to create it first.
abenbowAuthor Commented:
thanks for replying

database is created, with empty tables. they are still empty after I run the import script.

do you have shell access? It might help to try that command from the command line and see what happens.
abenbowAuthor Commented:

after some tweeking I've got it to work on a 6 meg database. But it won't work on the daddy, 50 meg. is that purely a size issue ?

both databases are being restored to the same server but originate from different servers.
hmm, I'm not sure why that would be...50 MB is actually not that large in the grand scheme of things. If you can get it to work for your smaller database but not the larger one, I would tend to think that the problem is with the input file. Are you sure that the table structures are exactly the same and that you're using exactly the same procedure to make the backups?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.