Archive

Posts Tagged ‘sql’

How to reset the MySQL root password

December 30th, 2010 Comments off

The MySQL server root password is not used very often and you can forget it or you inherit some already setup system and you do not have the MySQL root password. Technically, there is no way to recover the password but you can reset it.

The main inconvenience is that you have to stop the database server twice. The following instructions were tested on a Fedora Linux distribution but the main idea can be used on different operating systems, including Windows.

The main idea is to restart the mysql server without security, change the root password and restart again in normal mode. I recommend not doing this on a live server but if you already need this than there is a big sign that your system design has some issues.

First, stop the mysql server:

# service mysqld stop

Start the server with an option that enables anyone to connect without password.

# mysqld_safe --skip-grant-tables &

Wait for the mysql server start, you will get some info about it:

101230 20:00:03 mysqld_safe Logging to '/var/log/mysql/error.log'.
101230 20:00:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Connect as root without the need for a password:

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.52-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Let’s say that you want to use the password “NEWPASS”, in the mysql prompt type:

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

Restart the mysql server to use the new password:

 # service mysqld restart 

Now you have your regular database server with a shiny new root password. Do not forget it!

Categories: Linux, Software Tags: ,