DeveloperBreeze

How to Reset the MySQL Root Password Using DROP USER

bash
#Database #Server #VPS #MySQL security #MySQL tutorial #root password #reset password #DROP USER #MySQL root user #password reset #bypass MySQL authentication #create root user #flush privileges

Prerequisites:

  • You need root (superuser) access to your VPS.
  • You must be able to access your server via SSH.

Step 1: Stop the MySQL Service

Before proceeding, you need to stop the MySQL service to prevent any conflicts while resetting the password.

Run this command to stop MySQL:

sudo systemctl stop mysql

Step 2: Bypass Authentication by Editing the MySQL Configuration

To reset the root password, you need to bypass the MySQL authentication mechanism by allowing access without a password. To do this, we will temporarily disable the password authentication by editing the MySQL configuration file.

  1. Open the MySQL configuration file (mysqld.cnf or my.cnf):
   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  1. Add the following line under the [mysqld] section to skip the grant tables:
   skip-grant-tables

This setting allows you to access MySQL without requiring a password, but only for this temporary process.

  1. Save the file and exit (Ctrl + X, then press Y to confirm).

Step 3: Start MySQL in Safe Mode

Now, start MySQL again, which will now run without enforcing password restrictions.

sudo systemctl start mysql

Step 4: Log in to MySQL Without a Password

Now that MySQL is running in "safe mode" without requiring passwords, you can log in without being prompted for the root password.

mysql -u root

Step 5: Drop and Recreate the Root User

Once logged in to the MySQL shell, follow these steps to drop and recreate the root user:

  1. Drop the existing root user:
   DROP USER 'root'@'localhost';

This removes the existing root user, including any potential issues related to password or privileges.

  1. Recreate the root user with a new password:

Now, you can create a new root user and assign a new password to it.

   CREATE USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Replace 'your_new_password' with the actual password you want to set for the root user.

  1. Grant all privileges to the new root user:
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

This grants the new root user full access to all databases and tables, as well as the ability to grant permissions to other users.

  1. Flush privileges to apply the changes:
   FLUSH PRIVILEGES;

This ensures that all changes you've made to users and permissions take effect immediately.

Step 6: Exit MySQL and Remove skip-grant-tables

After resetting the password and recreating the root user, you need to revert the changes made to the MySQL configuration.

  1. Exit the MySQL shell:
   exit;
  1. Remove or comment out the skip-grant-tables line:

Open the MySQL configuration file again:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Remove the line:

   skip-grant-tables

Or comment it out by adding a # at the beginning:

   #skip-grant-tables
  1. Save the file and exit (Ctrl + X, then press Y to confirm).

Step 7: Restart MySQL Service

Now, restart the MySQL service so that it starts with the new configuration (with password authentication enabled again).

sudo systemctl restart mysql

Step 8: Log in with the New Root Password

Now that MySQL has restarted and is enforcing password authentication, you can log in to MySQL using the new root password you set earlier.

mysql -u root -p

You will be prompted to enter the password. Enter the new password you set in Step 5.

Troubleshooting

  • If you can't start MySQL after removing skip-grant-tables, double-check the configuration file (mysqld.cnf) for typos or errors.
  • Make sure MySQL is running by checking its status:
  sudo systemctl status mysql

Conclusion

This tutorial provided a step-by-step guide on resetting your MySQL root password using the DROP USER method. By dropping the existing root user and creating a new one with full privileges, you can resolve common issues related to root access and password management. Remember to always remove the skip-grant-tables option after completing the password reset to keep your MySQL server secure.

Continue Reading

Discover more amazing content handpicked just for you

Tutorial
bash

How to Grant MySQL Root Privileges for 127.0.0.1

Once you’ve granted privileges, you need to set the password for the root user when connecting from 127.0.0.1.

Run the following command to set or update the root password for TCP connections:

Oct 03, 2024
Read More
Tutorial
mysql

How to Resolve the "#1038 - Out of Sort Memory" Error in MySQL

Open the configuration file with your preferred text editor and find or add the following line:

[mysqld]
sort_buffer_size = 4M

Aug 26, 2024
Read More
Cheatsheet
json

JSON Operations in MySQL: Examples and Use Cases

Let’s start by creating a table that includes a JSON column.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    preferences JSON
);

