Friday, January 22, 2010

Tweaking phpMyAdmin configuration

Configuring the phpMyAdmin is the most tedious part. Patience and attention to detail will take us through this arduous task.

First, we will need to add a password to the "root" user of MySql. This is always a recommended procedure. For the sake of the tutorial the password will be the string "password". Navigate to WAMP system tray icon -> MySQL -> Mysql console to open the MySql console. Change the root password and reset privileges as illustrated below :

Remember to include password() function in your query to facilitate the password hashing.

You can now open the phpMyAdmin interface from the system tray. You will notice a new error message. This is because the password has not been updated in the phpMyAdmin config file.

Open your WAMP folder installation. Navigate to wamp\apps\phpMyAdmin3.2.0.1 (or the relevant version). Open the file in a text editor.

Add the password for the root, which is "password" in our case and change the authentication type to "cookie", as illustrated below:

$cfg['Servers'][$i]['auth_type'] = 'cookie'; $cfg['Servers'][$i]['password'] = 'password';

Also, add the the following line at the top of the file as illustrated below:

$cfg['blowfish_secret'] = 'password';

Relaunch the phpMyAdmin from the system tray icon. At the launch, you will now see a login screen asking you for the username and password. Enter the credentials as "root" and "password" as shown below.

In most cases, you will notice the following warning at the bottom of the page once you have logged in:

For intermediate and advanced users, these advanced features related to linked tables are important. We will now see how to activate these features. First create a database called "phpmyadmin" as shown below.

Click on the newly created database. Navigate to the privileges tab of the newly created database. Click on "Add a new user" as shown below:

Create a new user called "pma". Choose "localhost" for host and "password" for password. We are choosing "password" for all passwords only for the sake of simple explanation.
Choose "grant all privileges on database phpmyadmin" option in the Database for User segment.
Click on the "Go" button.

Go back to the file in wamp\apps\phpMyAdmin3.2.0.1 (or the relevant version) and open the file in a text editor. Add the following lines as shown in the illustration.

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';

$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'password';

Log out of phpMyAdmin interface. Login again to the interface using the credentials, username "pma" and password "password" as shown below.

This section is important. We need to import a file that is going to add all the additional features relating to linked tables. Once you have logged in as user "pma" click on the import button and import the file "wamp\apps\phpMyAdmin3.2.0.1 (or the relevant version)\scripts\create_tables.php".

This should add ~8 tables related to linked-tables, bookmarks, table-info, pdf-pages etc.

Restart the WAMP server.

After this step you may choose to the following:
  1. change all passwords
  2. revoke all privileges to the "pma" user on "phpmyadmin" and grant only basic privileges like SELECT,INSERT, UPDATE

We are done configuring the WAMP server on port 8080.

You can also navigate to the main page. Good luck.

Please leave a comment or mail me, if there are any questions.


Venkatesh Varalu said...

Bookmarking it. :)

aspyre said...

After i changed d password to 'password' and tried to launch phpMyAdmin it gave this error 'Cannot load mysqli extension'. Pls what do i do?

Abishek Ravi said...

@aspyre please check the following thread :

prajwal g said...

FOr "Enter password" which password should I enter in the beginning. I don't remember keeping any password for this