Import a Huge Database with MySQL Command Line in MAMP

Terminal window with a bunch of queries like "Query OK, 33 rows affected (0.00 sec) Records: 33 Duplicates: 0 Warnings: 0" listed

I needed to import a 500mb MySQL database today, and I didn’t want to mess with changing the max 32mb upload file size with phpmyadmin. The 32mb limit is due to having to upload and process the file, but if the file is on your local machine anyways, there’s no need to upload it. Let’s bypass the upload limit and import the database using the MySQL command line.

Start up MAMP, open terminal, and enter:

$ /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot

This will put you into a kind of MySQL shell. Now you can run a few basic commands to test it out.

## List all the databases
$ show databases;

## It outputs a pretty ASCII drawing for your tables
| Database                |
| my_database             |
| another_db              |
| etc_db                  |
3 rows in set (0.00 sec)

To import a new database, find the path to your MySQL database file, and run these commands:

## Create a new database if you haven't yet
$ CREATE DATABASE my_db_name

## Move into the database, kind of like the `cd` command to switch directories in terminal
$ use my_db_name

## Import the database
$ SET autocommit=0 ; source /path/to/database/my_mysql_database.sql ; COMMIT ;

Now you’ll see a bunch of queries whiz by. Once you see the command line ready for another prompt, you’re good to go!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: