• 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. What are the various types of locking used in the MySQL Server
  3. How to find Which Process Is Killing mysqld With SIGKILL or SIGTERM on Linux
  4. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  5. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  6. MySQL Cluster Point-In-Time Recovery (PITR)
  7. How to List Tables Per Tablespace In MySQL Cluster
  8. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  9. How MySQL Enterprise Backup (MEB) uses locking while making a backup
  10. How To Create a Local Yum Repository for MySQL Enterprise Packages

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright