How to Change MySQL Root Password in Ubuntu 22.04

How to allow remote connection to mysql

Method 2 — Update or change password

1. Login to the MySQL server, type the following command at the shell prompt:
2. Use the mysql database (type commands at the mysql> prompt):
3. Change password for a user:
4. Reload privileges:

This method you need to use while using PHP or Perl scripting.

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.

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.

sudo mysql -u root -p

show databases;

use mysql;

select user, authentication_string, plugin, password_lifetime, password_last_changed, password_expired from user;

desc user;

sudo -i mysql

sudo mysqladmin -u root password myPassWhichIWant

mysql_secure_connection

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

sudo systemctl stop mysql

sudo mysqld -init-file=~/mysql-password

sudo systemctl start mysql

Это руководство объясняет, каким образом можно установить, измененить или сбросить (если вы забыли пароль) рутовый пароль в MySQL. Снова и снова я наблюдаю одну и ту же картину:

Поэтому я нашёл время, чтобы напомнить вам как решить связанную с этим проблему в MySQL. Если вы ищете быстрое решение проблемы по сбросу пароля root, можете найте его в конце данного руководства.

Метод 1. Установка пароля root в первый раз.

где newpass будет пароль который вы устанавливаете.

Для изменения (обновления) пароля root воспользуйтесь следующей командой:

где oldpassword — ваш старый пароль, а newpassword соотвественно новый. Если же вы в ответ получили следующее сообщение:

то это означает, что пароль вы попросту забыли, либо его сменил кто-то другой. Воспользуйтесь следующей инструкцией для восстановления пароля к вашему MySQL.

Изменения пароля MySQL для других пользователей.

Для изменения пароля обычного пользователя введите следующую команду:

Метод 2 — Обновление или изменение пароля.

MySQL хранит имена пользователей и пароли в таблице пользователей внутри базы данных. Вы можете обновить пароль используя следующий метод:

mysql -u root -p

2.  Начните работу с базой данных. В качестве приглашения для ввода команд вначале строки у вас должно быть mysql>

mysql> use mysql;

3. Смените пароль пользователя

mysql> flush privileges;
mysql> quit

Этот метод применим в случае использования на вашем сервере PHP и скриптов Perl.

Восстановление пароля root в MySQL.

Вы можете восстановить пароль от баз данных MySQL если повторите следующие 5 шагов:

• Остановите демон MySQL.
• Запустите демон MySQL (mysqld) с опцией —skip-grant-tables, т.к. в этом случае пароль не запрашивается.
• Подключитесь к серверу MySQL c root-привелегиями
• Введите новый пароль.
• Выйдите и перегрузите демон MySQL.

Далее приводятся команды, которые необходимо использовать для каждого шага, при условии, что вы вошли в систему с root-привелегиями.

1. Останавливаем службу MySQL:

/etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2.  Запускаем службу с опцией —skip-grant-tables

mysqld_safe —skip-grant-tables &

Должен быть следующий вывод:

3.   Подключаемся с серверу MySQL при помощи клиента mysql:

mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

4. Вводим новый пароль для root:

5. Останавливаем сервер MySQL:

/etc/init.d/mysql start
mysql -u root -p

Оригинал статьи youisbee.ru

mysqladmin -u root password newpass

mysqladmin -u root -p password newpass

Enter password:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

mysql_secure_installation

<-- Enter a new MySQL root password
Re-enter new password: <-- Repeat the MySQL root password



 

mysqladmin -u user-name -p password newpass

mysql -u root -p

mysql> use mysql;

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

mysql> flush privileges;
mysql> quit

service mysql stop

Stopping MySQL database server: mysqld.

mysqld_safe --skip-grant-tables &

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56299
Server version: 5.6.34-1 (Debian)

Copyright (c) 2000, 2016, 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.

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

mysql>

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

;
mysql> flush privileges;

service mysql stop

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

service mysql start
mysql -u root -p

Ever forgotten or just wanted to change your MySQL password? You’ve come to the right place – in this guide, we’ll walk you through how to set, change and recover your MySQL root password as well as other useful MySQL commands.

RunCloud uses MariaDB, an enhanced drop-in replacement for MySQL, as a database. MariaDB is a community-driven package by the original developers of MySQL, with the promise of future compatibility, hence, terminal commands and software tools for MySQL database can be used for MariaDB as well.

However, if you want to avoid any of these risks then SSH is the way to go. If you have no prior experience managing MySQL, don’t worry, with RunCloud, it is as easy as ABC.

