Learning how to manage MySQL databases and users from the command line makes routine server work faster and safer. This guide covers creating databases, adding users, granting only the access they need, changing passwords, checking privileges, and safely removing accounts.
The examples follow current MySQL 8 syntax. Replace the sample names and passwords before using any command.
Before you begin
You need MySQL installed and an administrative account. Connect locally with one of these methods:
sudo mysql
# Or use password authentication
mysql -u root -pDo not put the password directly in the command. The -p option safely displays a password prompt.
Check the server version and current account:
SELECT VERSION();
SELECT CURRENT_USER();MySQL statements end with a semicolon. Type exit when you are finished.
List MySQL databases
SHOW DATABASES;An account only sees databases it has permission to view. System databases such as mysql, information_schema, performance_schema, and sys should not be used for application tables.
Create a MySQL database
CREATE DATABASE app_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;utf8mb4 supports the full Unicode range. The best collation can depend on your MySQL version and application, so confirm compatibility before changing an existing system.
Check the exact database definition:
SHOW CREATE DATABASE app_database;Select it for the current session:
USE app_database;Create a MySQL user
A MySQL account includes both a username and a host. This creates an application account that can connect only from the database server:
CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'replace-with-a-long-random-password';MySQL uses its configured default authentication plugin when none is specified. Avoid forcing an older plugin just to fix an outdated application. Updating the database driver is usually the safer long-term answer.
You can ask MySQL to generate the initial password:
CREATE USER 'app_user'@'localhost' IDENTIFIED BY RANDOM PASSWORD;MySQL returns the generated password once. Store it in an approved secret manager rather than a shared note or source-code repository.
Grant database privileges using least privilege
Give the application access to its own database:
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_database.*
TO 'app_user'@'localhost';If the application runs its own schema migrations, it may also need privileges such as CREATE, ALTER, INDEX, or DROP. Grant them only when the application truly needs them.
A broad grant is sometimes used for a dedicated database owner:
GRANT ALL PRIVILEGES
ON app_database.*
TO 'app_user'@'localhost';This applies only to app_database, not every database on the server. Avoid ON *.* for normal websites because that can provide global administrative power.
You do not need to run FLUSH PRIVILEGES after CREATE USER, GRANT, REVOKE, or ALTER USER. Those account-management statements take effect immediately.
Check a user’s privileges
SHOW GRANTS FOR 'app_user'@'localhost';Also inspect how the account was created:
SHOW CREATE USER 'app_user'@'localhost';Test the new account in a separate terminal:
mysql -u app_user -p app_databaseTesting before closing the administrator session helps you catch host, password, and grant mistakes.
Change a MySQL user password
ALTER USER 'app_user'@'localhost'
IDENTIFIED BY 'new-long-random-password';Update the application secret at the same time and test a new connection. A cleartext password entered interactively may be saved in client history in some circumstances, so use your organisation’s secure password workflow on sensitive systems.
Create a remote MySQL user safely
Use the specific application server address instead of the % wildcard:
CREATE USER 'app_user'@'10.20.30.40'
IDENTIFIED BY 'replace-with-a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_database.*
TO 'app_user'@'10.20.30.40';The host part limits where the account may connect from. It is not a replacement for a firewall. Restrict TCP port 3306 to trusted private addresses and use encrypted MySQL connections. Never expose a MySQL root account to every host.
Our separate guide explains the risks and controls for remote MySQL connections. You should also follow the Linux server hardening checklist.
List MySQL users
An administrator can list account names and hosts with:
SELECT User, Host
FROM mysql.user
ORDER BY User, Host;Do not edit rows in the mysql.user system table manually. Use account-management statements such as CREATE USER, ALTER USER, GRANT, REVOKE, and DROP USER.
Revoke privileges
Remove selected permissions:
REVOKE DELETE, UPDATE
ON app_database.*
FROM 'app_user'@'localhost';Remove every privilege granted on that database:
REVOKE ALL PRIVILEGES
ON app_database.*
FROM 'app_user'@'localhost';Run SHOW GRANTS afterwards to confirm the result.
Lock or unlock a MySQL account
Locking is useful when access should stop temporarily without immediately deleting the account:
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;Restore access with:
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;Delete a MySQL user
First check the grants and confirm that no application still uses the account:
SHOW GRANTS FOR 'old_user'@'localhost';
DROP USER 'old_user'@'localhost';The username and host must match the real account. For example, 'app_user'@'localhost' and 'app_user'@'10.20.30.40' are separate MySQL accounts.
Delete a MySQL database
DROP DATABASE app_database;Warning: this permanently deletes every table in the database. Create and test a backup before running it. Database-specific privileges are not automatically removed when the database is dropped, so review and remove old accounts or grants separately.
Follow our guide to back up and restore MySQL with mysqldump before making destructive changes.
Common MySQL user-management errors
Access denied for user
Check the full 'user'@'host' identity, password, and grants. A connection from another machine does not match an account restricted to localhost.
You are not allowed to create a user
The current account lacks CREATE USER or another required administrative privilege. Sign in through an approved administrator account instead of granting global power to the application user.
The application can connect but cannot create a table
Its grant may include data privileges but not schema privileges. Check SHOW GRANTS and add only the exact migration permissions required.
Remote connection is refused
Account grants are only one part of remote access. Check the MySQL bind address, firewall, cloud security rules, routing, and TLS configuration. Do not solve the problem by opening port 3306 to the entire internet.
Quick command summary
SHOW DATABASES;
CREATE DATABASE app_database CHARACTER SET utf8mb4;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new-password';
REVOKE DELETE ON app_database.* FROM 'app_user'@'localhost';
DROP USER 'app_user'@'localhost';Frequently asked questions
Should I use the MySQL root user for my website?
No. Create a separate account limited to the website’s database and required operations. This reduces damage if the website credentials are stolen.
Do GRANT changes require FLUSH PRIVILEGES?
No. Changes made with MySQL account-management statements take effect immediately. FLUSH PRIVILEGES is associated with direct grant-table changes, which normal administration should avoid.
Why does a MySQL username include a host?
MySQL uses the username and client host together to identify an account. This lets administrators permit the same name from one host while rejecting it from another.
Is GRANT ALL PRIVILEGES safe?
It can be acceptable for a dedicated owner when limited to one database. Global *.* access is too powerful for a normal application account.
Official MySQL references
- MySQL 8.4 CREATE DATABASE statement
- MySQL 8.4 CREATE USER statement
- MySQL 8.4 SHOW GRANTS statement
- MySQL 8.4 DROP DATABASE statement
You can now manage MySQL databases and users from the command line using separate accounts, narrow host rules, and least-privilege grants. Always verify access from a new session and keep a tested backup before deleting data.
Comments