Wednesday, April 25, 2012

How to : Connect MYSQL and phpMyAdmin


Due to security measures you cannot connect phpMyAdmin to MYSQL "out of the box". You require to set MYSQL root password first!

After installing XAMPP,if you try to connect to MYSQL using phpMyAdmin from Tools menu, you may be greeted with this message  Access denied for user: 'root'@'localhost' (Using password: NO)
The following steps will help to resolve this problem.
1. First Set the MYSQL root password from command line
2. Then edit "config.inc.php" file
3. Take some additional steps

Step 1.Set the MYSQL root password from command line
Suppose XAMPP is installed in c:\XAMPP
Switch to folder c:\XAMPP\MYSQL\bin
Run the following command
$ MYSQL -u root -p
you will be in MYSQL prompt. It clearly indicates that the  password was set initially as blank. Now the password need to be set first. Lets set the password as "linux".
2) First switch to MYSQL database. issue the following command
MYSQL> use MYSQL;
3) In order to change password for user "root", enter the following update command:

MYSQL> update user set password=PASSWORD("linux") where User='root';
4) Then run following commands to reload the privileges
MYSQL> flush privileges;
MYSQL> quit

Up to this stage we have set the MYSQL root password to "linux" from blank password. Now we have to tell about this change to config.inc.php file.
Step 2. Edit "config.inc.php" file
Switch to folder C:\xampp\phpMyAdmin
open config.inc.php file in text editor,go to block which has comment /* Authentication type and info */ .There you can set the root password as follows
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'linux';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
 Restart the XAMPP. Hope you will be able to connect to phpMyAdmin (tools-> phpMyAdmin)

3. Additional Steps: 
Check seurity of database

3.1. Click on Security tab to confirm that MYSQL root is protected. (http://localhost/security/). you should now able to see following.
The MYSQL admin user root has no longer no password                :    Secure
Done! Not only you get rid off from error but also secure MYSQL too.

Allow MYSQL to import larger sized file
3.2. By default MYSQL permit you to import file size upto 8MB .(MYSQL maximum import ax 8,192KiB). Must get rid off at this initial stage itself
Open php.ini file from c:\xampp\php folder and search for "post_max_size ". Change the value to 32.

Happy MYSQLing with phpMyAdmin

2 comments:

Todaydownload.com said...

These facts are really interesting. Few of them were well known for me but many of them were brand new for me too!
I will print this one out and show to my friends because they will be definitely interested in that. Thanks!
phpMyAdmin

Todaydownload.com said...
This comment has been removed by a blog administrator.