Import MySQL Dumpfile, SQL Datafile Into My Database

How can I import a MySQL dumpfile into my database? I'm using CentOS Linux 5 server. My old hosting provider gave me a data.sql file and I do have access to my Unix / Linux server via ssh. So How do I restore my data using command line over the ssh session?

You can easily restore or import MySQL data with the mysql command itself. First you need to login to your system using ssh or putty client.

Step #1: Upload File To MySQL Server

You can upload data.sql file using the sftp or scp command, enter:
$ scp data.sql vivek@example.cyberciti.biz:/home/vivek
The data.sql file will be uploaded to /home/vivek directory. Avoid using /tmp or Apache document directory such as /var/www/html as anyone can see your data on the remote server.

Step #2: Login To Remote Server

Type the following command at the shell prompt:
$ ssh loginname@example.cyberciti.biz
Replace example.cyberciti.biz with actual server name or an IP address.

Step#3: Import Datafile

Type the following command to import sql data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
In this example, import 'data.sql' file into 'blog' database using vivek as username:
$ mysql -u vivek -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.cyberciti.biz
$ mysql -u username -p -h mysql.cyberciti.biz 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

A Note About Creating A New Database and Importing Data

In this example create a mysql database called foo and import data from bar.sql.gz as follows:

mysql -u root -p -h localhost

Sample outputs:

mysql> create database foo;
mysql> exit;

Import bar.sql.gz:

gunzip bar.sql.gz
ls -l
mysql -u root -p -h localhost foo <bar.sql

You can also create a username and password for foo database using the following syntax:

 
mysql -u root -p -h localhost
 

Sample outputs:

mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@localhost IDENTIFIED BY 'YOUR-PASSWORD-HERE';
### allow access from 192.168.1.5 too ##
mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@192.168.1.5 IDENTIFIED BY 'YOUR-PASSWORD-HERE';
mysql> quit;

Page last updated at 4:37 PM, January 6, 2012.

Featured Articles:

• 20 Linux System Monitoring Tools Every SysAdmin Should Know
• 20 Linux Server Hardening Security Tips
• My 10 UNIX Command Line Mistakes
• Linux: 20 Iptables Examples For New SysAdmins
• 25 PHP Security Best Practices For Sys Admins
• The Novice Guide To Buying A Linux Laptop
• 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