Usually i use phpMyAdmin to configure, change and import/export my databases. However, recently, one of my databases went so big that it i have some problems to export or import it. The solutions is doing it with the Linux shell.

Linux shell can be very painful but can be the solution either. Using it to import or export MySQL databases can be a better and easier way. With shell we can backup all our databases and download only one file, or we can set up a cron job that do backup according to a defined schedule. Another major advantage is the possibility of import large SQL dump files (the phpMyAdmin has a limit).

In this post i’ll explain out to import or export MySQL databases using mysql and mysqldump respectively.

Import MySQL database

Like i said, using the shell we can import larger files, making easier to restore a database or import it from another MySQL server (easy server migration).

First we have to put the file on the server where it will be accessed. This can be done throw FTP or other method.

When the file is in server, run the following command (replace the italic words by your values):

mysql -u username -ppassword database_name < file_name.sql

Or

mysql -u username -p database_name < file_name.sql

In both ways we define the user, the database and the file that has the data. In the second command, instead of define the password in the command, the password will be prompt later.

Export MySQL database

To export a MySQL database is almost the same. This command can be very helpful for schedule database backups. Use the following command (replace the italic words by your values):

mysqldump -u username -ppassword database_name > file_name.sql

The result of this execution is a file_name.sql file that holds your database information. This backup can them be compressed to be smaller, downloaded or stored somewhere else.