Solved

read mySQL dump file back into empty database

Posted on 2007-11-15
9
1,614 Views
Last Modified: 2010-04-21
Hi, I'm having a heck of a time importing a mysqldump file back into my database using Perl.

documentation and Google searches suggest this:
system "mysql -uUSER -pPASS  DBNAME < source $prodFilename";

but this doesn't work. I have had problems with system() before, and have resolved it by separating all parameters using commas, like this:
system("mysql", "-uUSER",  "-pPASS", etc....);

however, the in-file operator "<" doesn't appear to work in this context

from the command line, I'm able to use the following command:
$ mysql -uUSER -pPASS -e "source 07-11-15_TEST.sql" DBNAME
and it does what I want

But when I translate this into a system() call:
system("mysql", "-uUSER",  "-pPASS", "-e \"source $prodFilename\"", "DBNAME");
I get an SQL error - it doesn't like the "source" command for some reason

I've tried going another route, using DBI:
$sth = $dbh->do("LOAD DATA INFILE '$prodFilename'");
but I get a mysql error on the file name.

Any help would be appreciated!

Tom
0
Comment
Question by:tomaugerdotcom
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 20292321
Have you tried like this:
system("mysql -uUSER -pPASS -e \"source 07-11-15_TEST.sql\" DBNAME");

What OS is this on?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 20292326
Sometimes I've found that the path of the program you're trying to run needs to be specified. ie Try:

system '/opt/mysql/bin/mysql -uUSER -pPASS -e "source 07-11-15_TEST.sql" DBNAME';

Substituting /opt/mysql/bin for the path that mysql resides in.

It sometimes helps simplify things if you get the command working with a hard-coded filename before trying to use a variable in the command too...
0
 
LVL 14

Author Comment

by:tomaugerdotcom
ID: 20292449
The full path thing didn't work, and neither did hard-coding the file name. I'm getting no error in my error log at this point, but the db is empty.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 20292775
in case it helps, apparently you can get the actual exit status from the system command like this:

$exitStatus = system('command')/256;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:Adam314
ID: 20292943
Try providing the full path to the .sql file:
system '/opt/mysql/bin/mysql -uUSER -pPASS -e "/path/to/source 07-11-15_TEST.sql" DBNAME';
0
 
LVL 13

Accepted Solution

by:
marchent earned 500 total points
ID: 20296080
assume your sql file is sql.sql

system("mysql -uURER -pPASS DBNAME < sql.sql");

if the sql file path is at different location then use

system("mysql -uURER -pPASS DBNAME < /mypath/sql.sql");

~marchent~
0
 
LVL 14

Author Comment

by:tomaugerdotcom
ID: 20834764
Sorry I've been absent from this thread. None of these solutions have borne any fruit at all.

I've tried every combination and permutation and this just doesn't seem to be working at all. I think I really need to figure out what the error is - I assumed it was a pathing error, but it's starting to look fishy.

Here's how I'm dumping the DB:
my $prodFilename = "$backup_dir/PROD/$year-$mon-$day" . "_PROD" . ".sql";
system ("mysqldump",  "-uuser",  "-ppass", "-ldatabase", "-r" . $prodFilename) == 0 or generateMsg("Could not back up the live environment");

So it stands to reason that to read it back in:
my $exitstatus = system("mysql -uuser -ppass dbname < $prodFilename") / 256;
... or some other variant ought to work in reverse.

But it's not.

any additional thoughts would be very much appreciated. Otherwise, can anyone suggest an alternate way of re-importing a database from an sqldump file?

thanks
0
 
LVL 14

Author Comment

by:tomaugerdotcom
ID: 21699931
Well, after what - 7 months it's now working. I really don't get it because it looks like it's exaclty the solution I posted in February.

$rslt = system("mysql -uUSERNAME -pPASSWORD DBNAME < $prodFilename") / 256;

I'm closing this sucker.

T
0
 
LVL 14

Author Closing Comment

by:tomaugerdotcom
ID: 31409424
Not quite sure why this didn't work the first time around. But now that I review your answer, it's exactly what I have that is currently working.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now