How to Create a MySQL Database and User from Command Line

Every MySQL-driven application — WordPress, Laravel, a custom API — needs two things before it can talk to the database: a database to talk to, and a dedicated user account with the right permissions. Doing this from the command line takes about five minutes, gives you full control, and avoids the quirks of GUI tools that hide the actual SQL from you. In this tutorial you will create a database, create a user with a strong password, grant that user exactly the privileges it needs, and verify everything works — on Ubuntu 24.04 or 22.04 with MySQL 8.

Prerequisites

• A server (or VM) running Ubuntu 24.04 or 22.04 with sudo access.
• MySQL Server 8.x installed and running. Check with mysql --version and sudo systemctl status mysql. If it is not installed, run sudo apt update && sudo apt install mysql-server.
• Root or sudo access to the MySQL server itself. On Ubuntu's default install, the MySQL root account authenticates via the auth_socket plugin, so sudo mysql logs you in as root without a password prompt.

Step 1 — Log in to MySQL as root

Open a terminal and connect to the MySQL server as the root user:

sudo mysql

sudo mysql uses your system root privileges to authenticate through the auth_socket plugin that Ubuntu ships by default — no MySQL password needed. You will land at the mysql> prompt. Every SQL statement you type from here on must end with a semicolon.

Step 2 — Create the database

Create a new database with a clear, descriptive name. The utf8mb4 character set with utf8mb4_unicode_ci collation is the modern default — it supports the full Unicode range, including emoji:

CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE DATABASE makes the empty database. Specifying the character set and collation at creation time prevents encoding bugs later — especially when your app stores names, emojis, or non-Latin text. Confirm it exists:

SHOW DATABASES;

You should see myapp_db in the list.

Step 3 — Create the database user

Never point an application at the root account. Create a dedicated user tied to that application, identified by a strong password:

CREATE USER 'myapp_user'@'localhost'
  IDENTIFIED BY 'Str0ng!Passw0rd-Here';

CREATE USER defines the account. 'myapp_user'@'localhost' means the user may only connect from the same machine — the correct, secure choice when the app and the database live on one server. IDENTIFIED BY sets the password. Use a long random password; generate one with openssl rand -base64 20 on the shell if you need one.

If your application runs on a different server than MySQL, create the user with 'myapp_user'@'%' instead — or, better, 'myapp_user'@'203.0.113.10' with your app server's IP. '%' allows connections from any host, which you should only open when necessary.

Step 4 — Grant privileges on the database

Give the new user all privileges, but only on its own database. This is the least-privilege sweet spot for a typical application:

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

GRANT ALL PRIVILEGES ON myapp_db.* hands the user full control (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, …) inside myapp_db — but nowhere else. It cannot read other databases, create new databases, or manage other users. Apply the change immediately:

FLUSH PRIVILEGES;

FLUSH PRIVILEGES reloads the grant tables so the change takes effect without restarting MySQL. (Modern MySQL applies GRANT immediately, but flushing costs nothing and is good hygiene.)

Step 5 — Exit and test the new credentials

Leave the MySQL prompt and immediately log back in as the new user:

EXIT;
mysql -u myapp_user -p

EXIT closes the root session. mysql -u myapp_user -p prompts for the password you set in Step 3. Once logged in, check what the user can see:

SHOW DATABASES;
SHOW GRANTS;

SHOW DATABASES should list only myapp_db (plus information_schema). SHOW GRANTS prints the user's privileges — confirm they are scoped to myapp_db.*.

Step 6 — Create a table and insert test data

Prove the user can actually work by creating a table and a row of data:

USE myapp_db;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email)
VALUES ('Ravi', 'ravi@example.com');

SELECT * FROM users;

USE myapp_db selects the working database. The CREATE TABLE statement builds a simple users table with an auto-increment primary key. INSERT adds one row and SELECT reads it back — if you see Ravi's row, the user has full working privileges on this database.

Bonus — create a read-only user for reporting

Applications need full write access, but reporting tools, dashboards, and backup scripts usually only need to read. A read-only user limits the damage a compromised reporting tool can do:

CREATE USER 'reports'@'localhost' IDENTIFIED BY 'R3ad0nly-Strong-Pass';
GRANT SELECT, SHOW VIEW ON myapp_db.* TO 'reports'@'localhost';
FLUSH PRIVILEGES;

GRANT SELECT, SHOW VIEW lets this user run SELECT queries and inspect views, but any INSERT, UPDATE, or DELETE fails with an access-denied error. Test it:

mysql -u reports -p
USE myapp_db;
SELECT * FROM users;        -- works
DELETE FROM users;          -- ERROR 1142: DELETE command denied

Keep a list of which user does what — myapp_user for the application, reports for read-only tools — and rotate the passwords whenever a team member with access leaves.

Verify it worked

Run this checklist to confirm the setup is complete and secure:

• Credentials: mysql -u myapp_user -p logs in successfully with the new password.
• Scope: SHOW DATABASES shows only myapp_db and information_schema — the user cannot peek into other databases.
• Privileges: SHOW GRANTS shows GRANT ALL PRIVILEGES ON \`myapp_db\`.* and nothing broader.
• Negative test: from the app user, CREATE DATABASE sneaky; should fail with ERROR 1044 (42000): Access denied — that is the least-privilege boundary doing its job.
• Connection for the app: the app's database DSN/config should now point at host localhost, database myapp_db, user myapp_user, and the password you set.

Common issues & fixes

1. ERROR 1045 (28000): Access denied for user 'myapp_user'@'localhost'

Almost always a wrong password, or the user was created with a different host part (e.g. '%' vs 'localhost'). Log in as root and check exactly which accounts exist:

SELECT user, host FROM mysql.user WHERE user = 'myapp_user';

If the password is the problem, reset it: ALTER USER 'myapp_user'@'localhost' IDENTIFIED BY 'New-Strong-Pass'; FLUSH PRIVILEGES;

2. ERROR 1396 (HY000): Operation CREATE USER failed — user already exists

You (or a previous attempt) already created that user. Either reuse it, or drop and recreate it cleanly:

DROP USER 'myapp_user'@'localhost';
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'Str0ng!Passw0rd-Here';

3. "Can't connect to local MySQL server through socket"

The MySQL service is not running. Start and enable it:

sudo systemctl start mysql
sudo systemctl enable mysql
sudo systemctl status mysql

4. App on another server can't connect (ERROR 2003)

Three things must align for remote connections: the user needs a remote-capable host ('user'@'app-ip'), MySQL must listen on more than 127.0.0.1 (check bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf), and the firewall must allow port 3306 from the app server's IP (sudo ufw allow from 203.0.113.10 to any port 3306). Granting '%'@'%' without these won't help.

5. Special characters in the password break shell commands

If your password contains $, !, or backticks and you pass it as mysql -u user -p$ecret, the shell expands it before MySQL sees it. Always let -p prompt you, or wrap the password in single quotes.

Conclusion

You now have a cleanly isolated MySQL database and a dedicated user with least-privilege access — the foundation every production application should start from. The same pattern scales: one database and one scoped user per application keeps blast radius small when credentials leak or an app misbehaves.

---

Need server setup done right? XpertBees helps businesses install, secure, and manage Linux servers and databases — from MySQL tuning to full DevOps automation. Get in touch and we will take the server headaches off your plate.