It happens - situations occur where there's a server with MySQL installed, working databases and so on, but no administrative access. For example, you inherited this server. You have Unix superuser access, but you can't connect to the MySQL server as root - the password is unknown.
In that case you have to change this password.
So (to run all the commands you must have root superuser privileges on the Unix system):
1) Stop the MySQL server (yes, this has to be done).
For example, for FreeBSD:
# /usr/local/etc/rc.d/mysql-server stop
or for Debian:
# /etc/init.d/mysql stop
2) Start the server with the --skip-grant-tables parameter
# mysqld_safe --skip-grant-tables &
The & character at the end of the line is mandatory - otherwise the mysqld process will start and take over the whole console. Of course, you could connect from another console instead, but this way is faster and more convenient.
3) Connect to MySQL as the root user without a password.
# mysql -u root
// MySQL server welcome message
mysql> use mysql;
mysql> update user set password=PASSWORD('MYNEWPASSWORD') where user='root' and host='localhost';
mysql> flush privileges;
mysql> quit;
Here MYNEWPASSWORD is the password you want to set for the root user.
Note that, as you can see, we're only changing the password for the root@localhost user. To change other passwords, you'll be able to use the normal root access.
4) Stop mysqld and start the server normally.
FreeBSD:
# killall mysqld
# /usr/local/etc/rc.d/mysql-server start
or, for Debian:
# killall mysqld
# /etc/init.d/mysql start
5) Check the connection as root:
# mysql -u root -p
Password:
That's it, the root password has been changed.
Comments