How do I log in as root in MySQL?
I've installed MySQL and during installation I was asked for a password which I entered, but now my teacher says that we should be able to log in as root without any password.
This works but I don't get any privileges:
This doesn't work:
Neither does this
login mysql phpmyadmin
|
show 1 more comment
I've installed MySQL and during installation I was asked for a password which I entered, but now my teacher says that we should be able to log in as root without any password.
This works but I don't get any privileges:
This doesn't work:
Neither does this
login mysql phpmyadmin
3
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
Jan 1 at 8:57
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19
|
show 1 more comment
I've installed MySQL and during installation I was asked for a password which I entered, but now my teacher says that we should be able to log in as root without any password.
This works but I don't get any privileges:
This doesn't work:
Neither does this
login mysql phpmyadmin
I've installed MySQL and during installation I was asked for a password which I entered, but now my teacher says that we should be able to log in as root without any password.
This works but I don't get any privileges:
This doesn't work:
Neither does this
login mysql phpmyadmin
login mysql phpmyadmin
edited Jan 1 at 12:52
Zanna
50.4k13133241
50.4k13133241
asked Jan 1 at 8:03
Sagnik DasSagnik Das
213
213
3
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
Jan 1 at 8:57
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19
|
show 1 more comment
3
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
Jan 1 at 8:57
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19
3
3
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
1
1
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
Jan 1 at 8:57
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
Jan 1 at 8:57
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19
|
show 1 more comment
1 Answer
1
active
oldest
votes
Cause of the issue
The default configuration for logging in as root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying the user
table:
mysql> select user,authentication_string,plugin from user where user='root';
+------+-----------------------+-------------+
| user | authentication_string | plugin |
+------+-----------------------+-------------+
| root | | auth_socket |
+------+-----------------------+-------------+
1 row in set (0.00 sec)
From the documentation:
The socket plugin checks whether the socket user name (the operating
system user name) matches the MySQL user name specified by the client
program to the server. If the names do not match, the plugin checks
whether the socket user name matches the name specified in the
authentication_string column of the mysql.user system table row. If a
match is found, the plugin permits the connection.
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.
Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user jdoe
and have no such user on the host system. Thus, root
is MySQL's root user, not system user.
Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.
Enter the following sequence of SQL queries:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password',authentication_string=PASSWORD('newpassword') WHERE user = 'root'
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Exit and attempt to sign in:
mysql> exit
Bye
$ PS1='$ '
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.21-1 (Debian)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
If everything goes well, you should be able to sing in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command issudo mysql -u root
. You usedsudo mysql
which is incomplete.
– Kulfy
Jan 1 at 14:24
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "89"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1105970%2fhow-do-i-log-in-as-root-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Cause of the issue
The default configuration for logging in as root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying the user
table:
mysql> select user,authentication_string,plugin from user where user='root';
+------+-----------------------+-------------+
| user | authentication_string | plugin |
+------+-----------------------+-------------+
| root | | auth_socket |
+------+-----------------------+-------------+
1 row in set (0.00 sec)
From the documentation:
The socket plugin checks whether the socket user name (the operating
system user name) matches the MySQL user name specified by the client
program to the server. If the names do not match, the plugin checks
whether the socket user name matches the name specified in the
authentication_string column of the mysql.user system table row. If a
match is found, the plugin permits the connection.
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.
Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user jdoe
and have no such user on the host system. Thus, root
is MySQL's root user, not system user.
Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.
Enter the following sequence of SQL queries:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password',authentication_string=PASSWORD('newpassword') WHERE user = 'root'
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Exit and attempt to sign in:
mysql> exit
Bye
$ PS1='$ '
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.21-1 (Debian)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
If everything goes well, you should be able to sing in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command issudo mysql -u root
. You usedsudo mysql
which is incomplete.
– Kulfy
Jan 1 at 14:24
add a comment |
Cause of the issue
The default configuration for logging in as root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying the user
table:
mysql> select user,authentication_string,plugin from user where user='root';
+------+-----------------------+-------------+
| user | authentication_string | plugin |
+------+-----------------------+-------------+
| root | | auth_socket |
+------+-----------------------+-------------+
1 row in set (0.00 sec)
From the documentation:
The socket plugin checks whether the socket user name (the operating
system user name) matches the MySQL user name specified by the client
program to the server. If the names do not match, the plugin checks
whether the socket user name matches the name specified in the
authentication_string column of the mysql.user system table row. If a
match is found, the plugin permits the connection.
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.
Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user jdoe
and have no such user on the host system. Thus, root
is MySQL's root user, not system user.
Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.
Enter the following sequence of SQL queries:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password',authentication_string=PASSWORD('newpassword') WHERE user = 'root'
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Exit and attempt to sign in:
mysql> exit
Bye
$ PS1='$ '
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.21-1 (Debian)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
If everything goes well, you should be able to sing in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command issudo mysql -u root
. You usedsudo mysql
which is incomplete.
– Kulfy
Jan 1 at 14:24
add a comment |
Cause of the issue
The default configuration for logging in as root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying the user
table:
mysql> select user,authentication_string,plugin from user where user='root';
+------+-----------------------+-------------+
| user | authentication_string | plugin |
+------+-----------------------+-------------+
| root | | auth_socket |
+------+-----------------------+-------------+
1 row in set (0.00 sec)
From the documentation:
The socket plugin checks whether the socket user name (the operating
system user name) matches the MySQL user name specified by the client
program to the server. If the names do not match, the plugin checks
whether the socket user name matches the name specified in the
authentication_string column of the mysql.user system table row. If a
match is found, the plugin permits the connection.
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.
Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user jdoe
and have no such user on the host system. Thus, root
is MySQL's root user, not system user.
Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.
Enter the following sequence of SQL queries:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password',authentication_string=PASSWORD('newpassword') WHERE user = 'root'
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Exit and attempt to sign in:
mysql> exit
Bye
$ PS1='$ '
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.21-1 (Debian)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
If everything goes well, you should be able to sing in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange
Cause of the issue
The default configuration for logging in as root
user in MySQL 5.7 requires use of an authentication socket. This can be verified via querying the user
table:
mysql> select user,authentication_string,plugin from user where user='root';
+------+-----------------------+-------------+
| user | authentication_string | plugin |
+------+-----------------------+-------------+
| root | | auth_socket |
+------+-----------------------+-------------+
1 row in set (0.00 sec)
From the documentation:
The socket plugin checks whether the socket user name (the operating
system user name) matches the MySQL user name specified by the client
program to the server. If the names do not match, the plugin checks
whether the socket user name matches the name specified in the
authentication_string column of the mysql.user system table row. If a
match is found, the plugin permits the connection.
In other words, mysql by default doesn't have root password set - you need to either run phpMyAdmin as root or via sudo
(both of which are bad idea for security reasons), or you change the authentication method and reset the root password as shown in Digital Ocean tutorial.
Note that aside from same name, MySQL users and system users are not the same. You can have a MySQL user jdoe
and have no such user on the host system. Thus, root
is MySQL's root user, not system user.
Steps to change plugin and password:
open terminal and run
sudo mysql -u root
. You should see a greeting message andmysql>
prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.
Enter the following sequence of SQL queries:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password',authentication_string=PASSWORD('newpassword') WHERE user = 'root'
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Exit and attempt to sign in:
mysql> exit
Bye
$ PS1='$ '
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.21-1 (Debian)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
If everything goes well, you should be able to sing in from phpMyAdmin via the new password. If something goes wrong, attempt to restart the server without permission checking (that's step num. 3 in the Digital Ocean tutorial). For other issues, feel free to ask another question here or on Database Administrators - Stack Exchange
edited Jan 1 at 11:37
answered Jan 1 at 9:39
Sergiy KolodyazhnyySergiy Kolodyazhnyy
70.7k9147310
70.7k9147310
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command issudo mysql -u root
. You usedsudo mysql
which is incomplete.
– Kulfy
Jan 1 at 14:24
add a comment |
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command issudo mysql -u root
. You usedsudo mysql
which is incomplete.
– Kulfy
Jan 1 at 14:24
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
The problem is that i can enter the CLI for mysql if i type >sudo mysql i get >ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
– Sagnik Das
Jan 1 at 14:22
@SagnikDas Command is
sudo mysql -u root
. You used sudo mysql
which is incomplete.– Kulfy
Jan 1 at 14:24
@SagnikDas Command is
sudo mysql -u root
. You used sudo mysql
which is incomplete.– Kulfy
Jan 1 at 14:24
add a comment |
Thanks for contributing an answer to Ask Ubuntu!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2faskubuntu.com%2fquestions%2f1105970%2fhow-do-i-log-in-as-root-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
Jan 1 at 8:31
Also Take a look here:how to reset mysql root password
– Yaron
Jan 1 at 8:33
1
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
Jan 1 at 8:57
@kulfy I am getting "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"
– Sagnik Das
Jan 1 at 14:16
That means you've entered wrong password. Which Ubuntu version are you using? How did you installed mysql? I tried installing MySQL in Ubuntu that I use in virtuabox and haven't got any prompt to set a password.
– Kulfy
Jan 1 at 14:19