Chapter 1
Chapter 1 — Installing MySQL on Ubuntu / Debian
MySQL is the database engine that runs behind the overwhelming majority of the web — WordPress, Joomla, phpBB, custom PHP applications — all store their data in MySQL tables. Before you can create databases, run queries, or connect web applications, you need a running MySQL server. This chapter gets it installed, started, and verified on Ubuntu and Debian systems.
What this chapter covers: What MySQL is and how it fits into a web stack. The MySQL vs MariaDB distinction — knowing which one you're about to install. Installing MySQL on Ubuntu (straightforward via apt). Installing MySQL on Debian (requires the official MySQL APT repository, since the default Debian packages install MariaDB instead). Starting and enabling the service. First login via Unix socket authentication. Verifying the installation and exploring the MySQL client. A tour of the files and directories MySQL creates. The four default databases explained.
What MySQL Is — and Where It Fits
The LAMP stack — where MySQL lives:
Browser ──▶ Apache (web server) ──▶ PHP (application logic) ──▶ MySQL (data)
│
┌───────┴────────┐
│ Tables/Rows │
│ books │
│ users │
│ orders │
└────────────────┘
MySQL's job: store data persistently, retrieve it fast, enforce relationships,
and handle multiple simultaneous users without data corruption.
A relational database management system (RDBMS) stores data in tables — rows and columns, like a spreadsheet, but with strict structure and the ability to link tables together. MySQL is the world's most widely deployed open-source RDBMS. It handles everything from a personal blog's twenty posts to a billion-row e-commerce catalogue.
MySQL runs as a background service (mysqld). Your PHP code, Python scripts, or admin tools connect to it over a socket or TCP connection, send SQL commands, and receive results. MySQL itself never touches your web files — it only speaks SQL.
MySQL vs MariaDB — Know Before You Install
MariaDB was created in 2009 as a community fork of MySQL after Oracle acquired MySQL AB. The two share the same SQL syntax, the same client tools, and are largely compatible — but they are different software with diverging internals. On Debian, the default package default-mysql-server installs MariaDB, not MySQL. On Ubuntu, mysql-server installs MySQL.
MySQL (Oracle)
Maintained by Oracle. Packages:
Used throughout this course. Newer features: JSON columns, window functions (8.0+), InnoDB improvements.
Check you have it:
mysql-server on Ubuntu, or the MySQL APT repository on Debian.Used throughout this course. Newer features: JSON columns, window functions (8.0+), InnoDB improvements.
Check you have it:
mysqld --version should say MySQL Community Server.
MariaDB (Community)
Open-source fork. Package:
Default on Debian. Uses
Check you have it:
default-mysql-server or mariadb-server.Default on Debian. Uses
mysqld binary and mysql client — the commands look identical, which causes confusion.Check you have it:
mysqld --version says mariadb or MariaDB.
The SQL you'll write in this course runs on both. The administration details (service names, config file locations, some system variables) can differ slightly. The instructions in this chapter install MySQL specifically on both distros, so you'll be on the same version regardless of which OS you're running.
Installing MySQL
Ubuntu (20.04 / 22.04 / 24.04)
Ubuntu's official repositories ship MySQL Community Server — apt install mysql-server gets you the real thing with no extra steps.
# Update the package index first
$ sudo apt update
# Install MySQL server (includes the client tools automatically)
$ sudo apt install mysql-server -y
# Verify the installed version
$ mysql --version
mysql Ver 8.0.36 Distrib 8.0.36, for Linux (x86_64) using EditLine wrapper
# Check the service started automatically
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled)
Active: active (running) since Mon 2026-06-15 10:00:01 BST
On Ubuntu, MySQL starts and enables itself automatically during installation. You don't need to run
systemctl start or systemctl enable — it's already running and set to start on boot.
Debian (11 Bullseye / 12 Bookworm)
Debian's default repositories don't include MySQL — default-mysql-server installs MariaDB instead. To get MySQL specifically, you need to add Oracle's official MySQL APT repository first.
Setup Walkthrough · Debian — Adding the MySQL APT Repository
Install MySQL on Debian by adding Oracle's official APT source.
1
Download the MySQL APT configuration package. This adds the MySQL repository and its signing key in one step.
$ sudo apt install wget lsb-release gnupg -y
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
Visit dev.mysql.com/downloads/repo/apt/ to get the latest version number if the filename above has changed.
2
Install the configuration package. A menu appears — select MySQL Server & Cluster and choose the version you want (MySQL 8.0 or 8.4 LTS). Then select OK.
$ sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
# An ncurses menu appears — select MySQL 8.0 (stable) or 8.4 (LTS), then OK
3
Update apt and install MySQL.
$ sudo apt update
$ sudo apt install mysql-server -y
# You will be prompted to set a root password during installation on Debian
4
Start and enable the service.
$ sudo systemctl start mysql
$ sudo systemctl enable mysql
$ sudo systemctl status mysql
Active: active (running)
5
Clean up the downloaded package.
$ rm mysql-apt-config_0.8.29-1_all.deb
The MySQL APT repository also keeps MySQL up to date via
apt upgrade — you don't need to manually download new versions in the future.Managing the MySQL Service
# Start MySQL (if not already running)
$ sudo systemctl start mysql
# Stop MySQL (flushes data to disk, closes connections gracefully)
$ sudo systemctl stop mysql
# Restart MySQL (stop then start — disconnects all active clients)
$ sudo systemctl restart mysql
# Reload configuration without restarting (not all changes take effect this way)
$ sudo systemctl reload mysql
# Enable MySQL to start on boot (usually already set after install)
$ sudo systemctl enable mysql
# Disable auto-start on boot
$ sudo systemctl disable mysql
# Detailed service status (shows recent log lines)
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; ...)
Active: active (running) since Mon 2026-06-15 10:00:01 BST; 5min ago
Process: ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 1234 (mysqld)
Tasks: 38 (limit: 4678)
Memory: 386.2M
CPU: 1.843s
systemctl stop mysql before maintenance — not kill. Killing the mysqld process abruptly risks leaving data in an inconsistent state. Always use systemctl stop, which triggers a clean shutdown that flushes the InnoDB buffer pool and closes all open transactions.
Your First Login — Unix Socket Authentication
On a fresh Ubuntu install, MySQL's root account uses Unix socket authentication instead of a password. This means you log in as the MySQL root user by being the Linux root user (or using sudo) — no password required over the terminal. This is intentional and secure: root database access is only possible from the server itself, by a user who already has sudo.
# Connect to MySQL as root (Ubuntu default — no password prompt)
$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# You are now inside the MySQL client. The mysql> prompt is waiting for SQL.
# All SQL statements end with a semicolon (;) — without it, MySQL waits for more input.
# Show the server version
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
# List all databases
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# Exit the MySQL client
mysql> EXIT;
Bye
Debian difference: On Debian, you set a root password during installation. Log in with:
mysql -u root -p — you'll be prompted for that password. If you need to connect from the terminal without a password on Debian, use sudo mysql — this also bypasses the password via the Unix socket.
What Got Installed — Files and Directories
/etc/mysql/ ← main config directory
├── mysql.conf.d/
│ └── mysqld.cnf ← primary server config (Ubuntu)
├── conf.d/
│ └── mysql.cnf ← client config (charset etc.)
└── my.cnf ← top-level include file
/var/lib/mysql/ ← data directory — your actual databases live here
├── mysql/ ← the system mysql database
├── performance_schema/
├── sys/
├── ibdata1 ← InnoDB shared tablespace
├── ib_logfile0 ← InnoDB redo log
└── mysql.sock ← Unix socket (how local connections work)
/var/log/mysql/ ← log files
└── error.log ← startup errors, crashes, warnings — check here first
/usr/bin/
├── mysql ← the MySQL command-line client
├── mysqldump ← backup tool (Chapter 5)
├── mysqladmin ← admin operations from the shell
└── mysqlcheck ← table check/repair/optimise
Never edit files directly in
/var/lib/mysql/ — that's where MySQL stores its binary data files. Touching them outside MySQL will corrupt your databases. All data manipulation goes through SQL or the mysqldump backup tool.
The Four Default Databases
Every fresh MySQL install comes with four databases. You'll see them in SHOW DATABASES — they're not yours to use for application data, but understanding what they are saves confusion later.
mysql
System database
Stores user accounts, privileges, and server settings. The
user table inside it controls who can connect and what they can do. You'll interact with this indirectly — using CREATE USER and GRANT commands (Chapter 3) rather than editing the table directly.information_schema
Metadata read-only
A virtual read-only database containing metadata about everything on the server — tables, columns, indexes, views, privileges. Useful for: discovering what columns a table has when you don't have the original schema.
SELECT * FROM information_schema.TABLES.performance_schema
Monitoring
Runtime performance statistics — query execution times, lock waits, I/O events, memory usage. Data is collected continuously. Used in Chapter 7 (monitoring). Read-only, memory-resident — no data persisted to disk.
sys
Helper views
A collection of views and stored procedures that make
performance_schema human-readable. Example: SELECT * FROM sys.processlist — shows active queries in plain English rather than raw performance counters.Essential MySQL Client Commands
# ── Connecting ───────────────────────────────────────────────────
# Connect as root via Unix socket (no password on Ubuntu)
$ sudo mysql
# Connect as a named user with a password prompt
$ mysql -u myuser -p
# Connect to a specific database directly
$ mysql -u myuser -p mydatabase
# Connect to a remote server
$ mysql -u myuser -p -h 192.168.1.100
# ── Inside the MySQL client ───────────────────────────────────────
# Show all databases
mysql> SHOW DATABASES;
# Switch to a database
mysql> USE myDatabase;
Database changed
# Show all tables in the current database
mysql> SHOW TABLES;
# Show the columns of a table
mysql> DESCRIBE tableName;
# Show the CREATE TABLE statement for a table (reveals indexes, constraints)
mysql> SHOW CREATE TABLE tableName\G
# Check server status summary
mysql> STATUS;
--------------
mysql Ver 8.0.36 Distrib 8.0.36, for Linux (x86_64)
Connection id: 8
Current database:
Current user: root@localhost
Server version: 8.0.36 MySQL Community Server - GPL
Uptime: 15 min 4 sec
Threads: 2 Questions: 12 Slow queries: 0 ...
# Show all active connections and queries
mysql> SHOW PROCESSLIST;
# Get help on any SQL command
mysql> HELP SELECT;
# Clear the current input (abandoned command) — use \c instead of backspace
mysql> SELECT * FROM broken_query \c
# Exit the client
mysql> EXIT;
# or:
mysql> QUIT;
SQL keywords are case-insensitive —
SELECT, select, and Select all work. Convention is to write SQL keywords in UPPERCASE and table/column names in lowercase — it makes queries easier to read at a glance. All examples in this course follow that convention.
The semicolon is not optional. MySQL won't execute a statement until it sees a
;. If you press Enter without one, you get a continuation prompt (->). This is a feature — you can split a long statement across multiple lines. Type ; on its own line to run what you've written so far.
Troubleshooting
mysql: command not found after installation
The client binary wasn't installed. On Ubuntu,
apt install mysql-server includes the client. If you installed only mysql-client and need the server, run sudo apt install mysql-server. On Debian, if you only added the APT repo but didn't install, run sudo apt update && sudo apt install mysql-server. Confirm: which mysql — should return /usr/bin/mysql.
ERROR 2002: Can't connect to local MySQL server through socket
MySQL isn't running. Check:
sudo systemctl status mysql — look at the Active line. If it says failed, check the error log: sudo tail -30 /var/log/mysql/error.log. Common causes: another process is using port 3306 (sudo ss -tlnp | grep 3306), or the data directory has wrong permissions (ls -la /var/lib/mysql — should be owned by mysql:mysql). Fix permissions: sudo chown -R mysql:mysql /var/lib/mysql.
ERROR 1045: Access denied for user 'root'@'localhost'
On Ubuntu, root uses socket authentication — run
sudo mysql (with sudo), not mysql -u root -p. Without sudo, MySQL sees you as your regular Linux user, not root, and rejects the connection. On Debian where you set a root password during install, use mysql -u root -p and enter that password — without the sudo prefix.
apt install mysql-server on Debian installs MariaDB instead
On Debian,
mysql-server is a virtual package that resolves to default-mysql-server → MariaDB. You must add the MySQL APT repository first (see the Debian walkthrough above). After adding the repo and running apt update, running apt install mysql-server will find the real MySQL package from Oracle's repo and install that instead of MariaDB. Confirm after install: mysqld --version should say MySQL Community Server, not MariaDB.
MySQL is running but
SHOW DATABASES shows nothing or gives access denied
You're probably connected as a user with no privileges. Connect as root:
sudo mysql (Ubuntu) or mysql -u root -p (Debian). If you're already root and see only information_schema, this is correct — a root user with socket auth has access to all databases, but non-root users only see databases they have privileges on. Run SHOW GRANTS; inside MySQL to see what the current user can access.
Quick Reference — Chapter 1
| Command | Purpose |
|---|---|
| sudo apt install mysql-server -y | Install MySQL on Ubuntu (includes client tools) |
| sudo systemctl start mysql | Start the MySQL service |
| sudo systemctl enable mysql | Enable MySQL to start automatically on boot |
| sudo systemctl status mysql | Check whether MySQL is running (and see recent log lines) |
| mysql --version | Print the MySQL client version |
| mysqld --version | Print the server version — use this to confirm MySQL vs MariaDB |
| sudo mysql | Connect as root via Unix socket (Ubuntu — no password needed) |
| mysql -u root -p | Connect as root with a password prompt (Debian) |
| SHOW DATABASES; | List all databases the current user can see |
| SELECT VERSION(); | Show the MySQL server version from inside the client |
| STATUS; | Show connection info, server version, and uptime |
| EXIT; / QUIT; | Leave the MySQL client |
| sudo tail -f /var/log/mysql/error.log | Watch the MySQL error log — first place to check if something's wrong |