Few days ago I’ve had in front of me a Gentoo installation with a MySQL Daemon, no documentation provided with the machine and absolutely no root password for the DB. I’ve tried to obtain the root password for MySQL and that’s what I’ve done. These instructions are valid for every linux distro, no matter about the release or flavor.
First of all, you need to stop mysql daemon and all running instances of mysql, something like that:
~# /etc/init.d/mysql stop
* Stopping mysql ...
* Stopping mysqld (0) [ ok ]
Starting/stopping services may vary according to your linux distribution documentation, make a double check to remove even zombie processes or mysql opened instances (ps aex|grep mysql) and kill them if any
Now you can run this command to start the daemon with full privileges and no authentication:
mysqld_safe --skip-grant-tables &
Even if not reported in the man page or not documented with:
~# mysqld_safe --help
Usage: /usr/bin/mysqld_safe [OPTIONS]
--no-defaults Don't read the system defaults file
--defaults-file=FILE Use the specified defaults file
--defaults-extra-file=FILE Also use defaults from the specified file
--ledir=DIRECTORY Look for mysqld in the specified directory
--open-files-limit=LIMIT Limit the number of open files
--core-file-size=LIMIT Limit core files to the specified size
--timezone=TZ Set the system timezone
--mysqld=FILE Use the specified file as mysqld
--mysqld-version=VERSION Use "mysqld-VERSION" as mysqld
--nice=NICE Set the scheduling priority of mysqld
--skip-kill-mysqld Don't try to kill stray mysqld processes
--syslog Log messages to syslog with 'logger'
--skip-syslog Log messages to error log (default)
--syslog-tag=TAG Pass -t "mysqld-TAG" to 'logger'
All other options are passed to the mysqld program.
you can find more info on it at http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html (wish to have this link or just read the doc before doing it…)
Now enter in the DB with root privileges:
mysql -u root
and locate mysql default schema
use mysql;
“user” table is where you can find/reset/update information related to mysql users (not that strange…)
mysql> show columns from user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)
Now update your password with something like that:
update user set password=PASSWORD("rememberyournewpassword") where User='root';And don’t forget to flush privileges to have everything updated
flush privileges;
and quit from DB
quit;
then stop/kill the running daemon and restart it in “normal” mode
/etc/init.d/mysql stop
## Even stop running daemons if any
ps aex |grep mysqld # to find sockets
kill -SIGKILL <pid> # to kill running mysqld pid's
Now test your new password with something like that
mysql --host=127.0.0.1 --user=root -p
and insert your new password
that’s it
Ben
