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 Upgrade Debian 8,9,10 to Debian 12 Bookworm
  • Linux dhcp dhclient Mint Redhat Ubuntu Debian How To Use Local Domain DNS Server Instead of ISPs
  • Docker dockerd swarm high CPU usage cause solution
  • Docker Minimum Requirements/How Efficient is Docker? How Much Memory Does Dockerd Use?
  • qemu-nbd: Failed to set NBD socket solution qemu-nbd: Disconnect client, due to: Failed to read request: Unexpected end-of-file before all bytes were read
  • apache2 httpd apache server will not start [pid 22449:tid 139972160445760] AH00052: child pid 23248 exit signal Aborted (6) solution Mint Debian Ubuntu Redhat
  • How to use the FTDI USB serial cable to RJ45 adapter to connect to the console on Cisco/Juniper Switch Router Firewall in Linux Ubuntu Debian Redhat
  • How To Setup Python3 in Ubuntu Docker Image for AI Deep Learning
  • How to Configure NVIDIA GPUs with Docker on Ubuntu: A Comprehensive Guide for AI Deep Learning CUDA Solution
  • Linux Ubuntu Mint how to check nameservers when /etc/resolv.conf disabled solution
  • Docker cannot work on other overlayfs filesystems such as ecryptfs won't start overlayfs: filesystem on '/home/docker/overlay2/check-overlayfs-support130645871/upper' not supported as upperdir
  • Linux How To Access Original Contents of Directory Mounted Debian Mint CentOS Redhat Solution
  • ecryptfs how to manually encrypt your existing home directory or other directory
  • How to Reset CIPC Cisco IP Communicator for CME CUCM CallManager
  • Internet Explorer Cannot Download File "Your security settings do not allow for this file to be downloaded." Security Settings Solution
  • Linux How To Upgrade To The Latest Kernel Debian Mint Ubuntu
  • Firefox how to restore and backup saved passwords and history which files/location
  • Linux How To echo as root solution to use tee permission denied solution Ubuntu Debian Mint Redhat CentOS
  • Linux how to keep command line bash process running if you are disconnected or need to logout of SSH remotely
  • Linux swapping too much? How to check the swappiness and stop swapping