How to recover mysql root password in Linux

mysql

I installed my mysql two months ago and got busy afterwards. I already set everything, root password, permissions, cron for dump, phpmyadminl and some other stuffs. Since i got busy, never had a chance to explore and go back until now. But the problem is, i forgot the root password i set. In case you need to recover the root password in Linux here are some steps that can help you.

 

 

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

 

The corresponding commands to use in accordance to above steps are the following.  Make sure you are logged as root in your linux box when doing it.

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

Output:

130607 14:16:49  mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130607 14:16:49  InnoDB: Initializing buffer pool, size = 8.0M
130607 14:16:49  InnoDB: Completed initialization of buffer pool
130607 14:16:49  InnoDB: Started; log sequence number 0 44233

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("New_root_password") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

 

Reference

Repairing Corrupted Mysql tables

MySQL tables are the most critical component of the database that allows you to store data in an organized manner. Thus, you can easily manipulate them and get effective query results. But sometimes, these tables give unexpected results and error messages. The reason could be table corruption, which needs to be restored from backup. However, if backup falls short of restoring data, you should use commercial MySQL Database Recovery applications.

You might receive the below error message in mysqld error log:

“[ERROR] Got error 127 when reading table “

You receive a series of such error messages.

Cause

The prime cause of this error message to occur is table corruption. A MySQL table might get corrupt due to any of these reasons:

• mysqld process is killed unexpectedly
• Hardware issues
• MySQL code or MyISAM/InnoDB code is suffering from bugs
• An external program and server is modifying the same table at the same time
• Unexpected system shutdown

Solution

To correct table corruption, you should follow these steps in order:

• You should first check the table for corruption using CHECK TABLE command. To run the command, make sure that the server is running. This check is used with following syntax:

CHECK TABLE tablename [options]

Here, options used are FAST, QUICK, MEDIUM etc.

• If CHECK TABLE command gives corruption errors, you should repair it using REPAIR TABLE command. Before running the command, ensure that about twice as much disk space as of affected table is present on disk. The syntax is as:

REPAIR TABLE tablename [options]

Here, options used are QUICK, EXTENDED etc.

• Again, check the table for corruption.
• If table still remains corrupt, you should delete the damaged table if you have a valid database backup.
• In case, no database backup is available or it seems to be damaged or incomplete, make use of third-party MySQL Recovery utilities. These software are specifically designed to scan damaged MySQL databases and correct corruption errors. Designed to perform thorough database repair, these MySQL Database Recovery tools provide advanced file repair functionalities, while maintaining data integrity.

Stellar Phoenix Database Recovery For MySQL is the finest utility to repair and restore damaged MySQL databases. It restores files created with InnoDB (.ibdata, .ibd and .frm) and MyISAM (.myd, .myi and .frm) database storage engines. It supports MySQL 5.x. This MySQL Recovery is compatible with Windows Vista, XP, 2003, and 2000. It can repair databases created with both Windows and Linux platforms.