Resetting MySQL root password
In this short little tip/trick we are going to look at how to reset the root password of a MySQL server. I have been as on many occasions “How can I reset the root users password on MySQL? I have also forgotten the root password.” the answer to this question is simple.
The first step is to stop the MySQL daemon, this can be done by simply issuing the service command followed by ‘mysql stop‘ as shown in Figure 2.1.
linux-7ve1:~ # service mysql stop Shutting down service MySQL done
Figure 2.1: Stopping the MySQL daemon.
Once the MySQL daemon has stopped you will need to modify the “/etc/init.d/mysql” initialization script, within the initialization script you should see a section similar to the one shown in Figure 2.2.
$SAFE_MYSQLD \ --user=$mysql_daemon_user \ --pid-file=$pid_file \ --socket=$socket \ --datadir=$datadir &>/dev/null &
Figure 2.2: MySQL initialization script.
Once you have fond the section shown in Figure 2.2 you will need to add two arguments to this statement. The first argument is “–skip-grant-tables” which tells MySQL not to use the grants table so we will not be asked for a password when logging in as root, the second argument is “–skip-networking” this argument makes sure MySQL has no networking functionality e.g. (No one on the network can connect to our MySQL server while we are resetting the root password). Figure 2.3 shows what the MySQL initialization script looks like after the two arguments have been added.
$SAFE_MYSQLD \ --user=$mysql_daemon_user \ --pid-file=$pid_file \ --socket=$socket \ --skip-grant-tables \ --skip-networking \ --datadir=$datadir &>/dev/null &
Figure 2.3: MySQL initialization script modified.
Once you have modified the MySQL initialization script you can start the MySQL daemon by issuing the service command followed by “mysql start“. Once the MySQL daemon is running you can connect to the daemon using the mysql command as shown in Figure 2.4.
linux-7ve1:~ # mysql -u root
Figure 2.4: Connecting to the MySQL daemon.
Once you have connected to the MySQL daemon you can issue the SQL statements shown in Figure 2.5 to update the root users password.
linux-7ve1:~ # mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.26 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; Database changed mysql> UPDATE user SET Password=PASSWORD("NewPassword") WHERE User="root"; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0
Figure 2.5: Updating the root users password.
Once the root users password has been successfully updated you can exit the MySQL command prompt and stop the MySQL daemon as shown in Figure 2.1. Once the MySQL daemon has been stopped you will need to modify the “/etc/init.d/mysql” initialization script and remove the two argument we added earlier.