First, you need a terminal to work on. On a Windows machine, you can download the PuTTY software. If you are on a Mac or Linux, there is already a built-in Terminal app installed.

The MySQL root password that is displayed on your terminal screen at the end of the RunCloud agent installation on your server (example below is installation on DigitalOcean) is meant to be kept for safekeeping elsewhere, but if you forgot to copy it down, you can still retrieve it.

Pic1: Keep the MySQL password somewhere safe!

1. Log in as root into your server through SSH (eg: puTTY/terminal/bash). Alternatively, run the commands that follow as su or sudo as root user. This method is safer.
2. Navigate to /etc/mysql

   /cd /etc/mysql

3. View the file my.cnf either using the command cat or use any text editing software (vi/vim/nano).

   cat my.cnf

Pic 2: Viewing the MySQL root password

How to Reset/Change MySQL Password

1. Login to your server using SSH.These commands must be run with root user privileges. Although you can login to your server as root user, it is highly recommended not to. If you can, log in to your server as a system user with root privileges and run these commands as su or sudo commands using those administrative privileges. You can also close the incoming traffic for MySQL port (default port is 3306 unless you changed it to something else) temporarily at the firewall if you want to be extra careful.
2. Stop the MySQL service.

   service mysql stop

3. Restart MySQL service in single-user mode and bypassing password authorization, by running the command below. Since the command --skip-grant-tables enables anyone to connect without a password, it is considered highly insecure, hence, all commands that follow must be done within a brief period. The ampersand (symbol &) at the end is required as it ensures that the command is being run parallel with the commands that follow afterwards.

   mysqld_safe --skip-grant-tables &

4. Login into MySQL to connect.

   mysql

5. At the MariaDB> prompt, change the root password by running the commands below. Replace the NEWSTRONGPASSWORD with your chosen new strong password.

   UPDATE mysql.user SET Password=password('NEWSTRONGPASSWORD') WHERE User='root';

6. At the MariaDB> prompt, run the following commands.

   FLUSH PRIVILEGES;
   exit;

7. Stop the MySQL server using the following command. Before the MySQL server shuts down, you will be prompted to key in the root password. Key in your new root password.

   mysqladmin -u root -p shutdown

8. Start the MySQL server in normal mode.

   service mysql start

9. Verify that you are able to connect to MySQL by running the command mysql again. If you disable incoming traffic for the mysql port, please remember to enable it again.
10. Lastly, you need to update the MySQL root password in the file /etc/mysql/my.cnf (as shown in Pic2) or you might not be able to add new database or user. You can use vi, vim or nano to edit the file.

    nano /root/.my.cnf

Pic 3: Resetting the MySQL root password

Other Basic MySQL Commands

Even though you can run some basic MySQL commands in the RunCloud control panel, it would not hurt to know how to do the same tasks in command line. Below are some of the basic commands that should get you going.

When you run the commands below, replace the database name of your choice at the DatabaseNameHere.

To show the available databases.

SHOW DATABASES;

Creating a database.

CREATE DATABASE DatabaseNameHere;

DROP DATABASE DatabaseNameHere;

To use or operate on a particular database. The MySQL prompt will change to reflect the database name that you have specified.

USE DatabaseNameHere;

SELECT database();

CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';

GRANT ALL PRIVILEGES ON *.* TO 'UsernameHere'@'localhost'
->     WITH GRANT OPTION;

Categories: Tutorials, Server Management, Tips & Tricks

Mysqladmin Command To Change Root Password

Recover MySQL root password

Step # 1  : Stop the MySQL server process.

Step # 2  : Start the MySQL (mysqld) server/daemon process with the —skip-grant-tables option so that it will not prompt for a password.

Step # 4  : Set a new root password.

Step # 5  : Exit and restart the MySQL server.

Step # 1  : Stop the MySQL service:

Step # 2  : Start the MySQL server w/o password:

Step # 3  : Connect to the MySQL server using the MySQL client:

Step # 5  : Stop the MySQL server:

Start the MySQL server and test it:

Recover your MySQL password

1. Stop the MySQL server process using the command:

sudo service mysql stop

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

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.

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

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

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

Change MySQL password for other users

43 Comment(s)

Comments

This feature is only available to subscribers. Get your subscription here.

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

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

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

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

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

sudo apt 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

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

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 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

• 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 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.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

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

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.

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

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

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

• Now that the daemon is stopped, issue and execute the following command on your terminal:

sudo mysqld -init-file=~/mysql-pwd

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

• 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

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.

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

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.