Solved

read mySQL dump file back into empty database

Posted on 2007-11-15
9
1,619 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
Six Sigma Control Plans

628 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