Mysql root password change in linux

How to change the mysql root password

I have installed MySQL server 5 on redhat linux. I can’t login as root so I can’t change the root password.

mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

mysqladmin -u root password 'newpass' 

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

/sbin/service mysqld start --skip-grant-tables 

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass') -> WHERE User='root'; ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user' 

I even uninstalled mysql-server (using yum) and then reinstalled it but that did not help. How do I force reset the root password?

10 Answers 10

One option is to save UPDATE mysql.user SET Password=PASSWORD(‘newpass’) WHERE User=’root’; into a file and then manually start mysqld with —init-file=FILENAME . Once the server starts, it should reset your password, and then you should be able to log in. After this, you should shut down the server and start it normally.

Good lord, why would someone give a -1 to the only solution I found to be working. An init sql file was the only way I could overwrite the mysql root user password and be able to use it. It is a shame you need to fallback to such operations on a freshly installed software, yet it is good to have a reliable workaround.

A little late to the game, but I had the same issue on a raspberry pi install and found out that it needs elevation. Adding a sudo to the front of the password change allowed it to work.

sudo mysqladmin -u root password 'newpass' 

followed by an elevated sql access

If either are not run as sudo, it will fail.

The root user password is an empty string by default.

And (using password: NO) says that there is no password.

Do you try to login from another system? I imagine you can only login as root user locally.

I am not sure, but I don’t think so. Unfortunately I never used redhat. EDIT: But why don’t you just try it?

Note that if you want to try logging in with no password, you should just avoid specifying -p on the command line altogether.

Note: The root password may not by blank by default, now (just found this out). I installed MySQL 5.7.16 from a yum repository and found that the installer had created a temporary password that was stored in a plain text file 😛 (for yum this was /var/log/mysqld.log)

I removed the MySQL installation and deleted the data files, and then reinstalled it.

Then I was able to set the root password. Once you set the root password to something. mysqladmin won’t let you reset it if you don’t know it.

To reset it, you’ve got to have ownership over how mysqld is executed, and feed it an init file to change the root password: https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

This helped me on Windows with MySQL Server 5.6. Make sure you change the mysqld path to point to where you have installed MySql Server, for me it was «C:\Program Files\mysql\MySQL Server 5.6\bin\mysqld.exe» :

1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop.
3. Create a text file containing the following statements. Replace the password with the password that you want to use.

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

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

If you installed MySQL to a location other than C:\mysql, adjust the command accordingly. The server executes the contents of the file named by the —init-file option at startup, changing each root account password. You can also add the —console option to the command if you want server output to appear in the console window rather than in a log file. If you installed MySQL using the MySQL Installation Wizard, you may need to specify a —defaults-file option:

C:\> "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\\mysql-init.txt 

Источник

How to Change MySQL Root Password in Ubuntu 22.04

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.

Changing MySQL root password in Ubuntu 22.04

For the purpose of changing the MySQL root password in Ubuntu 22.04, follow the given instructions.

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

Step 2: Stop MySQL service

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

Now, the MQL service is stopped:

Step 3: Check MySQL status

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

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

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

Then head towards the next step.

Step 5: Start MySQL service

Utilize the following command for starting the MySQL service

MySQL service is now started:

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:

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:

Step 8: Flush privileges

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

Step 9: Choose MySQL database

Then choose the MySQL database:

Step 10: Change MySQL root password

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

Step 11: Exit MySQL

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

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:

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

Step 13: Kill MySQL processes

Execute the following “killall” command to kill all MySQL processes:

Step 14: Restart MySQL service

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

Now, move to the next step.

Step 15: Log in to MySQL

Log in to MySQL by writing out the following command in Ubuntu 22.04 terminal and specify the password you have added with the “ALTER” command:

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

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

Sharqa Hameed

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.

Источник

How to Change MySQL Root Password in Ubuntu 20.04

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:

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:

Check the status of the MySQL server to verify using the command:

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:

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:

Step 5: Confirm the status of the MySQL Server

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

Step 6: Sign In to the MySQL shell

Now, you need to log in as a root user to the MySQL server and for signing in to the MySQL shell, type the command:

Without providing any password, you will log in to the MySQL shell.

Step 7: Alter the root password

Now, flush the privileges first:

Select the MySQL database:

And set the new password for the root user using the ALTER command by typing the following statement:

At the place of “the-new-password” provide your new password. After successfully changing the MySQL root password, log out from the MySQL shell:

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:

After doing so, remove the modified system configuration by reverting “MySQL”:

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

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

After having the output as shown in the picture, hit “Enter” and restart the MySQL server using the command given below:

Step 10: Log in with the newly set Password

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

Provide the recently set password in the ALTER command:

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

Shehroz Azam

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.

Источник