10 easy phpMyAdmin tweaks to simplify MySQL administration

One of the easiest ways to manage MySQL databases is using phpMyAdmin. phpMyAdmin is a free tool intended to handle the administration of MySQL using the web. There are several good reference materials available, in case you want to learn more about phpMyAdmin and what it can do. Recently, I described how to install phpMyAdmin in Ubuntu. In this post, I will provide a few phpMyAdmin tweaks to your liking. If find that with a little bit of tweaking I can increase my productivity within phpMyAdmin.

phpMyAdmin Tweaks

Not all phpMyAdmin tweaks may be of interest to you and so just pick the ones that you like. Refer to phpMyAdmin Wiki for further details and tweaks.

1. Increase phpMyAdmin import file size

Although this is not one of the phpMyAdmin tweaks (it is a PHP tweak), it is one of the most commonly encountered one while exporting databases as files. By default, PHP allows importing files up to 2 MB. To increase this, edit /etc/php5/apache2/php.ini. Before you begin making changes, make a backup of the file:

sudo cp -a /etc/php5/apache2/php.ini /etc/php5/apache2/php.ini.bak

First, make sure file uploads are allowed. Find the following line and ensure file_uploads is set to On.

; Whether to allow HTTP file uploads.
; http://php.net/file-uploads
file_uploads = On

Next, to increase file import size, find the following line and set the file size to your desired value (example: 32M):

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
upload_max_filesize = 32M

Then, find the following line and set the POST data size to your desired value (example: 32M):

; Maximum size of POST data that PHP will accept.
; http://php.net/post-max-size
post_max_size = 32M

Finally, find the following line and set the script memory limit to your desired value (example: 128M):

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
memory_limit = 128M

The “M” in the “128M” refer to MB or Megabytes. Note that you should have at least 128M of available memory. This should increase the file import size for PHP and phpMyAdmin. Reload Apache for the settings to take effect:

sudo service apache2 reload

You may have to exit and re-login into phpMyAdmin.

2. Increase authentication timeout or login cookie validity

It can be annoying while you move to the other window and your phpMyAdmin session ends due to inactivity. You can increase the time your phpMyAdmin session remains active by editing /etc/phpmyadmin/config.inc.php. First backup the file:

sudo cp -a /etc/phpmyadmin/config.inc.php /etc/phpmyadmin/config.inc.php.bak

Then edit it and add the following line at the end of the file:

$cfg['LoginCookieValidity'] = 36000;

36000 is the number of seconds your phpMyAdmin session will remain active during inactivity. Reload your Apache server for the settings to take effect. You may have to exit and re-login into phpMyAdmin.

Please note that this loosens your security just a little bit. But doing so can help you greatly during development or initial database setup. But once your work is done, I recommend removing the line or commenting it out by adding // in the front.

3. Change destination for table icon link

This is not the greatest of annoyances for most people but can be of help to some. Within a database, when you click one of the table names listed on the left side, it takes you to “Browse” tab of the table. Instead if you click on the little table icon, it takes you to “Structure” tab of the table. I personally like this. However, if you would like to make the table icon link take you to “Browse” tab instead, then /etc/phpmyadmin/config.inc.php and add the following line at the end of the file:

$cfg['LeftDefaultTabTable'] = 'sql.php';

Save, exit, and reload Apache for the settings to take effect. You may have to exit and re-login into phpMyAdmin. In addition, you can make the icon link take you any other tab if you like:

$cfg['LeftDefaultTabTable'] = 'tbl_structure.php'; //default. shows fields list
$cfg['LeftDefaultTabTable'] = 'tbl_sql.php'; //shows SQL form
$cfg['LeftDefaultTabTable'] = 'tbl_select.php'; //shows search
$cfg['LeftDefaultTabTable'] = 'tbl_change.php'; //shows insert row

Recommended Reading:

Mastering phpMyAdmin 3.4 for Effective MySQL Management

Learn PHP Programming by Examples

Note that you can have only one of the above options active at any time. If you want to reverse this, just delete the line or comment it out by adding // in front of it.

4. Change destination for table link

By default, clicking on the table name on the left takes you to the “Browse” tab. If you want to change this behavior add one of the following lines at end of /etc/phpmyadmin/config.inc.php:

$cfg['DefaultTabTable'] = 'sql.php'; // default. takes you to the Browse tab
$cfg['DefaultTabTable'] = 'tbl_properties_structure.php'; // shows fields list
$cfg['DefaultTabTable'] = 'tbl_properties.php'; // shows SQL form
$cfg['DefaultTabTable'] = 'tbl_select.php'; // shows search form
$cfg['DefaultTabTable'] = 'tbl_change.php'; // shows insert row

Save, exit, and reload Apache for the settings to take effect. You may have to exit and re-login into phpMyAdmin. If you want to reverse this, just delete the line or comment it out by adding // in front of it.

Continue to next page for more phpMyAdmin tweaks.