Software Engineering in WordPress and Musings on the Deep Life

Importing A Large Database in MAMP

When working with WordPress databases, we’re pretty fortunate to have tools like WP Migrate DB Pro, phpMyAdmin, Sequel Pro, and so on. But there are times when importing a large database is a bit more demanding than the front-end utilities we have.

Perhaps we have to change up our PHP configuration, perhaps we’re dealing with hundreds of megabytes worth of data, perhaps we don’t have the kind of control on the environment that we need.

Whatever the case, we always have the command line. And if you’re working with WordPress, MAMP, and you’re faced with importing large databases, then you may need to stop tweaking your PHP settings and simply import the file via the command-line.

Importing A Large Database

One of the first questions that often comes up when having to do this is:

Why can’t I just use a front-end?

Technically, you can. It may take a lot of tweaking, but if you can…

  • get the environmental variables configured in such a way that a request won’t time out,
  • define a value such that a large enough upload is permitted,
  • and the software won’t opt to cut you off because it thinks something is broken because of the time that has passed

Then you may be okay and this post isn’t for you, but if that’s not the situation you find yourself working in, then here’s how you can go about importing a large database using an SQL file and the command line.

1. Grab The SQL File

This post assumes you have a copy of the SQL file you’re trying to import. This is something you can usually grab from the server, from whoever has hired you, or whatever system it is with which you’ll be working.

SQL Export

Ultimately, the goal is to replicate the production environment on your local machine so you can make the necessary changes and then have what you need to deploy to the staging environment or back to the production environment.

2. Connect To The Database

If you’re running MAMP, then you’re going to need to navigate to the MySQL executable. You’ll need to launch your command-line then navigate to the following path (or similar path depending on your setup and when you’re reading this):

`$ cd ~/Applications/MAMP/Library/bin`

From there, you can connect to your MySQL server (assuming it’s running) by entering the following command:

`$ mysql -uroot -proot`

And then you’ll be presented with the MySQL command-line:

MySQL

At this point, you’ll need to connect to the database into which you want to import your file. To do this, simply enter the following at the mysql> prompt:

`connect your_database_name`

Then it’s time to actually import the file.

3. Import The Export

Next, simply enter the following command:

`source /path/to/your/file/sql_export.sql`

And let MySQL do its thing.

SQL Import

And when it’s done, it will return you to the command prompt.

Bypassing The Time

And with that, you’ve bypassed all the usual headaches of having to configure an environment just to import a single file and you’ve still got your development environment setup so you can access the database with your front-end of choice.

13 Comments

  1. German Hernández

    Hi Tom

    How do you deal with serialization in this case. Every time I try to import a database like that all of the objects stored in the options table are messed up. Specially those related to plugin o themes settings.

    • Tom

      You know, that’s an interesting question. I’ve honestly never had a problem like this.

      It seems like there may be a problem with your local configuration. After all, the import is just an import of an SQL export. Do you have more information about the types of errors you normally see? What client do you use?

      • German Hernandez

        We started using wp_migrate. The funny thing is that when we do the themes ourselves everything works perfectly.

        But when using 3rd party themes like enfold all theme settings are gone. It’s like the site has never been customized.

        So we have to export the theme settings from the theme export utility and reimport them again. But sometimes errors occurred and we had to redo all the customization.

        We found wp_migrate and are partially happy with it. It’s a great plugin but way to expensive to use it.

  2. Chris McCoy

    you can also do

    mysql -u username -p databasename < file.sql

  3. James Morrison

    Hi Tom,

    Any reason you’d use this method over something like Sequel Pro?

    I’ve been involved in medium sized multisite installs (~5000 tables) which don’t have any issues importing / exporting through Sequel Pro and don’t involve the command line.

    James

    • Tom

      Any reason you’d use this method over something like Sequel Pro?

      I don’t use Sequel Pro. I mean, I have in the past, but I’ve stopped for no other reason than the front-end built into my IDE.

      I rarely have to import that many files and when I do it’s easy enough to open Terminal, issue a few commands, and let it run.

  4. Craig Tommola

    Right out of the gate, terminal tells me that mysql -uroot -proot command not found. Thoughts?

    • Tom

      This could be one of two things (maybe more, but I’d be suspect of that):

      • MySQL isn’t installed globally
      • MySQL isn’t available in your environmental variables (or your PATH depending on what OS you’re on)

      In any respect, the operating system doesn’t know where to look for the actual binary. If you go into a terminal and type echo $PATH, see what dumps out.

      You should see a path pointing to your MAMP directory and/or /usr/bin and /usr/sbin which is where the binaries are normally stored.

    • Jan van Vliet

      Try it with ./

      ./mysql -uroot -proot

      Works for me

      • Tom

        Thanks for this suggestion!

    • Darren Cooney

      Way late on this but I had the same issue because MAMP had installed MySQL in a different directory.

      http://blog-en.mamp.info/2009/08/using-mysql-command-line-with-mamp.html

      Thanks for the article, Tom.

  5. Curtis Marais

    How could I run something like this via Automator? (Sorry, new-ish to OSX)

    mysql -u username -p databasename < file.sql

    Thank you for the article

    • Tom

      I don’t know Automator for something like this — instead, it should be a Terminal command should be set like this:

      $ mysql --user=user_name --password=your_password db_name

      Then if you want to run a script against the database and write the output to file, then you’d do this:

      $ mysql db_name < script.sql > output.text

      Hope this helps!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2023 Tom McFarlin

Theme by Anders NorenUp ↑