Solved

read mySQL dump file back into empty database

Posted on 2007-11-15
9
1,612 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
in case it helps, apparently you can get the actual exit status from the system command like this:

$exitStatus = system('command')/256;
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:Adam314
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now