[HowTo] Stop phpmyadmin from asking username, password in localhost

Yesterday, I showed you how you can keep local installation of phpmyadmin from logging you out every few minutes. I took one more step and edited configuration to not ask me username, password at all. Now phpmyadmin never asks me for mysql username and password.

Keep in mind if you follow this tutorial anyone would be able to access your database using phpmyadmin unless you deny access via apache configuration. Don’t do this on production servers.

Steps to follow:

  • Run: gksudo gedit /etc/phpmyadmin/config.inc.php
  • Find:
    $cfg['Servers'][$i]['auth_type'] = 'cookie';

    And change it to:

    $cfg['Servers'][$i]['auth_type'] = 'config';
  • Just below this line, add:
    $cfg['Servers'][$i]['user'] = '';
    $cfg['Servers'][$i]['password'] = '';

    Replace and with mysql username and password.

  • Save the file.
  • Changes will be applied immediately, no need to restart apache.

Now, you’ll never have to enter username and password in phpmyadmin.

Cheers!

[HowTo] Extend phpmyadmin session expiration time

Recently, I got frustrated being logged out every few minutes (24 minutes, to be precise) from phpmyadmin on my localhost. I make some db changes, write some code and when I get back to phpmyadmin, I would see my session had expired. To fix this, I changed the configuration so that my session would expire only after 24 hours of inactivity.

Steps to follow:

  • Copy the following code at the end of /etc/phpmyadmin/config.inc.php:
    $cfg['LoginCookieValidity'] = 60*60*24;
    ini_set('session.gc_maxlifetime', $cfg['LoginCookieValidity']);

This will let your session expire only after 60*60*24 seconds, that is one day.

Cheers!

[HOWTO] Reset MySQL root password on Ubuntu when you’ve forgotten it

To err is human. Humans forget things, MySQL root password is one of those things. However, its not very difficult to reset the root password if you have root access to the machine.

The tutorial does seem a bit long because of all the alternate ways to kill and start the mysqld, but trust me its simple.

Here’s in short what we will be going to do:

  • Step 1: Stop mysql server process
  • Step 2: Start mysqld with –skip-grant-tables option.
  • Step 3: Run mysql without any parameters.
  • Step 4: Change the root admin password (old password not required.)
  • Step 5: Close mysql and restart mysql server.

And here are the detailed instructions to reset MySQL root password:

  • The first thing you need to do is shut down mysqld if its running. In most of the linux flavor (including Ubuntu, ofcourse), you’d execute the following command to stop the mysqld:

    sudo /etc/init.d/mysql stop

    This would stop mysqld. You can confirm it by the following command:

    ps -A | grep mysql

    If it shows any entry of mysqld, it means it is still running. You can try the following command to stop it:

    sudo stop mysql

    See if it has closed now, if not, you’d need to remove its entry from the init.d by using the following command and restart the computer so it doesn’t autostart:

    sudo update-rc.d -f mysql remove

    Now after restarting the computer, mysql should not be running you check it from the same command ie. ps -A | grep mysql

  • Now you need to again start the mysqld with –skip-grant-tables. This switch enables any client to login to mysql root without password. Use the following command to start mysqld again:

    sudo mysqld_safe –skip-grant-tables &

  • Now type in mysql to start the client.
  • Execute the following queries to change the password:

    UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE User=’root’;
    FLUSH PRIVILEGES;

    This would change the root password to new_password. You can change it to any password of your choice.

  • Type in exit to shut down mysql.
  • You now have to close the mysqld again. You can close the terminal, that would kill the process. If it doesn’t, just kill it. Use the following command:

    sudo pkill mysql*

    That would definitely kill the mysqld and all related subprocesses.

  • Restart the mysql in normal mode. If you had removed the mysql from starting up using update-rc.d, see below for how to add it to startup scripts back again. To start mysqld execute either of the following command:

    sudo start mysql

    OR

    sudo /etc/init.d/mysql start

  • To add the mysql to startup script:

    sudo update-rc.d mysql defaults

    You can now restart the computer and mysql will start automatically.

That was simple, wasn’t it?

Cheers!

[HOWTO] Use local phpmyadmin with remote MySQL

My host recently started allowing remote connections to its mysql database. It was a really useful feature – I could not change database settings from my own computer. Since it started allowing remote connections from my IP, I could use any mysql client to connect to it. I fired up terminal and tried to connect using the command:

mysql –host=69.89.31.242 –user=username –password=password

It connected. This was just to test the connection. I wanted to connect my phpmyadmin with this remote host. For that I edited /etc/phpmyadmin/config-db.php file and changed:

$dbserver=”;

to

$dbserver=’69.89.31.242′;

I loaded the url http://localhost/phpmyadmin, entered the database username and password and I got in. I could administer my database from my local phpmyadmin installation.

Cheers!