How to Grant MySQL Root Privileges for 127.0.0.1

How to Grant MySQL Root Privileges for 127.0.0.1

When setting up a MySQL database, you may encounter an issue where the root user is only allowed to connect via Unix sockets (localhost), but not via TCP/IP (127.0.0.1). This tutorial walks you through resolving the Access denied for user 'root'@'127.0.0.1' issue by granting the proper privileges to the root user for TCP connections.

Prerequisites:

• You must have root access to your MySQL server.
• You need to be able to connect to MySQL via the command line using Unix socket (by default localhost).

Step 1: Log in to MySQL Using `localhost`

First, log in to MySQL using the root user without specifying the host (localhost works via Unix socket):

mysql -u root -p

Once prompted, enter the MySQL root password.

Step 2: Grant Privileges for `127.0.0.1`

By default, MySQL treats connections via localhost (Unix socket) and 127.0.0.1 (TCP/IP) differently. To resolve the issue, you need to grant privileges to root for connections via 127.0.0.1.

Inside the MySQL shell, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;

This grants all privileges to the root user for connections coming from 127.0.0.1.

Step 3: Set the Root Password for `127.0.0.1`

Once you’ve granted privileges, you need to set the password for the root user when connecting from 127.0.0.1.

Run the following command to set or update the root password for TCP connections:

ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'your_new_password';

Replace 'your_new_password' with the desired password.

Step 4: Flush Privileges

To ensure all changes take effect, flush the privileges:

FLUSH PRIVILEGES;

Step 5: Verify the Connection

Now exit the MySQL shell:

exit;

Next, verify the new connection by attempting to log in using 127.0.0.1 as the host:

mysql -h 127.0.0.1 -u root -p

When prompted, enter the new password you set earlier.

Step 6: (Optional) Update Laravel or Application Configuration

If you're working with a Laravel or any other application that uses MySQL, update your .env file or configuration file to use 127.0.0.1 as the DB_HOST instead of localhost:

DB_HOST=127.0.0.1
DB_DATABASE=your_database_name
DB_USERNAME=root
DB_PASSWORD=your_new_password

Make sure the port is correct (3306 for default MySQL installations).

Why This Happens:

• MySQL treats connections from localhost (via Unix socket) and 127.0.0.1 (via TCP/IP) as distinct connections. By default, the root user may only have privileges for localhost, causing an "Access Denied" error when trying to connect from 127.0.0.1.

Conclusion:

This tutorial explains how to grant privileges to the MySQL root user for connections over TCP/IP (127.0.0.1). By following these steps, you can resolve access denied errors and ensure smooth connections to your MySQL database for both local development and remote applications.