phpMyAdmin is an open source tool used for the administration of MySQL. In addition to offering the capability to perform administration tasks such as creating, editing, or deleting databases, and managing users and permissions, phpMyAdmin provides a graphical user interface to do all of these tasks and more.

• These instructions are intended specifically for installing phpMyAdmin on Ubuntu 14.04 LTS.
• I’ll be working from a Liquid Web Core Managed Ubuntu 14.04 LTS server, and I’ll be logged in as root.
• A LAMP, Linux, Apache, MySQL and PHP, must be installed on your server. If you’re working from a Liquid Web Core Managed Ubuntu 14.04 LTS server as I am, then the LAMP stack is already installed!

First, you’ll follow a simple best practice: ensuring the list of available packages is up to date before installing anything new.

apt-get -y update

Then it’s a matter of just running one command for installation via apt-get:

apt-get -y install phpmyadmin

As the installation runs you’ll be asked a few simple questions regarding the basic configuration of phpMyAdmin.

At the first screen, select apache2 by using the space bar, then hit enter to continue.

At the second screen, which asks “configure the database for phpmyadmin with dbconfig-common?”, select Yes , then hit enter to continue.

At the third screen enter your MySQL password, then hit enter to continue.

And finally at the fourth screen set the password you’ll use to log into phpmyadmin, hit enter to continue, and confirm your password.

For a refresher on editing files with vim see: New User Tutorial: Overview of the Vim Text Editor

vim /etc/apache2/apache2.conf

Add the following to the bottom of the file:

# phpMyAdmin Configuration
Include /etc/phpmyadmin/apache.conf

Then exit and save the file with the command :wq .

And, restart Apache 2 with the following command:

service apache2 restart

Verify that phpMyAdmin is working by visiting the_IP_of_your_server/phpmyadmin. For example: http://127.0.0.1/phpmyadmin

http://domain_name_or_IP/phpmyadmin

You can now log into the interface using the root username and the administrative password you set up during the MySQL installation.

When you log in, you’ll see the user interface, which will look something like this:

Step Two — Secure your phpMyAdmin Instance

We were able to get our phpMyAdmin interface up and running fairly easily. However, we are not done yet. Because of its ubiquity, phpMyAdmin is a popular target for attackers. We need to secure the application to help prevent unauthorized use.

One of the easiest way of doing this is to place a gateway in front of the entire application. We can do this using Apache’s built-in .htaccess authentication and authorization functionalities.

Configure Apache to Allow .htaccess Overrides

First, we need to enable the use of .htaccess file overrides by editing our Apache configuration file.

We will edit the linked file that has been placed in our Apache configuration directory:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

We need to add an AllowOverride All directive within the <Directory /usr/share/phpmyadmin>section of the configuration file, like this:

<Directory /usr/share/phpmyadmin>
    Options FollowSymLinks
    DirectoryIndex index.php
    AllowOverride All
    . . .

When you have added this line, save and close the file.

To implement the changes you made, restart Apache:

sudo service apache2 restart

Create an .htaccess File

Now that we have enabled .htaccess use for our application, we need to create one to actually implement some security.

In order for this to be successful, the file must be created within the application directory. We can create the necessary file and open it in our text editor with root privileges by typing:

sudo nano /usr/share/phpmyadmin/.htaccess

Within this file, we need to enter the following information:

AuthType Basic
AuthName "Restricted Files"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user

Let’s go over what each of these lines mean:

• AuthType Basic: This line specifies the authentication type that we are implementing. This type will implement password authentication using a password file.
• AuthName: This sets the message for the authentication dialog box. You should keep this generic so that unauthorized users won’t gain any information about what is being protected.
• AuthUserFile: This sets the location of the password file that will be used for authentication. This should be outside of the directories that are being served. We will create this file shortly.
• Require valid-user: This specifies that only authenticated users should be given access to this resource. This is what actually stops unauthorized users from entering.

When you are finished, save and close the file.

Create the .htpasswd file for Authentication

Now that we have specified a location for our password file through the use of the AuthUserFiledirective within our .htaccess file, we need to create this file.

We actually need an additional package to complete this process. We can install it from our default repositories:

sudo apt-get install apache2-utils

Afterward, we will have the htpasswd utility available.

The location that we selected for the password file was “/etc/phpmyadmin/.htpasswd“. Let’s create this file and pass it an initial user by typing:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd username

You will be prompted to select and confirm a password for the user you are creating. Afterwards, the file is created with the hashed password that you entered.

