How to set root password to null
MysqlMysql ManagementMysql Problem Overview
How can I change the password for root
user of MySQL to null
-- meaning no password or ''
-- from the MySQL command line client?
Mysql Solutions
Solution 1 - Mysql
Worked for me and "5.7.11 MySQL Community Server":
use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';
I had to change the 'plugin' field as well because it was set to 'auth_socket'.
After that I could connect as mysql -u root
without a password.
Solution 2 - Mysql
If you want an empty password, you should set the password to null and not use the Password hash function, as such:
On the command line:
sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot
In MySQL:
use mysql;
update user set password=null where User='root';
flush privileges;
quit;
Solution 3 - Mysql
- connect to mysql as user root (use one of the two following methods)
- login as root and start mysql using
mysql -p
, enter current root password - login as self and start mysql using
mysql -u root -p
, enter current root password
- login as root and start mysql using
mysql> set password = password('');
Done! No root password.
Solution 4 - Mysql
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');
Solution 5 - Mysql
This worked for me on Ubuntu 16.04 with v5.7.15 MySQL:
First, make sure you have mysql-client installed (sudo apt-get install mysql-client
).
Open terminal and login:
mysql -uroot -p
(then type your password)
After that:
use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';
(tnx @Stanislav Karakhanov)
And the very last important thing is to reset mysql service:
sudo service mysql restart
You should now be able to login (without passsword) also by using MySQL Workbench.
Solution 6 - Mysql
You can recover MySQL database server password with following five easy steps.
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 password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password i.e. reset mysql password.
Step # 5: Exit and restart the MySQL server.
Here are commands you need to type for each step (login as the root user):
Step # 1 : Stop mysql service
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld.
Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &
Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started
Step # 3: Connect to mysql server using mysql client:
# mysql -u root
Output:
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>
Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe --skip-grant-tables
Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# mysql -u root -p
Source: http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Solution 7 - Mysql
For MySQL 8.0 just:
SET PASSWORD FOR 'root'@'localhost' = '';
Solution 8 - Mysql
It's not a good idea to edit mysql
database directly.
I prefer the following steps:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;
Solution 9 - Mysql
This is from MySQL 8.0.13:
use mysql;
update user set authentication_string=null where user='root';
quit;
Solution 10 - Mysql
It works for me.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
Solution 11 - Mysql
The answer by user64141
use mysql;
update user set password=null where User='root';
flush privileges;
quit;
didn't work for me in MariaDB 10.1.5 (supposed to be a drop in replacement for MySQL). While didn't tested it in MySQL 5.6 to see if is an upstream change, the error I got was: > ERROR 1048 (23000): Column 'Password' cannot be null
But replacing the null with empty single or double quotes worked fine.
update user set password='' where User='root';
or
update user set password="" where User='root';
Solution 12 - Mysql
I noticed a few of these solutions above are now deprecated.
To set an empty password simply follow these steps:
mysql -u root -p
use mysql
SET PASSWORD FOR 'root'@'localhost' = '';
\q (to quit)
now run: mysql -u root
You should be able to start mysql up without a password now.
Solution 13 - Mysql
If you know your Root Password and just wish to reset it then do as below:
-
Start MySQL Service from control panel > Administrative Tools > Services. (only if it was stopped by you earlier ! Otherwise, just skip this step)
-
Start MySQL Workbench
-
Type in this command/SQL line
ALTER USER 'root'@'localhost' PASSWORD EXPIRE;
To reset any other user password... just type other user name instead of root.
Solution 14 - Mysql
For connect to mysql without password:
SET PASSWORD = ""```
https://dev.mysql.com/doc/refman/5.7/en/set-password.html
Solution 15 - Mysql
I am using nodejs and windows 10. A combination of two answers worked for me.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;
followed by:
restart;
Hope this helps for others who still have an issue with this.
Solution 16 - Mysql
The syntax is slightly different depending on version. From the docs here: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY '';
MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');
Solution 17 - Mysql
My variant for MySQL 5.7:
Stop service mysql:
$ sudo service mysql stop
Running in Safe Mode:
$ sudo mysqld_safe --skip-grant-tables --skip-networking
(above line is the whole command)
Open a new terminal window:
$ mysql -u root
$ mysql use mysql;
$ mysql update user set authentication_string=password('password') where user='root';
$ mysql update user set plugin="mysql_native_password" where User='root';
$ mysql flush privileges;
$ mysql quit;
Run the mysql service:
$ sudo service mysql start
Solution 18 - Mysql
Wanted to put my own 2cents in here bcuz the above answers did not work for me. On centos 7, mysql community v8, shell is bash.
The correct commands would be as follows:
# start mysql without password checking
systemctl stop mysqld 2>/dev/null
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" &&
systemctl start mysqld
# set default password to nothing
mysql -u root mysql <<- 'EOF'
FLUSH PRIVILEGES;
UNINSTALL COMPONENT 'file://component_validate_password';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;
INSTALL COMPONENT 'file://component_validate_password';
EOF
# restart mysql normally
systemctl restart mysqld
then you can login without password:
mysql -u root
Solution 19 - Mysql
its all because you installed greater then 5.6 version of the mysql
Solutions
1.you can degrade mysql version solution
2 reconfigure authentication to native type or legacy type authentication using
configure option
Solution 20 - Mysql
On ubuntu 19.10, mysql 8, this is what worked for me:
$ sudo mysqld --skip-grant-tables &
$ mysql
> use mysql
> alter user set authentication_string='', plugin='mysql_native_password' where user = 'root';
> quit
$ sudo mysqladmin shutdown
$ sudo systemctl start mysql
If you get errors trying to run mysqld_safe
, in particular: /var/run/mysqld for UNIX socket file don't exists
, you can try creating the dir and running mysqld_safe
again.
$ sudo mkdir /var/run/mysqld
$ sudo chown mysql /var/run/mysqld
$ sudo chgrp mysql /var/run/mysqld
Solution 21 - Mysql
After searching for hours i found it . just Change the password to something contains Upper case numeric and special characters in it.