phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. Operations can be performed via the user interface, while we still have the ability to directly execute any SQL statement.
Here we will learn how to install phpMyAdmin. After that we will Configure phpMyAdmin for basic functionality and Secure it for general usage.
Installing phpMyAdmin involves the following steps.
1) Downloading
2) Unpacking
3) Creating the Configuration storage
4) Creating the Control User
5) Configuring Apache HTTP Server installation
6) Configuring PHP installation
7) Configuring phpMyAdmin
8) Installation Review
After installing phpMyAdmin, it is recommended to view the following sections.
Securing phpMyAdmin
Customizing phpMyAdmin
1) Downloading
phpMyAdmin is available from the official website.
Let us goto http://phpmyadmin.net. On the top of page, click on the Downloads tab, which will take us to the download page listing different download packages. Under the phpMyAdmin 4.2.5 section, click on the gzipped tarball phpMyAdmin-4.2.5-all-languages.tar.gz. This will take us to the Sourceforge’s Project download page. Our download must start now.
The downloaded file phpMyAdmin-4.2.5-all-languages.tar.gz will be 8.2 MB in size.
2) Unpacking
Make sure you have copied the downloaded file phpMyAdmin-4.2.5-all-languages.tar.gz to the /usr/local/apache2/ directory. In the terminal, change to /usr/local/apache2/ directory.
# cd /usr/local/apache2/
Extract the gzipped tarball containing phpMyAdmin.
# tar --transform s/phpMyAdmin-4.2.5-all-languages/phpmyadmin/ -zxvf phpMyAdmin-4.2.5-all-languages.tar.gz
OPTIONS EXPLAINED --transform s/regexp/string transform is a sed-like replace expression. Any file or directory name processed by tar, matching the regexp will be replaced with the string. Luckly for us directory 'phpMyAdmin-4.2.5-all-languages' is the only one matching the regexp. -C Switches to the specified directory before extraction, so that the contents are extracted to that directory -z filter the archive through gzip -x extract files from an archive -v verbosely list files processed -f use archive file or device ARCHIVE
Now we will have a directory phpmyadmin with the extracted files.
3) Creating the Configuration storage
phpMyAdmin Configuration Storage was formerly called the Linked Tables Infrastructure. It is a database that is used internally by phpMyAdmin for additional features such as bookmarks, comments, SQL history, relations, PDF schema, and MIME transformations etc.
In the terminal, change to the phpmyadmin/examples/ directory.
# cd /usr/local/apache2/phpmyadmin/examples
Import the phpMyAdmin Configuration Storage database.
# mysql -u root -p < create_tables.sql
We will be prompted for the root password. Enter it.
The phpMyAdmin Configuration Storage database will be created with name phpmyadmin.
Let us view the details of the newly created database.
Login to MySQL Server as root.
# mysql -u root -p
We will be prompted for the root password. Enter it and we will be in mysql prompt.
List the existing databases.
mysql> show databases;
We will see the following including our new phpmyadmin database.
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | test | +--------------------+
Select the phpmyadmin database.
mysql> use phpmyadmin;
List all the tables in phpmyadmin database.
mysql> show tables;
We will see the following tables. Note these tables, for we will be using them later.
+-----------------------+ | Tables_in_phpmyadmin | +-----------------------+ | pma__bookmark | | pma__column_info | | pma__designer_coords | | pma__favorite | | pma__history | | pma__navigationhiding | | pma__pdf_pages | | pma__recent | | pma__relation | | pma__savedsearches | | pma__table_coords | | pma__table_info | | pma__table_uiprefs | | pma__tracking | | pma__userconfig | | pma__usergroups | | pma__users | | pma_bookmark | | pma_column_info | | pma_designer_coords | | pma_history | | pma_pdf_pages | | pma_relation | | pma_table_coords | | pma_table_info | | pma_tracking | | pma_userconfig | +-----------------------+
Now we can exit from the MySQL prompt.
mysql> exit;
4) Creating the Control User
The Control User is a special MySQL user configured with limited permissions and needed by phpMyAdmin in the following situations:
• To use any of the phpMyAdmin Configuration Storage features.
• To use Cookie authentication and HTTP authentication with MySQL Server versions 4.1.2 or older.
• To use Cookie authentication and HTTP authentication, if the MySQL Server is running with the --skip-show-databases flag.
Here we will be creating the Control User named pma with access from example.com, so the full username will be [email protected].
NOTE: The standard name for Control User is pma. But you can use another name if you like.
Login to MySQL Server as root.
# mysql -u root -p
We will be prompted for the root password. Enter it and we will be in mysql prompt.
Creating the Control user
Let us create the MySQL user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
• Replace PASSWORD with your preferred password.
mysql> create user 'pma'@'example.com' identified by 'PASSWORD';
Granting limited privileges for Control user on mysql database
For phpMyAdmin to work with Cookie authentication and HTTP authentication, Control User needs limited privileges on the mysql database.
The mysql database contains the MySQL Server‘s privilege system grant tables. So before continuing, you may want to take a look at http://dev.mysql.com/doc/refman/5.6/en/grant-table-structure.html.
Also it is good to have an understanding of the following.
• List of all existing databases.
• List of tables in the database mysql.
• Contents of the table db.
• Contents of the table tables_priv.
• Contents of the table user.
For help, goto http://howtolamp.com/lamp/mysql/5.6/basics/.
Execute the following commands in mysql prompt.
Grant USAGE privilege on mysql database, to the user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
mysql> GRANT USAGE ON mysql.* TO 'pma'@'example.com';
Grant SELECT privilege on db table of mysql database, to the user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
mysql> GRANT SELECT ON mysql.db TO 'pma'@'example.com';
Grant SELECT privilege on the listed fields of tables_priv table of mysql database, to the user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
• The fields that have been excluded are Grantor and Timestamp.
mysql> GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'example.com';
Grant SELECT privilege on the listed fields of user table of mysql database, to the user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
• The fields that have been excluded are Password, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string and password_expired.
mysql> GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO 'pma'@'example.com';
Granting privileges for Control user on Configuration Storage database
For phpMyAdmin to work with Configuration Storage features, Control User needs to have privileges on the Configuration Storage database.
Execute the below command in mysql prompt.
Grant SELECT, INSERT, UPDATE and DELETE privileges on the phpmyadmin database to the user [email protected].
NOTE:
• Replace pma with your preferred username.
• Replace example.com with the hostname of your machine.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'example.com';
5) Configuring Apache HTTP Server installation
We will be using the the URL http://phpmyadmin.example.com for accessing phpMyAdmin. If you are planning to use a different URL, make changes accordingly from here on.
WARNING: Make sure DNS or /etc/hosts entries are properly configured, so that the domain name phpmyadmin.example.com resolves to the IP address 192.168.0.100.
First, make sure you have configured a Virtualhost for the main website example.com, as explained in the first step Setting up Virtualhost for the main website.
Now, open Apache HTTP Server‘s Virtualhost configuration file httpd-vhosts.conf. Add the following content to the end of file.
NOTE:
• Replace 192.168.0.100 with the IP address of your machine.
• Replace phpmyadmin.example.com with your preferred URL for accessing phpMyAdmin.
<VirtualHost 192.168.0.100:80> ServerAdmin root@localhost DocumentRoot "/usr/local/apache2/phpmyadmin" ServerName phpmyadmin.example.com ErrorLog "logs/phpmyadmin.example.com-error_log" CustomLog "logs/phpmyadmin.example.com-access_log" common <Directory "/usr/local/apache2/phpmyadmin"> AllowOverride AuthConfig Limit Require all granted </Directory> <Directory "/usr/local/apache2/phpmyadmin/libraries"> Require all denied </Directory> <Directory "/usr/local/apache2/phpmyadmin/setup/lib"> Require all denied </Directory> <Directory "/usr/local/apache2/phpmyadmin/setup/frames"> Require all denied </Directory> </VirtualHost>
Restart the apache service gracefully.
# service apache graceful
6) Configuring PHP installation
Make sure you have configured your PHP installation to work with MySQL Server, as instructed here.
7) Configuring phpMyAdmin
phpMyAdmin reads it’s configuration from the main configuration file config.inc.php in the base directory.
There are two ways to create our configuration file.
♦ Using the setup script (Intended for GUI interface)
OR
♦ Manually using a text editor (Intended for both GUI and Terminal interface)
♦ Using the setup script
Switch to the phpmyadmin directory in terminal.
# cd /usr/local/apache2/phpmyadmin
Create a directory named config inside phpmyadmin directory.
# mkdir config
Copy the sample configuration file config.sample.inc.php to the config directory as config.inc.php.
# cp -v config.sample.inc.php config/config.inc.php
OPTIONS EXPLAINED
-v
explain what is being done
Provide write permission to others for config directory.
# chmod -R o+w config
OPTIONS EXPLAINED -R operate on files and directories recursively o+w Set write permission for others who are not members of the file's group
Open the web browser and goto http://phpmyadmin.example.com/setup/
We will be greeted with the setup page.
STEP 1
In the Servers section, create a new configuration for our existing MySQL Server.
Click on the button New server. This will take us to the server configuration page.
In the Basic settings tab, perform the following steps.
• Leave the Verbose name of this server field blank, so that the hostname of server will be used. However if we want, we can enter a name for the server.
• Set the Server hostname field to the hostname of our machine. I set mine as localhost.
NOTE: Make sure you have read Disabling remote login for MySQL Server.
• Set the Server port field to 3306, which is the default for our MySQL Server installation.
• Set the Server socket field to /tmp/mysql.sock, which is the default for our MySQL Server installation.
• Set the Connection type field as socket. We will be using the UNIX socket instead of TCP to connect to the MySQL Server. Because it works whether Remote login for MySQL Server is disabled or not.
Switch to the Authentication tab and perform the following steps.
• Look for the User for config auth field. If it is root, delete it. Because it is meant for Config Authentication and we are using Cookie authentication.
NOTE: Read about different Authentication modes for phpMyAdmin.
Switch to the Configuration storage tab and perform the following steps.
• Set the Database name field to phpmyadmin
• Leave the Control host field blank. We do not need it under current circumstances.
• Leave the Control port field blank. We do not need it under current circumstances.
• Set the Control user field to the name of your Control user. I set mine as pma
• Set the Control user password field to the password of your Control user.
• Set the Bookmark table field to pma__bookmark
• Set the Relation table field to pma__relation
• Set the User preferences storage table field to pma__userconfig
• Set the Users table field to pma__users
• Set the User groups table field to pma__usergroups
• Set the Hidden navigation items table field to pma__navigationhiding
• Set the Display columns table field to pma__table_info
• Set the Column information table field to pma__column_info
• Set the SQL query history table field to pma__history
• Set the Recently used table field to pma__recent
• Set the UI preferences table field to pma__table_uiprefs
• Set the SQL query tracking table field to pma__tracking
• Set the PDF schema: table coordinates field to pma__table_coords
• Set the PDF schema: pages table field to pma__pdf_pages
• Set the Designer table field to pma__designer_coords
• Set the Servers_savedsearches_name field to pma__savedsearches
NOTE: Read about phpMyAdmin Configuration Storage settings.
Now, click the Apply button. So all our changes are saved.
STEP 2
On the left side, click on the Features menu. In the features section, goto Security tab. Here the value for Blowfish secret has been automatically generated for us. It is the passphrase used for encrypting cookies in Cookie authentication mode. Take a note of this value. Click Apply.
NOTE: In the Features menu, in Import / export tab, there will be an option for Recoding engine. Here the default value iconv will be used while recode will not be available. This is because we have not compiled recode extension with PHP, as it conflicts with IMAP extension.
STEP 3
All essential settings have been made. However if we want we can take a look around. When finished, click on the Overview menu. This will take us to the initial page. Under the Configuration file section, click Save. Our server configuration will be saved. We can close our web browser now.
STEP4
phpMyAdmin’s setup script does not provide a way to set the Configuration Storage setting favorite. So we will have to set this manually.
Open the configuration file config.inc.php in a text editor. Add the following line to the end of Server configuration section.
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
Save and close the file.
Come back to the terminal. We must still be in the phpmyadmin directory. Move the newly created configuration file to the phpmyadmin directory.
# mv config/config.inc.php .
Remove the config directory. We do not need it anymore.
# rmdir config
♦ Manually using a text editor
Switch to the phpmyadmin directory in terminal.
# cd /usr/local/apache2/phpmyadmin
Create a new file named config.inc.php with the following entry.
NOTE:
• Make sure you have read Disabling remote login for MySQL Server.
• Replace example.com with the hostname of your machine.
• Replace CONTROL_USERNAME, CONTROL_PASSWORD and BLOWFISH_SECRET with your preferred values.
• BLOWFISH_SECRET – Read how to generate your own Blowfish secret.
• Read about phpMyAdmin Configuration Storage settings.
• View the configuration options for phpMyAdmin.
• There is a sample configuration file config.sample.inc.php in the base directory, which can be used as a template.
<?php /* Servers configuration */ $i = 0; /* Server: example.com [1] */ $i++; $cfg['Servers'][$i]['verbose'] = ''; $cfg['Servers'][$i]['host'] = 'localhost'; $cfg['Servers'][$i]['port'] = 3306; $cfg['Servers'][$i]['socket'] = '/tmp/mysql.sock'; $cfg['Servers'][$i]['connect_type'] = 'socket'; $cfg['Servers'][$i]['auth_type'] = 'cookie'; $cfg['Servers'][$i]['user'] = ''; $cfg['Servers'][$i]['password'] = ''; $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; $cfg['Servers'][$i]['controluser'] = 'CONTROL_USERNAME'; $cfg['Servers'][$i]['controlpass'] = 'CONTROL_PASSWORD'; $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark'; $cfg['Servers'][$i]['relation'] = 'pma__relation'; $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig'; $cfg['Servers'][$i]['users'] = 'pma__users'; $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups'; $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding'; $cfg['Servers'][$i]['table_info'] = 'pma__table_info'; $cfg['Servers'][$i]['column_info'] = 'pma__column_info'; $cfg['Servers'][$i]['history'] = 'pma__history'; $cfg['Servers'][$i]['recent'] = 'pma__recent'; $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs'; $cfg['Servers'][$i]['tracking'] = 'pma__tracking'; $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords'; $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages'; $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords'; $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches'; $cfg['Servers'][$i]['favorite'] = 'pma__favorite'; /* End of servers configuration */ $cfg['blowfish_secret'] = 'BLOWFISH_SECRET'; $cfg['DefaultLang'] = 'en'; $cfg['ServerDefault'] = 1; $cfg['UploadDir'] = ''; $cfg['SaveDir'] = ''; ?>
Let give proper permissions to the install directory and configuration files, so phpMyAdmin can work properly.
Change the ownership of phpmyadmin directory to apache.
# chown -R apache:apache /usr/local/apache2/phpmyadmin
Change the permission mode of phpmyadmin directory to 750.
# chmod -R 750 /usr/local/apache2/phpmyadmin
Change the permission mode of of configuration file config.inc.php to 600.
# chmod 600 /usr/local/apache2/phpmyadmin/config.inc.php
Finally let us access our phpMyAdmin installation.
Open the web browser and goto http://phpmyadmin.example.com. We will be greeted with the phpMyAdmin login page. The Username and Password correspond to the MySQL Server login details. Entering them will present us with the phpMyAdmin interface.
8) Installation Review
Install location
/usr/local/apache2/phpmyadmin/
URL
http://phpmyadmin.example.com
Main configuration file
/usr/local/apache2/phpmyadmin/config.inc.php
After installing phpMyAdmin, it is recommended to view the following sections.
Securing phpMyAdmin
Customizing phpMyAdmin
