?
Solved

MySQL Imprt Help Needed

Posted on 2011-10-21
12
Medium Priority
?
283 Views
Last Modified: 2012-05-12
I have a .sql exported from a mysql database that is 14.1MB. I need to import it into another one of my mysql databases but the page times out. I changed the php.ini file to allow 30MB & restarted mysql but now I get the page can not be displayed. I desparately need to get this imported. Can someone help me? Thanks Please Help
0
Comment
Question by:wantabe2
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:xterm
ID: 37006586
If you changed php.ini, you'll have to restart Apache, not MySQL.   But if you're uploading your 14mb import over a slow link, you may want to increase the page timeout in php.ini as well (max_execution_time=X)

Can you not simply upload the file to the MySQL server, and then use the command line mysql to import it?

ie.
$ mysql -uuser -ppass databasename < file.sql
0
 
LVL 15

Author Comment

by:wantabe2
ID: 37006698
I've got the .sql file on the root of the C drive at this time. It is named mydb.sql

The database name is workdb

I am using WAMP & I have restarted all the services. I continue to get the "Internet Explorer Cannot Display the Webpage" when I try to import it via the GUI after I set the max_execution_time from 30 to 120 but the page times out imediatley after I hit the go button. If I try to import it via the MySQL Console from the mysql> line & use the code you provided then hit the enter button I just get the -> on the lext line like it is expecting me to type something else....
0
 
LVL 11

Expert Comment

by:maeltar
ID: 37006726
Ensure you create the database before doing the import..

At the command line, login to your mysql instance :

 mysql -u <username> -p <password>
Enter password:
mysql> create database <database name>;
Query OK, 1 row affected (0.06 sec)

mysql> exit
Bye

Open in new window


Now use the command shown by xterm

If you are using a web app (php etc) to access this database you will need to ensure you have a user set with the correct privileges to access the database..

Login to mysql
CREATE USER <username> IDENTIFIED BY PASSWORD '<password>';

GRANT SELECT,INSERT,UPDATE,DELETE ON <database name>.* TO '<username>'@'localhost';

FLUSH PRIVILEGES;

Open in new window

The line "FLUSH PRIVILEGES;" Reloads the privileges from the grant tables in the mysql database.




0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Expert Comment

by:xterm
ID: 37006750
The command I gave you was to run from the shell, not from the actual MySQL CLI prompt - I didn't realize you were running Windows, not Unix/Linux, so your command line is a little different.

But you should still be able to run my command from a DOS prompt like:


mysql -uuser -ppass databasename < C:\mydb.sql

Open in new window

0
 
LVL 11

Expert Comment

by:maeltar
ID: 37006776
Sorry was a bit slow in my reply and you managed to post before I did ....

Is your instance of apache actually running ?

In the WAMPP control panel is should display if it is running or not.

0
 
LVL 15

Author Comment

by:wantabe2
ID: 37006829
My syntax is wrong...now I'm trying to import the exported database named modb.sql

I'm typing in:

$ mysql -uuser root -ppass 12345 mo < modb.sql;

When I execute it I get the error:

"ERROR 1064 (42000) : You may have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$ mysql -uuser root -ppass 12345 mo <modb.sql"

I have the mosql.sql file that I exported on the root of C drive

Please HELP!! Thanks for any assistance!

0
 
LVL 19

Expert Comment

by:xterm
ID: 37006921
Okay, it should look like this:

$ mysql -uroot -p12345 workdb < modb.sql

Take out the user/pass stuff - that was just a placeholder to let you know to put in your own user/pass there.  And change workdb to whatever the name of your actual database is if its not workdb.
0
 
LVL 15

Author Comment

by:wantabe2
ID: 37006941
Does it matter which directory the modb.sql file I exported is locaed? Does it need to be on the desktop, root of C, or where?
0
 
LVL 19

Accepted Solution

by:
xterm earned 2000 total points
ID: 37007017
If the modb.sql file is not in the directory you're in then change the command to reflect where it is:

$ mysql -uroot -p12345 workdb < C:\path\to\modb.sql
0
 
LVL 15

Author Comment

by:wantabe2
ID: 37007032
I continue to get syntax errors...
0
 
LVL 19

Expert Comment

by:xterm
ID: 37007054
Please cut and paste your command, and your resulting syntax error here.
0
 
LVL 15

Author Closing Comment

by:wantabe2
ID: 37007086
I just had to point to the correct directory where the restore file was...thanks
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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

615 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