Assign A Password To root

• The ways to assign passwords to the root accounts are:
  • Use the SET PASSWORD statement.
  • Use the mysqladmin command-line client program.
  • Use the UPDATE statement.

• To assign passwords using SET PASSWORD:
  • Connect to the server as root and issue two SET PASSWORD statements.
  • Encrypt the password using the PASSWORD() function.

    mysql -u root
    

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpw');
    

    SET PASSWORD FOR 'root'@'hostname' = PASSWORD('newpw');

  • Replace hostname with the name of the server host.
    • This is the same hostname used to the anonymous account passwords.

• To assign passwords to root accounts using mysqladmin, execute the following commands:

  mysqladmin -u root password "newpw"
  

  mysqladmin -u root -h hostname password "newpw"

  • Double quotes around the password are not always necessary, but should be used if the password contains spaces or other special characters.

• The following UPDATE statement assigns a password to both root accounts at once:

  mysql -u root
  

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

  FLUSH PRIVILEGES;

• Once the passwords have been set, the appropriate password must be supplied with every connection to the server.

What should I do before an upgrade?

---

---

© 2007 John Michael Pierobon

Notes