MySQL on Debian versions is configured differently than the native local MySQL plugin so you will be disappointed when your password on the mysql client fails by default.
Here is how you reset the MySQL root password the proper and "working way"
#first we gracefully stop mysql
sudo systemctl stop mysql;
#then we forcefully kill any mysqld process just in case
sudo killall -9 mysqld mysqld_safe;........
The reason we use the command below is because we need the md5sum value hash of the password. This means that we cannot use the md5sum
Change "yournewpass" to the pass you want to set
echo -n "yournewpass" | md5sum
Then you get the md5sum hash of whatever you entered eg. in this case "yournewpass"
5a9351ed00c7d484486c571e7a78c913 ........
mysql reset root password.
Oops I can't remember my MySQL root password!
[root@centos7test etc]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
First we need to stop mariadb:
systemctl stop mariadb
Now we need to restart it with skip-grant-tables whic........
The commands below will help you reset any mysql user password.
use mysql;
update user set password=PASSWORD('thenewpass') where User='theusername';
flush privileges;
The first line says to use the "mysql" database which contains all the user info.
the second update line sets the new password "thenewpass" for the u........
The solution is simple but strange, if you copy your /var/lib/mysql directory to another server and think it will work, be sure to check if you have /var/log/mysql and binary log files. If you do, the server will not work and will give you errors like below and crash without the proper log files.
UPDATE user SET password=password("newpass") WHERE user='root';
flush privileges;
ERROR 2013 (HY000): Lost connection to MySQL server durin........
One note is to secure MySQL, I don't know for sure but I believe you could login to MySQL remotely with no password during this operation (I'm not sure, maybe it doesn't accept blank passwords but I firewall MySQL port anyway and recommend you do the same).
First edit /etc/my.cf
Under the [mysqld] field add the following line somewhere:
skip-grant-tables
Now restart mysql: service mysql restart or on Debian sty........