My perfect OS X development environment - MySQL part 2 - Installing phpMyAdmin

>> Wednesday, 19 November 2008

In the last post we installed MySQL and I showed you how to administer it from the command line but that's not really ideal in day to day usage for most people so today I'm going to show you how to install phpMyAdmin a really handy tool for administering MySQL databases through a web-based interface. There are other alternatives out there but unlike some of the more popular choices like AquaDataStudio this is free to use which in the current economic climate can only be a good thing.

As you may have guessed by it's name phpMyAdmin runs on PHP so before we start installing it lets get the default Leopard PHP install enabled in Apache (apologies if this offends the sensibilities of any particularly hardline CF purists out there :-p ).

Enabling PHP

First up we need to make some changes to the apache config file (httpd.conf) so fire up Terminal and enter the following command...

$ sudo vi /etc/apache2/httpd.conf
Scroll down until you see this line of text and remove the # from the beginning (effectively uncommenting it)
#LoadModule php5_module libexec/apache2/libphp5.so
Handy tip: To search in VI hit the esc key then forward slash followed by what you're looking for then hit enter. It'll jump to the first result automatically and you can then skip through the remaining results by hitting n.

Typically that would be all we needed to do as by default a file in /etc/apache2/other/ called php5.conf is included in the Apache config which checks if the libphp5.so module is loaded and sets up how php files are handled automatically but as we're using a slightly finer grained setup with our virtual hosts we need to update the virtual host configuration file for our default site. In Terminal run the following commands.
$ sudo vi /etc/apache2/vhosts/yourhostname.local.conf
Find the line that looks like this
DirectoryIndex index.cfm index.html index.htm
And add index.php to the end so it looks like this
DirectoryIndex index.cfm index.html index.htm index.php
Save and exit the file

Simple as that! We just need to restart Apache now to pickup our changes so we can see if PHP is running correctly.
$ sudo apachectl restart
To test our handiwork just create a new file called phptest.php in the web root of your default web site and inside the file add the following text.
<?php phpinfo(); ?>
Save and close the file then browse to the file in a web browser. If everything is working you should see a very long page that tells you the exact setup of your PHP installation. For a development environment this is a pretty handy page to have hanging around if you plan on doing anything with PHP but it is not advisable to leave this lying around on a public facing server. Just bear that in mind.

Installing phpMyAdmin

Now we have the basic php install working so we can finally download phpMyAdmin. Go to the projects site and download the latest version (i'm using 3.0.1.1). unzip it to somewhere sensible on your machine, rename the folder to phpMyAdmin and copy it into the webroot of your default web site.

Before I go any further I should probably explain the way I plan to set up the default (hostname) web site on my laptop. The idea is to use the default site as an overarching admin area where I can administer everything across all projects. To achieve this for phpMyAdmin we'll be setting up this phpMyAdmin install in the default webroot to have have full visibility of all databases (using an admin level user) and then later on I'll setup each individual site with its own phpMyAdmin install as part of the semi-automated site creation process I have planned so that it only allows visibility of the databases related to that project. It's not strictly necessary to do it this way but I like the separation of concerns between each project as you know you'll never accidentally change the wrong project as long as you're browsing via that projects domain. But I digress...

phpMyAdmin kindly includes a script to perform all the basic configurations we need to make but before we can run it we need to make sure we have a valid MySQL user account (using root is always a bad idea mmmkay) and an empty database for holding some phpMyAdmin data, we'll also need to create some folders to hold the configuration file that the setup script creates and any uploaded or saved files used by phpMyAdmin. The easiest way to do all this is by Terminal so fire it up again and run the following commands

First we login to the MySQL command line
$ mysql -u root -p
Then we create our new admin user that will hold dominion over any databases we create
mysql> GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'enterapasswordhere';
Then we'll create an empty database for phpMyAdmin to use and exit the MySQL command line
mysql> create database phpMyAdmin;

mysql> quit;
Now we need to change to the directory where you put the phpMyAdmin folder under your default web root e.g.
$ cd ~/Development/Sites/yourhostname.local/webroot/phpMyAdmin/
Handy tip: you can use '~/' as a shortcut to the root of your OS X directory, saves some typing sometimes :-)

Now we can create the required folders
$ mkdir config upload save

$ chmod 777 config upload save
Now open your browser and browse to the following URL

http://yourhostname.local/phpMyAdmin/scripts/setup.php

You may see a warning about 'Not secure connection' but for now we'll ignore that as we haven't setup and kind of ssl yet.

Now we need to add our MySQL to the config so click the Add button under the Servers heading and fill in the following details...
Server Hostname: localhost
Server Port: 3306
Server socket: /tmp/mysql.sock
Connection type: socket
php extension to use: default (mysqli)
compress connection: no
Authentication type: config
user for config auth: admin
password for config auth: youradminpassword
only database to show: leave blank
verbose name of this server: .local
phpMyAdmin control user: admin
phpMyAdmin control user password:
youradminpassword
phpMyAdmin database for advanced features: phpMyAdmin
session name for signon auth: leave blank
login url for signon auth: leave blank
logout url: leave blank
Click the green add button under actions to add the server to the config

Now we need to setup the upload/download functionality within phpMyAdmin so hit the Upload/Download button under features and enter the following
Upload directory: upload
Save directory: save
Then hit the update button under actions to save

If you would like to be able to do charset conversions then you'll need to enable the functionality now. Click the Charsets button under features and check the tick box that says 'Allow charset conversion' then hit update to save.

Feel free to go through and make any further configurations you may want, when you've finished hit the save button under configuration to write out all the settings you've just configured. The config file is written into the config folder but we need to move it into the root of the phpMyAdmin folder and then remove the config folder to make things a bit more secure so we need run to the following commands in Terminal
$ cp config/config.inc.php config.inc.php

$ rm -rf config
If you browse to http://yourhostname.local/phpMyAdmin/ you should now see your lovely fresh phpMyAdmin install ready for use.

Until next time Happy Databasing :)

1 comments:

Michael 25 November 2008 at 19:14  

Great series so far Ciqala - can't wait for the next installment.