Posted by Steve on Thu 28 Sep 2006 at 09:12
Resetting the root password of a MySQL database is trivial if you know the current password if you don't it is a little tricker. Thankfully it isn't too difficult to fix, and here we'll show one possible way of doing so.
If you've got access to the root account already, because you know the password, you can change it easily:
steve@steve:~$ mysql --user=root --pass mysql Enter password: mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root'; Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) mysql> exit Bye
However if you don't know the current password this approach will not work - you need to login to run any commands and without the password you'll not be able to login!
Thankfully there is a simple solution to this problem, we just need to start MySQL with a flag to tell it to ignore any username/password restrictions which might be in place. Once that is done you can successfully update the stored details.
First of all you will need to ensure that your database is stopped:
root@steve:~# /etc/init.d/mysql stop
Now you should start up the database in the background, via the mysqld_safe command:
root@steve:~# /usr/bin/mysqld_safe --skip-grant-tables & [1] 6702 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6763]: started
Here you can see the new job (number "1") has started and the server is running with the process ID (PID) of 6702.
Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:
root@steve:~$ mysql --user=root mysql Enter password: mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root'; Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) mysql> exit Bye
Now that you've done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing "fg", then kill it by pressing "Ctrl+c" afterwards.
This will now allow you to start the server:
root@steve:~# /etc/init.d/mysql start Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables..
Now everything should be done and you should have regained access to your MySQL database(s); you should verify this by connecting with your new password:
root@steve:~# mysql --user=root --pass=new-password-here Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.24a-Debian_4-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> exit Bye
If you'd like to automate this process you could start by looking at this simple shell script which will allow you to reset a password with one command.
[ Parent ]
The debian.cnf file? Well - its used by the mysql startup scripts as the config file for mysqladmin, its used (AFAIK) by the install scripts of the mysql deb's for upgrades, perhaps others too.
On a properly configured system - the debian.cnf file is root access only. If you're already root then you can restart mysql without the grant tables anyway.
[ Parent ]
[ Parent ]
Feel free to raise a bug on the mysql packages :)
[ Parent ]
Hmm - missed a line there.
Regards to "Worse yet, we find one of the first comments in a visible-to-anyone public discussion on how to change the DBA's password is an open explanation about how to DEFEAT the DBA's password and take control of the database anyway."
I can't agree. "Security by not talking about it" isn't security to me. This file is on ALL debian systems that use debian's packaged mysql. Hardly unknown and many many machines.
The better the administrators of these systems understand on what is present on their systems and why the better.
If the file should or should not be present is an interesting argument - but - pretending it isn't there isn't going to help.
[ Parent ]
IF you have an unauthorized user with root access, the debian-sys-main@localhost user is the least problem you have.
AND if an unauthorized user has root access, he can then to as suggested in this blog post and get any access he choose to have. Just add a new administrator and restart the MySQL as usual. And you have no trace of what hit you and your server.
So, this special administration user are not a real problem.
[ Parent ]
[ Parent ]
The line can be seen futher down.
https://www.debian-administration.org/article/442/Resetting_a_for gotten_MySQL_root_password#comment_30
[ Parent ]
[ Parent ]
[ Parent ]
export HISTCONTROL=ignorebothin your .bashrc file. With this set all you need to do to is to start the command with a space in front of it to prevent it from being written to the history file.
[ Parent ]
[ Parent ]
[ Parent ]
Yes it probably would, still for a simple demonstration script it appears to work fairly well.
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
# dpkg -l mysql-server* | grep ii ii mysql-server 5.0.51a-3 MySQL database server (meta package depending on the latest version) ii mysql-server-5.0 5.0.51a-3 MySQL database server binaries # dpkg-reconfigure mysql-server-5.0 Stopping MySQL database server: mysqld. [debconf screen appears] While not mandatory, it is highly recommended that you set a password for the MySQL administrative "root" user. If that field is left blank, the password will not be changed. New password for the MySQL "root" user: _______________________________________ <Ok> Stopping MySQL database server: mysqld. Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables..
...et voil�, it seems :)
[ Parent ]
[ Parent ]
If you don't have the debian-sys-maint@localhost, then Debian can't do some things, like stop MySQL.
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]
[ Parent ]