Install MySQL 9.7 or 8.4 LTS on Ubuntu 26.04 / 24.04, Debian 13

Oracle’s MySQL APT repository still selects the 8.4 LTS track when you install its config package, even though MySQL 9.7 LTS went generally available in April 2026. The packages it installs also listen on every interface the moment the service starts, and root logs in through auth_socket, which sends the classic mysql_secure_installation script into an endless password loop. None of it shows up on the install screen.

Original content from computingforgeeks.com - post 71700

This guide shows how to install MySQL on Ubuntu 26.04, 24.04 or Debian 13 from Oracle’s official repository. You pick the LTS track on purpose, lock the listener down, create an application user, open remote access and measure the result with sysbench. We ran the full path in October 2026 on three VMs: Debian 13 with MySQL 9.7.2, Ubuntu 26.04 with 8.4.11 and Ubuntu 24.04 with 9.7.2.

Pick the MySQL LTS track

Pick 9.7 LTS for a new application and 8.4 LTS when you’re matching an existing fleet or an app that still needs mysql_native_password. Both are long-term support lines that only get bug and security fixes. Oracle’s release policy gives each LTS series five years of premier support plus three of extended support, so 9.7 simply starts that clock two years later. The one hard difference you will hit on day one is authentication: 8.4 keeps the old native password plugin as an opt-in, while 9.x removed it completely.

What the distributions ship on their own is a weaker option than it looks, and on two of the three releases it’s the wrong version entirely:

Release	mysql-server in the distro archive	From the MySQL APT repo
Debian 13	none (default-mysql-server pulls MariaDB)	8.4.11 or 9.7.2
Ubuntu 26.04	8.4.11	8.4.11 or 9.7.2
Ubuntu 24.04	8.0.46 (8.0 reached end of life in April 2026)	8.4.11 or 9.7.2

Ubuntu 26.04 users who only want 8.4 can stay on the archive package, and our Ubuntu 26.04 MySQL guide covers that route. Everyone else should use Oracle’s repository, because it is the only source that gives Debian 13 any MySQL at all and the only way off the dead 8.0 line on 24.04. The repository also carries an Innovation track (26.7 at the time of testing) that changes behaviour between releases, so we’d keep it off production servers.

Prerequisites

You need one server with sudo access and outbound HTTPS to dev.mysql.com and repo.mysql.com. Size it from your data, not from our lab. The InnoDB buffer pool should hold your working set, which on a dedicated database host means roughly 50 to 75 percent of RAM. The disk needs the dataset plus binary logs, redo log and about 30 percent headroom, on storage with fast fsync because every commit waits for one. A real OLTP application usually lands somewhere between 8 and 64 GB of RAM. The checklist:

• Debian 13, Ubuntu 26.04 or Ubuntu 24.04, 64-bit
• A user with sudo rights
• TCP 3306 reachable only if remote clients need it
• 2 vCPU, 4 GB RAM and 40 GB disk to follow along

That last line is the spec of our Debian 13 lab VM. It is a floor for following along, not a production recommendation, and the sysbench section at the end shows what happens when the buffer pool is left at its default on a dataset bigger than it.

Set the shell variables

The track, database name, user and password show up in several commands, so set them once. MYSQL_TRACK takes mysql-9.7-lts or mysql-8.4-lts, exactly as the repository spells them. Pick a real password with upper and lower case, a digit and a symbol, because the password policy we enable later rejects anything weaker:

export MYSQL_TRACK="mysql-9.7-lts"
export DB_NAME="appdb"
export DB_USER="appuser"
export DB_PASS="Ch4nge#Me2026"

These only live in the current shell. If you reconnect or switch to sudo -i halfway through, run the same block again before you continue, otherwise the SQL statements later on expand to empty strings.

Add the MySQL APT repository

The repository is installed through a small config package called mysql-apt-config. It pre-depends on lsb-release, which full server installs have but minimal and container images don’t, and when it is missing dpkg refuses to install the package. Install the helpers first, with vim for the config file you edit later, since minimal images ship without it:

sudo apt update
sudo apt install -y wget gnupg ca-certificates lsb-release vim

Oracle bumps the config package version every few months, so read the current file name from the MySQL APT repository download page instead of hardcoding one:

