MySQL and MariaDB maintenance: backups, tuning and slow queries

The database is the first component to strain in most web applications. The good news: the largest gains come from a handful of simple maintenance habits.

Taking consistent backups

Copying database files is not a backup — the files of a running database can be inconsistent at any moment. Take a dump:

bashmysqldump -u root -p --single-transaction --routines --triggers --databases app | gzip > app-$(date +%F).sql.gz

--single-transaction takes a consistent snapshot of InnoDB tables without locking, so the site keeps serving. Without --routines and --triggers, your stored procedures and triggers are not in the backup — a gap people usually discover during a restore.

Restoring:

bashgunzip < app-2026-09-22.sql.gz | mysql -u root -p

Practise the restore regularly. An untested backup is not a backup; detail in our backup guide.

Finding slow queries

Measure instead of guessing. Turn on the slow query log:

text[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Every query taking longer than a second is recorded. To summarise what accumulates:

bashmysqldumpslow -s t /var/log/mysql/slow.log | head -20

To inspect a suspicious query, prefix it with EXPLAIN:

textEXPLAIN SELECT * FROM orders WHERE customer_id = 42;

ALL in the type column means a full table scan, and rows tells you how many rows are examined. The fix is usually an index:

textCREATE INDEX idx_orders_customer ON orders (customer_id);

Indexes are not magic: each one slows writes slightly and takes disk space. Add them only to columns that are genuinely queried.

Memory tuning: the one setting that matters

Among dozens of options, the InnoDB buffer pool makes more difference than the rest combined. When frequently read data fits there, the disk is never touched.

text[mysqld]
innodb_buffer_pool_size = 2G

Rule of thumb: 50-70% of total memory on a dedicated database server. But if the web server and PHP share the machine, you cannot give it that much — around 25% is safer there. Giving it too much pushes the system into swap, which slows everything down.

To see the current value:

bashmysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Restart the service after changing it.

Close off remote access

This is the most common security mistake on database servers. By default it should listen only locally:

text[mysqld]
bind-address = 127.0.0.1

Check it:

bashss -lptn | grep 3306

If you see 0.0.0.0:3306, your database is exposed to the internet. Never open port 3306 outward in the firewall. If you need to connect remotely, use an SSH tunnel:

bashssh -L 3306:127.0.0.1:3306 user@server-ip

You can now connect to 127.0.0.1:3306 from your own machine, with the traffic encrypted over SSH.

Regular cleanup

Data that accumulates at the application level slows queries over time:

• Session and cache tables — delete expired rows regularly
• Log and audit tables — set a retention period and archive what is older
• On WordPress, post revisions and autoloaded options; detail in our WordPress optimization guide

To see table sizes:

textSELECT table_name, ROUND((data_length + index_length) / 1024 / 1024) AS mb
FROM information_schema.tables
WHERE table_schema = 'app'
ORDER BY mb DESC LIMIT 10;

If you would like to review your database server sizing together, get in touch.