PHPMyAdmin (PMA) is an excellent free, open source web-based database client which can be used to interact more easily with MySQL and application databases. I'll describe how to install it, secure it and some common scenarios with which it can assist you in database administration. Here's an online demo of PMA for you to explore.
In addition to offering a visual GUI for database operations, I also appreciate being able to run command line SQL operations via my browser without having to log in to the server via SSH. For example, some WiFi connections and mobile hotspots regularly terminate persistent SSH sessions, making database tasks problematic.
Getting started with PMA is fairly straightforward on Linux. I'll describe how to do so with Ubuntu 14.x at Digital Ocean. Log in to your server via SSH.
apt-get install phpmyadmin
You can use the default settings during installation or customize them to your liking.
Once you limit MySQL access to only localhost (which you should), there aren't any remote ports to MySQL for a hacker to try to access. They might try to break in via SSH or try SQL injection attacks against your applications, but they can't directly attack the database. Once you install PMA, anyone can run web-based attacks against it in order to gain control of your database, so care is warranted.
There are a few precautions I recommend when configuring PMA.
1. Use very strong passwords for all of your MySQL accounts, especially the root account. e.g. 25 characters for the root password.
2. Use different MySQL accounts and privileges for each application/site running on a single server. This way if one password is compromised, only one site's database is compromised.
3. Change the default URL used by PMA. This way people can't visit http://yourblog.com/phpmyadmin. While this security by obscurity isn't a very effective technique, it does add some protection.
Add an alias to the
Alias /myobscuredpma /usr/share/phpmyadmin
service apache2 reload
Then, to access PMA, visit http://yourblog.com/myobscuredpma
If you need to modify your PHPMyAdmin password, you can edit the
4. Configure Web Authentication for the PMA Site. This will require that you enter an additional password to gain access to PMA, in addition to your database password, like this:
To configure Apache user restrictions, follow these steps:
htpasswd as part of apache2-utils:
apt-get install apache2-utils
Make a directory to store your passwords:
htaccess support for PMA:
AllowOverride All below
<Directory /usr/share/phpmyadmin> Options FollowSymLinks DirectoryIndex index.php AllowOverride All
Configure your user authentication for PMA:
AuthType Basic AuthName "Login Required for Access" AuthUserFile /etc/htpasswd/.htpasswd Require valid-user
Set your password:
htpasswd -c /etc/htpasswd/.htpasswd username
And, restart Apache:
service apache2 restart
Browse to your PMA site, and you'll be prompted for your username and password as shown above.
Using PHPMyAdmin During Web Development
1. Creating and Dropping Databases
I find PMA especially useful during development and testing phases where I might want to reset the database or back up operations repeatedly.
Without PMA, I would sign in to my server via SSH. Log in to MySQL, and then run:
create database myapp; grant all privileges on myapp_database.* TO "your-mysql-username"@"localhost" identified by "your-mysql-password"; flush privileges;
With PMA, you can run any command line query with the visual interface. Click the SQL tab and paste the MySQL database instructions above. Then click Go to create the database.
Or, you can use the visual interface directly:
You can also add users and define privileges this way. Click on the privileges tab:
Click add user, and define the privileges for the database that you wish:
For your typical application database account, you'll want to check the boxes in the Data and Structure squares.
To drop a database, visit the Databases menu. Click the database you want to drop and click Drop:
2. Backing Up Your Database
It's a very good idea to backup your application database before performing any extended operations on the database or code upgrades. If something goes wrong, you'll be able to restore your site from the backup.
Using the PMA web interface, click on your database, click the Export tab and select Custom.
Enable "Add Drop Table / View / Procedure / Function / Event" statement:
When you click Go, PMA will download a backup of your entire database. If you're Apache PHP timeout settings aren't properly configured, some long file downloads may timeout and fail. You can adjust this in
PHP.ini as I describe on my site.
3. Testing Queries
PMA is fantastic for testing your SQL queries during development. When I created Geogram (see also my upcoming tutorial MapApp on Tuts+), I needed to learn and test a number of complex geolocation queries for neighborhood shapefiles e.g. find the closest neighborhoods to my address. PMA made this much easier.
Select your database, click on Query. Paste or edit your complex queries and test them directly from PMA:
Once I had my queries working, I could write programmatic ActiveRecord code more easily. Just as an example:
$criteria = new CDbCriteria; $criteria->together=true; $criteria->having= "distance < 100"; $criteria->order = "distance ASC"; $criteria->with = array('place_geometry'=>array('select'=>'place_id,center,'.new CDbExpression('(3959 * acos( cos( radians('.$lat.') ) * cos( radians( X(center) ) ) * cos( radians( Y(center) ) - radians('.$lon.') ) + sin( radians('.$lat.') ) * sin( radians( X(center) ) ) )) as distance'))); $dataProvider = new CActiveDataProvider(Place::model()->active()->includesMember($id), array('criteria' => $criteria, 'pagination' => array( 'pageSize' => 10, ), ));
4. Repairing Database Entries
If you're a developer, you've likely had a bug in an app corrupt your database. I know, right?
For instance, some of my email management apps will sometimes get stuck on unusual messages that come in; these bugs are hard to find in regular testing. When this happens, I've found it useful to modify the database directly with PMA to unstick my tasks and get the site running again. In some cases, just being able to easily browse tables can help you diagnose what's broken.
In PMA, open the database and select your table. Double click on a column to directly edit it in place. Make the change you need and hit return:
5. Resetting the Database for Migrations
If you use a framework (like Yii), you might be using programmatic database migrations. Database migrations make it easier to iterate feature development and to upgrade your applications. However, testing them can break things in your local database.
Most commonly, I'll run into migration errors and I can't migrate up or down because of undropped indexes, foreign keys or tables. In this case, I can use PMA to drop a table and delete unwanted index files.
In PMA, select your database, click the SQL tab and enter the SQL for dropping your foreign key or index. Here are a couple of examples: