Create and Manage MySQL Databases and Users

Summary:
Basic MySQL commands for creating databases and managing permissions.

MySQL is a widely used open-source relational database management system. Whether you are a web developer, a systems administrator, or a learner, mastering the creation and management of databases and users is fundamental to database security and organization. This guide walks you through essential MySQL commands to create databases, manage users, grant permissions, and ensure your environment is well-structured and secure.

Prerequisites

MySQL installed: Ensure MySQL is installed. You can check using mysql --version.
MySQL access: Have credentials for a user account with sufficient privileges (typically root).

Note: For all examples, replace db_name, user_name, and password with your actual values.

Connect to MySQL

Start by accessing the MySQL shell:

mysql -u root -p

Enter your MySQL root password when prompted.

1. Creating a New Database

To create a new database:

CREATE DATABASE db_name;

Example:

CREATE DATABASE myapp_db;

You can list all existing databases with:

SHOW DATABASES;

2. Creating a New User

A new MySQL user can be created with the following command:

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';

'host': Usually set as 'localhost' for local connections or '%' for any host.

Example:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'MyP@ssw0rd!';

To see existing users:

SELECT User, Host FROM mysql.user;

3. Granting Permissions

Permissions control what each MySQL user can do. The most common practice is granting a user permission to access specific databases.

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'host';

Example:

GRANT ALL PRIVILEGES ON myapp_db.* TO 'myuser'@'localhost';

To grant limited permissions (e.g., only SELECT, INSERT, and UPDATE):

GRANT SELECT, INSERT, UPDATE ON myapp_db.* TO 'myuser'@'localhost';

Always reload privilege tables after changes:
```
FLUSH PRIVILEGES;
```

4. Changing User Passwords

Updating a user password can be done in the following ways (MySQL 5.7.6+):

ALTER USER 'user_name'@'host' IDENTIFIED BY 'new_password';

Example:

ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'N3wP@ssw0rd!';

5. Revoking Permissions

To revoke a user's privileges:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user_name'@'host';

Or revoke specific privileges:

REVOKE INSERT, UPDATE ON db_name.* FROM 'user_name'@'host';

Remember to flush privileges again:

FLUSH PRIVILEGES;

6. Deleting a User or Database

Delete a user:
```
DROP USER 'user_name'@'host';
```
Delete a database:
```
DROP DATABASE db_name;
```

7. Viewing Current Privileges

To see the privileges granted to a user:

SHOW GRANTS FOR 'user_name'@'host';

MySQL Permission Levels

ALL PRIVILEGES: Full control
SELECT, INSERT, UPDATE, DELETE: Data manipulation
CREATE, DROP: Create or delete databases/tables
GRANT OPTION: User can grant privileges to others

Use minimal privileges that your application or user needs ("principle of least privilege").

Best Practices

Never use the root account for apps. Create dedicated users with limited permissions.
Use strong passwords for all database users.
Back up databases and user privilege information regularly.
Monitor user activity and review privileges periodically.

Conclusion

Managing MySQL databases and users efficiently keeps your data organized and secure. By creating unique databases for different projects and assigning users only the permissions they require, you minimize risks and improve maintainability. Practice with the commands above, and always follow up-to-date security recommendations for your deployment environment.

Further Reading: