Friday, May 7, 2010

How an administrator can unlock a table to overcome deadlock situation in MySQL

This is a common problem for a MYSQL DBA if the application has table lock bug. To unlock a table I use session kiil procedure. The steps are....

Step 1: login into mysql user root user.
Step 2: list all the sessions running in MySQL server.

show processlist;

The output will be looks like....

+-----+----------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+------+---------+------+-------+------------------+
| 349 | his_user | localhost | NULL | Query | 0 | NULL | show processlist | +-----+----------+-----------+------+---------+------+-------+------------------



...if the MySQL server has no other running process. Otherwise the output will show other process id also. For locked processes the status will indicate the work lock.

Step 3: The last step is to kill the locked running process using process Id.

kill xxx;

'xxx' is the process Id value.

Monday, May 3, 2010

How to reset MySQL root password

These steps are required if we have lost(for my case) the root password for MySQL administration.

Step 1: At first I stop the MySQL service from Linux root user.

/etc/init.d/mysql stop

Step 2: Then I start again MySQL service with --skip-grant-tables, so that at the time of login into the MySQL service system should not ask for authentication.

mysqld_safe --skip-grant-tables &

Step 3: In this step I first login into MySQL root user without password.

mysql -u root

Then I issue the following MySQL command to change the root password and quit to Linux shell..

use mysql;
update user set password=password('password') where user='root';
flush privileges;

Step 4: The last stage is to restart MySQL server using default option.

/etc/init.d/mysql restart


The password for root user is changed now. I have done all these procedures on the OS Debuan 5.