Q. How can I import a MySQL dumpfile into my database? My old hosting provider gave me data.sql file. I do have access via ssh to server. I'm using CentOS Linux 5 server.
A. You can easily restore or import MySQL data with mysql command itself. First you need to login to your system using ssh or putty (from Windows based system). For example:
Type the following command at the shell prompt:
$ ssh loginname@server.hosting.com
Now type following command to import sql data file:
$ mysql -u username -p -h localhost data-base-name < data.sql
If you have dedicated database server, replace localhost name with actual server name or IP address:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.hosting.com
$ mysql -u username -p -h mysql.hosting.com database-name < data.sql
If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- My 10 UNIX Command Line Mistakes
- 10 Greatest Open Source Software Of 2009
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
- Email FAQ to a friend
- Download PDF version
- Printable version
- Comment RSS feed
- Last Updated: 10/3/07
{ 21 comments… read them below or add one }
If you’re having troubles try removing the space between -u and the username (eg: -uusername), -p and the password (eg: -ppassword), -h and the host (eg: -h11.11.11.11)
Worked like a charm! I did have to remove the spaces as Cameron suggested (thanks). I’m running Fedora Core 7.
I’m so stupid ;)
I have decicated server and I was using all the time phpMyAdmin … that was terrible….
now I import DB in 2 sek :D
THX a lot for this very important for me command :)
Greetings, mosh
if you want to import an UTF8 database you can use this command
$mysql -u databaseuser -p -h localhost –default-character-set=utf8 databasename < sqlfile.sql
Thanks to Cameron James. Removing the spaces did it for me as well. Fought with that sucker for almost 4 hours…
thanx a lot for this script. and thanks cephalex for utf8 option.
can anyone help me how to start using php in linux?i already installed it but i do not know how to run it using command line.
and one more thing how to convert data from pcap to mysql?
please give me a hint on how to start the program
Very nice tut ! I just moving from another host to a new one. This commands works perfectly :)
Thx a lot ;) Very usefull tips
If Your Using Red Hat Linux,
take the $ out.
and try this,
mysql -u username -p -h localhost data-base-name < data.sql
Thanks
tnx … easy to understanc
go to mysql folder and execute this command:
[root@localhost mysql]# bin/mysql [Database Name] < /home/sandeep/software/[file-name].sql
I installed oracle 11g, but I want to know who do I create database table in it and also describe me about how to type command to see the database table in Sql.
I want to add a “login” feature to my website, that consists of an email address, password and a unique user ID. The feature should also be able to store the names, send an email verification reply, provide for emailing the user for forgotten passwords, etc.
plz replay me on my mail id.
I am totally new at mysql data base and php script.
bksondagar
thx a lot, it’s very helpfull for me.
Thanks alot for these. Its helped me out of a big jam that I was in..
mysql -u username -p data-base-name < data.sql
the above statement running but tables are not created in database
mysql -u username -p data-base-name < data.sql
gives error , can u help me
ERROR 1227 (42000) at line 500: Access denied; you need the SUPER privilege for this operation on win
How can I import a MySQL dumpfile into my database?
am using windows..
yeah, everywhere instructions how to import sql file when it is stored in web server, but nowhere when it is stored in desktop. absolutly no one give us command how to upload stupid mysql file from desktop to web server without phpmyadmin.
I use Joomla 1.0.x CMS, and they have plugin to export but there is no import button in plugin and I don’t have command.
Upload file to your server using ftp or sftp client. Once uploaded run above mysql command.