If you want to enter an additional user, you need to do so without the -c flag, like this:

sudo htpasswd /etc/phpmyadmin/.htpasswd additionaluser

Now, when you access your phpMyAdmin subdirectory, you will be prompted for the additional account name and password that you just configured:

http://domain_name_or_IP/phpmyadmin

After entering the Apache authentication, you’ll be taken to the regular phpMyAdmin authentication page to enter your other credentials. This will add an additional layer of security since phpMyAdmin has suffered from vulnerabilities in the past.

Conclusion

You should now have phpMyAdmin configured and ready to use on your Ubuntu 14.04 server. Using this interface, you can easily create databases, users, tables, etc., and perform the usual operations like deleting and modifying structures and data.

To learn how to further secure your interactions with the server by encrypting your communication with SSL, check out our article on setting up SSL certificates with phpMyAdmin.

PhpMyAdmin Tutorial: Create Database

In this part of our PhpMyAdmin tutorial we will describe the steps which must be followed in order to create a new database, add a table with records, create a database backup and perform a database restore procedure.

We will start with the database creation.

How to Create a MySQL Database?

Please note that you can not create a database directly through cPanel->PhpMyAdmin due to the lack of user privileges. However, you can easily create a new database from your cPanel->MySQL Databases. Navigate to the Create New Database box. Enter the database name in the New Database text field and click on the Create Database button.

The database name will be preceded by the cPanel username. For example, if your cPanel user name is user and you want to have a database named test, the actual database name will be user_test. You will get a confirmation message.

How to Add MySQL Database Tables?

Navigate to your cPanel->PhpMyAdmin tool and open the newly create database. It is empty and there are no tables.

Enter the table name and the number of fields. Click on the Go button to create the table.

On the next screen you should enter the fields’ names and the corresponding properties. The properties are:

Type

Here you should pick the type of the data, which will be stored in the corresponding field. More details about the possible choices can be found in the official MySQL Data Types documentation.

Length/Values

Here you should enter the length of the field.  If the field type is “enum” or “set”, enter the values using the following format: ‘a’,’b’,’c’…

Collation

Pick the data collation for each of the fields.

Attributes

The possible attributes’ choices are:

BINARY – the collation for the field will be binary, for example utf8_bin;

UNSIGNED –  the field numeric values will be positive or 0;

UNSIGNED ZEROFILL – the field numeric values will be positive or 0 and leading zeros will be added to a number;

ON UPDATE CURRENT_TIMESTAMP – the value for a data type field has the current timestamp as its default value, and is automatically updated;

Null

Here you define whether the field value can be NULL. More about the NULL value can be found in the corresponding MySQL documentation.

Default

This property allows you to set the default value for the field.

Extra

In the Extra property you can define whether the field value is auto-increment.

The radio buttons that come below define whether there is an Index defined for the particular field and specify the Index type.

Comments

Here you can add comments, which will be included in the database sql code.

At the end you can include Table comments and pick the MySQL Storage Engine and the Collation. Once you are ready, click on the Save button.

If you want to add more fields you should specify their number and click on the Go button instead of Save.

The database table will be created and you will see the corresponding MySQL query.

Now we will proceed with the populating of the table with data.

How to Add Content in a Database Table?

In order to add records in a database table click on the Insert tab.

Enter the data in the corresponding fields and click on the Go button to store it.

At the bottom of the page you will see a drop-down menu labelled Restart insertion with x rows . There you can pick the number of the rows that you can populate with data and insert at once. By default the value is 2.

The Ignore check box will allow you to ignore the data entered below it. It will not be added.

You can see the newly inserted record by clicking on the Browse tab.

You can edit or delete the record by clicking on the corresponding icons.

To insert more records, return to the Insert tab and repeat the procedure.

How to Backup a Database?

Once you are ready, you can create a backup of your database through the Export tab.

Select the tables which you want to be exported.

Leave the radio button selection to the SQL option.  The Structure and the Data check boxes should remain checked.

Select the Save as file check box and then click on the Go button.

In this way you will save the dump SQL file with your database structure and content on your local computer.

If you have a large database with a lot of records, the server timeout value can be reached. In such a case you can export the database in several batches. You can find more details in our MySQL Knowledge Base.

How to Restore a Database Backup?

You can restore your database backup from the Import tab.

Click on the Browse button to select your database backup file from your local computer.

Pick the charset of the file from the corresponding drop-down menu.

If the file is too big, the MySQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption.