How to Change MySQL Root Password in Ubuntu 22.04

While setting up MySQL, it is recommended that you set up a root password for your database for security-related concerns. However, this does not mean that you cannot use MySQL without a root password. It is upon you to think about the task at hand and decide whether to set up a root password or not.

How to Change MySQL Root Password in Ubuntu 22.04

Содержание
  1. Changing Password using SET:
  2. Changing Password using UPDATE:
  3. About the author
  4. Introduction
  5. To log in as a root user in Linux:
  6. Introduction
  7. Introduction
  8. Step 2 — Restarting the Database Server Without Permission Checks
  9. Configuring MariaDB to Start Without Grant Tables
  10. Configuring MySQL to Start Without Grant Tables
  11. Step 2 — Restarting the Database Server Without Permission Checks
  12. Configuring MariaDB to Start Without Grant Tables
  13. Configuring MySQL to Start Without Grant Tables
  14. Windows
  15. General
  16. Visually Manage MySql using DbSchema
  17. Step 1: Check MySQL version
  18. Step 2: Stop MySQL service
  19. Step 3: Check MySQL status
  20. Step 4: Skipping Networking and Grant Tables
  21. Step 5: Start MySQL service
  22. Step 6: Check MySQL status
  23. Step 7: Log in to MySQL
  24. Step 8: Flush privileges
  25. Step 9: Choose MySQL database
  26. Step 10: Change MySQL root password
  27. Step 11: Exit MySQL
  28. Step 12: Reverting Database to its normal settings
  29. Step 13: Kill MySQL processes
  30. Step 14: Restart MySQL service
  31. Step 15: Log in to MySQL
  32. Conclusion
  33. About the author
  34. Step 1: Check the version of MySQL on Ubuntu 20.04
  35. Step 2: Stop the MySQL server
  36. Step 3: Skip Grant Tables & Networking
  37. Step 4: Start the MySQL service
  38. Step 5: Confirm the status of the MySQL Server
  39. Step 6: Sign In to the MySQL shell
  40. Step 7: Alter the root password
  41. Step 8: Revert Database Server to Normal settings
  42. Step 9: Kill all MySQL processes & Restart the MySQL service
  43. Step 10: Log in with the newly set Password
  44. Wrap Up
  45. About the author
  46. Resetting MySQL root password
  47. Prerequisites
  48. 1. How to reset MySQL root password in Linux
  49. Step 1: Log in to MySQL
  50. Step 2: Finding the .pid file used by the MySQL service
  51. Step 3: Killing the mysqld / .pid process
  52. Step 4: Creating a new root password file
  53. Step 5: Restarting the MySQL Server and Applying the Newly created Password
  54. How to reset MySQL root password in Windows
  55. Step 1: Stopping the MySQL server.
  56. Step 2: Open your favorite text editor
  57. Step 3: Creating a new Password text file using the password command
  58. Step 4: Launch CMD (Command Prompt)
  59. Step 5: Restarting MySQL Server and Applying the new Config file
  60. Generic instructions for resetting MySQL root password
  61. Recover your MySQL password
  62. How To Solve The MySQL Error: Access denied for user root@localhost
  63. Step 4 — Reverting Your Database Server to Normal Settings
  64. Step 3 — Changing the Root Password
  65. Changing the MariaDB Password
  66. Changing the MySQL Password
  67. Step 4 — Testing MySQL
  68. Step 2 — Configuring MySQL
  69. Step 1 — Installing MySQL
  70. How to set up, change, and recover a MySQL root password
  71. Setting the password for the first time
  72. Changing the MySQL root user password
  73. Step 3 — Changing the Root Password
  74. Changing the MariaDB Password
  75. Changing the MySQL Password
  76. Step 1 — Identifying the Database Version and Stopping the Server
  77. Step 4 — Reverting Your Database Server to Normal Settings
  78. Prerequisites
  79. Step 3 — Creating a Dedicated MySQL User and Granting Privileges
  80. Prerequisites
  81. Step 1 — Identifying the Database Version and Stopping the Server
  82. Conclusion
  83. Conclusion
  84. Conclusion

Changing Password using SET:

How to Change MySQL Root Password in Ubuntu 22.04

How to Change MySQL Root Password in Ubuntu 22.04

How to Change MySQL Root Password in Ubuntu 22.04

Now, run the command with correct root password which is set in previous step. If you provide the correct password then it will be able to connect successfully with the server.

Changing Password using UPDATE:

How to Change MySQL Root Password in Ubuntu 22.04

Again, exit from the server and restart the database server before testing the new password.

$ sudo service mysql restart

How to Change MySQL Root Password in Ubuntu 22.04

About the author

Introduction

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data.

This tutorial will go over how to install MySQL version 8.0 on an Ubuntu 20.04 server. By completing it, you will have a working relational database that you can use to build your next website or application.

Note: Empty/blank password means there is no password; hence anyone can log in as long as MySQL privileges have been granted.

To log in as a root user in Linux:

  1. Select start menu > Log Out on the top right corner. This allows you to log out of the user account that you are using at the moment.
  2. You will now see a login window, and here is where you log in using the username “root” and the password you set up for the root user. If there are several users in the login window, select others and log in.

If the password is not set up or you forgot or need to change your MySQL password, below is a guide on how to do set up/recover the password.

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

Introduction

This tutorial demonstrates how to reset the root password for MySQL and MariaDB databases installed with the apt package manager on Ubuntu 20.04. The procedure for changing the root password differs depending on whether you have MySQL or MariaDB installed and the default systemd configuration that ships with the distribution or packages from other vendors. While the instructions in this tutorial may work with other system or database server versions, they have been tested with Ubuntu 20.04 and distribution-supplied packages.

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

Introduction

This tutorial demonstrates how to reset the root password for MySQL and MariaDB databases installed with the apt package manager on Ubuntu 18.04. The procedure for changing the root password differs depending on whether you have MySQL or MariaDB installed and the default systemd configuration that ships with the distribution or packages from other vendors. While this tutorial’s instructions may work with other system or database server versions, they have been explicitly tested with Ubuntu 18.04 and distribution-supplied packages.

Step 2 — Restarting the Database Server Without Permission Checks

Depending on which database server you’ve installed, the way of starting the server without loading the grant tables differs.

Configuring MariaDB to Start Without Grant Tables

In order to start the MariaDB server without the grant tables, we’ll use the systemd unit file to set additional parameters for the MariaDB server daemon.

  1. systemctl set-environment

Then start the MariaDB server:

  1. systemctl start mariadb

This command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.

You can ensure it started with sudo systemctl status mariadb.

  1. mysql root

You’ll immediately see a database shell prompt:


Now that you have access to the database server, you can change the root password as shown in Step 3.

Configuring MySQL to Start Without Grant Tables

In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.

  1. systemctl edit mysql

This command will open a new file in the nano editor, which you’ll use to edit MySQL’s service overrides. These change the default service parameters for MySQL.

MySQL service overrides



/usr/sbin/mysqld --skip-grant-tables --skip-networking

The first ExecStart statement clears the default value, while the second one provides systemd with the new startup command, including parameters to disable loading the grant tables and networking capabilities.

Press CTRL-x to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.

Reload the systemd configuration to apply these changes:

  1. systemctl daemon-reload

Now start the MySQL server:

  1. systemctl start mysql

The command will show no output, but the database server will start. The grant tables and networking will not be enabled.

  1. mysql root

You’ll immediately see a database shell prompt:


Now that you have access to the server, you can change the root password.

Step 2 — Restarting the Database Server Without Permission Checks

Depending on which database server you’ve installed, the way of starting the server without loading the grant tables differs.

Configuring MariaDB to Start Without Grant Tables

In order to start the MariaDB server without the grant tables, we’ll use the systemd unit file to set additional parameters for the MariaDB server daemon.

  1. systemctl set-environment

Then start the MariaDB server:

  1. systemctl start mariadb

This command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.

You can ensure it started with sudo systemctl status mariadb.

  1. mysql root

You’ll immediately see a database shell prompt:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now that you have access to the database server, you can change the root password as shown in Step 3.

Configuring MySQL to Start Without Grant Tables

In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.

  1. systemctl edit mysql

MySQL service overrides



/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking

The first ExecStart statement clears the default value, while the second one provides systemd with the new startup command, including parameters to disable loading the grant tables and networking capabilities.

Press CTRL-x to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.

Reload the systemd configuration to apply these changes:

  1. systemctl daemon-reload

Now start the MySQL server:

  1. systemctl start mysql

The command will show no output, but the database server will start. The grant tables and networking will not be enabled.

  1. mysql root

You’ll immediately see a database shell prompt:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now that you have access to the server, you can change the root password.


Design
• 21-Apr-2020

by DbSchema Team


If you set a password for MySQL and you can’t recall it, you can always reset it and choose another one.

Windows

  1. Make sure that MySQL Server is not running. Open Task Manager, search for the MySQL process and force stop it.

  2. Create a new text file that will contain the statement below:

     SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

    Replace the password with the new one

  3. Save the file with the mysql-init name in C:. The path should look like this:

     C:\mysql-init.txt
  4. Open the Start menu, enter Run then write cmd to open the command prompt

  5. Go to the MySQL server bin folder

     cd "C:\Program Files\MySQL\MySQL Server 5.6\bin"

    If you installed MySQL with a different path, adjust the cd

  6. Run it with the mysql-init file

     mysqld --init-file=C:\\mysql-init.txt

    If MySQL was installed using the Wizard, add the defaults file command:

     mysqld
           --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.6\\my.ini"
           --init-file=C:\\mysql-init.txt
  7. After MySQL server started, delete the mysql-init file.

Дополнительно:  Батарея не обнаружена на ноутбуке как угроза тотального нарушения работы компьютера: причины и что делать

DbSchema Database Designer

General

Alternatively, you can use a more general method that works on every system, but it’s less safe.

  1. Restart it with the --skip-grant-tables option

     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
  2. Connect to MySQL server using the mysql client

     mysql -u root
  3. Reload all grant tables by executing:

     FLUSH PRIVILEGES;
  4. Set the new password for your account:

     SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Visually Manage MySql using DbSchema

DbSchema is a MySQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >«></p><h3 id=Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.


Connection Dialog alt >«></p><h3 id=Simple Connection Dialog


Relational Data Explorer alt >«></p><h3 id=Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.


Query Builder alt >«></p><h3 id=Query Builder

Create SQL Queries using the mouse.


SQL Query Editor alt >«></p><h3 id=SQL Query Editor

Edit and execute SQL Queries


Schema Synchronization alt >«></p><h3 id=Design Schema in Team & Schema Deployment


Dark Theme alt >«></p><h3 id=Dark Theme

Configurable styles & dark theme.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.

Not everybody is good at remembering passwords. Do not panic in case you cannot remember the password of your MySQL account. You can still access the MySQL server and change the root password even if you have misplaced or forgotten the root password. But how is a beginner going to perform this operation?

This blog will demonstrate the method for changing the MySQL root password in Ubuntu 22.04. Let’s get started.

Note: The given method for changing password only implies for MySQL version greater or equal to “8”.

Step 1: Check MySQL version

First thing you need to do is check the version of installed MySQL on your system:

As you can see, we have installed MySQL version “8.0.29”:

How to Change MySQL Root Password in Ubuntu 22.04

Step 2: Stop MySQL service

In the next step, utilize the below-given command for stopping the MySQL service:

systemctl stop mysql.service

Now, the MQL service is stopped:

How to Change MySQL Root Password in Ubuntu 22.04

Step 3: Check MySQL status

After stopping the MySQL service, verify its status with the help of the provided command:

systemctl status mysql.service

The given output indicates that MySQL is currently inactive on our system:

How to Change MySQL Root Password in Ubuntu 22.04

Step 4: Skipping Networking and Grant Tables

MYSQL server should start without networking checks and granting tables. To do so, set the value of “MYSQLD_OPTS”:

systemctl set-environment =

How to Change MySQL Root Password in Ubuntu 22.04

Then head towards the next step.

Step 5: Start MySQL service

systemctl start mysql.service

MySQL service is now started:

How to Change MySQL Root Password in Ubuntu 22.04

Step 6: Check MySQL status

Again, check the status of the MySQL service to ensure that it is currently active and running on the Ubuntu 22.04 system:

systemctl status mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Now head towards the next step.

Step 7: Log in to MySQL

Write out the provided command in terminal for loging in to MySQL account:

How to Change MySQL Root Password in Ubuntu 22.04

Step 8: Flush privileges

To change the MySQL root password, firstly, it is required to flush all privileges:

How to Change MySQL Root Password in Ubuntu 22.04

Step 9: Choose MySQL database

Then choose the MySQL database:

How to Change MySQL Root Password in Ubuntu 22.04

Step 10: Change MySQL root password

After doing so, utilize the “ALTER” command and specify the new password:

How to Change MySQL Root Password in Ubuntu 22.04

Step 11: Exit MySQL

Lastly, type “quit” to logout from the active MySQL shell:

How to Change MySQL Root Password in Ubuntu 22.04

Step 12: Reverting Database to its normal settings

In order to restart MySQL database back in “normal” mode, first of all, “revert” the added changes by unsetting the value of environment variable:

systemctl unset-environment MYSQLD_OPTS

How to Change MySQL Root Password in Ubuntu 22.04

Next, revert “MySQL” for removing the set system configuration:

systemctl revert mysql

How to Change MySQL Root Password in Ubuntu 22.04

