• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL : how to set (change) user password

by admin

There are several ways to set a MySQL user password:
– CREATE USER…IDENTIFIED BY
– GRANT…IDENTIFIED BY
– SET PASSWORD
– mysqladmin password
– UPDATE grant tables (not recommended)

For all methods, the privileges required to change a password are:
1. All users can change their own password.
2. Users with UPDATE on mysql database can update passwords for other users using SET PASSWORD or in MySQL 5.7 and later using ALTER USER.
3. If the read_only option is enabled, in addition to the privileges above, SUPER is required.

SET PASSWORD

The most common way to change an existing account’s password without changing any of its privileges is to use the SET PASSWORD statement. For example, to set the password for tom on the local host to new_password, use the following statement:

mysql> SET PASSWORD FOR tom@localhost = PASSWORD('new_password');
Query OK, 0 rows affected (0.00 sec)

If you are logged in as a non-root user and your user does not have the UPDATE privilege for the mysql database, you can change only your own password. Do this by using the SET PASSWORD statement without the FOR clause:

mysql> SET PASSWORD = PASSWORD('new_password');

MySQL 5.7 and later (SET PASSWORD)

In MySQL 5.7 and later it is not necessary to use the PASSWORD() function:

MySQL 5.7> SET PASSWORD FOR myuser@localhost = 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

To change the password for the current user:

MySQL 5.7> SET PASSWORD = 'my_new_password';
Query OK, 0 rows affected (0.00 sec)

mysqladmin

The mysqladmin utility allows you to change the password for a user you know the existing password for. The usage is:

$ mysqladmin --defaults-file=etc/my.cnf --user=tom --password my_new_password
Enter password:
New password:
Confirm new password:

ALTER USER

The ALTER USER statement can also be used to change the password of an existing user:

MySQL 5.7> ALTER USER tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

ALTER USER also supports manipulating several users with one statement, for example:

MySQL 5.7> ALTER USER myuser@localhost, tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)
Note : It is generally recommended to use different passwords for each account!

Filed Under: mysql

Some more articles you might also be interested in …

  1. Beginners Guide to Storage Engines in MySQL
  2. How To Shut Down a Node in MySQL Cluster
  3. How To Install MySQL RPM packages in a different location to allow multiple versions (versions < 5.6.10)
  4. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  5. Excluding a table or database from MySQL replication
  6. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  7. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  8. How to Change the Default Character Set and Collation for a Database in MySQL
  9. MySQL: How to kill a Long Running Query using max_execution_time
  10. How to configure resource groups for MySQL Server running on Linux

You May Also Like

Primary Sidebar

Recent Posts

  • fprintd-delete Command Examples in Linux
  • fprintd-delete: command not found
  • foreman: command not found
  • foreman Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright