Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

read mySQL dump file back into empty database

Posted on 2007-11-15
9
Medium Priority
?
1,620 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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
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, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

664 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