How to Create MySQL Credentials for a Database: A Complete Guide

When working with MySQL databases, it's essential to set up proper credentials for accessing and managing databases. This guide walks you through the process of creating a new MySQL database and assigning user credentials that work not only for localhost but also for connections from any host (%).

Step-by-Step Guide to Create a MySQL Database and User Credentials

1. Log in to MySQL

First, log in to your MySQL server using the root user (or another user with sufficient privileges). You can do this from the command line:

mysql -u root -p

You'll be prompted to enter the root password. After entering the correct password, you will have access to the MySQL command prompt.

2. Create a New Database

Once logged in, you can create a new database. For this example, we’ll assume you want to create a database called example_db. To create the database, run:

CREATE DATABASE example_db;

This command creates a new empty database named example_db.

3. Create a New User

Next, you'll need to create a new MySQL user. In this case, we’ll create a user called example_user and assign it a password. Replace your_password with a secure password of your choice:

CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'your_password';
CREATE USER 'example_user'@'%' IDENTIFIED BY 'your_password';

The first command creates the user for localhost, allowing the user to connect from the same machine where the MySQL server is running. The second command creates the same user for all hosts (%), enabling the user to connect remotely from any IP address.

4. Grant Privileges to the User

After creating the user, you need to assign privileges for the user to access the database. The following command grants example_user full privileges on the example_db database:

GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';
GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'%';

The first GRANT command allows example_user to manage the database when connecting from localhost. The second GRANT command extends the same privileges for connections from any host (%).

5. Flush Privileges

To apply the changes and ensure that MySQL recognizes the new permissions, you need to flush the privileges:

FLUSH PRIVILEGES;

This command forces MySQL to reload the user privileges from the grant tables, making the new settings effective immediately.

6. Verify the New User

Now that the user and database are set up, you can test the credentials. To test the local connection, log in using the newly created user from localhost:

mysql -u example_user -p

To test remote connections, try logging in from a different machine using the mysql command-line tool or a MySQL client:

mysql -u example_user -p -h your_mysql_server_ip

7. (Optional) Limit User Privileges

If you do not want to grant the user full privileges on the database, you can limit the access by specifying which privileges the user should have. For example, to allow only SELECT, INSERT, and UPDATE queries, you would use:

GRANT SELECT, INSERT, UPDATE ON example_db.* TO 'example_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON example_db.* TO 'example_user'@'%';

After applying more specific privileges, remember to flush them:

FLUSH PRIVILEGES;

Finally

Creating a MySQL user with access to a specific database is a simple but crucial task. In this guide, we’ve shown how to create a database, set up a user, and grant full privileges for both local and remote connections. Here's a quick recap of the steps:

1. Log in to MySQL as root.
2. Create a new database.
3. Create a new user with credentials.
4. Grant privileges to the user for both localhost and all hosts (%).
5. Flush privileges to apply changes.
6. Verify the connection.

By following these steps, you can ensure secure and flexible access to your MySQL databases.

Hope it helps.

Support Us