can't login as mysql user root from normal user account in ubuntu 16.04
I just installed Ubuntu 16.04 LTS along with the packages php
, mariadb
and nginx
. I ran mysql_secure_installation
and changed the root password.
Now when I try to login to mysql
using the root account while logged in Ubuntu as normal user account I get access denied.
When I login using sudo mysql
, mysql doesn't even ask me password. If I run mysql_secure_installtion
I see that old settings never got set permanently.
What am I doing wrong?
permissions mysql
add a comment |
I just installed Ubuntu 16.04 LTS along with the packages php
, mariadb
and nginx
. I ran mysql_secure_installation
and changed the root password.
Now when I try to login to mysql
using the root account while logged in Ubuntu as normal user account I get access denied.
When I login using sudo mysql
, mysql doesn't even ask me password. If I run mysql_secure_installtion
I see that old settings never got set permanently.
What am I doing wrong?
permissions mysql
add a comment |
I just installed Ubuntu 16.04 LTS along with the packages php
, mariadb
and nginx
. I ran mysql_secure_installation
and changed the root password.
Now when I try to login to mysql
using the root account while logged in Ubuntu as normal user account I get access denied.
When I login using sudo mysql
, mysql doesn't even ask me password. If I run mysql_secure_installtion
I see that old settings never got set permanently.
What am I doing wrong?
permissions mysql
I just installed Ubuntu 16.04 LTS along with the packages php
, mariadb
and nginx
. I ran mysql_secure_installation
and changed the root password.
Now when I try to login to mysql
using the root account while logged in Ubuntu as normal user account I get access denied.
When I login using sudo mysql
, mysql doesn't even ask me password. If I run mysql_secure_installtion
I see that old settings never got set permanently.
What am I doing wrong?
permissions mysql
permissions mysql
edited Jun 11 '16 at 10:05
Videonauth
23.8k126898
23.8k126898
asked May 2 '16 at 11:45
codescope
841257
841257
add a comment |
add a comment |
10 Answers
10
active
oldest
votes
I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:
First, connect in sudo mysql
sudo mysql -u root
Check your accounts present in your db
SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account
mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)
Recreate your user
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don't forget to flush privileges)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.
I hope this will help someone :)
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
Hold it, Doesn't%
mean you can connect from anywhere...remotely?
– Stevie G
Nov 4 '16 at 8:01
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement togrant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
|
show 14 more comments
If you install 5.7 and don’t provide a password to the
root
user, it
will use theauth_socket
plugin. That plugin doesn’t care and
doesn’t need a password. It just checks if the user is connecting
using a UNIX socket and then compares the username.
Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"
So in order to to change the plugin
back to mysql_native_password
:
Login with sudo:
sudo mysql -u root
Change the
plugin
and set a password with a single command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Of course you can also use the command above to set an empty password.
Just for the record, (and MariaDB < 10.2
users) there is also another way to only change the plugin
without providing a password (leaving it empty):
update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587
– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
|
show 6 more comments
In short, on MariaDB
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
where you replace NEWPASSWORD with the password you want, and everything else verbatim.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user.
You can test this as follows by switching to system root, and then type:
mysql -uroot -p
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql
as the password is irrelevant and the user is defined).
So what's happening? Well, if you log in as root and do the following:
select User,host,plugin from mysql.user;
+----------------+-----------+-----------------------+
| User | host | plugin |
+----------------+-----------+-----------------------+
| root | localhost | auth_socket |
+----------------+-----------+-----------------------+
you'll note auth_socket
(which may read unix_socket
on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
So the solution is to update the Users to not use the auth_socket/unix_socket
and properly set a password.
On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password
you type verbatim.
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)
UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
Otherwise:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';
Then
FLUSH PRIVILEGES;
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant
statement exactly right - easier to simply update the root you already have.
In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.
2
Found this to work perfectly. Makes me wonder though why changing the root password withmysql_secure_installation
didn't have the same effect. Where does it store the new password?
– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the twoUPDATE
s that apply to MariaDB <10.2 and theALTER
to other versions? And is the secondUPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?
– Michael Scheper
Dec 17 '18 at 2:23
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". Themysql_native_password
is that option.
– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
add a comment |
If you have installed MySQL/MariaDB from base repository, users can not login to MySQL as the MySQL root user from their Unix logins (not applicable if they have sudo access)
Login to MySQL root shell:
$ sudo mysql -u root -p
Execute below queries:
use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit;
Open a new shell, then:
$ mysql -u root -p
Source:
http://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
add a comment |
Try to create new mysql account, for me it has worked (mysql 5.7.12):
Login as sudo:
sudo mysql -uroot
Create new user and grant him privileges (no password):
CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
Login as new user:
mysql -uadmin
add a comment |
I had to do two things (thanks to @Todor and @Loremhipsum):
update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';
and then:
FLUSH PRIVILEGES;
I would not recommend to drop user root
.
add a comment |
Try this code first,
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql
echo "FLUSH PRIVILEGES;" >> your_init_file.sql
and then,
killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql
then press Ctrl+Z
and type: bg
to run the process from the foreground into the background, then verify your access by:
mysql -u root -proot
mysql> show grants;
add a comment |
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
.
The only way to set password is to switch to native authentication like:
$ sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'test';
add a comment |
I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket
/unix_socket
setting.
So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:
UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;
The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin
value, flush privileges and get on with life.
add a comment |
I had the same issue and running the following fixed it:
mysql_upgrade --force
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the userroot
being set withplugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if themysql_upgrade
process itself accounts for theplugin
setting which I doubt.
– JakeGould
Oct 20 '17 at 1:37
add a comment |
protected by Community♦ Mar 11 '18 at 13:34
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
10 Answers
10
active
oldest
votes
10 Answers
10
active
oldest
votes
active
oldest
votes
active
oldest
votes
I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:
First, connect in sudo mysql
sudo mysql -u root
Check your accounts present in your db
SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account
mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)
Recreate your user
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don't forget to flush privileges)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.
I hope this will help someone :)
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
Hold it, Doesn't%
mean you can connect from anywhere...remotely?
– Stevie G
Nov 4 '16 at 8:01
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement togrant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
|
show 14 more comments
I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:
First, connect in sudo mysql
sudo mysql -u root
Check your accounts present in your db
SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account
mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)
Recreate your user
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don't forget to flush privileges)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.
I hope this will help someone :)
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
Hold it, Doesn't%
mean you can connect from anywhere...remotely?
– Stevie G
Nov 4 '16 at 8:01
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement togrant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
|
show 14 more comments
I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:
First, connect in sudo mysql
sudo mysql -u root
Check your accounts present in your db
SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account
mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)
Recreate your user
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don't forget to flush privileges)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.
I hope this will help someone :)
I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:
First, connect in sudo mysql
sudo mysql -u root
Check your accounts present in your db
SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account
mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)
Recreate your user
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don't forget to flush privileges)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.
I hope this will help someone :)
edited Jan 29 '18 at 11:45
Community♦
1
1
answered Jun 8 '16 at 9:04
Loremhipsum
2,847135
2,847135
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
Hold it, Doesn't%
mean you can connect from anywhere...remotely?
– Stevie G
Nov 4 '16 at 8:01
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement togrant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
|
show 14 more comments
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
Hold it, Doesn't%
mean you can connect from anywhere...remotely?
– Stevie G
Nov 4 '16 at 8:01
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement togrant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
9
9
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
This was the only thing that worked for me, does somebody knows exactly what happened with the root user when you install mysql-server on 16.04?
– Ruggi
Aug 18 '16 at 17:45
21
21
Hold it, Doesn't
%
mean you can connect from anywhere...remotely?– Stevie G
Nov 4 '16 at 8:01
Hold it, Doesn't
%
mean you can connect from anywhere...remotely?– Stevie G
Nov 4 '16 at 8:01
6
6
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
You can change row to: CREATE USER 'root'@'localhost' IDENTIFIED BY '';
– vladnev
Dec 5 '16 at 8:36
8
8
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
To the security-concerned: the unsecured root@localhost mysql connection pattern is a bread-and-butter staple of local development but should appear absolutely nowhere else.
– Charney Kaye
Jan 11 '17 at 20:31
4
4
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement to
grant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
The grant statement for the new root user here does not give "with grant" to root so root can't grant without running this afterwards: dba.stackexchange.com/a/62046/115679 Please change the grant statement to
grant all privileges on *.* to 'root'@'localhost' with grant option;
– Loren
Apr 4 '17 at 14:17
|
show 14 more comments
If you install 5.7 and don’t provide a password to the
root
user, it
will use theauth_socket
plugin. That plugin doesn’t care and
doesn’t need a password. It just checks if the user is connecting
using a UNIX socket and then compares the username.
Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"
So in order to to change the plugin
back to mysql_native_password
:
Login with sudo:
sudo mysql -u root
Change the
plugin
and set a password with a single command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Of course you can also use the command above to set an empty password.
Just for the record, (and MariaDB < 10.2
users) there is also another way to only change the plugin
without providing a password (leaving it empty):
update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587
– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
|
show 6 more comments
If you install 5.7 and don’t provide a password to the
root
user, it
will use theauth_socket
plugin. That plugin doesn’t care and
doesn’t need a password. It just checks if the user is connecting
using a UNIX socket and then compares the username.
Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"
So in order to to change the plugin
back to mysql_native_password
:
Login with sudo:
sudo mysql -u root
Change the
plugin
and set a password with a single command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Of course you can also use the command above to set an empty password.
Just for the record, (and MariaDB < 10.2
users) there is also another way to only change the plugin
without providing a password (leaving it empty):
update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587
– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
|
show 6 more comments
If you install 5.7 and don’t provide a password to the
root
user, it
will use theauth_socket
plugin. That plugin doesn’t care and
doesn’t need a password. It just checks if the user is connecting
using a UNIX socket and then compares the username.
Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"
So in order to to change the plugin
back to mysql_native_password
:
Login with sudo:
sudo mysql -u root
Change the
plugin
and set a password with a single command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Of course you can also use the command above to set an empty password.
Just for the record, (and MariaDB < 10.2
users) there is also another way to only change the plugin
without providing a password (leaving it empty):
update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
If you install 5.7 and don’t provide a password to the
root
user, it
will use theauth_socket
plugin. That plugin doesn’t care and
doesn’t need a password. It just checks if the user is connecting
using a UNIX socket and then compares the username.
Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket"
So in order to to change the plugin
back to mysql_native_password
:
Login with sudo:
sudo mysql -u root
Change the
plugin
and set a password with a single command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Of course you can also use the command above to set an empty password.
Just for the record, (and MariaDB < 10.2
users) there is also another way to only change the plugin
without providing a password (leaving it empty):
update mysql.user set plugin = 'mysql_native_password' where User='root';
// to change the password too (credits goes to Pothi Kalimuthu)
// UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;
edited Oct 20 '17 at 0:00
JakeGould
19719
19719
answered Jul 23 '16 at 7:46
Todor
1,23149
1,23149
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587
– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
|
show 6 more comments
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587
– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
2
2
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
Thanks, for some reason this got set automatically suddenly after an apt-get upgrade I think...
– Tominator
Oct 28 '16 at 9:32
2
2
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
Also, I like this answer more - it's a lot less destructive ;-)
– benzkji
Jan 30 '17 at 11:27
3
3
For MariaDB < 10.2, to change plugin along with the password, here's the query:
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587– Pothi Kalimuthu
Jul 11 '17 at 11:12
For MariaDB < 10.2, to change plugin along with the password, here's the query:
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root'; FLUSH PRIVILEGES;
Source: stackoverflow.com/a/41537019/1004587– Pothi Kalimuthu
Jul 11 '17 at 11:12
1
1
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
10x @PothiKalimuthu, just updated the answer.
– Todor
Jul 11 '17 at 14:50
2
2
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
This should be the accepted answer
– Geo C.
Nov 17 '17 at 12:48
|
show 6 more comments
In short, on MariaDB
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
where you replace NEWPASSWORD with the password you want, and everything else verbatim.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user.
You can test this as follows by switching to system root, and then type:
mysql -uroot -p
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql
as the password is irrelevant and the user is defined).
So what's happening? Well, if you log in as root and do the following:
select User,host,plugin from mysql.user;
+----------------+-----------+-----------------------+
| User | host | plugin |
+----------------+-----------+-----------------------+
| root | localhost | auth_socket |
+----------------+-----------+-----------------------+
you'll note auth_socket
(which may read unix_socket
on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
So the solution is to update the Users to not use the auth_socket/unix_socket
and properly set a password.
On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password
you type verbatim.
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)
UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
Otherwise:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';
Then
FLUSH PRIVILEGES;
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant
statement exactly right - easier to simply update the root you already have.
In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.
2
Found this to work perfectly. Makes me wonder though why changing the root password withmysql_secure_installation
didn't have the same effect. Where does it store the new password?
– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the twoUPDATE
s that apply to MariaDB <10.2 and theALTER
to other versions? And is the secondUPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?
– Michael Scheper
Dec 17 '18 at 2:23
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". Themysql_native_password
is that option.
– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
add a comment |
In short, on MariaDB
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
where you replace NEWPASSWORD with the password you want, and everything else verbatim.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user.
You can test this as follows by switching to system root, and then type:
mysql -uroot -p
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql
as the password is irrelevant and the user is defined).
So what's happening? Well, if you log in as root and do the following:
select User,host,plugin from mysql.user;
+----------------+-----------+-----------------------+
| User | host | plugin |
+----------------+-----------+-----------------------+
| root | localhost | auth_socket |
+----------------+-----------+-----------------------+
you'll note auth_socket
(which may read unix_socket
on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
So the solution is to update the Users to not use the auth_socket/unix_socket
and properly set a password.
On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password
you type verbatim.
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)
UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
Otherwise:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';
Then
FLUSH PRIVILEGES;
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant
statement exactly right - easier to simply update the root you already have.
In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.
2
Found this to work perfectly. Makes me wonder though why changing the root password withmysql_secure_installation
didn't have the same effect. Where does it store the new password?
– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the twoUPDATE
s that apply to MariaDB <10.2 and theALTER
to other versions? And is the secondUPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?
– Michael Scheper
Dec 17 '18 at 2:23
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". Themysql_native_password
is that option.
– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
add a comment |
In short, on MariaDB
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
where you replace NEWPASSWORD with the password you want, and everything else verbatim.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user.
You can test this as follows by switching to system root, and then type:
mysql -uroot -p
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql
as the password is irrelevant and the user is defined).
So what's happening? Well, if you log in as root and do the following:
select User,host,plugin from mysql.user;
+----------------+-----------+-----------------------+
| User | host | plugin |
+----------------+-----------+-----------------------+
| root | localhost | auth_socket |
+----------------+-----------+-----------------------+
you'll note auth_socket
(which may read unix_socket
on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
So the solution is to update the Users to not use the auth_socket/unix_socket
and properly set a password.
On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password
you type verbatim.
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)
UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
Otherwise:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';
Then
FLUSH PRIVILEGES;
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant
statement exactly right - easier to simply update the root you already have.
In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.
In short, on MariaDB
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
where you replace NEWPASSWORD with the password you want, and everything else verbatim.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user.
You can test this as follows by switching to system root, and then type:
mysql -uroot -p
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql
as the password is irrelevant and the user is defined).
So what's happening? Well, if you log in as root and do the following:
select User,host,plugin from mysql.user;
+----------------+-----------+-----------------------+
| User | host | plugin |
+----------------+-----------+-----------------------+
| root | localhost | auth_socket |
+----------------+-----------+-----------------------+
you'll note auth_socket
(which may read unix_socket
on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
So the solution is to update the Users to not use the auth_socket/unix_socket
and properly set a password.
On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password
you type verbatim.
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
(It's possible that setting the plugin to empty would work. YMMV. I didn't try this. So this is an alternative.)
UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';
Otherwise:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';
Then
FLUSH PRIVILEGES;
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant
statement exactly right - easier to simply update the root you already have.
In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.
edited Dec 17 '18 at 4:30
answered Oct 14 '17 at 4:18
Gazzer
23326
23326
2
Found this to work perfectly. Makes me wonder though why changing the root password withmysql_secure_installation
didn't have the same effect. Where does it store the new password?
– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the twoUPDATE
s that apply to MariaDB <10.2 and theALTER
to other versions? And is the secondUPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?
– Michael Scheper
Dec 17 '18 at 2:23
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". Themysql_native_password
is that option.
– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
add a comment |
2
Found this to work perfectly. Makes me wonder though why changing the root password withmysql_secure_installation
didn't have the same effect. Where does it store the new password?
– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the twoUPDATE
s that apply to MariaDB <10.2 and theALTER
to other versions? And is the secondUPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?
– Michael Scheper
Dec 17 '18 at 2:23
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". Themysql_native_password
is that option.
– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
2
2
Found this to work perfectly. Makes me wonder though why changing the root password with
mysql_secure_installation
didn't have the same effect. Where does it store the new password?– Mausy5043
Jul 28 '18 at 10:14
Found this to work perfectly. Makes me wonder though why changing the root password with
mysql_secure_installation
didn't have the same effect. Where does it store the new password?– Mausy5043
Jul 28 '18 at 10:14
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the two
UPDATE
s that apply to MariaDB <10.2 and the ALTER
to other versions? And is the second UPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?– Michael Scheper
Dec 17 '18 at 2:23
Can you edit your answer and say more about each of the commands as you go? I'm not clear on which are applicable. Is it the two
UPDATE
s that apply to MariaDB <10.2 and the ALTER
to other versions? And is the second UPDATE
an alternative to the first, or do we need to type both? Finally, is 'mysql_native_password' something to type verbatim, or should we replace it with our intended root password for MySQL?– Michael Scheper
Dec 17 '18 at 2:23
1
1
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". The
mysql_native_password
is that option.– Gazzer
Dec 17 '18 at 4:26
@MichaelScheper It's a while back so I don't want to edit too much, but here's what I remember. (1) Do the first UPDATE only (<10.2 may not be a requirement). (2) Yes, type 'mysql_native_password' verbatim. Essentially, what you are doing is telling mariadb: "Don't use Unix priveleges, use the database priveleges". The
mysql_native_password
is that option.– Gazzer
Dec 17 '18 at 4:26
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
Damn I spelt 'privilege' wrong!
– Gazzer
Dec 17 '18 at 4:32
1
1
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
Looks like you only missed the edit time limit by 1½ minutes, too! I get frustrated by that sometimes, too. Anyhow, thanks!
– Michael Scheper
Dec 21 '18 at 2:01
add a comment |
If you have installed MySQL/MariaDB from base repository, users can not login to MySQL as the MySQL root user from their Unix logins (not applicable if they have sudo access)
Login to MySQL root shell:
$ sudo mysql -u root -p
Execute below queries:
use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit;
Open a new shell, then:
$ mysql -u root -p
Source:
http://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
add a comment |
If you have installed MySQL/MariaDB from base repository, users can not login to MySQL as the MySQL root user from their Unix logins (not applicable if they have sudo access)
Login to MySQL root shell:
$ sudo mysql -u root -p
Execute below queries:
use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit;
Open a new shell, then:
$ mysql -u root -p
Source:
http://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
add a comment |
If you have installed MySQL/MariaDB from base repository, users can not login to MySQL as the MySQL root user from their Unix logins (not applicable if they have sudo access)
Login to MySQL root shell:
$ sudo mysql -u root -p
Execute below queries:
use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit;
Open a new shell, then:
$ mysql -u root -p
Source:
http://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html
If you have installed MySQL/MariaDB from base repository, users can not login to MySQL as the MySQL root user from their Unix logins (not applicable if they have sudo access)
Login to MySQL root shell:
$ sudo mysql -u root -p
Execute below queries:
use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit;
Open a new shell, then:
$ mysql -u root -p
Source:
http://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html
edited Dec 2 '17 at 9:55
answered Dec 2 '17 at 8:31
Gayan Weerakutti
1,8211328
1,8211328
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
add a comment |
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
This worked for me. Thanks
– Junaid Qadir
Dec 31 '17 at 9:42
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
When I did this, I couldn't sudo mysql as root anymore. I had to su first, and then could log into mysql as root. So I changed it back to auth_socket.
– untill
Jan 22 '18 at 14:17
Single command:
mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
Single command:
mysql -u root -p -e "use mysql;update user set plugin='mysql_native_password' where user='root';flush privileges;"
– Chris Stryczynski
Jul 23 '18 at 18:43
add a comment |
Try to create new mysql account, for me it has worked (mysql 5.7.12):
Login as sudo:
sudo mysql -uroot
Create new user and grant him privileges (no password):
CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
Login as new user:
mysql -uadmin
add a comment |
Try to create new mysql account, for me it has worked (mysql 5.7.12):
Login as sudo:
sudo mysql -uroot
Create new user and grant him privileges (no password):
CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
Login as new user:
mysql -uadmin
add a comment |
Try to create new mysql account, for me it has worked (mysql 5.7.12):
Login as sudo:
sudo mysql -uroot
Create new user and grant him privileges (no password):
CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
Login as new user:
mysql -uadmin
Try to create new mysql account, for me it has worked (mysql 5.7.12):
Login as sudo:
sudo mysql -uroot
Create new user and grant him privileges (no password):
CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
Login as new user:
mysql -uadmin
edited Jun 11 '16 at 10:01
Videonauth
23.8k126898
23.8k126898
answered May 28 '16 at 9:37
Alex Yakovlev
211
211
add a comment |
add a comment |
I had to do two things (thanks to @Todor and @Loremhipsum):
update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';
and then:
FLUSH PRIVILEGES;
I would not recommend to drop user root
.
add a comment |
I had to do two things (thanks to @Todor and @Loremhipsum):
update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';
and then:
FLUSH PRIVILEGES;
I would not recommend to drop user root
.
add a comment |
I had to do two things (thanks to @Todor and @Loremhipsum):
update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';
and then:
FLUSH PRIVILEGES;
I would not recommend to drop user root
.
I had to do two things (thanks to @Todor and @Loremhipsum):
update mysql.user set plugin = 'mysql_native_password' where User='root';
grant all privileges on *.* to 'root'@'localhost';
and then:
FLUSH PRIVILEGES;
I would not recommend to drop user root
.
answered Jan 3 '18 at 16:43
Franc Drobnič
1397
1397
add a comment |
add a comment |
Try this code first,
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql
echo "FLUSH PRIVILEGES;" >> your_init_file.sql
and then,
killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql
then press Ctrl+Z
and type: bg
to run the process from the foreground into the background, then verify your access by:
mysql -u root -proot
mysql> show grants;
add a comment |
Try this code first,
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql
echo "FLUSH PRIVILEGES;" >> your_init_file.sql
and then,
killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql
then press Ctrl+Z
and type: bg
to run the process from the foreground into the background, then verify your access by:
mysql -u root -proot
mysql> show grants;
add a comment |
Try this code first,
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql
echo "FLUSH PRIVILEGES;" >> your_init_file.sql
and then,
killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql
then press Ctrl+Z
and type: bg
to run the process from the foreground into the background, then verify your access by:
mysql -u root -proot
mysql> show grants;
Try this code first,
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';" > your_init_file.sql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;" >> your_init_file.sql
echo "FLUSH PRIVILEGES;" >> your_init_file.sql
and then,
killall mysqld
mysqld_safe --init-file=$PWD/your_init_file.sql
then press Ctrl+Z
and type: bg
to run the process from the foreground into the background, then verify your access by:
mysql -u root -proot
mysql> show grants;
answered May 28 '16 at 9:47
ADHITHYA SRINIVASAN
937
937
add a comment |
add a comment |
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
.
The only way to set password is to switch to native authentication like:
$ sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'test';
add a comment |
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
.
The only way to set password is to switch to native authentication like:
$ sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'test';
add a comment |
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
.
The only way to set password is to switch to native authentication like:
$ sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'test';
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
.
The only way to set password is to switch to native authentication like:
$ sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'test';
answered Jul 26 '17 at 15:23
Vadiaz
111
111
add a comment |
add a comment |
I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket
/unix_socket
setting.
So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:
UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;
The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin
value, flush privileges and get on with life.
add a comment |
I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket
/unix_socket
setting.
So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:
UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;
The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin
value, flush privileges and get on with life.
add a comment |
I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket
/unix_socket
setting.
So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:
UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;
The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin
value, flush privileges and get on with life.
I’ve been adapting some provisioning scripts I have created to use MariaDB and ran into this exact issue. Piecing together lots of info here an Gazzer’s answer really zeros in in the issue; it all boils down to the auth_socket
/unix_socket
setting.
So when using MariaDB 5.5 (under Ubuntu 14.04) and MariaDB 10 under (Ubuntu 16.04), logging into MySQL and running this command cleared things up right away:
UPDATE mysql.user SET plugin='' WHERE User='root';
FLUSH PRIVILEGES;
The other answers—including the highest voted answer as of this post by Loremhipsum—really encourage bad practices by recommending dropping a user and then recreating them. To me, that is a pretty radical solution. The best/simplest solution os to nullify the plugin
value, flush privileges and get on with life.
answered Oct 20 '17 at 0:54
JakeGould
19719
19719
add a comment |
add a comment |
I had the same issue and running the following fixed it:
mysql_upgrade --force
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the userroot
being set withplugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if themysql_upgrade
process itself accounts for theplugin
setting which I doubt.
– JakeGould
Oct 20 '17 at 1:37
add a comment |
I had the same issue and running the following fixed it:
mysql_upgrade --force
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the userroot
being set withplugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if themysql_upgrade
process itself accounts for theplugin
setting which I doubt.
– JakeGould
Oct 20 '17 at 1:37
add a comment |
I had the same issue and running the following fixed it:
mysql_upgrade --force
I had the same issue and running the following fixed it:
mysql_upgrade --force
answered May 3 '16 at 6:48
klob
1
1
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the userroot
being set withplugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if themysql_upgrade
process itself accounts for theplugin
setting which I doubt.
– JakeGould
Oct 20 '17 at 1:37
add a comment |
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the userroot
being set withplugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if themysql_upgrade
process itself accounts for theplugin
setting which I doubt.
– JakeGould
Oct 20 '17 at 1:37
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
Sadly that didn't work. I am reinstalling ubuntu.
– codescope
May 3 '16 at 9:58
1
1
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
I just did fresh install of ubuntu 16.04 and installed mariad-server. After installing I ran mysql_secure_installation and set password. After going through rest of the steps in mysql_secure_installation, I ran it again and it looks like it is not saving the changes. I still can't login from my normal user account. Could it be a bug?
– codescope
May 3 '16 at 11:45
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the user
root
being set with plugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if the mysql_upgrade
process itself accounts for the plugin
setting which I doubt.– JakeGould
Oct 20 '17 at 1:37
This will never help. The issue has nothing do with the actual install of the MariaDB binary but rather the user
root
being set with plugin
settings that are non-standard. The only edge case where an upgrade of the DB might help in a case like this is if the mysql_upgrade
process itself accounts for the plugin
setting which I doubt.– JakeGould
Oct 20 '17 at 1:37
add a comment |
protected by Community♦ Mar 11 '18 at 13:34
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?