Securing and optimizing your environment on Digital Ocean is crucial. One way to achieve this is by improving MySQL. This guide will show you how to reset your MySQL root password in Ubuntu 20.04, especially when using the Ghost.

Step 1: Verify MySQL Version

First, ensure that your server is running version 8 of MySQL (or higher). To check this, use the following command:

mysql --version

The output should be similar to:

mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Step 2: Skip Grant Tables

If you try to log in with MySQL but don't know the root password, you will get an error. To bypass this, you need to start the system without granting the tables. This can be done by editing the mysqld.cnf file.

Find the mysqld section and add skip-grant-table to the file, then save it. You can use the nano command to edit the file:

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

Step 3: Restart MySQL Server

To apply the changes, restart the service using the following command:

service mysql restart

Then, you can enter the database without a password using the command:

mysql -uroot -p

Step 4: Reset Root Password

Now, flush the privileges first. Run the following command:

mysql> flush privileges;

Select the MySQL database. Run the following command:

mysql> use mysql;

And set the new password for the root user, run the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '(YOUR NEW PASSWORD)';

Replace (YOUR NEW PASSWORD) with your new password. After successfully changing the MySQL root password, log out from the MySQL shell.

mysql> quit;

Step 5: MySQL Processes & Service

Finally, you will have to kill all the MySQL processes before restarting the MySQL server.

sudo killall -u mysql

And then restart the MySQL service, run the following command:

service mysql restart

Now, the root can connect to MySQL again using the new password. It is time to improve your database and how your server is dealing with data.

Don't forget to keep your ghost installation up to date. Whenever running a public-facing production web server it’s critically important to keep all software up to date. If you don’t keep everything up to date, you place your site and your server at risk of numerous potential exploits and hacks. If you need to have guide to install ghost on digitalocean you can explore out docs here.

Frequently Asked Questions

What version of MySQL is required for this guide?

This guide is designed for MySQL version 8 or higher.

What should I do if I don't know my MySQL root password?

This guide provides step-by-step instructions on how to reset your MySQL root password.

How do I check my MySQL version?

You can check your MySQL version by running the command mysql --version in your terminal.

What does the flush privileges command do?

The flush privileges command tells the server to reload the grant tables into memory so it notices any changes to privileges.

What does the ALTER USER command do?

The ALTER USER command is used to change the authentication credentials for a MySQL account. In this guide, it's used to reset the root password.

Why do I need to kill all MySQL processes before restarting the server?

Killing all MySQL processes ensures that there are no ongoing processes that could interfere with the restart and the application of the new settings.

How can I keep my Ghost installation up to date?

You can keep your Ghost installation up to date by regularly checking for updates from the official Ghost website or your hosting provider, and applying them as soon as they are available.

Share this post