Aug 21, 2024
Read More
Tutorial
mysql

Understanding and Using MySQL Indexes

For example, to view indexes on the users table:

SHOW INDEX FROM users;

Aug 12, 2024
Read More
Tutorial
mysql

How to Monitor MySQL Database Performance

Use the mysqldumpslow tool to analyze the slow query log:

mysqldumpslow -s t /path/to/slow-query.log

Aug 12, 2024
Read More
Tutorial
mysql

How to Optimize MySQL Queries for Better Performance 

SELECT first_name, last_name FROM users WHERE user_id = 1;

Use LIMIT to restrict the number of rows returned by a query, especially for large datasets.

Aug 12, 2024
Read More
Tutorial
mysql

Managing Transactions and Concurrency in MySQL

MySQL provides different isolation levels to control how transactions interact with each other. Each level offers a different balance between data consistency and performance:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Aug 12, 2024
Read More
Tutorial
mysql

Viewing the Database Size and Identifying the Largest Table in MySQL

This query will output the largest table in the specified database along with its size.

After executing these queries, you'll have a clear understanding of the total size of your database and which table is consuming the most space. This information can help you make informed decisions about database optimization and storage planning.

Aug 12, 2024
Read More
Code
bash

How to view free space on a Linux server

  • Specific Filesystem: To view the space for a specific filesystem, add its path at the end of the command, like so:
  df -h /home

Aug 11, 2024
Read More
Tutorial
bash

Finding the Top 10 Biggest Files on an Ubuntu Server 

sudo find /path/to/directory -type f -exec du -h {} + | sort -rh | head -n 10
  • sudo: Runs the command with superuser privileges, which might be necessary for accessing certain files or directories.
  • find /path/to/directory -type f: Searches for files (-type f) in the specified directory and its subdirectories. Replace /path/to/directory with the target directory, or use / to search the entire file system.
  • -exec du -h {} +: Executes the du (disk usage) command for each file found, with -h providing human-readable sizes (e.g., MB, GB).
  • sort -rh: Sorts the files in reverse order by size, with the largest files listed first.
  • head -n 10: Displays the top 10 files from the sorted list.

Aug 11, 2024
Read More
Tutorial
bash mysql

How to Allow MySQL Port and Restrict Access to Specific IPs

> - Enabling SSL/TLS for MySQL connections.

> - Regularly updating your server and MySQL installation.

Aug 04, 2024
Read More
Tutorial
python bash

Deploying a Flask Application on a VPS Using Gunicorn and Nginx

Paste the following:

# HTTP to HTTPS redirection
server {
    listen 80 default_server;
    listen [::]:80 default_server;
    server_name developerbreeze.com www.developerbreeze.com;

    return 301 https://developerbreeze.com$request_uri;
}

# HTTPS Server Block
server {
    listen 443 ssl http2;
    listen [::]:443 ssl http2;
    server_name developerbreeze.com www.developerbreeze.com;

    ssl_certificate /etc/letsencrypt/live/developerbreeze.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/developerbreeze.com/privkey.pem;
    include /etc/letsencrypt/options-ssl-nginx.conf;
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem;

    root /path/to/your-flask-app;
    index index.html;

    access_log /var/log/nginx/access.log;
    error_log /var/log/nginx/error.log;

    location / {
        include proxy_params;
        proxy_pass http://unix:/path/to/your-flask-app/developerbreeze.sock;
    }

    location /static {
        alias /path/to/your-flask-app/static;
    }

    location /favicon.ico {
        alias /path/to/your-flask-app/static/favicon.ico;
    }

    location ~ /.well-known {
        allow all;
    }
}

Aug 03, 2024
Read More
Code
php bash

Laravel Artisan Commands Cheatsheet 

  php artisan migrate:fresh
  • Run Specific Seeders

Aug 03, 2024
Read More
Tutorial
sql

Optimizing SQL Queries: Indexing and Query Optimization Techniques 

SELECT name FROM employees WHERE department = 'Engineering';

Ensure indexed columns are used:

Aug 03, 2024
Read More
Code
javascript

Simple WebSocket Server using 'ws' library

No preview available for this content.

Jan 26, 2024
Read More

Discussion 0

Please sign in to join the discussion.

No comments yet. Start the discussion!