Importing Large MySQL Database with XAMPP

September 24, 2014

Categorized: Webdev 101, Workflow

Even locally, phpMyAdmin has a size limit to the database you can import. You can change those limits, I’m pretty sure, but you can also bypass phpMyAdmin altogether and import the database with the XAMPP shell.

  1. Open the XAMPP shell (“Shell” button on your XAMPP Control Panel).
  2. Type:
    mysql -h localhost -u root database_name < J:\path\to\database.sql
    1. Change database_name to the name of the database you want to import to.
    2. Change the \path\to\database.sql to the appropriate path and filename.
    3. I'm working locally with XAMPP so I don't set a password on my root MySQL user (this is default behavior in XAMPP -- if you haven't gone looking for a way to set a password, you probably don't have one). If you do have a password on your root MySQL user, you'll need to supply it. Or, alternatively, you can give privileges to a new database user, and supply that user in place of "root" here, plus the password with the -p option.
  3. Wait. If you're working with a tiny database, it might be instant, or near to it! In this case, however, I was importing something about 6 GB in size. It took about ten minutes to import! The shell doesn't give you an indication that it's doing something, but if you didn't get an error, it sure is! If you want to, you can go to phpMyAdmin and open your database. Scroll to the bottom of the "Structure" tab, note the size, and refresh the page. The size should go up, meaning the import is working!