Step 13: Kill MySQL processes

How to Change MySQL Root Password in Ubuntu 22.04

Step 14: Restart MySQL service

In the final step, restart the MySQL service so that it can take changes from the configured settings:

systemctl restart mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Now, move to the next step.

Step 15: Log in to MySQL

How to Change MySQL Root Password in Ubuntu 22.04

That’s it. You have successfully changed the password of MySQL:

How to Change MySQL Root Password in Ubuntu 22.04

We have compiled the easiest method for changing the MySQL root password in Ubuntu 22.04.

Conclusion

To change the MySQL root password in Ubuntu 22.04, firstly, stop the MySQL service and unset the value of the environment variable. After doing so, start the MySQL service, log in to the MySQL root account, flush all privileges, choose a database, and use the “ALTER” command to specify the new password. Lastly, revert the database back to the normal mode, restart the MySQL service, and log in with the new password. This blog demonstrated the method for changing the MySQL root password in Ubuntu 22.04.

About the author

I am a Linux enthusiast, I love to read Every Linux blog on the internet. I hold masters degree in computer science and am passionate about learning and teaching.

Passwords are hard to remember, so if you have forgotten the MySQL root password, luckily, there is a way to change it. This post has been written for you, and by the end of this post, you will have successfully changed the password of MySQL.

Before getting straight to the solution, it is assumed that you are using the latest version of the MySQL database on Ubuntu 20.04 LTS system. This post will provide a step-by-step guide on how to change the MySQL root password in Ubuntu 20.04. So, without wasting any time, let’s start.

Step 1: Check the version of MySQL on Ubuntu 20.04

First of all, check the version of your MySQL because this post contains the solution of changing the root password on version 8 or higher. If the version of your MySQL is lower than 8, then the solution will be different. The command for checking the version of MySQL is given below:

How to Change MySQL Root Password in Ubuntu 22.04

Step 2: Stop the MySQL server

To change the MySQL root password, you first need to shut down the MySQL server, and you can do so using the command:

$ sudo systemctl stop mysql.service

How to Change MySQL Root Password in Ubuntu 22.04
Check the status of the MySQL server to verify using the command:

$ sudo systemctl status mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Step 3: Skip Grant Tables & Networking

To start the MySQL server without granting the tables and networking check, set the environment variable “MYSQLD_OPTS” which MySQL uses on startup:

$ sudo systemctl set-environment MYSQLD_OPTS=»—skip-networking —skip-grant-tables»

How to Change MySQL Root Password in Ubuntu 22.04
Alright, the environment variable is set, and we can log in to the MySQL shell without providing any password.

Step 4: Start the MySQL service

After setting the environment variable “MYSQLD_OPTS”, start the MySQL service now using the command:

$ sudo systemctl start mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Step 5: Confirm the status of the MySQL Server

Confirm the status of the MySQL service, whether it is running or not:

$ sudo systemctl status mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Step 6: Sign In to the MySQL shell

How to Change MySQL Root Password in Ubuntu 22.04
Without providing any password, you will log in to the MySQL shell.

Step 7: Alter the root password

Now, flush the privileges first:

How to Change MySQL Root Password in Ubuntu 22.04
Select the MySQL database:

How to Change MySQL Root Password in Ubuntu 22.04
At the place of “the-new-password” provide your new password. After successfully changing the MySQL root password, log out from the MySQL shell:

How to Change MySQL Root Password in Ubuntu 22.04

Step 8: Revert Database Server to Normal settings

For restarting the database server in “normal” mode, it is required to “revert” the changes we have made so that the networking is enabled and grant tables are loaded.

To do so, firstly unset the “MYSQLD_OPTS” environment variable which you have set previously:

$ sudo systemctl unset-environment MYSQLD_OPTS

How to Change MySQL Root Password in Ubuntu 22.04
After doing so, remove the modified system configuration by reverting “MySQL”:

$ sudo systemctl revert mysql

How to Change MySQL Root Password in Ubuntu 22.04

Step 9: Kill all MySQL processes & Restart the MySQL service

Now, kill all the processes of MySQL before restarting the MySQL server:

How to Change MySQL Root Password in Ubuntu 22.04
After having the output as shown in the picture, hit “Enter” and restart the MySQL server using the command given below:

$ sudo systemctl restart mysql.service

How to Change MySQL Root Password in Ubuntu 22.04

Step 10: Log in with the newly set Password

Log in to the MySQL shell with the newly set password:

How to Change MySQL Root Password in Ubuntu 22.04
Provide the recently set password in the ALTER command:

How to Change MySQL Root Password in Ubuntu 22.04
This is it. MySQL’s root password is successfully changed, and you are logged back into the MySQL shell.

Wrap Up

In this step-by-step guide, you have learned to change the MySQL root password on the Ubuntu 20.04 LTS system. This post contains a profound and easy-to-understand solution on resetting the root password of MySQL on Ubuntu 20.04.

About the author

A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.

Resetting MySQL root password

Prerequisites

  • Existing MySQL database
  • Admin privileges on the PC hosting the MySQL database
  • Access to Windows or Linux server running MySQL
  • Command-line interface or terminal access
  • A text editor. (Don’t worry since Windows users have Notepad installed by default on their computers while Linux users have vim pre-installed).

Once you have all the mentioned prerequisites, let’s roll on and learn how to reset or change the MySQL root password.

We shall do this process on two fronts:

  1. How to reset MySQL root password in Linux
  2. How to reset MySQL root password in Windows

1. How to reset MySQL root password in Linux

Step 1: Log in to MySQL

user=fosslinux

user = fosslinux

Step 2: Finding the .pid file used by the MySQL service

The next step is locating the .pid file. In most Linux systems, they are stored in this location:

/var/lib/mysql or /var/run/mysqld/ or /usr/local/mysql/data/. 

find the .pid file

Find the .pid file

Since not all Linux systems are the same, you can try out any of the paths mentioned above and see if you can locate the file. To quickly find the file, you can narrow your search to files starting with mysqld (or your hostname) and ending with .pid.

Note: Searching the file will simplify the process; hence we recommend you try it out.

Once you find the file, now proceed to the next step

Step 3: Killing the mysqld / .pid process

Before resetting or creating a new root password, we first have to stop the MySQL server. This can be done by opening the terminal and executing the command line below:

kill `cat /mysql-data-directory/host_name.pid`

kill the .pid process

kill the .pid process

Дополнительно:  Ошибка 720 при подключении к интернету на Windows 10

Note: Replace the mysql-data-directory/host_name.pid with the filename we found in step 2. Also, it is vital to specify the entire file path to avoid getting errors.

Additional tip: Always ensure you use the back-tick key instead of a single-quote mark key. The back-tick key is located above the tab-key on most computers and before the one numkey.

Step 4: Creating a new root password file

Now it is time to open your favorite text editor. For our case, we shall use the Vim text editor. Open terminal using the shortcut CTRL + ALT + T, then run the command below:

sudo vim mysql-init

sudo vim mysql init

sudo vim mysql-init

Note: if you get an error after running the command above, execute this command to proceed with the other processes:

sudo apt install vim

install vim

Now add the line below in the file:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

sudo vim and create the new password file by adding the command into the vim editor

Sudo vim and create the new password file by adding the command into the vim editor

Note: When manually typing the line above into the terminal, remember to include all the semicolons and single-quote marks. Also, replace the “NewPassword” phrase with the new password you want to use. Finally, ensure you use a strong and secure password or refer here to learn how to generate a strong random password on Linux.

The above command will only work on the host machine you are using when running the command. However, if you plan to connect to a different system, replacing the localhost with the relevant or rather appropriate hostname is recommended for the command to work. If you fail to do so, you will encounter errors.

Once you have added the new file to your text editor, save it to:

 home/mysql-init.

mysql.init file in the home directory

Mysql.init file in the home directory

Step 5: Restarting the MySQL Server and Applying the Newly created Password

It is time to apply the changes we made to our newly created password. First, we shall restart the MySQL server by executing the command below in the terminal:

mysqld --init-file=/home/mysql-init &

restart mysqlserver and apply newly created password

Restart MySQL server and apply the newly created password

This command will launch MySQL and apply the text-file password we created in the previous step. Depending on the method you use to start your server, you might be required to add other options such as –defaults-file before the init command.

How to reset MySQL root password in Windows

Step 1: Stopping the MySQL server.

  • Press the Win + R keys on windows to launch the Run dialogue box the type:
services.msc
  • Next click OK.

    run dialogue box

    Run dialogue box

  • Scroll down through the list of services and locate the MySQL service. Right-click on it and then select the Stop option as shown in the image below:

Step 2: Open your favorite text editor

For illustration purposes, we shall be using the default Notepad text editor. However, don’t freak out if you have a different text editor since the processes are similar.

  • Now click on the Start menu and search for Notepad. Click on it to launch.

Alternatively, use this path to find Notepad: menu>Windows Accessories>Notepad.

Step 3: Creating a new Password text file using the password command

Copy and paste the syntax below into the text editor to avoid errors while manually typing:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

create new text file

