How do I log in as root in MySQL?












4















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:



login prompt username phpmyadmin



This doesn't work:



login prompt username root, password has been entered, no access



Neither does this



login prompt, username root, no password, no access










share|improve this question




















  • 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


















4















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:



login prompt username phpmyadmin



This doesn't work:



login prompt username root, password has been entered, no access



Neither does this



login prompt, username root, no password, no access










share|improve this question




















  • 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
















4












4








4








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:



login prompt username phpmyadmin



This doesn't work:



login prompt username root, password has been entered, no access



Neither does this



login prompt, username root, no password, no access










share|improve this question
















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:



login prompt username phpmyadmin



This doesn't work:



login prompt username root, password has been entered, no access



Neither does this



login prompt, username root, no password, no access







login mysql phpmyadmin






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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, 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
















  • 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










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












1 Answer
1






active

oldest

votes


















6














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:




  1. open terminal and run sudo mysql -u root. You should see a greeting message and mysql> prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.



  2. 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



  3. 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






share|improve this answer


























  • 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













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
});


}
});














draft saved

draft discarded


















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









6














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:




  1. open terminal and run sudo mysql -u root. You should see a greeting message and mysql> prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.



  2. 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



  3. 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






share|improve this answer


























  • 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


















6














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:




  1. open terminal and run sudo mysql -u root. You should see a greeting message and mysql> prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.



  2. 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



  3. 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






share|improve this answer


























  • 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
















6












6








6







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:




  1. open terminal and run sudo mysql -u root. You should see a greeting message and mysql> prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.



  2. 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



  3. 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






share|improve this answer















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:




  1. open terminal and run sudo mysql -u root. You should see a greeting message and mysql> prompt. This is the MySQL shell, which is different from your command-line shell, so only SQL statements are accepted here.



  2. 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



  3. 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







share|improve this answer














share|improve this answer



share|improve this answer








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 is sudo mysql -u root. You used sudo 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











  • @SagnikDas Command is sudo mysql -u root. You used sudo 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Probability when a professor distributes a quiz and homework assignment to a class of n students.

Aardman Animations

Are they similar matrix