4elements, web design and consultancy

  1. Installing and Using PHPMyAdmin for Web Development

    Final product image
    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:

    Apache User Access Restrictions

    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.

    Create a new database with the manual query box

    Or, you can use the visual interface directly:

    Create a database

    You can also add users and define privileges this way. Click on the privileges tab:

    Add new user in the privileges tab

    Click add user, and define the privileges for the database that you wish:

    Add new user and privileges

    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:

    Drop database with PHPMyAdmin

    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.

    Export your database

    Enable "Add Drop Table / View / Procedure / Function / Event" statement:

    Add drop table commands

    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:

    Test complex queries with manual SQL

    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:

    Edit data directly in the database

    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:

    Drop foreign key and index

    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.

     

    0 Comments

    Leave a comment › Posted in: Daily

0 Comments

Got anything to add?

(Basic HTML is fine)