MySQL

References

• http://www.cyberciti.biz/faq/mysql-change-root-password/
• http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Passwords

!!! SECURITY HOLE !!! — don't forget to DISABLE THE HISTORY FILE prior running the commands below or else all passwords will be output to ~/.mysql_history (note that it is chmod 600 however):

export MYSQL_HISTFILE=/dev/null

Change

Using mysqladmin:

mysqladmin -u USERNAME password NEWPWD               # Assumes no password set - use user=root for admin pwd
mysqladmin -u USERNAME -p'OLDPWD' password NEWPWD

Using MySQL commands. First we connect to MySQL server:

% mysql -u root -p

Here the script:

use mysql;
update user set password=PASSWORD("NEWPWD") where User='USERNAME';
flush privileges;
quit

Recover root password

If the MySQL root password is lost, the same script can be used to define a new password, but it requires to restart the MySQL server with option --skip-grant-tables:

sudo /etc/init.d/mysql stop                   # Stop MySQL server
sudo mysqld_safe --skip-grant-tables &        # Restart it with option not to ask for passwords
sudo mysql -u root                            # Connect to MySQL

Apply the MySQL script above, and restart the server:

sudo /etc/init.d/mysql restart