Change MySQL root password

After initially installing MySQL on any platform your root user usually has no password. To set it from the command line issue the command:

# mysqladmin -u root password NEWPASSWORD


That's pretty basic stuff, but what if you get locked out of your server from having a typo in the password set, forgetting the password, etc, etc?

First, you need to stop the mysql daemon from running.
Then start the daemon again without the grant tables (the tables containing permissions and user data), and if you are on a live server, you may want to start it without networking since without the grant tables ANYONE can access ANY database from ANYWHERE.

Note: If you are running as root user on your system you will need to add the --user=USER argument to the following command, where user is the user that mysqld will run as (usually mysql)

# mysqld --skip-grant-tables --skip-networking [--user=mysql] &


Now you can log in to mysql without a password or user:

# mysql


Now you just need to update the root user password using mysql:

mysql> UPDATE mysql.user SET Password=PASSWORD('NewPass') WHERE User='root';


Then flush the privileges table:

mysql> FLUSH PRIVILEGES;


After that is done, you need to stop mysqld and restart it normally. You will now be able to gain access to the root user with the password you have set.