abenbow
asked on
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
Any tips / pointers / scripts ??
It has to be automatic, no intervention from me
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
thanks for replying
database is created, with empty tables. they are still empty after I run the import script.
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.
ASKER
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.
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?
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=<Te
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!