Pre-requisite reading: Mac Serving With Apache, PHP, and MySQL

Now that we’ve already set up Apache, PHP, and MySQL, it’s time to install and configure a database management tool. Database management tools are used to create, edit, and browse database structures and content. Installing a separate database management tool isn’t strictly necessary; after all, the Terminal can be used to perform all MySQL database functions. But the reason we use Macs is that we prefer graphical interfaces to command lines, right?

While there have traditionally been very few SQL database management options for the Mac, the number has grown steadily over the last twelve months. For the purposes of this tutorial we will focus on phpMyAdmin; an in-depth look at the various alternative database management options will be reserved for a future article.

[Note: This is a live document and will be constantly refined to make sure it assumes no prior knowledge or experience other than the pre-requisite reading listed above. If you have any trouble following the steps in any of our tutorials, please let us know.]

Installation

1. Download the latest stable version of phpMyAdmin.
2. Expand the downloaded archive with OpenUp or your preferred application. Rename the resulting folder to “phpMyAdmin” and copy it to either [Home]/Sites/ or to /Library/WebServer/Documents/ (see relevant article on where to store web files). For the purposes of this article, we will assume the latter location.
3. Inside the phpMyAdmin folder, find and open the config.inc.php file with TextEdit or your preferred PHP-friendly text editor. Change the following line:

   $cfg[’PmaAbsoluteUri’] = ‘’;

   …to…

   $cfg[’PmaAbsoluteUri’] = ‘http://localhost/phpMyAdmin/’;

   [If your server has a domain name, substitute www.yourdomain.com in place of localhost above.]

4. Further down will be a list of configuration variables starting with $cfg[’Servers’][$i]. The first line in that section is for the MySQL hostname. Enter localhost in between the empty single quotes (do not use www.mydomain.com, even if you have one for your server). The completed line should look like:

   $cfg[’Servers’][$i][’host’] = ‘localhost’; // MySQL control user settings

5. Find the line that sets the MySQL controluser variable and in between the single quotes enter root. Then type the control user password that you set when you originally installed MySQL in between the single quotes following the controlpass setting, just below the controluser line.
6. Enter the word cookie into the auth_type setting.
7. Set the user and password to the same values as controluser and controlpass above. Select Save from the File menu.
8. Make sure Apache and MySQL are started, and then load the following URL in your browser:

http://localhost/phpMyAdmin/index.php

9. Assuming all went well up until this point, an authentication dialog should appear. Enter root as the username and the same password as above to log into MySQL. Once the authentication step is complete, a “Welcome to phpMyAdmin” message should appear, along with a pop-list of databases on the left-hand side.
10. Now we’re going to create a MySQL control user, which offers better security since we won’t have to log in as the root user for routine database maintenance. Click on “Add a new User” and enter a control username. Select the “Any Host” pop-up and switch to “Local” (localhost will appear in the text field). Type a password for the control user into both fields as indicated.
11. Check the following checkboxes, leaving everything else alone: Select, Insert, Update, Delete, File, Create, Alter, Index, Drop, Grant, Process, Reload, Shutdown, and References. Hit the “Go” button, and you’ll see a “You have added a new user” confirmation screen.
12. Now it’s time to log out, change the control user for phpMyAdmin, and log back in again. Click on the Home link at upper left. Click on “Log out” at the bottom of the right pane.
13. Go back and edit the config.inc.php file so that the new control username replaces both instances of root, and change the password in both places to the new password for the control user.
14. Go back to the phpMyAdmin page and log in as the control user to make sure everything is set up properly. After logging in successfully, you should see the same phpMyAdmin screen that you saw when you first logged in as the root MySQL user.

Wrap-Up

We now have full control of MySQL and can create additional users and new databases by clicking on the Create new database and Privileges sections at right. We will use the phpMyAdmin tool in future articles to create databases for use with e-commerce, content management, and other server applications.

Posted by Justin at 4:29 AM PDT — Tags: mac, database