Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Loading a dumped MySQL database

Posted on 2013-06-02
22
Medium Priority
?
431 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 300 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 81

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 81

Accepted Solution

by:
arnold earned 1200 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 81

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
 

Author Comment

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

Thanks
0
 
LVL 81

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 81

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 81

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 81

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 81

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 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