MENU
Getting MySQL Running
Before any SQL you need a server you trust: the right series, from a source that keeps patching it, locked down, and configured in a file you can find again. This section takes MySQL 9.7 LTS from download to a client session on Ubuntu, Windows, macOS and Docker. Packages and systemd are in Chapter 1.3.1.1 What MySQL Is and Where It Sits in the LAMP Stack
MySQL is a relational database server: a program, mysqld, that owns a data directory and answers SQL from clients. Unlike SQLite it is not a library your code loads. PHP, the mysql command line and Workbench are all clients that connect, authenticate as an account, send statements and read result sets, so one server can serve a hundred PHP-FPM workers.
Figure 3.1: Where MySQL sits in a LAMP request, and the two ways a
client reaches itInside mysqld each connection gets a thread. The SQL layer parses a
statement, the optimizer chooses a plan (Section 3.12), and a
storage engine reads and writes rows: InnoDB, with
transactions, row locks and crash recovery (Section 3.14). Accounts and
metadata live in the mysql system schema in the same data directory.prompted Widenius to fork MariaDB. The Community Edition is GPLv2; the commercial
3.1.2 The Release Model: LTS, Innovation, and Calendar Versioning
MySQL 8.0 changed features and defaults across eight years of point releases. Since 2023 releases follow two tracks instead:LTS (long-term support) series get only necessary fixes: "There are no removals within an LTS release," and features change only in its first release. Each gets five years of premier and three of extended support under Oracle's Lifetime Support Policy.
Innovation releases carry new features and behavior changes, each supported only until the next one, roughly a quarter later.
3.1.3 Installing MySQL on Ubuntu
On Ubuntu 26.04, sudo apt install mysql-server gives you Ubuntu's 8.4.11 LTS, patched through unattended-upgrades. For 9.7 LTS you add Oracle's APT repository with the small mysql-apt-config package. Its debconf question, which component to enable, opens a text dialog that hangs a scripted install, so answer it in advance:curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.40-1_all.deb
echo "mysql-apt-config mysql-apt-config/select-server select mysql-9.7-lts" \
| sudo debconf-set-selections
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y ./mysql-apt-config_0.8.40-1_all.deb
sudo apt-get update
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server
grep '^deb ' /etc/apt/sources.list.d/mysql.list | cut -d' ' -f3-
apt-cache policy mysql-server | grep -E '^ {1,5}(\*\*\* )?[0-9]' # versions on offer
systemctl status mysql --no-pager | grep -E 'Loaded:|Active:|Status:'
http://repo.mysql.com/apt/ubuntu/ resolute mysql-apt-config
http://repo.mysql.com/apt/ubuntu/ resolute mysql-9.7-lts
http://repo.mysql.com/apt/ubuntu/ resolute mysql-tools
*** 9.7.2-1ubuntu26.04 500
8.4.11-0ubuntu0.26.04.1 500
8.4.8-0ubuntu1 500
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Wed 2026-09-23 14:47:23 +08; 33min ago
Status: "Server is operational"
sudo mysql -N -e "SELECT CURRENT_USER()"
mysql -u root -e "SELECT 1"
sudo mysql -h 127.0.0.1 -e "SELECT 1"
root@localhost
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
3.1.4 Installing MySQL on Windows
The "MySQL Installer" of older tutorials belongs to the 8.0 era. For 9.7 Oracle ships an MSI (mysql-9.7.2-winx64.msi, about 175 MB) or a ZIP, both containing MySQL Configurator, which must run before the server will start. It needs 64-bit Windows (Oracle lists Windows 11 and Server 2022) and the Visual C++ 2019 redistributable. Configurator asks for a server type that sizes memory, ports 3306 and 33060, the root password, optional sakila and world sample databases, and a Windows service. Its command line, from Oracle's manual (not run here, on Linux), skips the clicks:msiexec /i mysql-9.7.2-winx64.msi /qn /lv install.log
cd "C:\Program Files\MySQL\MySQL Server 9.7\bin"
.\mysql_configurator.exe --console --action=configure --password=S3cure-Root! `
--port=3306 --server-type=Developer --install-sample-databases=None
3.1.5 Installing on macOS and with XAMPP
Oracle's macOS build is a DMG with a native package (mysql-9.7.2-macos15-arm64.dmg; 9.7 is supported on macOS 14 and 15 on Apple silicon). It asks for a root password, installs into a versioned folder behind a /usr/local/mysql symlink, and registers the launch daemon com.oracle.oss.mysql.mysqld.plist, whose arguments override my.cnf.Homebrew's mysql formula is the 26.7.0 Innovation release, so ask for mysql@9.7 (9.7.2). It is keg-only, not linked into Homebrew's bin. These commands follow the formula's notes and were not run on this Linux machine:brew install mysql@9.7
echo 'export PATH="$(brew --prefix mysql@9.7)/bin:$PATH"' >> ~/.zshrc
brew services start mysql@9.7 # launchd agent, restarts at login
mysql_secure_installation # root starts with no password
3.1.6 Running MySQL in Docker
A container is a disposable server of any version. The official mysql:9.7 image (Oracle Linux 9, amd64 and arm64) serves every example in this chapter that changes server settings. The system server holds port 3306, so this one publishes 3301, on loopback only:docker run -d --name mysql97 -e MYSQL_ROOT_PASSWORD=secret \
-p 127.0.0.1:3301:3306 -v mysql97-data:/var/lib/mysql mysql:9.7
docker logs mysql97 2>&1 \
| grep -E '\[Entrypoint\]: (Init|Starting temp|Stopping temp|MySQL init)' | cut -c12-
docker ps --filter name=mysql97 --format 'table {{.Names}}\t{{.Status}}\t{{.Ports}}'
2d30291c4677ef62e9363371c8c5ced606b17a8f7675f8d4218d7d57069db6ac
07:07:32+00:00 [Note] [Entrypoint]: Initializing database files
07:07:42+00:00 [Note] [Entrypoint]: Starting temporary server
07:07:47+00:00 [Note] [Entrypoint]: Stopping temporary server
07:07:48+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
NAMES STATUS PORTS
mysql97 Up 11 minutes 33060/tcp, 127.0.0.1:3301->3306/tcp
3.1.7 Securing a Fresh Installation
mysql_secure_installation makes four fixes: a root password, no remote root, no anonymous accounts, no test database. First it offers the validate_password component, which checks every later password. The mysql:9.7 image lacks the tool, but it is an ordinary client, so the Ubuntu host's copy secures the container of Section 3.1.6 (answers follow each prompt):Enter password for user root:
...
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
...
Estimated strength of the password: 25
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
...
All done!
mysql -h 127.0.0.1 -P 3301 -uroot -psecret -e "SELECT 1"
docker exec mysql97 mysql -uroot -psecret \
-e "CREATE USER 'report'@'localhost' IDENTIFIED BY 'bookshop2026'"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '172.17.0.1' is not allowed to connect to this MySQL server
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements
3.1.8 The Option File and Server Variables
MySQL programs read INI-style option files at startup: [mysqld] for the server, [client] and [mysql] for clients. On Ubuntu that means /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf, later values winning (Section 1.2.2). /etc/mysql/my.cnf links to mysql.cnf, which includes conf.d/ and then mysql.conf.d/, home of the package's mysqld.cnf. Leave packaged files alone and add your own that sorts last:[mysqld]
max_connections = 200
innodb_buffer_pool_size = 512M
slow_query_log = ON
long_query_time = 0.5
SET GLOBAL max_connections = 300; -- every new connection, until restart
SET PERSIST long_query_time = 1; -- now, and after every restart
SET PERSIST_ONLY back_log = 300; -- read-only at runtime: next start only
SET GLOBAL back_log = 300;
SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN ('max_connections', 'long_query_time', 'back_log', 'sort_buffer_size');
+------------------+-----------------+--------------------------------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH |
+------------------+-----------------+--------------------------------+
| back_log | PERSISTED | /var/lib/mysql/mysqld-auto.cnf |
| long_query_time | PERSISTED | /var/lib/mysql/mysqld-auto.cnf |
| max_connections | GLOBAL | /etc/mysql/conf.d/zz-app.cnf |
| sort_buffer_size | COMPILED | |
+------------------+-----------------+--------------------------------+
3.1.9 The mysql Client and MySQL Shell
Keep passwords off the mysql command line, where ps and shell history see them: mysql_config_editor set --login-path=dev97 --host=127.0.0.1 --port=3301 --user=webdev --password prompts once and stores them, obfuscated, in ~/.mylogin.cnf. Here webdev is an account on the Docker server, which holds the shop example database:mysql --login-path=dev97 shop -N -B -e "SELECT sku, price FROM products ORDER BY id LIMIT 3" \
| tr '\t' ','
mysql --login-path=dev97 shop -e 'SELECT id, sku FROM products ORDER BY id LIMIT 1\G'
mysql --login-path=dev97 shop --commands \
-e 'SELECT id, sku FROM products ORDER BY id LIMIT 1\G'
BK-PHP-01,39.90
BK-SQL-01,44.50
BK-SQL-02,29.00
ERROR at line 1: Unknown command '\G'.
*************************** 1. row ***************************
id: 1
sku: BK-PHP-01
echo "$PW" | mysqlsh --sql --uri webdev@127.0.0.1:3301/shop --passwords-from-stdin \
--result-format=json/array -e "SELECT sku, price FROM products ORDER BY price DESC LIMIT 2"
[
{"sku":"BK-LAR-01","price":49},
{"sku":"BK-SQL-01","price":44.5}
]