How to Import From .SQL in MySQL

Use a SQL export file to transfer a database between MySQL servers.

You can take a backup of a MySQL database using the mysqldump utility, which creates a text file containing a list of SQL instructions needed to reconstruct the original database. This text file can then be stored to provide a backup of the data, or used to transfer the MySQL database to a new MySQL server. Use the mysql program at the command line to restore from the SQL text file, providing adequate user permissions and the name of the empty database that will hold the restored database.

Instructions

1. Log in to the remote server through SSH, or open a terminal prompt if you are in the same physical location as the server.

2. Open MySQL at the command prompt by typing:

   mysql -u username -p

   Replacing "username" with a user account that has the necessary permissions to create the database and import the data. Type in the password when prompted.

3. Type the command below at the MySQL prompt to create a new database to store the imported data:

   create database db_name

   Replace "db_name" with the name of the database to create.

4. Type "quit" at the MySQL prompt to close the MySQL command line.

5. Type the command below at the terminal prompt to import the data from the SQL file, and insert in to the new database:

   mysql -u username -p db_name < filename.sql

   Replace "db_name" with the name of the database created to store the data, and "filename.sql" with the name of the SQL dump file. If the dump file is large, the data may take some time to reconstruct.

Tips & Warnings

• Write a script to run the mysqldump command and back up the database. You can then run the script through cron to make a daily backup, which could even by automatically to another server to create an off-site backup.

References

• MySQL: Mysql -- The MySQL Command-Line Tool
• The Geek Stuff: Backup and Restore MySQL Database Using mysqldump; Ramesh Natarajan; September 2008

Resources

• MySQL: mysqldump -- A Database Backup Program
• Dev Shed: Backing Up and Restoring Your MySQL Database; Vinu Thomas; June 2004