Create a new text file

Like we did for Linux, ensure to keep the semicolon and the single quote marks and replace the NewPassword phrase with the new password you intend to use on your MySQL.

Click on the File option, then Save As to save the text file password onto the root of your hard drive. Save the file as mysql-init.txt.

save the file as mysql init

Save the file as mysql-init.txt.

Consequently, the localhost command can only make password changes on your system. Therefore, if you intend on changing the password on a different system like over the network, then substitute the localhost with the appropriate hostname for the different system.

Step 4: Launch CMD (Command Prompt)

  • Click on the Ctrl + Shift + Esc keys on your keyboard
  • Select file menu, then Run a new task
  • Type cmd.exe and select the Run as administrator checkbox
  • Click OK to launch a command prompt with administrator privileges.

    create a new task and run cmd

    Create a new task and run cmd

Step 5: Restarting MySQL Server and Applying the new Config file

  • Using the command prompt, navigate to the MySQL directory:
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
  • Next, input the following command:
mysqld --init-file=C:\\mysql-init.txt

navigate to mysql server directory, restart mysql server and apply the new config file

Navigate to MySQL server directory, restart MySQL server, and apply the new config file

Note: You should take into consideration that there are two backslashes before the mysql-init prompt. Besides, if you used a different name to the one we used for illustration in step 2 above, then you should use that name immediately after the two backslashes without spacing.

Ensure you double-check to ascertain that the new password works flawlessly. After confirmation, proceed and do away with the mysql-init.txt file we stored in our C:\ directory.

Generic instructions for resetting MySQL root password

The preceding sections have provided in-depth instructions on resetting MySQL root passwords for Linux and Windows. Alternatively, when using any platform, you can use the MySQL client to reset the password. However, it is vital to note that this method is less secure compared to the preceding methods.

1. The first step is to stop the MySQL server. After stopping, restart it with the option below:

--skip-grant-tables

This option is deemed insecure since if the server starts with the –skip-grant-tables option, skip-networking is enabled by disabling remote connections.

2. Now connect to the MySQL server by running the command below:

mysql

Note: no password is required while restarting the MySQL server using MySQL client since the server started with the –skip-grant-tables.

3. Now, in the MySQL client, run the command below to tell the server to reload the grant tables. This will enable the account management statements that had been stopped earlier to work again:

mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Note: To change the root password for a different system, ensure to alter the localhost with the system’s hostname.

That’s all you have successfully reset your MySQL root password.

Recover your MySQL password

1. Stop the MySQL server process using the command:

sudo service mysql stop

service stop mysql

service stop MySQL

sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
mysql> use mysql;

mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';

mysql> flush privileges;

mysql> quit

Once you are done running all the commands mentioned above, proceed and restart the MySQL daemon using this command:

sudo service mysql restart

restart mysql service

restart MySQL service

By now, you should be able to log into MySQL using the newly set password.

That’s all. We believe you are now in a position to set, reset, and recover your MySQL root password.

How To Solve The MySQL Error: Access denied for user root@localhost

mysql -u root -p
sudo mysql

Then, input your passcode at the prompt. A MySQL shell will load.

mysql shell

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';

For the sake of the tutorial, I will change the password to ‘Fosslinux as shown in the command line and output below:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Fosslinux';

open mysql shell

open MySQL shell

After running the commands listed above, exit the MySQL shell by clicking CTRL + D on your keyboard or key in “exit” on the MySQL shell and click enter. You do not have to restart the MySQL service for you to log in using the command below:

sudo service mysql restart 

restart mysql service

restart MySQL service

At this point, you can now attempt to access MySQL with root again. In the terminal, key in:

mysql -u root -p

login to mysql as root

login to MySQL as root

Step 4 — Reverting Your Database Server to Normal Settings

In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.

For MariaDB, unset the MYSQLD_OPTS environment variable you set previously:

  1. systemctl unset-environment MYSQLD_OPTS

Then, restart the service using systemctl:

  1. systemctl restart mariadb

For MySQL, remove the modified systemd configuration:

  1. systemctl revert mysql
Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

Then, reload the systemd configuration to apply the changes:

  1. systemctl daemon-reload

Finally, restart the service:

  1. systemctl restart mysql
  1. mysql root

You’ll be prompted for a password. Enter your new password, and you’ll gain access to the database prompt as expected.

Step 3 — Changing the Root Password

The database server is now running in a limited mode; the grant tables are not loaded, and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.

Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

  1. FLUSH PRIVILEGES

You can now change the root password. The method you use depends on whether you are using MariaDB or MySQL.

