MySQL 5.7 on Debian and Ubuntu - How To Reset Root Password

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;

# we need to make this dir otherwise you'll get an error "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists."

sudo mkdir -p /var/run/mysqld;

#chown /var/run/mysqld to mysql.mysql or you'll get errors still "mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended"

sudo chown mysql:mysql /var/run/mysqld;

#now start mysqld_safe with skip-grant-tables so you can login as root with no password to reset the root password or any account

sudo mysqld_safe --skip-grant-tables &

Now that we're in, let's reset the root password!

But before we do this let's see what type of auth our root account uses, as this explains why you need to change the plugin to native mysql otherwise you won't be able to login normally:

mysql -u root

use mysql;

mysql> select User,Host,authentication_string,plugin from user;
+------------------+-----------+-------------------------------------------+-----------------------+
| User             | Host      | authentication_string                     | plugin                |
+------------------+-----------+-------------------------------------------+-----------------------+
| root             | localhost | *7E877F388401BAB948632B9B213C144C24756EC6 | auth_socket           |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| debian-sys-maint | localhost | *13CC8C41C8677DD6F22E91C2E10647FA20B05C56 | mysql_native_password |
+------------------+-----------+-------------------------------------------+-----------------------+

 

As we can see above the method for root is "auth_socket".  We need to change the plugin to "mysql_native_password".

 

use mysql;
update user set authentication_string=PASSWORD('newpassword'),plugin='mysql_native_password' where User='root';
flush privileges;

You need to switch the auth plugin to "mysql_native_password' by adding the ,plugin='mysql_native_password' option to the query above.

Change "newpassword" to what you want the password to be above.

Now we need to kill mysqld and restart it normally:

 

sudo killall -9 mysqld_safe mysqld


sudo systemctl start mysql

Now you should be able to login with your root password.


Tags:

mysql, debian, ubuntu, reset, passwordmysql, versions, configured, native, plugin, password, fails, default, quot, gracefully, sudo, systemctl, forcefully, mysqld, killall, mysqld_safe, dir, ll, directory, var, unix, socket, mkdir, chown, errors, pid, login, auth, select, user, authentication_string, localhost, bab, ec, auth_socket, thisisnotavalidpasswordthatcanbeusedhere, mysql_native_password, sys, maint, cc, dd, fa, method, update, newpassword, flush, privileges, adding, query, restart,

Latest Articles

  • How to install Windows or other OS and then bring to another computer by using a physical drive and Virtual Machine with QEMU
  • PXE-E23 Error BOOTx64.EFI GRUB booting is 0 bytes tftp pxe dhcp solution NBP filesize is 0 Bytes
  • vagrant install on Debian Mint Ubuntu Linux RHEL Quick Setup Guide Tutorial
  • RHEL 8 CentOS 8, Alma Linux 8, Rocky Linux 8 System Not Booting with RAID or on other servers/computers Solution for dracut and initramfs missing kernel modules
  • How to Upgrade to Debian 11 from Version 8,9,10
  • Ubuntu Linux Mint Debian Redhat Cannot View Files on Android iPhone USB File Transfer Not Working Solution
  • Virtualbox Best Networking Mode In Lab/Work Environment without using NAT Network or Bridged
  • debootstrap how to install Ubuntu, Mint, Debian install
  • Linux grub not using UUID for the root device instead it uses /dev/sda1 or other device name solution
  • How To Restore Partition Table on Running Linux Mint Ubuntu Debian Machine
  • Debian Ubuntu apt install stop daemon questions/accept the default action without prompting
  • iptables NAT how to enable PPTP in newer Debian/Ubuntu/Mint Kernels Linux
  • Grandstream Phone Vulnerability Security Issue Remote Backdoor Connection to 207.246.119.209:3478
  • Linux How to Check Which NIC is Onboard eth0 or eth1 Ubuntu Centos Debian Mint
  • VboxManage VirtualBox NAT Network Issues Managment Troubleshooting
  • Dell PowerEdge Server iDRAC Remote KVM/IP Default Username, Password Reset and Login Information Solution
  • Nvidia Tesla GPUs K40/K80/M40/P40/P100/V100 at home/desktop hacking, cooling, powering, cable solutions Tutorial AIO Solutions
  • Stop ls in Linux Debian Mint CentOS Ubuntu from applying quotes around filenames and directory names
  • Thunderbird Attachment Download Error Corrupt Wrong filesize of 29 or 27 bytes Solution
  • Generic IP Camera LAN Default IP Settings DVR