Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Re: Restore Database With DBI?

by Marshall (Abbot)
on Oct 01, 2011 at 03:31 UTC ( #928989=note: print w/replies, xml ) Need Help??

in reply to Restore Database With DBI?

This page may be helpful: Using MyQSL in batch mode.

There are arguments to the mysql command line utility like: > mysql -e "source batch_file", where batch_file is a file with SQL batch commands in it. So what you may need to do is something like... system("mysql -e source $db_load_file");?

In other words, there are things that the mysql command line utility can do that are outside of the SQL spec and you might just need to use Perl to execute a shell command rather than using DBI.

Does this not work because DBI doesn't really encapsulate all the commands of the mysql command line? That could be right! If so, just run the shell command from Perl. Once the DB has been created, fire up DBI, connect to it and away you go!

This part: That would make sense but then how can I accomplish this without scp'ing the file over and running a system command via ssh? does confuse me as you say that the file is already in both locations already! But with the mysql command line, you can log-on to a remote host with the -h option and use your local version of "batch_file".

shell> mysql -h host -u user -p < batch_file Enter password: ********
So I would suggest reading the link I posted above, get what you want to have happen working manually with you typing the shell commands in, then if you can't get Perl to do everything you want in an "automagic" way, check back in and I'm sure you will get lots of help on how to get the last 10% working!

Replies are listed 'Best First'.
Re^2: Restore Database With DBI?
by kurt2439 (Sexton) on Oct 03, 2011 at 16:23 UTC

    Thanks for all the input guys. I ended up running the mysql command on the remote system via the perl system() command as you guys suggested. Getting it all done with native perl code wasn't necessary.

    The only trick to getting the system() call to mysql to work as a scheduled task was to use the .my.cnf file in the calling users home directory to specify their sql user, pass and target database without being prompted. Works great!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://928989]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2018-04-21 16:15 GMT
Find Nodes?
    Voting Booth?