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
New contributor
add a 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
New contributor
1
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
4 hours ago
add a 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
New contributor
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
New contributor
New contributor
edited 9 mins ago
Zanna
50k13131239
50k13131239
New contributor
asked 4 hours ago
Sagnik Das
113
113
New contributor
New contributor
1
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
4 hours ago
add a comment |
1
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
1
Open a terminal, runmysql -u root -p
enter the password. Can you login there?
– Kulfy
4 hours ago
1
1
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
1
1
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
4 hours ago
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
4 hours ago
add a 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
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
});
}
});
Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.
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
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
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 1 hour ago
answered 3 hours ago
Sergiy Kolodyazhnyy
69.5k9144306
69.5k9144306
add a comment |
add a comment |
Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.
Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.
Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.
Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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
1
Possible duplicate of sudo mysql_secure_installation : command not found
– Yaron
4 hours ago
Also Take a look here:how to reset mysql root password
– Yaron
4 hours ago
1
Open a terminal, run
mysql -u root -p
enter the password. Can you login there?– Kulfy
4 hours ago