MySQL Basics: Databases, Tables, Users, and Privileges

Once MySQL is installed on your server (VPS), day-to-day work revolves around four things: creating databases, designing tables, managing users and privileges, and reading or writing data. This guide walks you through that path using an Ubuntu/Debian setup.

Logging In

MySQL usually installs through the package manager (sudo apt install mysql-server). Once it's up, log in as root:

sudo mysql

On recent versions, root authenticates via authsocket, so sudo mysql needs no password. If you've set a password on an account, connect like this instead:

mysql -u appuser -p

Press Enter and type the password when prompted. Add -h to target a host and -P to set the port (3306 by default).

Creating a Database

At the MySQL prompt, one statement creates a database. Set the character set explicitly so text stores cleanly:

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;
USE shop;

utf8mb4 stores the full range of Unicode, including emoji, and is the recommended default today. USE shop; makes it the active database for everything that follows.

Creating Tables and Common Column Types

A table defines the shape of your data. Here's a users table:

CREATE TABLE users (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  email      VARCHAR(255) NOT NULL,
  age        INT,
  balance    DECIMAL(10,2) DEFAULT 0.00,
  is_active  BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DESCRIBE users;

Column types you'll reach for most:

• INT / BIGINT: whole numbers; UNSIGNED forbids negatives and pairs well with AUTOINCREMENT for primary keys.
• VARCHAR(n): variable-length strings, ideal for usernames and emails.
• DECIMAL(m,d): fixed-point numbers. Always use this for money, never FLOAT, to avoid rounding errors.
• DATE / DATETIME / TIMESTAMP: dates and times.
• TEXT: long text; BOOLEAN is really a TINYINT(1) under the hood.

Creating a User and Granting Access

Never run an application as root. Give each app its own account with only the privileges it needs — that's the principle of least privilege.

CREATE USER 'appuser'@'localhost'
  IDENTIFIED BY 'ChangeThisStrongPass!';

GRANT SELECT, INSERT, UPDATE, DELETE
  ON shop.*
  TO 'appuser'@'localhost';

'appuser'@'localhost' allows connections only from the local machine. If the app runs elsewhere, replace localhost with its IP, or use '%' for any host (grant that sparingly). The statement above hands over only read/write access to shop — no schema changes, no dropping databases.

Take privileges back with REVOKE, and inspect an account with SHOW GRANTS:

REVOKE DELETE ON shop.* FROM 'appuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost';

When You Actually Need FLUSH PRIVILEGES

When you change access through GRANT, CREATE USER, or REVOKE, MySQL reloads the grant tables automatically — you do not need to flush anything. You only need this after editing system tables like mysql.user directly:

FLUSH PRIVILEGES;

The Everyday Queries

-- Insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- Read
SELECT id, username, email
FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 10;

-- Update (always scope it)
UPDATE users SET age = 31 WHERE username = 'alice';

-- Delete (always scope it)
DELETE FROM users WHERE id = 5;

The single most important habit: always add a WHERE clause to UPDATE and DELETE, or you'll rewrite or wipe the entire table. Before running either against production, test the same WHERE with a SELECT first to confirm which rows it matches.

Summary

Log in with sudo mysql or mysql -u user -p. Create databases with utf8mb4, and pick column types by meaning — DECIMAL for money. Give each application a dedicated account, grant only what it needs with GRANT, take access back with REVOKE, and keep to least privilege. Reach for FLUSH PRIVILEGES only after editing system tables by hand. Everyday work is just SELECT, INSERT, UPDATE, and DELETE — and UPDATE and DELETE always carry a WHERE. Master this one path and you can manage data on your own server with confidence.