MYSQL_APT=$(wget -qO- https://dev.mysql.com/downloads/repo/apt/ | grep -oE "mysql-apt-config_[0-9.]+-[0-9]+_all\.deb" | head -1)
echo "${MYSQL_APT}"

You should get a single file name back. An empty line means the page layout changed or the request was blocked, so stop and check the download page by hand before going further:

mysql-apt-config_0.8.40-1_all.deb

Most guides skip this step. Left alone, the package selects mysql-8.4-lts, and we confirmed that on a fresh Debian 13 box where an unattended install wrote the 8.4 track into the sources file. Pre-answering the debconf question makes the choice explicit, so you get the track in ${MYSQL_TRACK} and no dialog appears:

wget -q "https://dev.mysql.com/get/${MYSQL_APT}"
echo "mysql-apt-config mysql-apt-config/select-server select ${MYSQL_TRACK}" | sudo debconf-set-selections
sudo DEBIAN_FRONTEND=noninteractive dpkg -i "${MYSQL_APT}"
grep -v "^#" /etc/apt/sources.list.d/mysql.list

The sources file has to name the track you picked on the server line. On Debian 13 with mysql-9.7-lts it reads like this, and Ubuntu shows ubuntu/ with resolute or noble instead:

deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ trixie mysql-apt-config
deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ trixie mysql-9.7-lts
deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ trixie mysql-tools
deb-src [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/debian/ trixie mysql-9.7-lts

If you ever want to switch tracks later, sudo dpkg-reconfigure mysql-apt-config opens the selection dialog again. Run sudo apt update after any change so APT sees the new packages. Moving up from 8.4 to 9.7 this way is a supported in-place upgrade, but going back down from 9.7 to 8.4 is not, and needs a dump and reload into a fresh 8.4 install.

Install MySQL on Ubuntu or Debian

Refresh the package index and install the server. Running it non-interactively skips the root password prompt on purpose: root then authenticates with auth_socket, so only the operating system’s root user can log in as MySQL root and there’s no database password to leak or rotate:

sudo apt update
sudo DEBIAN_FRONTEND=noninteractive apt install -y mysql-server

On our 4 GB Debian VM this pulled 16 packages (67.9 MB) and finished in 30 seconds, and the service started on its own. Check it:

systemctl status mysql --no-pager

Look for active (running) and the status line Server is operational. An idle 9.7 server sat at about 430 MB of memory right after startup (excerpt):

● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Sat 2026-10-17 23:36:57 UTC; 7s ago
       Docs: man:mysqld(8)
             https://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 1883 (mysqld)
     Status: "Server is operational"
      Tasks: 36 (limit: 4676)
     Memory: 428.7M (peak: 443.9M)

Now look at what it is listening on, because this is the part that surprises people. Oracle’s packages ship a mysqld.cnf with no bind-address, so the server binds to every interface, and the X Protocol port 33060 comes up too:

sudo ss -tlnp | grep mysqld

Both ports show *, which means any host that can route to the server can reach the MySQL login. The next sections fix that before you add a single user:

LISTEN 0      4096               *:3306             *:*    users:(("mysqld",pid=1883,fd=22))
LISTEN 0      70                 *:33060            *:*    users:(("mysqld",pid=1883,fd=19))

That is what Oracle’s 9.7 packages gave us on Debian 13, with no configuration touched yet.

Lock the listener to localhost

Bind both protocols to the loopback address now, even if you plan to allow remote clients later. A drop-in file is better than editing mysqld.cnf, because package upgrades can replace the stock file and the remote access step only has to change one line here. Create the file:

sudo vim /etc/mysql/mysql.conf.d/bind-address.cnf

Add these three lines. The mysqlx-bind-address line matters as much as the first one, because the X Protocol listener has its own setting and stays on every interface if you only change bind-address:

[mysqld]
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

Restart the service so it reads the new file, then check the listeners again. A restart drops open connections, which is one more reason to do this before an application depends on the database:

sudo systemctl restart mysql
sudo ss -tlnp | grep mysqld | awk '{print $1, $4}'

Both ports should now sit on 127.0.0.1. If you still see *, MySQL isn’t reading /etc/mysql/mysql.conf.d/ at all, which is the my.cnf alternatives problem covered in the troubleshooting section. On Ubuntu, Oracle’s mysql-community-server package also installs an AppArmor profile at /etc/apparmor.d/usr.sbin.mysqld, and aa-status listed the running mysqld under it on both 26.04 and 24.04. On 24.04 the kernel logged denials for mysqld reading its own /proc/<pid>/cgroup at every start, before and after this change alike, and the service ran normally each time, so the drop-in needs no profile edits:

LISTEN 127.0.0.1:3306
LISTEN 127.0.0.1:33060

Both ports are local now, and nothing outside the server can reach the login prompt. Applications on the same host still connect through the socket or 127.0.0.1 exactly as before.

Harden root and the password policy

Skip mysql_secure_installation on this install. With root on auth_socket the script demands a new root password, fails to set it and asks again, forever. At a keyboard you just see the same prompt over and over, and our automated expect test hit 18,497 failures in 90 seconds. It also has nothing left to clean up, which you can confirm in one query:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user; SHOW DATABASES LIKE 'test%';"

There are no anonymous accounts, no remote root and no test database, so the three cleanup questions the script asks are already answered. The internal mysql.* accounts are locked system accounts and stay:

user	host	plugin
mysql.infoschema	localhost	caching_sha2_password
mysql.session	localhost	caching_sha2_password
mysql.sys	localhost	caching_sha2_password
root	localhost	auth_socket

The one useful thing the script does is enable password validation, and you can do that directly. The component may already be there if you ran mysql_secure_installation earlier and answered y. Installing it twice fails with ERROR 3529 (HY000): Cannot load component from specified URN, so the command checks mysql.component first and only installs when it is missing:

sudo mysql -Ne "SELECT COUNT(*) FROM mysql.component WHERE component_urn = 'file://component_validate_password'" | grep -qx 1 || sudo mysql -e "INSTALL COMPONENT 'file://component_validate_password'"

We ran it twice on Ubuntu 26.04: the first run installed the component, the second skipped the install and exited cleanly, and on 24.04 it installed on the first run as well. MEDIUM is already the default policy (eight characters with mixed case, a digit and a symbol), so SET PERSIST here just pins it in mysqld-auto.cnf, which makes any later change a deliberate one:

sudo mysql -e "SET PERSIST validate_password.policy = 'MEDIUM'; SHOW VARIABLES LIKE 'validate_password.%';"

The policy line should read MEDIUM. The component checks new passwords only, so accounts created before it was installed keep whatever password they had, which is why this step comes before you create any users.

Variable_name	Value
validate_password.changed_characters_percentage	0
validate_password.check_user_name	ON
validate_password.dictionary_file
validate_password.length	8
validate_password.mixed_case_count	1
validate_password.number_count	1
validate_password.policy	MEDIUM
validate_password.special_char_count	1

With validation on, every password you set from here on has to meet that policy.

Create a database and application user

Applications should never connect as root, so give each one its own database and a user that only owns it. New accounts get caching_sha2_password by default, which current MySQL connectors support. utf8mb4_0900_ai_ci is the server’s default collation, spelled out here so the database does not change if someone edits the server default later:

sudo mysql -e "CREATE DATABASE ${DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; CREATE USER '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASS}'; GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'localhost';"

No output means it worked. If your password is too weak, the statement stops at CREATE USER with the error below, and the database is already created, so just rerun the CREATE USER and GRANT part with a stronger DB_PASS:

ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

Log in as the new user to prove the grant works. Testing it now, with the password typed exactly as the application will send it, separates a bad grant from a bad connection string later on:

mysql -u "${DB_USER}" -p"${DB_PASS}" -e "SELECT CURRENT_USER(), @@version; SHOW GRANTS;" "${DB_NAME}"

You should see the user, the server version and exactly two grants. The warning about a password on the command line is expected, and it’s the reason you’d use a ~/.my.cnf or the connector’s config in real use:

mysql: [Warning] Using a password on the command line interface can be insecure.
CURRENT_USER()	@@version
appuser@localhost	9.7.2
Grants for appuser@localhost
GRANT USAGE ON *.* TO `appuser`@`localhost`
GRANT ALL PRIVILEGES ON `appdb`.* TO `appuser`@`localhost`

The core setup ends here: MySQL runs from the official repository, listens only on localhost, root is socket-only and your application has its own account. If the app lives on the same server, you are done. The next section is only for databases that other machines connect to.

Allow remote MySQL connections (optional)

Remote access takes three changes, and a client only gets in when all of them agree:

• MySQL listening on the server’s private address
• a user scoped to the client network
• a firewall rule for that network on TCP 3306

Set the values this section needs. HOST_IP is the server’s private IP (ip -4 addr shows it), APP_SUBNET is the network your app servers live on, and APP_HOST is the same network in MySQL’s wildcard form:

export HOST_IP="10.0.1.50"
export APP_SUBNET="10.0.1.0/24"
export APP_HOST="10.0.1.%"

Add the private address to the bind list. Keeping 127.0.0.1 in it matters, because an app on the server that connects to 127.0.0.1:3306 over TCP would break if you replaced loopback with the LAN address. Port 33060 stays local, since nothing here uses the X Protocol. The address has to be static and already present in ip -4 addr. When we listed one the host did not have, the restart failed with Can't start server: Bind on TCP/IP port: Cannot assign requested address in /var/log/mysql/error.log, so fix the drop-in and restart again if that happens to you:

sudo sed -i "s/^bind-address = 127.0.0.1$/bind-address = 127.0.0.1,${HOST_IP}/" /etc/mysql/mysql.conf.d/bind-address.cnf
cat /etc/mysql/mysql.conf.d/bind-address.cnf
sudo systemctl restart mysql
sudo ss -tlnp | grep mysqld | awk '{print $1, $4}'

The file now lists both addresses. Port 3306 listens on each of them while 33060 stays on loopback, so nothing on the network can reach the X Protocol.

[mysqld]
bind-address = 127.0.0.1,10.0.1.50
mysqlx-bind-address = 127.0.0.1
LISTEN 127.0.0.1:3306
LISTEN 10.0.1.50:3306
LISTEN 127.0.0.1:33060

MySQL matches accounts on user and host together, so appuser@localhost doesn’t cover a remote client. Create the network-scoped account with the same password and grant:

sudo mysql -e "CREATE USER '${DB_USER}'@'${APP_HOST}' IDENTIFIED BY '${DB_PASS}'; GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'${APP_HOST}';"

The Debian 13 image we used had no firewall at all and both Ubuntu VMs had UFW installed but inactive, so install it (a no-op where it already exists) and enable it with SSH allowed first. Allowing OpenSSH before ufw enable is what keeps your current session alive; if you are on a console with no SSH, the order doesn’t matter. The OpenSSH profile only opens port 22, so if your sshd listens on another port, allow that port instead (for example sudo ufw allow 2222/tcp) before enabling the firewall:

sudo apt install -y ufw
sudo ufw allow OpenSSH
sudo ufw allow from "${APP_SUBNET}" to any port 3306 proto tcp
sudo ufw --force enable
sudo ufw status numbered

Port 3306 should be open to your subnet only, never to Anywhere, because a rule from anywhere exposes the MySQL login to every scanner that can reach the server. Your rule numbers may differ if UFW already had rules:

Status: active

     To                         Action      From
     --                         ------      ----
[ 1] OpenSSH                    ALLOW IN    Anywhere
[ 2] 3306/tcp                   ALLOW IN    10.0.1.0/24
[ 3] OpenSSH (v6)               ALLOW IN    Anywhere (v6)

Test from a machine on that subnet with any MySQL client. Our client was the 8.4 mysql binary from Ubuntu 26.04, which logged in to all three lab servers (two on 9.7, one on 8.4) over TLS 1.3, and you will be prompted for the password:

mysql -h 10.0.1.50 -u appuser -p appdb -e "SELECT CURRENT_USER(), @@hostname, @@version; SHOW STATUS LIKE 'Ssl_version';"

CURRENT_USER() shows which account matched, and it has to be the 10.0.1.% one. The session also negotiated TLS 1.3 without any setup, because mysqld creates a self-signed CA and server certificate in the data directory on first start (auto_generate_certs is on by default):

CURRENT_USER()	@@hostname	@@version
[email protected].%	db01	9.7.2
Variable_name	Value
Ssl_version	TLSv1.3

That certificate encrypts the link but doesn’t prove the server’s identity to the client. For traffic that leaves a trusted network, issue a proper certificate and require it, which our guide to securing MySQL with TLS certificates walks through. The final state of our lab server looked like this:

Root on auth_socket, two scoped application accounts and no listener on a public address is the shape you want before any application data goes in. If any of the three looks different on your server, fix it now, because changing accounts and bind addresses under a live application means downtime.

Troubleshooting

Every problem below came up on our lab server, either on the default path or while checking advice that older guides still give. Where a heading is an error message, it is the exact text the tool printed, so you can search this page for the message in front of you.

“SET PASSWORD has no significance for user ‘root’@’localhost'”

This is mysql_secure_installation trying to give a password to a root account that uses auth_socket. The script always asks for a new root password at that point, even when you decline the validation component, and when the SET PASSWORD fails it simply asks again. Press Ctrl+C to get out. Root is untouched, but if you answered y to the VALIDATE PASSWORD question, the script has already installed component_validate_password, and the hardening step below detects that and skips the install. The full message:

 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

The hardening section above does everything the script would do. If your policy really requires the script, give root a password first, run it with -u root -p and answer n to changing the root password, which completed cleanly for us. After that, sudo mysql without a password stops working, and every sudo mysql -e command later in this guide becomes mysql -u root -p -e, so only take this route if you want password-based root:

sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Str0ng#Root2026';"
mysql_secure_installation -u root -p

You only need this if your security policy insists on running the script itself. Everyone else can keep root on the socket and skip it.

“unknown variable ‘mysql_native_password=ON'”

Older guides tell you to add mysql_native_password=ON to mysqld.cnf for legacy apps. That option exists only on 8.4. On 9.7 the plugin is gone, so the server refuses to start and logs this to /var/log/mysql/error.log:

[ERROR] [MY-000067] [Server] unknown variable 'mysql_native_password=ON'.
[ERROR] [MY-010119] [Server] Aborting

Remove the line and restart. Creating a native-password account directly fails on 9.7 too, with ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded. If an application truly cannot speak caching_sha2_password, install the 8.4 track instead, where the same setting worked in our Ubuntu 26.04 container test and the account was created with the native plugin.

“mysql-apt-config pre-depends on lsb-release”

We hit this in a minimal Ubuntu 26.04 image. dpkg -i stops with pre-dependency problem - not installing mysql-apt-config, and the sources file is never written, so a later apt install mysql-server falls back to whatever the distribution offers. Install lsb-release and run the dpkg -i line again.

bind-address ignored after installing another package

This one is Debian-specific. On Debian 13, installing sysbench pulled in mariadb-common, which registers /etc/mysql/mariadb.cnf as the my.cnf alternative with priority 500 against MySQL’s 300. From the next restart, MySQL read MariaDB’s config tree instead of mysql.conf.d/ and our bind address silently went back to *. The error log moved to the journal too: @@log_error read stderr instead of /var/log/mysql/error.log, and the startup lines only showed up in journalctl -u mysql. Check where the link points:

update-alternatives --display my.cnf

Ours had moved to MariaDB’s file. In auto mode the highest priority wins, so whichever package registers the bigger number takes the link without asking:

my.cnf - auto mode
  link best version is /etc/mysql/mariadb.cnf
  link currently points to /etc/mysql/mariadb.cnf
  link my.cnf is /etc/mysql/my.cnf
/etc/mysql/mariadb.cnf - priority 500
/etc/mysql/my.cnf.fallback - priority 100
/etc/mysql/mysql.cnf - priority 300

Pin the link to MySQL’s file and restart. The --set switches the alternative to manual mode, so a later package that registers a higher priority no longer moves the link on its own. Then confirm the server picked up your bind list again.

sudo update-alternatives --set my.cnf /etc/mysql/mysql.cnf
sudo systemctl restart mysql
sudo mysql -Ne "SELECT @@bind_address"

The query should print your configured list again (127.0.0.1, plus HOST_IP if you enabled remote access) instead of *.

Benchmark and size the buffer pool

The default InnoDB buffer pool is 128 MB, and innodb_dedicated_server is off, so MySQL doesn’t size itself to the machine. That is fine for a small app and wrong for anything with a real dataset. To show the difference, we ran the same sysbench workload against both pool sizes:

• 10 tables of 200,000 rows, 561 MB on disk
• oltp_read_write, 8 threads, 60 seconds
• one discarded 60-second warm-up before each run

Install sysbench, then pin my.cnf back as described in the troubleshooting section, because on Debian 13 it brings in mariadb-common. On Ubuntu it pulled Oracle’s client library instead (libmysqlclient24 on 26.04, libmysqlclient21 on 24.04) and the link stayed on mysql.cnf, so there the pin is a harmless no-op:

sudo apt install -y sysbench
sudo update-alternatives --set my.cnf /etc/mysql/mysql.cnf

Create a throwaway database and user for the benchmark, then load the tables. Keep the benchmark away from appdb, since sysbench creates and later drops its own tables. Loading took 78 seconds on our VM:

sudo mysql -e "CREATE DATABASE sbtest; CREATE USER 'sbtest'@'localhost' IDENTIFIED BY 'Sb#Bench2026x'; GRANT ALL ON sbtest.* TO 'sbtest'@'localhost';"
sysbench oltp_read_write --mysql-user=sbtest --mysql-password='Sb#Bench2026x' --mysql-socket=/var/run/mysqld/mysqld.sock --tables=10 --table-size=200000 prepare

Take the baseline at the default pool first. The first run after loading only warms the cache, so throw its numbers away and keep the second; our cold first run managed 83.86 transactions per second, which is why the table below uses the warm figure:

sysbench oltp_read_write --mysql-user=sbtest --mysql-password='Sb#Bench2026x' --mysql-socket=/var/run/mysqld/mysqld.sock --tables=10 --table-size=200000 --threads=8 --time=60 run
sysbench oltp_read_write --mysql-user=sbtest --mysql-password='Sb#Bench2026x' --mysql-socket=/var/run/mysqld/mysqld.sock --tables=10 --table-size=200000 --threads=8 --time=60 run

The pool can be resized online, and SET PERSIST keeps the value across restarts. Grow it, then repeat the same pair of runs, warm-up first:

sudo mysql -e "SET PERSIST innodb_buffer_pool_size = 2147483648;"
sysbench oltp_read_write --mysql-user=sbtest --mysql-password='Sb#Bench2026x' --mysql-socket=/var/run/mysqld/mysqld.sock --tables=10 --table-size=200000 --threads=8 --time=60 run
sysbench oltp_read_write --mysql-user=sbtest --mysql-password='Sb#Bench2026x' --mysql-socket=/var/run/mysqld/mysqld.sock --tables=10 --table-size=200000 --threads=8 --time=60 run

Both runs used the same 2 vCPU, 4 GB Debian 13 VM and the same MySQL build, so the pool size is the only thing that changed. In our lab the warm 128 MB run came last, after setting the pool back from 2 GB, with the same discarded warm-up in front of it. Read the 95th percentile column first, because that is the latency your slowest users feel.

innodb_buffer_pool_size	Transactions/s	Avg latency	95th percentile
128 MB (default)	103.43	77.07 ms	244.38 ms
2 GB	173.74	46.02 ms	132.49 ms

Giving the pool room for the whole 561 MB dataset bought 68 percent more transactions and cut the 95th percentile latency almost in half, without touching a single query. The absolute numbers are low because this VM’s disk sits on a shared ZFS pool and every commit waits for an fsync, so don’t compare them with bare-metal NVMe results. Our PostgreSQL vs MySQL vs MariaDB benchmark does that side by side on one box.

Drop the benchmark database and user once you have your numbers, because 561 MB of test tables would otherwise end up in every backup. The same command removes the persisted 2 GB pool from mysqld-auto.cnf; leave that part out if you sized the server for it on purpose. RESET PERSIST does not shrink the running pool, so the change takes effect at the next restart.

sudo mysql -e "RESET PERSIST innodb_buffer_pool_size; DROP DATABASE sbtest; DROP USER 'sbtest'@'localhost';"

On a dedicated database server, set innodb_buffer_pool_size to 50 to 75 percent of RAM, or turn on innodb_dedicated_server and let MySQL size the pool and redo log itself. Leave the 128 MB default only where MySQL shares a small host with the application. Then watch real usage with the Prometheus mysqld_exporter, whose buffer pool hit ratio tells you when the pool needs to grow again.

Buffer pool sizing is only the first knob. Redo log capacity, flush behaviour and index design decide the rest, and the MySQL and MariaDB books we recommend explain InnoDB internals and query tuning in more depth than any config reference.

When a single tuned server stops being enough, because you need failover or more read capacity than one box gives, we’d move to Percona XtraDB Cluster with ProxySQL rather than stacking more RAM on one box.