Every MySQL administrator eventually faces this situation: you've gained access to a server but discover the previous admin didn't leave credentials. Here's the technical approach to regain control.

You'll need:

sudo access to the server
Ability to restart MySQL service
Basic terminal skills

First, stop the MySQL service:

sudo systemctl stop mysql

Start MySQL in safe mode with skipping grant tables:

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL without password:

mysql -u root

For MySQL 5.7.6+ or MariaDB 10.1.20+:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

For older versions:

UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

Exit MySQL and restart the service properly:

exit;
sudo systemctl restart mysql

If you prefer creating a new admin:

CREATE USER 'newadmin'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'newadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

After reset:

- Immediately change the temporary password
- Review all user accounts: SELECT User, Host FROM mysql.user;
- Remove unnecessary accounts: DROP USER 'olduser'@'localhost';

If you encounter "Unknown column 'password'":

UPDATE mysql.user SET plugin='mysql_native_password' WHERE User='root';

---

Before attempting any password reset procedure, ensure you have:

• SSH/sudo access to the server where MySQL is running
• Basic familiarity with Linux command line
• Knowledge of your MySQL version (5.7+ methods differ slightly from older versions)

This is the most common approach for modern MySQL installations:

# Stop MySQL service
sudo systemctl stop mysql

# Start MySQL in safe mode
sudo mysqld_safe --skip-grant-tables --skip-networking &

# Connect without password
mysql -u root

# For MySQL 5.7.6+:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

# For older versions:
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

For more controlled environments:

# Create reset file
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';" > /var/mysql-reset.sql

# Restart MySQL with init-file
sudo systemctl stop mysql
sudo mysqld --init-file=/var/mysql-reset.sql &

# Clean up
rm /var/mysql-reset.sql

Always verify the changes:

mysql -u root -p
# Enter your new password when prompted
SHOW DATABASES; # Should show default databases

After resetting:

• Immediately revoke any temporary privileges
• Check for other admin accounts that might exist
• Review the MySQL error log for suspicious activity
• Consider implementing 2FA for critical databases

If you encounter problems:

# Check MySQL error log
sudo tail -100 /var/log/mysql/error.log

# Verify mysqld process
ps aux | grep mysql

# For permission issues:
sudo chown -R mysql:mysql /var/lib/mysql

Here's a bash script for bulk resets:

#!/bin/bash
servers=("db1.example.com" "db2.example.com")
newpass="SecurePass123!"

for server in "${servers[@]}"; do
  ssh $server "systemctl stop mysql"
  ssh $server "mysqld_safe --skip-grant-tables &"
  ssh $server "mysql -e \"ALTER USER 'root'@'localhost' IDENTIFIED BY '$newpass'; FLUSH PRIVILEGES;\""
  ssh $server "pkill mysqld_safe"
  ssh $server "systemctl start mysql"
done