HowToCodePage

Installation, databases, users and engines

Get a server running, create a database and a least-privilege account, and know which storage engine is actually holding your rows.

Install and connect

MySQL is a client/server system: mysqld owns the data directory and every tool is a client speaking the wire protocol. Installing means installing both parts and initialising the data directory before the daemon starts.

# Debian / Ubuntu
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation      # root password, removes anonymous accounts

# Docker, fastest for local experiments
docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8.4

# Client
mysql -h 127.0.0.1 -P 3306 -u app -p
mysql -u app -p -e "SELECT VERSION();"   # one statement, script friendly
  • Use 127.0.0.1 rather than localhost in scripts: localhost can be routed through a Unix socket instead of TCP, which changes which host rules apply.
  • Server settings live in /etc/mysql/my.cnf (plus files under my.cnf.d/). After editing, restart and read the error log rather than guessing why the daemon refused to start.
  • The default port is 3306. Bind it to a private interface or a firewall rule, never to the public internet.

Databases, tables and users

A MySQL account is not just a name: it is a name plus a host pattern, and 'app'@'localhost', 'app'@'10.0.0.%' and 'app'@'%' are three different accounts with independent passwords and grants.

CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';

SHOW GRANTS FOR 'app'@'10.0.0.%';
SHOW CREATE TABLE app.orders;

-- change a password (MySQL 8 syntax)
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-newer-longer-password';
ConceptWhat it really is
DatabaseA namespace of tables; in MySQL schema and database are synonyms
Accountuser plus a host pattern, matched at connection time
PrivilegeA grant on a scope: global, database, table or column
Default engineInnoDB unless the table or server says otherwise

Use utf8mb4 everywhere. MySQL's older utf8 is a three-byte subset that cannot store emoji or many CJK extension characters, and mixing the two in a join silently forces slow conversions.

Storage engines

EngineTransactionsWhere it still makes sense
InnoDBYes — row locks + MVCCDefault; essentially all application tables
MyISAMNo — table-level locksLegacy read-mostly tables; avoid in new work
MEMORYNo — data lost on restartSmall temporary lookup tables
ARCHIVENo — insert and compress onlyLong retention of write-once logs
NDBYes (distributed)MySQL Cluster deployments
💡
On a dedicated server, innodb_buffer_pool_size is the single most important setting: it decides how much of your working set stays cached in RAM. Sizing it at roughly half to three quarters of available memory beats almost any query tweak.

Check what a table is using with SHOW TABLE STATUS LIKE 'orders' or SELECT ENGINE FROM information_schema.tables WHERE table_name = 'orders'.

FAQ

Should I use MySQL or MariaDB?
They share a protocol and much syntax, but versions diverge on features (JSON functions, replication internals, authentication plugins). Pick one and target its documented behaviour rather than assuming compatibility.
Why does my connection fail with 'Access denied' when the password is right?
Almost always the host part of the account. Connecting from a container or a different subnet means a different user@host row; check SELECT user, host FROM mysql.user.

Indexes and reading EXPLAIN Replication, backup and operational pitfalls

Last refreshed 2026-09-18.