Solved

Loading a dumped MySQL database

Posted on 2013-06-02
22
423 Views
Last Modified: 2013-06-03
See attached php. This is an attempt to load a dumped database into a new MySQL 5 database. The new database has NO tables as of now.

The file db2621629712013-06-02.sql was just produced using dbdump

When run, it produces the attached text file.

What's wrong?
dbrestore.php
dbrestore.txt
0
Comment
Question by:Richard Korts
22 Comments
 

Author Comment

by:Richard Korts
ID: 39214945
This might help. I was told that the host was "localhost/tmp/mysql5.sock".

I've never seen anything like that; maybe that's an error?

Is there a way to get an error message or number?
0
 
LVL 31

Assisted Solution

by:Frosty555
Frosty555 earned 100 total points
ID: 39214988
The TXT file contains the help text of the MySQL command line interface.

Typically, a command-line utility will output it's help text when the parameters it receives are invalid or syntactically incorrect.

One thing I can see that is wrong with your system() call is the "-p" parameter. There isn't supposed to be a space inbetween the "-p" and the actual password.

Also, check that $backupfile actually exists. It isn't an absolute path so you have to make sure your .sql file is in teh same directory as the executing php script.

So try it like this:
system("mysql -h $dbhost -u $dbuser -p$dbpass $dbname < $backupfile");


If that doesn't work, the first diagnostic step is to look at the actual command that is being executing and make sure it is valid. Do something like:

echo "EXECUTING:  " . "mysql -h $dbhost -u $dbuser -p$dbpass $dbname < $backupfile";

And look at what the results are. Maybe there's a typo in one of your variables or something else silly is going on that will become clear when you see what the actual command that was executed was.
0
 

Author Comment

by:Richard Korts
ID: 39215033
To Frosty555,

I did all that. When I got the parameters right, the page of text went away. But it still does nothing.

So I tried the echo "Executing" etc.

Everything looks right.

It's amazing that something so simple is SOOOOOO hard.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39215115
The dump file if it does not include use/create database directives,your first tasks to create the database
Create database databasename
You should have create table tablename directives.

Once you do tht using mysql on the command line

You can run
Mysql -u username -ppassword databasename <mysqldumpfiel.sql

Get phpmyadmin.

What or how did the dump of the database was made?
0
 

Author Comment

by:Richard Korts
ID: 39215129
To arnold

The dump file was made using mysqldump. If was run on a MySQL version 4 database.

I should have mentioned this earlier,

The whole project is to convert from MySQL 4 to MySQL 5 as the web host requires that by like tomorrow.

I created the MySQL 5 database earlier; it exists but no tables.

I looked in the dump file; it creates each table before it loads it.

Is there a compatibility issue with dump files between MySQL 4 & MySQL 5?
0
 

Author Comment

by:Richard Korts
ID: 39215132
To all.

I have phpmyadmin,. I have used it for years

I have been trying most of the day to do export / import. That doesn't work either (the dump file is like 280MB).
0
 
LVL 76

Accepted Solution

by:
arnold earned 400 total points
ID: 39215248
Does the mysqldump used for a single database backup or multiple?

You need to create the database.
Then use mysql -u username [-ppassword] databasename < dumpfile.

grep -i 'create table' dumpfile

You have to make sure your system as enough space and has the engines you used in the mysql 4.

Do you still have access to the old databse server?


It should take a while depending n whether you have binary logging (transaction) enabled.
0
 
LVL 7

Expert Comment

by:msifox
ID: 39215682
Maybe it's hitting a size or time limit. Try with one table instead of the whole database at once.
0
 

Author Comment

by:Richard Korts
ID: 39216306
We have a test database that is MUCH smaller. I will try that today.

But I don't think so; the MySQL command finishes in one second.
0
 

Author Comment

by:Richard Korts
ID: 39216316
To arnold,

It's the same server for BOTH databases; same host.

