Solved

read mySQL dump file back into empty database

Posted on 2007-11-15
9
1,617 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count the days a record spends in a step 21 55
Generate Unique ID in VB.NET 21 100
Using SQL*PLUS issue with where statement with trunc function 3 34
SQL to JSON 14 30
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…

735 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