Modify MySql/MariaDB password

Change Password

1. Know the original password and modify it

1. Use SET PASSWORD command to change password

SET PASSWORD Modify Password in MySql

mysql -uroot -p         #Enter the original password

MariaDB [(none)]> SET password for 'root'@'localhost'=password('newpassword');

MariaDB [(none)]> exit

SET PASSWORD modifies the password in command line mode (Note: Passwords set this way are displayed in clear text in command mode, so it is not recommended)

mysql -uroot -p password -e "SET password for 'root'@'localhost'=password('newpassword')"

2. UPDATE command modifies password

Versions prior to MariaDB and MySql 5.7

mysql -uroot -p         #Enter the original password

MariaDB [(none)]> UPDATE mysql.user SET password=password('newpassword') WHERE user='root';

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> exit

Note: In versions after MySql 5.7, the Password field is no longer present in the user table and authentication_has been replacedString, be careful when modifying.

mysql> UPDATE mysql.user SET authentication_string=password('newpassword') WHERE user='root';

3. mysqladim change password

Mysqladmin-u username-p old password new password

Example
mysqladmin -uroot -p123456 password 654321

Note: This method will also display the password in clear text and is not recommended.

2. Forget the original password and modify it

systemctl stop mariadb                      #Turn off the mariadb service, using service mysql stop on CentOS 6

ps aux | grep mysql                         #View the resulting mysql process
mysql      3451  0.0  0.1 113124  1576 ?        Ss   21:29   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql      3669  0.0 10.1 1245548 101656 ?      Sl   21:29   0:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysq --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock

mysqld_safe --skip-grant-tables &           #Log on to mysql to skip permission validation

mysqld_safe --skip-grant-tables --skip-networking &    #For security, you can add a ban on remote logins

mysql                                       #Go directly to mysql/maridb

MariaDB [(none)]> UPDATE user SET password=password('newpassword') WHERE user='root';
#Note the differences in database versions, as described above for versions after MySql 5.7

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> exit

pkill mysql                                 #Use pkill to kill the previous mysql process. The Kill Command is not clean. It will start automatically after a while.

systemctl start mariadb                     #Open the mariadb service using service mysql start on CentOS 6