It's seems clear to me that something simple is being overlooked.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39216327
If it is on the same server, make sure you do not have a "use databasename;"
in the dump file if you are not restoring.

if you already have a mysql 4 database loaded, you need to run the mysql_upgrade to convert the mysql 4 database files to mysql5 files.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Richard Korts
ID: 39216369
Where is the documentation on mysql_upgrade? That would likely solve ALL problems.

Thanks
0
 
LVL 76

Expert Comment

by:arnold
ID: 39216388
Man mysql_upgrade
What is the issue that you are trying to resolve with the dump.
0
 

Author Comment

by:Richard Korts
ID: 39216535
I ran the upgrade. I have to do it from php.

So I did this: system("mysql_upgrade --verbose -h $dbhost -u $dbuser -p$dbpass");

It produces this. I have NO CLUE what this means:

Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--host=localhost' Running 'mysqlcheck' with connection arguments: '--host=localhost'

There are several databases. How can it know which one I'm trying to upgrade?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39216597
You need to run this on the command line in the shell. not sure why think you have to run it from PHP? going through PHP would mean you are bound by the webserver/etc. and may be using more resources than needed.
if you do not specify, it will check all and and repair/fix those that need to be fixed/repaired.
0
 

Author Comment

by:Richard Korts
ID: 39216616
To All,

Just to eliminate the possibility of size issues, I dumped our "dev" (developer) database; dump file is 23 MB (as opposed to 275 MB fr the "real" database.

Same problem; the load DOES NOT work.  It does NOTHING.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39216674
There are different ways to get this.  The issue that you are trying to do it via a web browser/php interface may suggest that your limits on the web browser could adversely interact with the issue.

usually when using PHP, you have to upload the file first into a location that php/web/apache has access to and which will be used as input to the command.
If you place a file in some directory in the assumption that running the PHP will get to the file and be able to load the data you are mistaken.  You likely do not have a check to see whether your PHP script can actually open the file for viewing. I.e. when you run the PHP code, the message from the file system is "Access denied" and the script falls through to the next command seemingly completing the task.
0
 

Author Comment

by:Richard Korts
ID: 39216792
To arnold:

Result of running the upgrade from the command line:

Note, I dummied out the password.

(uiserver):u51003339:~ > mysql_upgrade --verbose -h localhost -u dbo274353673 -pabc123 Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--host=localhost'
mysqlcheck: Got error: 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'
FATAL ERROR: Upgrade failed
(uiserver):u51003339:~ >
0
 
LVL 76

Expert Comment

by:arnold
ID: 39216813
You need to run mysql_upgrade with a higher level user
I.e. root.
The error you get deals with the user not having rights to the mysql database (emissions, configuration)
0
 

Author Comment

by:Richard Korts
ID: 39217432
To arnold,

We ran the db load from the command line.

Got this:  

OK - running . . .

 mysql --verbose -h localhost  -u dbo473251127  -pabcdef db473251127 < /homepages/46/d261861815/htdocs/dev/db2743536732013-06-03.sql

Gives me . . .

ERROR 1045 (HY000): Access denied for user: 'dbo473251127@localhost' (Using password: YES)

We had earlier tried it with the host just like the hosting company specified; localhost/tmp/mysql5.sock. It said invalid host.

I think it's time to give this up, the path we are going down is NOT productive & is wasting WAAAAAY to much time.
0
 

Author Closing Comment

by:Richard Korts
ID: 39217443
There is no "best solution". The problem is NOT solved.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39217453
What are the contents of the dump file?
On the command line you are not specifying the database which is being "restrored"
If there is no use directive in the dump file ... not sure whether the error you received is a consequence of a missing use directive.

The user you are using might not have rights to create a database, is you use the command
mysql -u username -ppassword databasename
are you able to login?
if so, you need to add databasename to the mysql command above while making sure that the dump  file you use does not have a "use thisdatabasename" directive.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

20 Experts available now in Live!

Get 1:1 Help Now