How To Set Up Your WordPress Development Environment with a Large Database ft. MAMP & Mac, The Really Simple Guide

For the last two years, the majority of my dev work has been focused on React, but, since I am the more experienced engineer working with wordpress on my team and the one that doesn’t find it daunting (I’m looking at you php), when the occasion presents it self, I normally volunteer to get this sort of projects done quickly.

The one aspect I enjoy the least about working with wordpress, is setting up my dev environment, especially if I have a large database export, and not because is hard to do, but rather, because I always forget the whole process and end up googling it all step by step from different places, so I decided it was time to consolidate the whole process and leave it here for the whole world to see… and me of course.

Assumptions

  1. You have MAMP installed on your machine (if you don’t, go ahead and download it from here).
  2. You have a large database export (32MB and up). (the file may be compressed gzip, bzip2, zip or uncompressed). For smaller databases exports you can simply import it using the GUI in PhpMyAdmin.
  3. You have your site source code (probably cloned from github, bitbucket or what have you…).
  4. You are doing this on a Mac (Although I feel most of this is pretty much the same on windows … maybe).
  5. You are not afraid of the terminal (if you are, thats ok, I am sometimes too).

1. Alias your localhost url (optional)

I prefer to type in a url like http://mysite.local.com instead of http://localhost, or http://127.0.0.1 when working locally, to make that happen you just need to alias your localhost, is fairly simple, just edit your “host database” (is not really a database, is just a text file) and add a new alias.

Open a new terminal window and type:

vim /etc/hosts

and add something like this:

# My local dev subdomains

127.0.0.1 mysite.local.com

Save it and that’s it!, now you can use that url instead of localhost or 127.0.0.1, yay!

2. Set up MAMP

Open the MAMP app, go to preferences and set up the location of your source code under web server:

Set up your ports or just leave the default ones:

if you change the defaults, just be aware that when dumping the database export we will need to know the port you chose for MySQL.

3. Create the database

Click “Start Servers” in the MAMP app:

A browser window will pop up, click the tools drop down menu and then the PHPMYADMIN option:

and in the Databases tab, create a new one, preferable with the SAME NAME as the database you exported from your site (although I have successfully used different names).

You should also create an user with root privileges or you can use root, the important part here is that you have the user username and its password so that you can update the database:

We are all set now for the database import.

4. Database Import

  • Open a new terminal window:

CAREFUL: This will replace all tables in the database you specify!

  • cd into /Applications/MAMP/Library/bin/
  • Run the following mysql command inside that directory:

mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

Example:

/Applications/MAMP/Library/bin/mysql -u root -p my_db < /Users/owner/Downloads/my_site_db_backup.sql

  • Hit the enter key.
  • You should be prompted with the following line:
  • Enter password:
  • Type your password, (keep in mind that the letters will not appear, but they are there)
  • Hit the enter key
  • Check if you database was successfully imported
  • Navigate to phpMyAdmin in a browser

NOTE: MySQL default port is 3306, if you picked a different port like I did on this example, you need to pass two more flags, the port and the host just like this (make sure the host flag is 127.0.0.1):

/Applications/MAMP/Library/bin/mysql --port=8889 --host=127.0.0.1 -u root -p my_db < /Users/owner/Downloads/my_site_db_backup.sql

5. Add/Update wp-config.php

You need to add (if not present) or update wp_config.php, it should be located in the root of the project in order to make a successful database connection, just define the values with your database and host info, make sure to update 'DB_NAME', 'DB_USER', 'DB_PASSWORD' and define 'DB_HOST' to 'localhost'.

/** The name of the database for WordPress */
define( 'DB_NAME', 'database_name_here' );

/** MySQL database username */
define( 'DB_USER', 'username_here' );

/** MySQL database password */
define( 'DB_PASSWORD', 'password_here' );

/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

To learn more in detail about this file (and get a full file sample) go to the documentation page here.

6. Update the Site Url & Home Values

If you exported the database from a production site, chances are that when you try to open your dev site, it will redirect you to your live site, this happens because your site_url and home probably still have the production site url as its value in the local database, we just need to change that to our local url http://mysite.local.com (this is another reason why aliasing localhost is useful!).

To do that, we need to again go to phpMyAdmin, select our database, click on the + right next to the name, select wp_options table, and edit site_url and home to be http://mysite.local.com.

That’s it, your site should be working locally.

Bonus Tip: you could also export your uploads folder from your live site and paste it inside wp_content so that all your media shows up.

Lastly, sometimes dot files files like .htaccess get ignored and are not present in some repositories, in case you need it, you can copy it from here and place in the root of your project.

Advanced Tip

You can store your MAMP environment variables in /Applications/MAMP/Library/bin/envvars and then simply refer to them in your files, like wp-config.php .

For instance:

/Applications/MAMP/Library/bin/envvars

export DB_NAME="db_name"
export DB_USER="username"
export DB_PASSWORD="password"

Then just refer to them:

wp-config.php

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', $_ENV['DB_NAME'] );

/** MySQL database username */
define( 'DB_USER', $_ENV['DB_USER'] );

/** MySQL database password */
define( 'DB_PASSWORD', $_ENV['DB_PASSWORD'] );

I hope this was helpful, I know I will use it in the future!

You may also like