Solved

# MySQL Imprt Help Needed

Posted on 2011-10-21
Medium Priority
283 Views
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
Question by:wantabe2
• 5
• 5
• 2

LVL 19

Expert Comment

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 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 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  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;  The line "FLUSH PRIVILEGES;" Reloads the privileges from the grant tables in the mysql database. 0 LVL 19 Expert Comment 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  0 LVL 11 Expert Comment 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 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 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

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

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

ID: 37007032
I continue to get syntax errors...
0

LVL 19

Expert Comment

ID: 37007054
0

LVL 15

Author Closing Comment

ID: 37007086
I just had to point to the correct directory where the restore file was...thanks
0

## Featured Post

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.
###### Suggested Courses
Course of the Month8 days, left to enroll