Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • Last Modified:

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
0
abenbow
Asked:
abenbow
  • 4
  • 3
1 Solution
 
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!
0
 
nicholassolutionsCommented:
here's a tutorial on this, using the same general approach
http://www.nicholassolutions.com/tutorials/mysql_backup.html
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
abenbowAuthor Commented:
thanks for replying

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


0
 
nicholassolutionsCommented:
do you have shell access? It might help to try that command from the command line and see what happens.
0
 
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.
0
 
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?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now