Forgotten / lost MySQL root password

OK - I admit it. A very silly thing to do. I had lost / forgotten my MySQL password, and needed to do some admin work.

1. Log in to server as root

2. Stop the database server
/etc/init.d mysql.server stop

3. Start the database server without password check
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables

4. Manually change the grant tables

  /usr/local/mysql/bin/mysql -uroot

  use mysql;

  update user set password=PASSWORD("newpasswordhere") where user="root";

  flush privileges;

  quit

5. logout (which nicely kills the backgrounded daemon)

6. Log in as root again

7. Restart the server normally
/etc/init.d mysql.server stop

8 Logout
(written 2015-05-16)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles

S161 - Data Access and Security in MySQL
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)
  [193] The wrong MySQL - (2005-01-29)
  [401] What is an SQL injection attack? - (2005-08-02)
  [535] MySQL permissions and privileges - (2005-12-20)
  [647] Checking for MySQL errors - (2006-03-15)
  [947] What is an SQL injection attack? - (2006-11-27)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
  [4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)

Back to
Almost so wrong, but perhaps it's right for some?

Previous and next
or
Horse's mouth home

Forward to
Monday to Friday / TransWilts train times from 18 May to 11th December 2015

Some other Articles

Around the world from Melksham
Sunday train times - 17th May to 6th September 2015
Swindon to Westbury train services - Saturdays from 23rd May 2015
Monday to Friday / TransWilts train times from 18 May to 11th December 2015
Forgotten / lost MySQL root password
Almost so wrong, but perhaps it's right for some?
What and where are the Azores?
Election results - what if we had a party list system?
Election day, 2015