Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

importing large sql file to local LAMP (xampp) environment

I have used Linux to import a large mysql dump file (into a new database), but am new to how the process works in a local LAMP environment using xampp, as xampp does not support SSH. I've dowloaded the large_dump_file.sql from the Linux server to my local system. I'm using Windows XP and have used xampp to setup LAMP. I am able to access the local_database via phpMyAdmin, but the dump file is too large to import using that app. I'm trying to import the file via the command prompt, but so far with no success.

At the prompt:
cd ..
cd ..
cd xampp
cd mysql
cd bin

I've found that mysqlimport is used to import .csv and .txt files, and mysql is used to import .sql files, but can't find documentation as to whether or not to use the -u -p options so I've tried many variations of the command with no luck.

Also, do I need to change any config files on my local system?

Thanks.
0
mralston2010
Asked:
mralston2010
1 Solution
 
darren-w-Commented:
ho you need to cd into the mysql/bin folder and run a command similar to :

mysql -u username -p password database_name < filename.sql

example here:

http://www.velikan.net/import-sql-dump-file-to-mysql-database/
0
 
darren-w-Commented:
if you try logging into mysql like"mysql -u root -p" and use the password you have set to test first, before running the above command, ps you may not have a password set

so if this works the above coammand would be like

mysql -u root -p password database_name < filename.sql

if you omit the password in the command you should get prompted for it
0
 
mralston2010Author Commented:
Thanks.

I'm able to login to mysql via
mysql -u root -p password

and then I run
db_name < filename.sql

and get the error:
ERROR 1064 (42000): 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 'db_name < beta_20110303_11.sql' at line 1

I placed the dump file in my bin directory. Should it be somewhere else?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
darren-w-Commented:
run the command on the dos command line not within the sql command interface
0
 
Vimal DMSenior Software EngineerCommented:
Hai,

I have an good idea,

Just take the file db file from "xampp\mysql\data"  and put accordingly in the folder that you need

1) Windows xampp\mysql\data
2) Linux lib/mysql/
0
 
Beverley PortlockCommented:
@vimalmaria - your suggestion is reasonable if both machines have the same language, chararcter set, underlying OS, etc, etc, but to cross from from OS to another then I would regard the binary approach as unsafe. Exporting and reimporting as Darren-w- suggests is the safest route.
0
 
mralston2010Author Commented:
darren-w-:

If I run the command while not in the mysql command interface, it runs through the header and usaged, but doesn't do the import.

E.g. the output after running the command:
----------
mysql  Ver 14.14 Distrib 5.5.8, for Win32 (x86)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
...and more
-------------------------
0
 
darren-w-Commented:
Hi,

can I assume you have created the database that you are importing the sql dump file into and that the root account has full read and write permissions on the table?

Login into mysql as above, issue the command "show databases;" it will list the available databases, type "use your_database_name_here;"  and then issue the command' show tables;'  you should get a reply like "empty set.."

this will confirm that the database exists and you can gain access to it to view the tables.

Darren
0
 
mralston2010Author Commented:
Hi Darren

The database does exist and I've already imported the tables via phpMyAdmin (390). I verified that I could access everything via the standard commands (show databases, use db, show tables).

I'll keep doing some research as well. I guessing that it is some config file that needs to be modified.

Thanks.
0
 
TomunCommented:
The command line for mysql is not 'mysql -u root -p password' its 'mysql -u root -ppassword'

if there is a space after the -p then it assumes that's the name of the database you're connecting to. If you specify simply -p then it should prompt you for a password.

Can you get the command line client working now?
0
 
mralston2010Author Commented:
Thanks to all for your input. I found the solution by submitting to serverfault.  I didn't have the max_allowed_packet size large enough. It was set at 64M and my file was 840MB.
0
 
ee_autoCommented:
Question PAQ'd and stored in the solution database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now