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

  • Huion and Wacom Tablets How To Install in Linux Mint / Ubuntu and make the stylus work properly
  • ffmpeg how to cut certain parts of video out
  • ffmpeg how to concat and join two video clips
  • mencoder instead of ffmpeg to join or concatenate video files with different audio streams
  • Linux How To Stop Missing Drive from Halting Boot Process in fstab
  • How To Replace Audio Track of Video using ffmpeg
  • qemu-img convert formats vdi vmdk raw qcow2
  • Linux and Windows Dual Boot Crazy Time Issues
  • dynagen / dynamips 100% high CPU usage solution - how to set the idlepc value
  • How To Setup a Cisco CME (Cisco Manager Express) Virtual Router under Linux using dynamips and dynagen
  • Linux Mint Ubuntu Debian CentOS Dual Boot Install Issues
  • Linux Mint Ubuntu Debian Centos RHEL no sound solution
  • Linux Mint/Debian/Ubuntu/Centos Installer black grub screen and blank screen after trying to boot installer or main OS
  • Linux Mint Dual Boot Install Avoid Wiping our your Main C: drive /dev/sda MBR and EFI
  • QEMU-KVM soundhw deprecated how to enable sound in QEMU 4.x series
  • Virtualbox Error Cannot register the hard disk because a hard disk with UUID already exists solution
  • kernel: [549267.368859] mate-terminal[7871]: segfault at 2000000101 ip 00007f5d0a9548f0 sp 00007fff7012c610 error 4 in libgobject-2.0.so.0.4800.2[7f5d0a920000+52000]
  • apcupsd how to setup and monitor APC UPS units
  • How To Password Reset, Recover, Bypass, Remove and Unlock on Windows 10,8,7,Vista,XP,NT,2000,2003,2008,2012,2016,2019 Administrative Login Programs
  • Nvidia Ubuntu Linux Screentearing Video with solution driver