[MySQL] Reset & Import Database in MySQL using Command Line

Sometimes client’s server doesn’t has PHPMyAdmin installed (and it is 11MB, too big in my opinion), and I have to import database to deploy website from localhost to production server. Thus, I need to find a way to import SQL file directly in command line.

First, I tried PHP Mini Admin, which is lightweight (30kb) alternative to PHPMyAdmin. It is quite handy for dropping tables. However, when I tried to import SQL file of 4MB, it reported to be too big to upload.

The solution I used today is to upload SQL file via FTP, then import it using MySQL command line.

Import Database (SQL File) via MySQL Command Line

Go to the folder that contains SQL file we already uploaded. We can simply run this command to import:

mysql -u username -p database_name < file.sql

Note: This command don’t work with *.sql.gz, you will need to run this command first:

gunzip file.sql.gz

By importing this way, I didn’t find the size limitation message like PHP Mini Admin 😀

Reference: http://stackoverflow.com/questions/17666249/how-to-import-an-sql-file-using-the-command-line-in-mysql

Reset Database via MySQL Command Line

Unfortunately, this task is not as simple as in PHPMyAdmin where we can select all the tables and click ‘DROP’.

To really accomplish the same action as PHPMyAdmin, we have to use this command:

mysqldump -u[USERNAME] -p[PASSWORD] \
  --add-drop-table --no-data [DATABASE] | \
  grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \
  mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

The above SQL functions might look a little confusing. And it might not work well when copying 4 lines into Terminal.

Luckily, we can just delete the database and create new one (if we have permission to do that).

drop database [database name];
create database [database name];

In one of my client test website, we are running it on serverpilot. Therefore, we just go into serverpilot website to recreate the same name/username/password database.

Happy SQLing !

Reference: http://stackoverflow.com/questions/12403662/how-to-remove-all-mysql-tables-from-the-command-line-without-drop-database-permi


Also published on Medium.

Leave a Reply

Your email address will not be published. Required fields are marked *