Changing the MariaDB Password

  1. ALTER @ IDENTIFIED BY

You’ll see this output indicating that the password changed:

Query OK, 0 rows affected (0.001 sec)
  1. UPDATE mysql.user SET authentication_string WHERE user
  2. UPDATE mysql.user SET plugin WHERE user
Query OK, 0 rows affected (0.01 sec)

The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.

Changing the MySQL Password

  1. ALTER @ IDENTIFIED WITH caching_sha2_password BY

You’ll see this output indicating the password was changed successfully:

Query OK, 0 rows affected (0.01 sec)

The password is now changed. Exit the MySQL console by typing exit.

Let’s restart the database in normal operational mode.

Step 4 — Testing MySQL

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.

  1. systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2020-04-21 12:56:48 UTC; 6min ago
   Main PID: 10382 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 1137)
     Memory: 370.0M
     CGroup: /system.slice/mysql.service
             └─10382 /usr/sbin/mysqld

If MySQL isn’t running, you can start it with sudo systemctl start mysql.

  1. mysqladmin sammy version

You should see output similar to this:

mysqladmin  Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version        8.0.19-0ubuntu5
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/run/mysqld/mysqld.sock
Uptime:            10 min 44 sec

Threads: 2  Questions: 25  Slow queries: 0  Opens: 149  Flush tables: 3  Open tables: 69  Queries per second avg: 0.038

This means MySQL is up and running.

Дополнительно:  Что делать если не работает ни один браузер? • Удаляем ошибки, установка ПО, подключение оборудования

Step 2 — Configuring MySQL

Warning: As of July 2022, an error will occur when you run the mysql_secure_installation script without some further configuration. The reason is that this script will attempt to set a password for the installation’s root MySQL account but, by default on Ubuntu installations, this account is not configured to connect using a password.

 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

New password:

This will lead the script into a recursive loop which you can only get out of by closing your terminal window.

First, open up the MySQL prompt:

  1. mysql
  1. ALTER @ IDENTIFIED WITH mysql_native_password BY

After making this change, exit the MySQL prompt:

  1. mysql root

Then go back to using the default authentication method using this command:

  1. ALTER @ IDENTIFIED WITH auth_socket

Run the security script with sudo:

  1. mysql_secure_installation
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
 2
Please set the password for root here.


New password:

Re-enter new password:

If you used the Validate Password Plugin, you’ll receive feedback on the strength of your new password. Then the script will ask if you want to continue with the password you just entered or if you want to enter a new one. Assuming you’re satisfied with the strength of the password you just entered, enter Y to continue the script:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

Step 1 — Installing MySQL

On Ubuntu 20.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.27.

To install it, update the package index on your server if you’ve not done so recently:

  1. update

Then install the mysql-server package:

  1. mysql-server

Ensure that the server is running using the systemctl start command:

  1. systemctl start mysql.service

These commands will install and start MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.

How to set up, change, and recover a MySQL root password

Please note: there have been many attacks across the IT landscape, thus necessitating very strong passwords, which I highly recommend for your databases. You can try out an unsystematic password generator, then save the password via the aid of a password manager.

Let’s carry on.

Setting the password for the first time

Typically, during the installation of MySQL, you are required to set an initial password. However, if this was not done while installing and setting up MySQL, you will first have to set up a password. Here is how to do that:

  1. First, open up a terminal window.
  2. After launching the terminal, paste and execute this command:
mysqladmin -u root password Fosslinux

Where Fosslinux is the password that you will be using from now on, this means that every time you log into MySQL using the command mysql -u root -p, you will have to key in the password we have just configured.

mysql_secure_installation

mysql secure installation

MySQL secure installation

After setting up the MySQL root password for the first time, it is time to learn how to change it.

Changing the MySQL root user password

  • First, create a new file using the command below:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Fosslinux$11';

create new password

create new password

Where Fosslinux$11 is the new password that will be used.  Remember to satisfy the current password policy by combining uppercase, lowercase, numeric and special characters.

Save the file as ~/mysql-pwd

  • Next, stop the MySQL daemon using the following command:
sudo systemctl stop mysql

stop mysql service

stop MySQL service

  • Now that the daemon is stopped, issue and execute the following command on your terminal:
sudo mysqld -init-file=~/mysql-pwd

save mysql password

save MySQL password

  • As soon as the command prompt completes executing the above command, proceed and restart the MySQL daemon using the following command:
sudo systemctl start mysql

start mysql service

start MySQL service

  • At this point, you should be able to log into the MySQL command prompt using the newly set admin passcode by executing the command below:
mysql -u root -p

login as a root user

