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» :
- Log on to your system as Administrator.
- 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.
- 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?
MySQL is a standard query language for managing relational databases. If you forgot the MySQL root password, don’t worry; there is a method to reset it. The MySQL root password can be changed, and the server can be accessed with sudo privileges.
In this guide, we will elaborate on changing the root password for MySQL in Ubuntu 22.04.
How to Change MySQL Root User Password on Ubuntu 22.04?
This guide will take you through every step for changing the MySQL password for a root user on Ubuntu 22.04.
Step 1: Check MySQL Version
First, Check the installed version of MySQL server in our system using the below command:
MySQL version 8.0.30 is installed in the system.
Step 2: Stop MySQL service
Now, to change the MySQL root password; first, you need to stop the MySQL service using this command:
$ sudo systemctl stop mysql.service
You can verify the MySQL server service status if it’s running or stooped by executing provided command:
$ sudo systemctl status mysql.service
Output shows the system is “inactive”, verifying the MySQL service is stopped.
Step 3: Skip Grant Tables and Networking
Grant tables and networking store the user’s information in the MySQL database in Ubuntu 22.04. To access the database without a valid password, you need to skip grants tables and networking by running the following command:
$ sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables"
The MySQL environment is set up. We can log in to the MySQL server without using a password.
Step 4: Start MySQL Service
After setting the MySQL environment, run this command to start the server:
$ sudo systemctl start mysql.service
The service for MySQL has started.
Use the following command in the terminal to verify the MySQL server’s status, including whether it is active or not:
$ sudo systemctl status mysql.service
The current state of MySQL is active (running).
Step 5: Log in as Root User to MySQL
You don’t need a password to log in as the root user. To do so, use the following command:
You will be directed to the MYSQL prompt.
Step 6: Change MySQL root password
Now, the root password for MySQL can be modified. Run the following “ALTER” command in MySQL prompt for changing the root password:
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
The new MySQL root password is set up in Ubuntu 22.04.
Step 7: Quit MySQL Prompt
To log out, type “quit” in MySQL prompt:
Step 8: Restore Database Normal Settings
In the previous steps, we changed the environment variable by skipping networking and grant tables. Now, it’s time to restore the MySQL database’s normal setting for a secure configuration. For unsetting the environment variable:
$ sudo systemctl unset-environment MYSQLD_OPTS
Next, we need to remove the modified system configuration by reverting the MySQL using this command:
$ sudo systemctl revert mysql
Step 9: Kill All MySQL Services
Now, use the “killall” command to end all MySQL server’s processes:
MySQL is completely closed.
Step 10: Restart MySQL service
Restart the MySQL service after closing all the previous processes using:
$ sudo systemctl restart mysql.service
MySQL has been activated again.
Step 11: Log in to MySQL With New Password
Finally, log in to the MySQL server as a root user with a newly created password:
So, we have successfully changed the MySQL root password in Ubuntu.
Conclusion
MySQL root password can be changed in Ubuntu 22.04 using the “ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new_password’” command. Before that, we have to access the MySQL database skipping “grant tables” and “networking” that store user privilege information. In this guide, we have demonstrated the essential steps to change MySQL root password in Ubuntu 22.04.