How do I log in as root in MySQL?












2














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









New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 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
















2














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









New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 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














2












2








2







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









New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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









New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 9 mins ago









Zanna

50k13131239




50k13131239






New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 4 hours ago









Sagnik Das

113




113




New contributor




Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Sagnik Das is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 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














  • 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








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










1 Answer
1






active

oldest

votes


















3














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























    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.










    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









    3














    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




























      3














      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


























        3












        3








        3






        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 1 hour ago

























        answered 3 hours ago









        Sergiy Kolodyazhnyy

        69.5k9144306




        69.5k9144306






















            Sagnik Das is a new contributor. Be nice, and check out our Code of Conduct.










            draft saved

            draft discarded


















            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.




            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

            Eastern Orthodox Church

            Understanding the information contained in the Deep Space Network XML data?

            Zagreb