Loosing MySql root password could happen to anyone (specially to me :D ).

In this case, we need to start in MySql server in background with --skip-grant-tables flag in order to tell it to ignore any user/password restrictions.

Once MySql server is started with this flag, we can now connect to the database and update the password for user root (you'll probably use this method only if you have forgotten your root's password, not a regular user's password)

But first we need to stop MySql server, otherwise we cannot start it again.

FreeBSD: Stopping MySQL server

/usr/local/etc/rc.d/mysql-server stop Stopping mysql. Waiting for PIDS: 23239, 23239.

Linux (Red Hat): Stopping MySql server

$ service mysqld stop

FreeBSD: Starting MySql server in background with -skip-grant-tables flag

$ /usr/local/libexec/mysqld --skip-grant-tables --user=root & [1] 32549

Linux: Starting mysql server in background with --skip-grant-tables flag

$ /usr/bin/mysqld_safe --skip-grant-tables & [1] 6702

The last two FreeBSD - Linux commands will start mysql server without it asking you for a password.

Now start mysql client and change the password for root:

$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.1.20-beta-log FreeBSD port: mysql-server-5.1.20 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> UPDATE user SET Password=PASSWORD('newpassword') WHERE User='root'; Query OK, 0 rows affected (0.00 sec)

This command updates in user table column Password where column User is 'root' with the result from mysql function PASSWORD() and now the password for user root is recovered.

Now stop mysql server, start it in normal mode and try the new password.

Bookmarks:

Echo "Recover a lost MySQL root password" around:

-------------------advertising-----------------