This is a bit gnarly. If you have a better method of updating the password without triggering a warning about PASSWORD being deprecated, I’m all ears.

# Stop MySQL
sudo service mysql stop

#############################################
To kill all mysqld process :
----
# killall mysqld mysqld_safe
----
Wait for 10 seconds atleast so that it shut down cleanly.

Now, run this command to check whether still there are some mysqld process remained or not ?
----
# ps aux | grep mysqld
----

If you still able to see more than run this command :
----
killall -9 mysqld mysqld_safe
----
This will kill all mysqld processes.
########################################################

# Make MySQL service directory.
sudo mkdir /var/run/mysqld
# Give MySQL user permission to write to the service directory.
sudo chown mysql: /var/run/mysqld
# Start MySQL manually, without permission checks or networking.
sudo mysqld_safe --skip-grant-tables --skip-networking &

2017-12-25T16:49:30.551520Z mysqld_safe Logging to syslog.
2017-12-25T16:49:30.554646Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-12-25T16:49:30.578079Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-12-25T16:49:32.568746Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended


# Log in without a password.
mysql -uroot mysql

Update the password for the root user.

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;

# Turn off MySQL.
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
# Start the MySQL service normally.
sudo service mysql start

source : https://coderwall.com/p/j9btlg/reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts

UPDATE : mysql 8

1.if you in skip-grant-tables mode
in mysqld_safe:

UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
FLUSH PRIVILEGES;
exit;
and then, in terminal:

mysql -u root
in mysql:

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;
2.not in skip-grant-tables mode
just in mysql:

Jika ingin menggunakan plugin mysql native password’

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘passwordd’;

Posted on: December 19, 2018, by : Julian's | 19 views