CSUF LogoCSUF Site Navigation
optics.csufresno.edu

F14 Apache/MySQL/PHP Services & Applications phpMyAdmin

Department of Electrical and Computer Engineering
Associate Professor Gregory R. Kriehn
Forums
Wiki
F14 phpMyAdmin

phpMyAdmin is a MySQL Database Administration Tool based upon php. It is easy to use, and I often login to the program to check on various portions of the database (especially for phpBB, if I am unsure if a spammer has registered). To configure it, make sure that both httpd and mysqld are running. See the Apache and MySQL pages for details.

Install phpMyAdmin

phpMyAdmin can be installed using yum:
~> sudo yum install phpMyAdmin
Hit Enter and press 'y' when asked if you want to install the package and any dependencies.

Protection from Unauthorized Logins

The Apache configuration file for phpMyAdmin is located in /etc/httpd/conf.d/phpMyAdmin. If you take a look at it, you will notice an alias to /phpMyAdmin and /phpmyadmin, in addition to a <Directory "/usr/share/phpMyAdmin"> section that disallows anyone except for the localhost from logging into phpMyAdmin. If you want to change this you can, but if you do, you will at least probably want to password protect the directory using .htaccess. Because phpMyAdmin is a frequent source of attack from abusive hosts, I like the restrictions on the directory, so I have left it alone.

Now you can open a web browser and go to:
http://localhost/phpmyadmin
This will allow you then access the phpMyAdmin login page. Type in "root" in the Username: box and your MySQL super user password in the Password: box. Click the Go button, and you should see the "Welcome to phpMyAdmin" web page. Once you successfully login, exit.

Configure MySQL for phpMyAdmin

There are a few more configuration steps that need to be done before we can actually use phpMyAdmin in earnest. Assuming that you have MySQL Version 5 or above (as is the case with F14), login to MySQL from a terminal window:
~> mysql -u root -p
Type in the MySQL super user password and hit Enter. Then type in the following information:
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY '[pmapassword]';
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'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
where [pmapassword] is a password that you need to supply for the pma (phpmyadmin) user. This will create a special "control user" that has only the SELECT privilege on the mysql.user (all columns except "Password"), mysql.db (all columns), mysql.host (all columns), and the mysql.tables_priv (all columns except "Grantor" & "Timestamp") tables. You will probably have to Hit Enter twice.

For MySQL Version 5 or later (again, as is the case for F14), open Firefox, click on File -> Open File... :

file:///usr/share/phpMyAdmin/scripts/create_tables.sql
Once the web page opens, click Edit -> Select All followed by Edit -> Copy. Then go back to the terminal window that is still logged into MySQL and click Edit -> Paste (if you are running a Gnome Terminal), or the middle button of your mouse to paste the information into MySQL. When you hit Enter, the entire query should be executed at the mysql> prompt. If the query did not fully execute, carefully examine where it stopped compared to the text in create_tables.sql and copy the exact characters (to the end of the file) that need to be finished. Then paste them back into the terminal at the location where the query stopped and hit Enter again. Alternatively, you could use the key combination Ctrl + Shift + V to paste the entire query again, overwriting the original. When the query finishes, exit MySQL:
mysql> exit
Lastly, we need to edit /etc/phpMyAdmin/config.inc.php using sudo. There are several searches that need to be performed. Once you find each line, type in the appropriate information.

1.  Search for $cfg['Servers'][$i]['controluser'] = '';. Type in:

$cfg['Servers'][$i]['controluser'] = 'pma';
2.  Search for $cfg['Servers'][$i]['controlpass'] = '';. Type in:
$cfg['Servers'][$i]['controlpass'] = '[pmapassword]';
where [pmapassword] is the password that you chose in MySQL for the pma user.

3.  Search for $cfg['Servers'][$i]['pmadb'] = '';.  Type in:

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
4.  Search for $cfg['Servers'][$i]['bookmarktable'] = '';. Type in:
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
5.  Search for $cfg['Servers'][$i]['relation'] = '';. Type in:
$cfg['Servers'][$i]['relation'] = 'pma_relation';
6.  Search for $cfg['Servers'][$i]['table_info'] = '';. Type in:
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
7.  Search for $cfg['Servers'][$i]['table_coords'] = '';. Type in:
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
8.  Search for $cfg['Servers'][$i]['pdf_pages'] = '';. Type in:
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
9.  Search for $cfg['Servers'][$i]['column_info'] = '';. Type in:
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
10.  Search for $cfg['Servers'][$i]['history'] = '';. Type in:
$cfg['Servers'][$i]['history'] = 'pma_history';
Save and exit. Go back to your web browser and once again go to:
http://[host].[domain].[name]/phpmyadmin
Depending on whether your web browser is still open, you may (or may not) have to re-type the username and password to get by .htaccess. Regardless, login to phpMyAdmin as root. Once there, click on the down arrow in the Database: selection dialog box in the left column. You should see the "mysql", "phpmyadmin", and "test" databases listed. You may delete ("drop") the "test" database if you wish.

phpMyAdmin is now configured! Be very careful when using it, lest you corrupt a database...