MySQL Import Database
Written by Rudy Saturday, 01 September 2012 09:35
This tutorial walks you through a MySQL import database on Linux, this is non distro specific so should work on any version of Linux. The tutorial assumes you have already completed a backup of your database with mysqldump and have exported it to a file.
How to perform a basic MySQL Import of a Database
To perform the following MySQL import you will require shell (command line) access to your server and have permissions to the database you are attempting to import into, if you are in doubt and you are the admin it’s easier to use the root account for database imports. In the examples below I am using the root account to import databases.
The first option is by far the most easiest, transfer the database to the server login over SSH and import the database.
To transfer the MySQL dump file from your Linux server to the new MySQL server run the following command:
scp /path/to/database-backup.sql This e-mail address is being protected from spambots. You need JavaScript enabled to view it :/home/user/
mysql -u username -p -h localhost database-name < database-backup.sql
MySQL import sql.gz dump file
If your dump file ends in the extension .gz it has been compressed using gunzip compression you have two options, uncompress the database and then import it or uncompress and import it on the fly using a single command - both MySQL import examples are below:
To extract and then import use the following commands:
gunzip database-name.sql.gz
Then import the SQL database with:
mysql -u username -p -h localhost database-name < database-backup.sql
To uncompress and import at the same time (a lot faster) then use the following command:
zcat database-backup.sql.gz | mysql -u root -p database-name
Note, you must have created the database prior to running the import commands above on the new server, follow our guide here for a tutorial on MySQL create database.
Import database directly from the MySQL Shell
To import a mysqldump file from the mysql CLI, first change to the directory where your dump file resides in Linux and then execute the following:
Select the database you wish to import the dump file into with:
shell> mysql database-name
Import the database with the following command:
shell> source database-backup.sql
You should now see a verbose output scrolling up the page as the db data is imported. This is my prefered method of importing large MySQL databases, you can get a rough idea of progress and if the import fails you can normally see the error caused. (FYI, in my experience most MySQL imports fail due to corrupt database exports from PHPmyAdmin, caused by the php.ini max memory usage being to low to complete the dump via the web interface).
