How to reset MySQL root password, when forgotten..
- January 31st, 2012
- Posted in Documentation
- Write comment
This was bit more challenging in CentOS 7:
I couldn’t get MySQL/MariaDB to start up in safe mode. I kept getting the following no matter what options I tried:
170324 15:23:36 [ERROR] Can’t find messagefile ‘/share/mysql/errmsg.sys’
170324 15:23:36 [ERROR] Aborting
I know this is probably overkill, but I finally decided I would just pass mysqld_safe, fully qualified, all the same parameters as I noticed when I had mysql running as well as the –init-file to reset the root password I forgot:
/usr/bin/mysqld_safe –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –init-file=/tmp/a.sql –log-error=/var/lib/mysql/hostname.err –pid-file=/var/lib/mysql/hostname.pid –skip-grant-tables
I probably just needed to basedir option.
I checked this and this all I needed in CentOS 7:
mysqld_safe –basedir=/usr –skip-grant-tables &
These are the steps used to reset the root password in MySQL running on a linux system. In my case, it was CentOS 5.6.
Login as root.
Check to see what user ID MySQL is running under. In CentOS/RedHat it is mysql.
# su – mysql
Stop MySQL if it is currently running.
# kill `cat /var/run/mysqld/mysqld.pid`
or, as root:
# service mysqld stop
Create a temporary sql script containing the following:
# vi tmp.sql
UPDATE mysql.user SET Password=PASSWORD(‘newpassword‘) WHERE User=’root’;
FLUSH PRIVILEGES;
Start up MySQL in safe mode using the initialization script you just created:
# mysqld_safe –init-file=tmp.sql &
Test the new password:
# mysql -u root -p newpassword
mysql>
As root: &
# service mysqld stop
# service mysqld start
Process used in Ubuntu 12.04.
Here is the process in Ubunutu:
# service mysql stop
# mysqld –skip-grant-tables &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
mysql> quit
# service mysql start
That –basedir=/usr saved me. Brilliant catch. I’d love to buy you a beer.