Shineyrock

dislike -1 08
Installing and Using PHPMyAdmin for Web Development
1. September 2014
2. Daily
3. read 881
What You'll Be Creating
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.
Installing PHPMyAdmin
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 apache.conf file:
```
nano /etc/phpmyadmin/apache.conf
```
```
Alias /myobscuredpma /usr/share/phpmyadmin
```
Reload apache:
```
service apache2 reload
```
Then, to access PMA, visit http://yourblog.com/myobscuredpma
If you need to modify your PHPMyAdmin password, you can edit the config-db.php here:
nano /etc/phpmyadmin/config-db.php
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:
Install htpasswd as part of apache2-utils:
```
apt-get install apache2-utils
```
Make a directory to store your passwords:
```
mkdir /etc/htpasswd
```
Add htaccess support for PMA:
nano /etc/phpmyadmin/apache.conf
Add AllowOverride All below
```
<Directory /usr/share/phpmyadmin>
        Options FollowSymLinks
        DirectoryIndex index.php
        AllowOverride All
```
Configure your user authentication for PMA:
nano /usr/share/phpmyadmin/.htaccess
```
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:
I hope you've found this useful. Please post any comments, corrections or additional ideas below. You can browse my other Tuts+ tutorials on my author page or follow me on Twitter @reifman.
martijn broeders

founder/ strategic creative at shineyrock web design & consultancy
e-mail: .(JavaScript must be enabled to view this email address)
phone: 434 210 0245

Like

disLike

-1

Add a comment...

Comments

Name*

Email*

location

URL

Comment {comment}

Remember my personal information Notify me of follow-up comments?