How do I log in as root in MySQL?

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:

This doesn't work:

Neither does this

Answer 1

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
   
   $ sudo systemctl restart mysql
   $ 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 sign 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