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

TIA
abenbowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thyminfoCommented:
Hi!
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!

Cheers!
nicholassolutionsCommented:
here's a tutorial on this, using the same general approach
http://www.nicholassolutions.com/tutorials/mysql_backup.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abenbowAuthor Commented:
ok

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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

nicholassolutionsCommented:
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

system(...)

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.


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

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.
nicholassolutionsCommented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.