Now that we have fruitfully learned how to change the newly created MySQL root password, it is time to learn how to recover a forgotten or lost MySQL root password.

Step 3 — Changing the Root Password

The database server is now running in a limited mode; the grant tables are not loaded, and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.

Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

  1. FLUSH PRIVILEGES

You can now change the root password. The method you use depends on whether you are using MariaDB or MySQL.

Changing the MariaDB Password

  1. UPDATE mysql.user SET password PASSWORD WHERE user

You’ll see this output indicating that the password changed:

Query OK, 1 row affected (0.00 sec)
  1. UPDATE mysql.user SET authentication_string WHERE user
  2. UPDATE mysql.user SET plugin WHERE user
Query OK, 0 rows affected (0.01 sec)

The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.

Changing the MySQL Password

  1. UPDATE mysql.user SET authentication_string PASSWORD WHERE user

You’ll see this output indicating the password was changed successfully:

Query OK, 1 row affected (0.00 sec)
  1. UPDATE mysql.user SET plugin WHERE user

You’ll see output similar to the previous command:

Query OK, 1 row affected (0.00 sec)

The password is now changed. Exit the MySQL console by typing exit.

Let’s restart the database in normal operational mode.

Step 1 — Identifying the Database Version and Stopping the Server

  1. mysql

If you’re running MariaDB, you’ll see “MariaDB” preceded by the version number in the output:

mysql  Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

You’ll see output like this if you’re running MySQL:

mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
  1. systemctl stop mariadb

For MySQL, shut down the database server by running:

  1. systemctl stop mysql

With the database stopped, you can restart it in safe mode to reset the root password.

Step 4 — Reverting Your Database Server to Normal Settings

In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.

For MariaDB, unset the MYSQLD_OPTS environment variable you set previously:

  1. systemctl unset-environment MYSQLD_OPTS

Then, restart the service using systemctl:

  1. systemctl restart mariadb

For MySQL, remove the modified systemd configuration:

  1. systemctl revert mysql
Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

Then, reload the systemd configuration to apply the changes:

  1. systemctl daemon-reload

Finally, restart the service:

  1. systemctl restart mysql
  1. mysql root

You’ll be prompted for a password. Enter your new password, and you’ll gain access to the database prompt as expected.

Prerequisites

To recover your MySQL or MariaDB root password, you will need:

  • Access to the Ubuntu 18.04 server running MySQL or MariaDB with a sudo user or other way of accessing the server with root privileges. In order to try out the recovery methods in this tutorial without affecting your production server, use the initial server setup tutorial to create a test server with a regular, non-root user with sudo privileges. Then install MySQL following How to install MySQL on Ubuntu 18.04.

Step 3 — Creating a Dedicated MySQL User and Granting Privileges

  1. mysql
  1. mysql root
  1. CREATE @ IDENTIFIED WITH authentication_plugin BY
  1. CREATE @ IDENTIFIED BY
  1. CREATE @ IDENTIFIED WITH mysql_native_password BY
  1. ALTER @ IDENTIFIED WITH mysql_native_password BY
  1. GRANT PRIVILEGE ON database.table TO @

You can find the full list of available privileges in the official MySQL documentation.

  1. GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO @ WITH GRANT OPTION
  1. GRANT ALL PRIVILEGES ON *.* TO @ WITH GRANT OPTION
  1. FLUSH PRIVILEGES

Then you can exit the MySQL client:

  1. mysql sammy

Finally, let’s test the MySQL installation.

Prerequisites

To recover your MySQL or MariaDB root password, you will need:

Step 1 — Identifying the Database Version and Stopping the Server

  1. mysql

If you’re running MariaDB, you’ll see “MariaDB” preceded by the version number in the output:

mysql  Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

You’ll see output like this if you’re running MySQL:

mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper
  1. systemctl stop mariadb

For MySQL, shut down the database server by running:

  1. systemctl stop mysql

With the database stopped, you can restart it in safe mode to reset the root password.

Conclusion

The article further breaks down the steps to set up, change and recover a MySQL root password and set up the root password for the first time. Ensure to stick to all the processes provided in this article to avoid security breaches by using strong password combinations. We hope the article will be helpful. If yes, give a thumbs up via the comments section.

Conclusion

You now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take:

  • Set up a LAMP stack or a LEMP stack
  • Practice running queries with SQL
  • Manage your MySQL installation with phpMyAdmin

Conclusion

If you had previously assigned a root password to MySQL but forgot it, you can reset it by assigning a new password to your MySQL, as shown in this article. 

Оцените статью
Master Hi-technology
Добавить комментарий