MySQL Performance Tuning, Security and Backups for Beginners

MySQL Performance Tuning, Database Security & Backup Guide for Beginners

Deepak is a backend developer in Gwalior. He built a school-management app for a private school: admissions, attendance and fee collection, with a Node.js API and one MySQL database. In the first term the class-wise fee report opened instantly. Two years and twenty thousand student records later, the principal's office complains that the report page "keeps loading". Deepak's code has not changed, and MySQL has not changed. Only the amount of data has. There is also something he has been meaning to fix for months: the app connects to MySQL as root, and the nightly backup script was copied from a blog and has never once been restored. What makes a working database slow down as it grows, and what should he fix before a bad evening turns into a lost table?

This guide answers that in three parts. Part 1 is performance: reading EXPLAIN and EXPLAIN ANALYZE, pagination, the N+1 problem, connection pools, the slow query log and monitoring. Part 2 is security on the database side: MySQL users, GRANTs, roles, password policy, TLS and encryption at rest. Part 3 is backup and recovery: mysqldump, physical backups, binary-log point-in-time recovery, the 3-2-1 rule, RPO and RTO. Every SQL statement and Node.js snippet below was run on a real MySQL 8.4.9 server with mysql2, and the outputs in the comments are copied from those runs. Timings printed by EXPLAIN ANALYZE are from one laptop and will be different on your machine; read them as "small versus large", not as exact numbers.

Part 1: MySQL Performance Tuning

Performance tuning means making the database answer the same questions with less work: fewer rows read, fewer bytes sent, fewer round trips and fewer connections opened. The goal is simple to state. A parent searching for a student's fee receipt should see it in a fraction of a second, not after ten seconds of spinning, and the server should not need a bigger machine every time the school adds a section.

Think of a restaurant kitchen. With five customers, the cooks can afford to walk to the store room for every onion. With five hundred customers, that habit causes chaos, and the fix is not "hire more cooks" but "organise the kitchen". A database is the same. Small tables hide bad habits, because reading every row of a 500-row table is still fast. The habits only show up when the table has 50,000 or 5,00,000 rows.

Why a Database Gets Slow

MySQL itself is rarely the problem. The usual causes are the queries and the design around them:

CauseWhat it looks likeSection
Reading columns you don't needSELECT * on a table with photos and long textProblem 1
Missing or unusable indexesFull table scans; type: ALL in EXPLAINProblem 2, Reading EXPLAIN
Fetching too many rowsLoading every student on page load; deep OFFSET pagesProblem 3
Repeating the same queryA dashboard recalculating the same totals every few secondsProblem 4
Too many small queriesOne query per row in a loop (N+1)Problem 5
Opening a new connection per requestConnection set-up cost on every API call; "Too many connections"Connection pooling
Poor schema design and heavy reportsReports joining many large tables with no planSlow query log, monitoring, large scale

The Test Data

To make the effects visible, the examples use two tables from Deepak's school app. students has 20,000 rows, including a long address column and a photo column, and fee_payments has 60,000 rows. The rows are generated inside MySQL with a recursive CTE (a query that calls itself to produce a number series), so you can rebuild the same data on your own machine. The CTE needs a higher recursion limit than the default 1,000.

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admission_no VARCHAR(12) NOT NULL,
  full_name VARCHAR(80) NOT NULL,
  class_section VARCHAR(5) NOT NULL,      -- '7-B', '10-A' ...
  city VARCHAR(40) NOT NULL,
  parent_phone CHAR(10) NOT NULL,
  address TEXT,
  photo MEDIUMBLOB,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE fee_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  paid_on DATE NOT NULL,
  mode VARCHAR(10) NOT NULL               -- 'UPI', 'Cash', 'Card'
);

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO students (admission_no, full_name, class_section, city, parent_phone, address)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
SELECT CONCAT('GW', LPAD(i, 6, '0')),
       CONCAT(ELT(1 + i % 8, 'Aarav','Diya','Kabir','Ananya','Vivaan','Isha','Rohan','Sana'), ' ',
              ELT(1 + (i DIV 8) % 6, 'Sharma','Verma','Tomar','Gupta','Jain','Khan')),
       CONCAT(1 + i % 12, '-', ELT(1 + (i DIV 12) % 4, 'A','B','C','D')),
       ELT(1 + i % 4, 'Gwalior','Morena','Bhind','Datia'),
       CONCAT('98', LPAD(i, 8, '0')),
       REPEAT('House no. and street details ', 10)
FROM n;

INSERT INTO fee_payments (student_id, amount, paid_on, mode)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 60000)
SELECT 1 + (i * 7) % 20000, 1500 + (i % 5) * 500,
       DATE '2026-04-01' + INTERVAL (i % 200) DAY, ELT(1 + i % 3, 'UPI','Cash','Card')
FROM n;

ANALYZE TABLE students, fee_payments;   -- refresh index statistics
SELECT COUNT(*) FROM students;          -- 20000
SELECT COUNT(*) FROM fee_payments;      -- 60000

Twenty thousand rows is small by production standards. That is deliberate: if a problem is already visible at this size, it will hurt much more at a few lakh rows.

Problem 1: SELECT * and Why It Hurts

SELECT * FROM students looks harmless, and in a tutorial with three columns it is. In a real table it asks MySQL to read and send every column, including ones the page never shows. The class list page needs a name and an admission number. It does not need the 290-byte address (the average in this test table), and it certainly does not need a photo blob. Multiply that by a few hundred rows per request and many requests per minute, and you are paying for memory, disk reads and network transfer that nobody sees.

-- Bad: sends address, photo, created_at ... to a page that shows two columns
SELECT * FROM students WHERE class_section = '7-B';

-- Good: ask only for what the page shows
SELECT id, admission_no, full_name FROM students WHERE class_section = '7-B';

SELECT AVG(LENGTH(address)) AS avg_address_bytes FROM students;   -- 290.0000

There is a second, less obvious cost. When every column a query needs is inside an index, MySQL can answer from the index alone and never touch the main table row. EXPLAIN reports this as Using index, and it is called a covering index. With an index on (class_section, full_name), which we add in the next section, compare these two:

EXPLAIN SELECT id, full_name FROM students WHERE class_section = '7-B' ORDER BY full_name;
-- type: ref | key: idx_class_name | rows: 417 | Extra: Using index

EXPLAIN SELECT * FROM students WHERE class_section = '7-B' ORDER BY full_name;
-- type: ref | key: idx_class_name | rows: 417 | Extra: NULL

Both use the index to find the 417 rows of class 7-B, but only the first one is answered entirely from the index. (InnoDB stores the primary key id inside every secondary index, which is why id is "free" here.) SELECT * forces MySQL to go back to the table for every one of those rows. A smaller benefit: listing columns also protects your code when someone adds a large column to the table later.

Problem 2: Missing Indexes

An index is like the catalogue in a library. Without it, the librarian walks every shelf to find one book; with it, she looks up the shelf number and goes straight there. The Joins, Indexing and Query Optimization post explains how B-tree indexes work, composite indexes and the basics of EXPLAIN, so this section focuses on seeing the difference and fixing slow queries.

The fee screen for one student runs this query. With no index on student_id, MySQL has to read every payment row to find the three that belong to student 4321:

EXPLAIN SELECT SUM(amount) FROM fee_payments WHERE student_id = 4321;
-- type: ALL | possible_keys: NULL | key: NULL | rows: 58858 | filtered: 10 | Extra: Using where

CREATE INDEX idx_student ON fee_payments (student_id);

EXPLAIN SELECT SUM(amount) FROM fee_payments WHERE student_id = 4321;
-- type: ref | possible_keys: idx_student | key: idx_student | ref: const | rows: 3 | Extra: NULL

The estimate drops from 58,858 rows examined to 3. (The estimate is 58,858 rather than exactly 60,000 because rows comes from sampled statistics, not a fresh count.) This kind of query is called a full table scan before the index and an index lookup after it. Note that if you declare student_id as a FOREIGN KEY, InnoDB creates an index on it automatically; this table has no foreign key, which is common in older schemas and exactly where missing indexes hide.

Reading EXPLAIN Properly

EXPLAIN is the database's X-ray. Put it in front of a SELECT, UPDATE or DELETE and MySQL shows the plan it would use, without running the query. Most people look only at the key column. The other columns tell you much more, so here is the full output for Deepak's slow class-wise report query, first without a useful index:

EXPLAIN SELECT id, admission_no, full_name
FROM students WHERE class_section = '7-B' ORDER BY full_name;
ColumnBefore (no index)After idx_class_nameWhat it means
typeALLrefHow rows are found. ALL = read the whole table; ref = jump to matching index entries
possible_keysNULLidx_class_nameIndexes MySQL considered. NULL means none were usable
keyNULLidx_class_nameThe index actually chosen
key_lenNULL22Bytes of the index used. 22 = only the class_section part (VARCHAR(5) in utf8mb4 is 5 × 4 + 2 length bytes)
refNULLconstWhat the index is compared with; const = a fixed value ('7-B')
rows19560417Estimated rows MySQL will read. The single most useful number
filtered10100Estimated % of those rows that survive the WHERE. Low = lots of wasted reading
ExtraUsing where; Using filesortNULLExtra work. "Using filesort" = a separate sort step for ORDER BY

The fix was one composite index whose first column matches the WHERE and whose second column matches the ORDER BY. Because index entries are stored already sorted by (class_section, full_name), MySQL reads class 7-B's entries in name order and the filesort disappears.

CREATE INDEX idx_class_name ON students (class_section, full_name);

The type column, from best to worst

typeMeaningTypical cause
const / systemAt most one matching row, read onceWHERE id = 5 on the primary key
eq_refOne row per row of the previous table in a joinJoining on a primary key or unique index
refAll rows matching one value of a non-unique indexWHERE class_section = '7-B'
fulltextUses a FULLTEXT indexMATCH ... AGAINST
rangeA slice of an index between two valuesBETWEEN, >, <, LIKE 'abc%'
indexReads the whole index from start to endThe index covers the columns but can't be used to seek
ALLReads the whole tableNo usable index

Beginners often think type: index is good because the word "index" appears. It is only slightly better than ALL: MySQL still reads every entry, just from the smaller index instead of the table.

Common Extra values

  • Using where: rows are filtered after being read. Fine on its own; worrying when rows is large and filtered is low.
  • Using index: a covering index; the table itself is not touched. Good.
  • Using filesort: an extra sort step for ORDER BY. Cheap on 50 rows, expensive on 50,000.
  • Using temporary: MySQL builds a temporary table, usually for GROUP BY or DISTINCT. Watch it on large row counts.
  • Using index condition: part of the WHERE is checked inside the index before reading rows (index condition pushdown). Good.

EXPLAIN ANALYZE: The Plan Plus What Really Happened

Plain EXPLAIN shows estimates. EXPLAIN ANALYZE (MySQL 8.0.18 and later) actually runs the query and reports, for each step, the real time and the real number of rows. Because it executes the statement, use it on SELECTs, and be careful with it on a busy production server: a slow query stays slow under ANALYZE. Its output is a tree: read it from the most indented line upwards, because the innermost step runs first.

-- Before the index
EXPLAIN ANALYZE SELECT id, admission_no, full_name
FROM students WHERE class_section = '7-B' ORDER BY full_name;

-> Sort: students.full_name  (cost=2092 rows=19560) (actual time=14.1..14.1 rows=417 loops=1)
    -> Filter: (students.class_section = '7-B')  (cost=2092 rows=19560) (actual time=0.244..13.8 rows=417 loops=1)
        -> Table scan on students  (cost=2092 rows=19560) (actual time=0.228..10.9 rows=20000 loops=1)

-- After CREATE INDEX idx_class_name ON students (class_section, full_name)
-> Index lookup on students using idx_class_name (class_section='7-B')  (cost=146 rows=417) (actual time=0.188..1.64 rows=417 loops=1)

How to read one line: cost and the first rows are the optimizer's estimate. actual time=0.228..10.9 means the first row came out after 0.228 ms and the last after 10.9 ms. The second rows is the real count, and loops is how many times the step ran (in joins, the inner table runs once per outer row). The story here is in the row counts: before the index, MySQL read all 20,000 rows to keep 417 and then sorted them; after it, MySQL read exactly 417 entries, already in order. On this laptop the time went from about 14 ms to under 2 ms, but those milliseconds depend on hardware, cache state and load, while "20,000 rows read versus 417" stays true on any machine.

There is also EXPLAIN FORMAT=TREE, which prints the same tree with estimates only and does not run the query. It is a safe first look on production.

When a plan's estimate and the actual row count are far apart (say, estimated 10 rows, actual 50,000), the optimizer was working from stale statistics. Running ANALYZE TABLE table_name; refreshes them and often fixes a strange plan on its own.

Five Ways to Accidentally Disable an Index

Having an index does not mean MySQL can use it. These four patterns were all run against the same 20,000-row table, and each one turned a cheap lookup into a scan. The fix is almost always to rewrite the query, not to add another index.

1. Wrapping the column in a function. MySQL cannot look up YEAR(paid_on) in an index built on paid_on. Write the condition as a range on the raw column instead. Both queries below return 9,000 rows, but look at type and rows:

CREATE INDEX idx_paid_on ON fee_payments (paid_on);

EXPLAIN SELECT COUNT(*) FROM fee_payments WHERE YEAR(paid_on) = 2026 AND MONTH(paid_on) = 6;
-- type: index | possible_keys: NULL | rows: 58858 | Extra: Using where; Using index

EXPLAIN SELECT COUNT(*) FROM fee_payments WHERE paid_on >= '2026-06-01' AND paid_on < '2026-07-01';
-- type: range | possible_keys: idx_paid_on | rows: 9000 | Extra: Using where; Using index

2. Comparing a text column with a number. parent_phone is CHAR(10). Searching it with a number makes MySQL convert every stored value to a number before comparing, so the index can't be used to seek. Quoting the value fixes it.

CREATE INDEX idx_phone ON students (parent_phone);

EXPLAIN SELECT id FROM students WHERE parent_phone = 9800004321;
-- type: index | rows: 19560 | filtered: 10

EXPLAIN SELECT id FROM students WHERE parent_phone = '9800004321';
-- type: ref | ref: const | rows: 1

In Node.js this happens when a phone number arrives as a JavaScript number from JSON. Pass it as a string to the query.

3. A leading wildcard in LIKE. An index on names is sorted like a phone directory, so LIKE 'Kabir%' can jump to the K section, but LIKE '%Tomar%' could be anywhere in the directory.

CREATE INDEX idx_name ON students (full_name);

EXPLAIN SELECT id, full_name FROM students WHERE full_name LIKE 'Kabir%';
-- type: range | key: idx_name | rows: 2500

EXPLAIN SELECT id, full_name FROM students WHERE full_name LIKE '%Tomar%';
-- type: index | possible_keys: NULL | rows: 19560

4. Skipping the first column of a composite index. Before idx_name existed, the same LIKE 'Kabir%' query could only scan idx_class_name end to end (type: index, 19,560 rows), because that index is sorted by class_section first. A composite index helps queries that use its columns from the left: (class_section) or (class_section, full_name), never full_name alone.

5. Indexing everything "just in case". This one doesn't disable an index, it slows writes instead. Every INSERT, UPDATE and DELETE has to update each index on the table, and each index uses disk and memory. Add indexes for queries you actually run, and remove the ones nobody uses (the sys schema can list them, as shown later).

Searching text properly

For "find any notice containing these words", a FULLTEXT index is the right MySQL tool instead of LIKE '%word%'. It splits text into words and indexes each word:

CREATE TABLE notices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120),
  body TEXT,
  FULLTEXT KEY ft_notice (title, body)
);
INSERT INTO notices (title, body) VALUES
  ('Fee deadline extended', 'Term 2 fees can now be paid till 15 July.'),
  ('Sports day', 'Annual sports day on Saturday. Parents are welcome.'),
  ('Holiday notice', 'School closed on Monday for Raksha Bandhan.');

SELECT id, title FROM notices WHERE MATCH(title, body) AGAINST ('fees deadline');
-- 1 | Fee deadline extended
-- EXPLAIN shows type: fulltext, key: ft_notice

For product-style search with typos, synonyms and ranking across millions of rows, teams usually add a dedicated search engine such as Elasticsearch or OpenSearch alongside MySQL. That is a later-stage step; a FULLTEXT index covers a lot of ground before it.

Problem 3: Fetching Too Much Data, and Pagination

No user can read 20,000 students on one screen, yet "load everything and let the frontend filter" is a common first version. The fix is pagination: return one page at a time with LIMIT. The question is how you find the start of each page.

OFFSET pagination

SELECT id, full_name FROM students ORDER BY id LIMIT 20 OFFSET 0;    -- page 1
SELECT id, full_name FROM students ORDER BY id LIMIT 20 OFFSET 20;   -- page 2
SELECT id, full_name FROM students ORDER BY id LIMIT 20 OFFSET 40;   -- page 3

This is simple and lets users jump to "page 37". The catch is that MySQL cannot skip rows for free: to return rows 19,001 to 19,020 it walks through the first 19,000 and throws them away. EXPLAIN ANALYZE shows it clearly:

EXPLAIN ANALYZE SELECT id, full_name FROM students ORDER BY id LIMIT 20 OFFSET 19000;

-> Limit/Offset: 20/19000 row(s)  (cost=1982 rows=20) (actual time=9.52..9.53 rows=20 loops=1)
    -> Index scan on students using PRIMARY  (cost=1982 rows=19020) (actual time=0.156..8.56 rows=19020 loops=1)

It read 19,020 rows to return 20. The deeper the page, the more work, so the last pages of a large list are always the slowest.

Keyset (cursor) pagination

Keyset pagination remembers where the previous page ended and asks for rows after that point. With an index on the sort column, MySQL jumps straight there:

EXPLAIN ANALYZE SELECT id, full_name FROM students WHERE id > 19000 ORDER BY id LIMIT 20;

-> Limit: 20 row(s)  (cost=369 rows=20) (actual time=0.0412..0.0448 rows=20 loops=1)
    -> Filter: (students.id > 19000)  (cost=369 rows=1842) (actual time=0.0406..0.0435 rows=20 loops=1)
        -> Index range scan on students using PRIMARY over (19000 < id)  (cost=369 rows=1842) (actual time=0.0394..0.0414 rows=20 loops=1)

-- Same rows either way:
SELECT id, full_name FROM students ORDER BY id LIMIT 3 OFFSET 19000;
SELECT id, full_name FROM students WHERE id > 19000 ORDER BY id LIMIT 3;
-- 19001 | Diya Khan
-- 19002 | Kabir Khan
-- 19003 | Ananya Khan

Twenty rows read instead of 19,020. The API returns the last id of each page as a cursor (for example ?after=19020), and the client sends it back for the next page.

When you sort by a column that is not unique, such as a name, add the primary key as a tie-breaker so no row is skipped or repeated. In this test data the first three students of 7-B all have the same name, which is exactly the case that breaks a cursor based on the name alone:

SELECT id, full_name FROM students
WHERE class_section = '7-B' ORDER BY full_name, id LIMIT 3;
-- 18  | Kabir Tomar
-- 66  | Kabir Tomar
-- 114 | Kabir Tomar      <- cursor for the next page: ('Kabir Tomar', 114)

SELECT id, full_name FROM students
WHERE class_section = '7-B'
  AND (full_name > 'Kabir Tomar' OR (full_name = 'Kabir Tomar' AND id > 114))
ORDER BY full_name, id LIMIT 3;
-- 162 | Kabir Tomar
-- 210 | Kabir Tomar
-- 258 | Kabir Tomar
-- EXPLAIN: type: range | key: idx_class_name | Extra: Using where; Using index
OFFSET paginationKeyset pagination
Rows read for a deep pageoffset + limit (19,020 above)about limit (20 above)
Jump to page NYesNo, only next/previous
New rows inserted while browsingRows can shift, so items repeat or get skippedStable
Best forAdmin tables with page numbers, small listsInfinite scroll, feeds, exports, large lists

A practical middle path: use OFFSET for the first few pages of admin screens and cap the maximum page, and use keyset for anything that scrolls or exports. The E-Commerce Database Design post shows where this fits in a product catalogue.

Problem 4: Repeated Queries, Caching and the Missing Query Cache

Imagine the school's admin dashboard shows "fees collected this month", "students present today" and "pending admissions", and twenty staff members keep it open with auto-refresh every minute. The same three heavy aggregates run again and again, and the answers barely change between runs. Two habits help. First, reduce calls per page: combine related queries, use joins, and for heavy reports keep a small summary table that is updated as payments arrive or by a scheduled job. Second, cache the answers.

Correction to a common tip: MySQL 8 has no query cache. Older MySQL versions had a built-in "query cache" that stored the result of identical SELECT statements. It was deprecated in 5.7.20 and removed in MySQL 8.0, mainly because it scaled badly: every write to a table threw away all cached results for that table, and the cache itself became a point of contention under load. On MySQL 8.4 the variables are simply gone:

SELECT @@have_query_cache;              -- NO
SHOW VARIABLES LIKE 'query_cache%';     -- 0 rows in set

Any article that tells you to set query_cache_size on MySQL 8 is out of date.

Caching now belongs in your application or in a shared cache such as Redis. The idea is "remember the answer for a short time". This minimal in-memory version, run against the test server, answered six calls with one database query:

const cache = new Map();                 // key -> { value, expiresAt }

async function cached(key, ttlMs, loader) {
  const hit = cache.get(key);
  if (hit && hit.expiresAt > Date.now()) return hit.value;
  const value = await loader();
  cache.set(key, { value, expiresAt: Date.now() + ttlMs });
  return value;
}

let dbHits = 0;
const juneTotal = () => cached('fees:june', 60_000, async () => {
  dbHits++;
  const [[row]] = await pool.query(
    "SELECT SUM(amount) AS total FROM fee_payments WHERE paid_on >= '2026-06-01' AND paid_on < '2026-07-01'");
  return row.total;
});

for (let i = 0; i < 5; i++) await juneTotal();
console.log('total:', await juneTotal(), '| calls: 6 | database queries:', dbHits);
// total: 5500.00 | calls: 6 | database queries: 1

An in-process Map works for one server, but each server in a cluster would keep its own copy and they would disagree. For anything shared, use Redis with cache-aside, expiry and invalidation on writes, which the Node.js Backend Architecture: API Security, Rate Limiting, Redis Caching post covers in detail. Cache things that are read often and change rarely (dashboard totals, class lists, settings), never things that must always be exact at that instant, like a fee balance just before payment.

Problem 5: The N+1 Query Problem

N+1 happens when code runs one query to get a list, then one more query for each item in that list. For a class of 40 students that is 41 queries; for a report across 2,000 students it is 2,001. Each query is fast on its own, so nothing looks wrong in testing, but every query pays a network round trip and the total grows with the data. ORMs make it easy to write by accident, because student.payments looks like a property but quietly runs a query.

This test counts queries in a small mysql2 wrapper. It uses a separate small table where class 7-B has three students, so N+1 means four queries:

let queryCount = 0;
const q = async (sql, params) => { queryCount++; const [rows] = await pool.query(sql, params); return rows; };

// N+1: one query for the list, one per student
queryCount = 0;
const students = await q('SELECT id, full_name FROM students WHERE class_section = ?', ['7-B']);
for (const s of students) {
  const [row] = await q('SELECT COALESCE(SUM(amount), 0) AS paid FROM fee_payments WHERE student_id = ?', [s.id]);
  s.paid = Number(row.paid);
}
console.log(students, 'queries =', queryCount);
// [ { id: 1, full_name: 'Aarav Sharma', paid: 3000 },
//   { id: 2, full_name: 'Diya Verma', paid: 2500 },
//   { id: 3, full_name: 'Kabir Tomar', paid: 3000 } ] queries = 4

Fix 1: one JOIN. Let MySQL combine the data. LEFT JOIN keeps students with no payments, and COALESCE turns their NULL total into 0.

queryCount = 0;
const joined = await q(`
  SELECT s.id, s.full_name, COALESCE(SUM(f.amount), 0) AS paid
  FROM students s
  LEFT JOIN fee_payments f ON f.student_id = s.id
  WHERE s.class_section = ?
  GROUP BY s.id, s.full_name
  ORDER BY s.id`, ['7-B']);
// same three rows, queries = 1

Fix 2: one IN query. When the list comes from somewhere else (another service, a cache) or a join would duplicate rows, fetch all children in one query and match them up in JavaScript. mysql2 expands an array into a comma-separated list:

queryCount = 0;
const list = await q('SELECT id, full_name FROM students WHERE class_section = ?', ['7-B']);
const ids = list.map(s => s.id);
const sums = await q(
  'SELECT student_id, SUM(amount) AS paid FROM fee_payments WHERE student_id IN (?) GROUP BY student_id', [ids]);
const byId = new Map(sums.map(r => [r.student_id, Number(r.paid)]));
list.forEach(s => { s.paid = byId.get(s.id) ?? 0; });
// same three rows, queries = 2

console.log(mysql.format('SELECT ... WHERE student_id IN (?)', [ids]));
// SELECT ... WHERE student_id IN (1, 2, 3)

Two queries no matter how many students, instead of one per student. Keep very large lists in batches (for example 1,000 IDs per query), and make sure student_id is indexed, or the "fixed" query becomes one big scan. Note that mysql2 returns DECIMAL and SUM results as strings by default to avoid losing precision, which is why the code wraps them in Number(). For money you may prefer to keep them as strings or paise integers.

Connection Pooling

Opening a MySQL connection is not free: a TCP connection, often a TLS handshake, authentication and session set-up, all before the first query. Doing that for every API request wastes time and, under load, can hit the server's max_connections limit (151 by default) and fail with "Too many connections". A connection pool keeps a few connections open and lends them out. It is like a taxi stand: you reuse the taxis that are waiting rather than buying a new car for every trip.

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: 3306,
  user: process.env.DB_USER,          // a limited app user, never root (see Part 2)
  password: process.env.DB_PASSWORD,
  database: 'school',
  connectionLimit: 10,      // at most 10 open connections
  waitForConnections: true, // extra requests wait in a queue
  queueLimit: 0,            // 0 = no limit on the queue
});

// pool.query() borrows a connection, runs the query and returns it automatically
const [rows] = await pool.query('SELECT id, full_name FROM students WHERE id = ?', [42]);

In the test, 20 queries were fired in parallel at a pool with connectionLimit: 10:

const [[before]] = await pool.query("SHOW STATUS LIKE 'Threads_connected'");
await Promise.all(Array.from({ length: 20 }, () => pool.query('SELECT SLEEP(0.1)')));
const [[after]] = await pool.query("SHOW STATUS LIKE 'Threads_connected'");
console.log('before:', before.Value, 'after 20 parallel queries:', after.Value);
// before: 1 after 20 parallel queries: 10

The server never saw more than 10 connections; the other 10 queries waited briefly in the pool's queue. Size the pool so that pool size × number of app instances stays well under max_connections, leaving room for admin and backup connections. A bigger pool is not automatically faster; past a point, extra connections just compete for the same CPU and disk. If you take a connection manually with pool.getConnection() (for a transaction), always call conn.release() in a finally block, or the pool slowly runs dry.

Finding Slow Queries: The Slow Query Log

EXPLAIN helps once you know which query is slow. The slow query log tells you which ones. When it is on, MySQL records every statement that takes longer than long_query_time seconds. It is off by default, and the default threshold is 10 seconds, which is far too high for a web app:

SHOW VARIABLES WHERE Variable_name IN ('slow_query_log', 'long_query_time', 'log_output');
-- log_output      | FILE
-- long_query_time | 10.000000
-- slow_query_log  | OFF

You can switch it on at runtime (needs an admin account) and persist it in the server's configuration file so it survives a restart:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;        -- log anything slower than half a second
-- optional: also log queries that use no index at all (can be noisy)
-- SET GLOBAL log_queries_not_using_indexes = ON;

# my.cnf / my.ini (config file, not SQL)
[mysqld]
slow_query_log      = 1
long_query_time     = 0.5
slow_query_log_file = /var/log/mysql/slow.log

By default entries go to a file. For a quick experiment you can send them to the mysql.slow_log table instead with SET GLOBAL log_output = 'TABLE'. In this test a session threshold of 0 seconds was used so that a small query would be logged:

SET SESSION long_query_time = 0;
SELECT COUNT(*) FROM students WHERE full_name LIKE '%Tomar%';   -- 3336

SELECT query_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log;
-- 00:00:00.011475 | 1 | 20000 | SELECT COUNT(*) FROM students WHERE full_name LIKE '%Tomar%'

The most useful column is often rows_examined compared with rows_sent: 20,000 examined to send 1 row is a query worth a look. On a real server the log file grows quickly; MySQL ships a summary script, mysqldumpslow, and tools such as pt-query-digest group similar queries so you can fix the ones that cost the most in total, not just the single slowest one. The workflow is: find in the slow log, explain with EXPLAIN / EXPLAIN ANALYZE, fix (index, rewrite, paginate, cache), then confirm the query disappears from the log.

Monitoring Database Health

Problems in a database grow quietly: a few more rows each day, one more report, one more cron job, until one morning everything is slow at once. A few built-in views let you see what is happening without installing anything.

What is running right now: SHOW PROCESSLIST lists every connection and its current statement. A query with a large Time value is your suspect, and KILL <Id> stops it.

SHOW PROCESSLIST;
-- Id | User            | Host            | db     | Command | Time | State                  | Info
-- 5  | event_scheduler | localhost       | NULL   | Daemon  | 5    | Waiting on empty queue | NULL
-- 8  | root            | localhost:58978 | dbdata | Query   | 0    | init                   | SHOW PROCESSLIST

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected', 'Max_used_connections', 'Slow_queries', 'Uptime');
-- Max_used_connections | 1
-- Slow_queries         | 7
-- Threads_connected    | 1
-- Uptime               | 6

What has been expensive over time: performance_schema is MySQL's built-in instrument panel. It quietly counts what every statement does, grouping similar statements into one "digest" with the values replaced by ?. The sys schema is a set of friendlier views on top of it, written for humans. After resetting the counters and running a few app queries, this view pointed straight at the payment query that had no index on student_id:

SELECT query, exec_count, rows_examined_avg
FROM sys.statements_with_full_table_scans WHERE db = DATABASE();
-- SELECT COUNT ( * ) FROM `fee_payments` WHERE `student_id` = ? | 2 | 60000

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = DATABASE();
-- dbdata | students | idx_name

Treat schema_unused_indexes with care: it only knows about activity since the server last started, so check it after the server has been up through a full business cycle (including month-end reports) before dropping anything.

For a live system, track a handful of numbers on a dashboard and alert when they move:

MetricWhere to read itWarning sign
Query latency, slow queriesSlow log, Slow_queries, sys viewsRising trend, new entries after a release
ConnectionsThreads_connected, Max_used_connectionsClose to max_connections
CPU and memoryServer or cloud monitoringCPU pinned near 100% during normal traffic
Disk space and I/OServer monitoringDisk filling up (binary logs and backups grow too)
Replication lag (if using replicas)SHOW REPLICA STATUSReplicas falling seconds or minutes behind

Open-source stacks such as Prometheus with a MySQL exporter and Grafana, or your cloud provider's database metrics, can draw these for you. What matters is that someone notices "CPU at 95%, slow queries rising, connections almost exhausted" before the principal's office does.

Large-Scale Optimization (Briefly)

Most applications become fast again with the basics above: good schema, the right indexes, pagination and efficient queries. When a single server truly isn't enough, the next tools are:

  • Read replicas: copies of the database that receive changes from the main server through replication. Reports and read-heavy pages go to replicas; writes stay on the primary. Replicas can lag slightly behind, so "read your own write" pages should still use the primary.
  • Partitioning: one table stored as several physical pieces, for example by year. Queries that filter on the partition key read only the matching pieces (called partition pruning), and dropping an old year becomes a quick ALTER TABLE ... DROP PARTITION. Every unique key, including the primary key, must include the partitioning column.
  • Sharding: splitting data across separate servers, for example by school or region. Powerful, but joins and transactions across shards become your application's problem, so it is usually the last step.
  • Caching layers and search engines: Redis for hot reads, and a search engine for full-text product-style search.
CREATE TABLE attendance (
  student_id INT NOT NULL,
  att_date DATE NOT NULL,
  status CHAR(1) NOT NULL,
  PRIMARY KEY (student_id, att_date)       -- includes the partitioning column
)
PARTITION BY RANGE (YEAR(att_date)) (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);
INSERT INTO attendance VALUES (1,'2025-12-01','P'), (1,'2026-07-01','P'), (2,'2026-07-01','A');

EXPLAIN SELECT COUNT(*) FROM attendance WHERE att_date >= '2026-07-01' AND att_date < '2026-08-01';
-- partitions: p2026   <- only one partition is read

Partitioning is not a replacement for indexes. On small tables it often makes queries no faster; it pays off mainly for very large, time-based tables where you regularly archive or drop old data.

Back in Gwalior. Deepak runs EXPLAIN ANALYZE on the class-wise report and sees "Table scan on students ... rows=20000" feeding a sort. One composite index, (class_section, full_name), turns it into an index lookup of a few hundred rows with no filesort. He also finds the report loops over students and fetches each one's payments separately, and replaces the loop with one LEFT JOIN ... GROUP BY. The page is quick again. The two items he kept postponing, the root login and the untested backup, are next.

Part 2: MySQL Security (Database Side)

A system can survive a slow page for a week. It may not survive a leaked parent database or a deleted fee ledger. Database security means protecting data from unauthorised access, theft, modification, deletion and corruption, whether the cause is an attacker or a tired teammate. Think of the database as a bank vault: there are guards at the door (authentication), rules about who may enter which room (authorization), locked boxes (encryption), cameras (audit logs), and copies of the important papers kept somewhere else (backups).

Many breaches come from simple mistakes rather than clever attacks: a shared admin password, a database port open to the internet, an unencrypted backup in a public bucket, or an app account that can drop tables. This part covers the MySQL side of security. Application-side topics, such as SQL injection in your code, rate limiting and hashing your users' passwords with bcrypt, are covered in the API Security and Authentication, JWT and Password Hashing posts.

The evening of the DELETE. At 7:40 one evening, Deepak's teammate is cleaning test data. He believes his terminal is connected to the staging database and runs DELETE FROM fee_payments; with no WHERE. It is production. MySQL replies Query OK and removes every payment row, because the app's root credentials, which he copied from the .env file, are allowed to do anything. Nothing in the database said "no". With a limited account the same statement would have stopped with ERROR 1142 (42000): DELETE command denied, and with safe-update mode it would not have run without a WHERE. The rest of this part is about building those "no"s in.

The Principle of Least Privilege

Give every account exactly the access it needs to do its job, and nothing more. In an office, the receptionist has a key to the reception area, not to the server room or the payroll cabinet. If her key card is stolen, the thief gets into reception and no further. Database accounts work the same way: if the app's account can only read and write a few tables, a leaked password or a bug in the app can only do that much damage.

WhoNeedsShould not have
The school app (API server)SELECT, INSERT, UPDATE on the school schemaDROP, ALTER, GRANT, access to mysql.*; usually DELETE too (use soft deletes)
Reports and dashboardsSELECT only, ideally on a replicaAny write privilege
Developers on productionRead-only access when debugging, granted for a reasonShared root, write access by default
Migration / deploy jobCREATE, ALTER, INDEX on the schema, used only during deploysUse by the running app
Backup jobRead privileges plus the few extra that the backup tool needsWrite privileges on the data
Human DBA / adminAdmin rights, with their own named accountA shared account that hides who did what

Users, Hosts and GRANT

A MySQL account is a user name plus a host: 'school_app'@'10.0.1.15' and 'school_app'@'%' are two different accounts. The host part says where the connection may come from. '%' means "from anywhere", which is convenient and risky; in production use the app server's private IP or subnet. The tests below use 127.0.0.1 because the app and the database ran on the same machine.

Roles: grant to a job, not to each person

Since MySQL 8.0 you can create roles, which are named bundles of privileges. Grant privileges to the role once, then grant the role to each account. When a new developer joins, you grant a role instead of retyping a list of privileges and hoping it matches everyone else's.

CREATE ROLE 'app_rw', 'report_ro';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'app_rw';
GRANT SELECT ON school.* TO 'report_ro';

CREATE USER 'school_app'@'127.0.0.1' IDENTIFIED BY 'Gw#App-2026-xT7!pQ'
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;   -- lock for 1 day after 5 wrong passwords
CREATE USER 'report_user'@'127.0.0.1' IDENTIFIED BY 'Gw#Rpt-2026-kq9!Lm';

GRANT 'app_rw' TO 'school_app'@'127.0.0.1';
GRANT 'report_ro' TO 'report_user'@'127.0.0.1';

-- Roles are NOT active at login unless you say so:
SET DEFAULT ROLE ALL TO 'school_app'@'127.0.0.1', 'report_user'@'127.0.0.1';

That last line trips up many people. A granted role is inactive until it is set as a default role (or activated with SET ROLE), so without it the app logs in and gets "command denied" on everything. To see what an account can do, use SHOW GRANTS, and add USING to expand the role's privileges:

SHOW GRANTS FOR 'school_app'@'127.0.0.1';
-- GRANT USAGE ON *.* TO `school_app`@`127.0.0.1`
-- GRANT `app_rw`@`%` TO `school_app`@`127.0.0.1`

SHOW GRANTS FOR 'school_app'@'127.0.0.1' USING 'app_rw';
-- GRANT USAGE ON *.* TO `school_app`@`127.0.0.1`
-- GRANT SELECT, INSERT, UPDATE ON `school`.* TO `school_app`@`127.0.0.1`
-- GRANT `app_rw`@`%` TO `school_app`@`127.0.0.1`

USAGE means "no privileges"; it just shows the account exists. Now connect as the app user and try the teammate's statement:

-- connected as school_app
SELECT CURRENT_USER(), CURRENT_ROLE();
-- school_app@127.0.0.1 | `app_rw`@`%`

UPDATE students SET class_section = '7-C' WHERE id = 2;   -- Query OK, 1 row affected

DELETE FROM students;
-- ERROR 1142 (42000): DELETE command denied to user 'school_app'@'localhost' for table 'students'
DROP TABLE students;
-- ERROR 1142 (42000): DROP command denied to user 'school_app'@'localhost' for table 'students'
SELECT * FROM mysql.user;
-- ERROR 1142 (42000): SELECT command denied to user 'school_app'@'localhost' for table 'user'

-- connected as report_user
UPDATE fee_payments SET amount = 0 WHERE id = 1;
-- ERROR 1142 (42000): UPDATE command denied to user 'report_user'@'localhost' for table 'fee_payments'

In Node.js, mysql2 reports these with err.code === 'ER_TABLEACCESS_DENIED_ERROR' and err.errno === 1142. The error shows 'localhost' rather than '127.0.0.1' because MySQL prints the client host name it resolved for the connection, while CURRENT_USER() shows the account that matched. Taking a privilege away is the mirror image:

REVOKE INSERT ON school.* FROM 'app_rw';
SHOW GRANTS FOR 'app_rw';
-- GRANT USAGE ON *.* TO `app_rw`@`%`
-- GRANT SELECT, UPDATE ON `school`.* TO `app_rw`@`%`

A few more habits that make least privilege real: give every human their own named account so the logs show who did what; use a separate migration account for schema changes; and for anyone running ad-hoc SQL on production, turn on safe-update mode, which refuses UPDATE/DELETE without a key in the WHERE (the mysql command-line client has a --safe-updates option that does the same):

SET SESSION sql_safe_updates = 1;
DELETE FROM school.fee_payments;
-- ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
-- without a WHERE that uses a KEY column.

Consider what happens when a support staff member's login is stolen. With roles set up properly, the attacker sees what support can see, which is bad but contained. With one shared all-powerful account, the attacker gets payments, parent phone numbers and the ability to delete everything. That difference is the whole point of least privilege.

Password Policy for MySQL Accounts

MySQL can refuse weak passwords for its own accounts through the validate_password component. It is not always installed by default; on this server it was installed with one statement, and its default policy (MEDIUM) requires at least 8 characters with a number, an upper-case letter, a lower-case letter and a special character:

INSTALL COMPONENT 'file://component_validate_password';

SHOW VARIABLES LIKE 'validate_password.%';
-- validate_password.length             | 8
-- validate_password.mixed_case_count   | 1
-- validate_password.number_count       | 1
-- validate_password.special_char_count | 1
-- validate_password.policy             | MEDIUM
-- validate_password.check_user_name    | ON
-- (plus changed_characters_percentage and dictionary_file)

CREATE USER 'school_app'@'127.0.0.1' IDENTIFIED BY 'school123';
-- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

SELECT VALIDATE_PASSWORD_STRENGTH('school123') AS weak,
       VALIDATE_PASSWORD_STRENGTH('Gw#Rpt-2026-kq9!Lm') AS strong;
-- 50 | 100

Beyond the policy: use long random passwords for application accounts (a password manager or openssl rand -base64 24 can generate them), keep them in environment variables or a secrets manager rather than in Git, rotate them when someone leaves, and use FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME to slow down guessing. Do not confuse this with your users' passwords (parents, teachers): those belong in your own table as bcrypt or Argon2 hashes, never as plain text, as shown in the password hashing post.

SQL Injection, in One Paragraph

SQL injection happens when user input is glued into a SQL string, so input such as ' OR 1=1 -- changes the meaning of the query. The fix is the same everywhere: send values separately with placeholders (?) so the database treats them as data, never as SQL. mysql2 escapes the value for you:

// Never: "SELECT id FROM students WHERE admission_no = '" + input + "'"
const [rows] = await pool.query('SELECT id FROM students WHERE admission_no = ?', [input]);

console.log(mysql.format('SELECT id FROM students WHERE admission_no = ?', ["' OR 1=1 -- "]));
// SELECT id FROM students WHERE admission_no = '\' OR 1=1 -- '

The quote is escaped, so the attacker's text is just a strange admission number that matches nothing. Least privilege is your second line of defence: even if an injection bug slips through, an app account without DELETE or DROP limits what it can do. The full treatment, including NoSQL injection and testing, is in the Node.js API Security post.

Encryption in Transit: TLS

Data travels between your app server and MySQL over the network. Without TLS (the same encryption HTTPS uses), anyone who can watch that network can read queries, results and even login exchanges. MySQL 8 supports TLS out of the box and generates self-signed certificates when the data directory is initialised; this test server had them and allowed TLS 1.2 and 1.3.

You can make TLS mandatory per account with REQUIRE SSL, or for the whole server with require_secure_transport = ON. A client that connects without TLS is then refused:

ALTER USER 'report_user'@'127.0.0.1' REQUIRE SSL;

-- mysql2 without the ssl option:
// ER_ACCESS_DENIED_ERROR Access denied for user 'report_user'@'localhost' (using password: YES)

-- with ssl: { rejectUnauthorized: false } (test only, see below):
SHOW SESSION STATUS WHERE Variable_name IN ('Ssl_version', 'Ssl_cipher');
-- Ssl_cipher  | TLS_AES_256_GCM_SHA384
-- Ssl_version | TLSv1.3

SHOW VARIABLES WHERE Variable_name IN ('require_secure_transport', 'tls_version');
-- require_secure_transport | OFF
-- tls_version              | TLSv1.2,TLSv1.3

Notice the error is a plain "Access denied", not "TLS required", so check REQUIRE SSL when a correct password is rejected. In production, do not use rejectUnauthorized: false, because it encrypts the traffic but does not check who you are talking to. Give mysql2 the server's CA certificate instead, for example ssl: { ca: fs.readFileSync('/etc/ssl/mysql-ca.pem') }, so a fake server cannot pretend to be your database. Managed cloud databases publish their CA bundle for this purpose.

Don't Expose Port 3306 to the Internet

MySQL listens on port 3306 by default. Automated scanners sweep the internet for open database ports constantly, then try common user names and passwords. Your database should be reachable only from the machines that need it:

  • Set bind_address to 127.0.0.1 if the app runs on the same machine, or to a private network address if it doesn't. (The test server ran with bind_address = 127.0.0.1.)
  • Use a firewall or cloud security group that allows port 3306 only from the app servers' private IPs. Never "0.0.0.0/0".
  • For your own admin access, connect through an SSH tunnel, a VPN or a bastion host, not by opening the port to your home IP "just for today".
  • Avoid accounts with host '%', and never leave root able to log in remotely.

Encryption at Rest

Encryption at rest protects data stored on disk: data files, logs and backups. If someone copies the disk or steals a backup file, encrypted data is unreadable without the key. MySQL's InnoDB engine supports tablespace encryption: each table's data file is encrypted with its own key, and those keys are protected by a master key held in a keyring (a component or plugin that stores the master key in a file, a key vault or a cloud key management service). The high-level steps are: install and configure a keyring component, then turn encryption on per table (ENCRYPTION = 'Y') or by default (default_table_encryption), and consider also encrypting binary logs and redo/undo logs, because they contain row data too.

Without a keyring, MySQL refuses, which is a good way to check your setup. This is the real error from the test server, which had no keyring configured:

ALTER TABLE school.students ENCRYPTION = 'Y';
-- ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log
-- if a keyring is loaded and initialized successfully.

The keyring set-up itself was not run here, because it depends on your server layout and key storage; follow the MySQL manual's keyring and InnoDB data-at-rest encryption pages for your version. On cloud-managed MySQL, storage encryption is usually a checkbox when the instance is created. The most important rule: never store the key next to the data it protects. A backup and its decryption key in the same folder is not encrypted in any useful sense.

What Should Be Encrypted?

Hashing and encryption are different tools. A hash is one-way: you can check a password against it but never get the password back. Encryption is two-way: with the key, you get the original value back. Use hashing for passwords and encryption for data you need to read later.

Data in a school appTreatment
Staff and parent login passwordsHash (bcrypt / Argon2), never encrypt or store plain
Aadhaar numbers, ID document scansEncrypt, restrict access, mask on screen (show last 4 digits); store only if you truly need them
Bank account details for fee refundsEncrypt; separate role for the accounts team
Medical notes, special-needs informationEncrypt; very limited access
Parent phone numbers and addressesAt-rest encryption of the whole database plus access control; mask in logs
Everything, on the wireTLS between app and database, HTTPS between users and app

Tablespace encryption protects against stolen disks and files, but anyone who can log in to MySQL still sees plain data. For the most sensitive columns, some teams also encrypt in the application before saving, so even a database admin sees only ciphertext; that adds key-management work, so reserve it for data that really needs it. India's Digital Personal Data Protection Act, 2023 also expects reasonable security safeguards for personal data, which is one more reason to collect less and protect what you keep.

Backup Security

Teams often lock down the production database and then leave a full, unencrypted copy of it in a shared folder or a public storage bucket. An attacker doesn't need to break into MySQL if a .sql file with every row is lying around. Treat backups as sensitive as the database itself:

  • Encrypt backup files before they leave the server, and keep the key elsewhere.
  • Use a dedicated backup account with read privileges only, not root.
  • Restrict who can read the backup storage, and make at least one copy hard to delete (write-once or versioned storage), so ransomware or a compromised admin account can't wipe production and backups together.
  • Never store backups in the web server's public folder or a Git repository.
  • Test restores regularly (Part 3); a protected backup that cannot be restored is still useless.

An Audit Trail Table

When a fee amount changes, someone will ask: who changed it, when, and from what to what? Without a record, nobody knows. A simple, portable approach is an audit_log table filled by triggers, so every change is recorded even if it is made outside the app. The app sets a session variable with the logged-in staff member, and the trigger stores both the MySQL account and that name:

CREATE TABLE audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(64) NOT NULL,
  row_id INT NOT NULL,
  action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  db_user VARCHAR(288) NOT NULL,
  app_user VARCHAR(80) NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_fee_update AFTER UPDATE ON fee_payments FOR EACH ROW
  INSERT INTO audit_log (table_name, row_id, action, old_value, new_value, db_user, app_user)
  VALUES ('fee_payments', OLD.id, 'UPDATE',
          JSON_OBJECT('amount', OLD.amount), JSON_OBJECT('amount', NEW.amount),
          USER(), @app_user);

CREATE TRIGGER trg_fee_delete AFTER DELETE ON fee_payments FOR EACH ROW
  INSERT INTO audit_log (table_name, row_id, action, old_value, db_user, app_user)
  VALUES ('fee_payments', OLD.id, 'DELETE',
          JSON_OBJECT('student_id', OLD.student_id, 'amount', OLD.amount, 'paid_on', OLD.paid_on),
          USER(), @app_user);

-- as school_app, on behalf of a logged-in accounts staff member:
SET @app_user = 'accounts.meena';
UPDATE fee_payments SET amount = 2000 WHERE id = 1;

SELECT table_name, row_id, action, old_value, new_value, db_user, app_user FROM audit_log;
-- fee_payments | 1 | UPDATE | {"amount": 2500} | {"amount": 2000} | school_app@localhost | accounts.meena

Two cautions. A trigger runs with the privileges of the account that created it, so the app does not need any rights on audit_log itself; grant the app role privileges on the business tables by name rather than on school.*, so it cannot edit or delete the log directly. And because anyone with enough privileges can still drop a trigger or change the table, ship important audit records somewhere the database users cannot change. And a delete trigger that stores old values is also a small safety net: it would have recorded every row of the evening's DELETE. For server-wide auditing of logins and statements, MySQL Enterprise Edition and some MySQL distributions offer audit log plugins.

Security in Layers

LayerQuestion it answersMySQL tools
NetworkWho can even reach the server?bind_address, firewall / security group, no public 3306
AuthenticationWho are you?Named accounts per host, validate_password, FAILED_LOGIN_ATTEMPTS
AuthorizationWhat may you do?Roles, GRANT/REVOKE, SHOW GRANTS
EncryptionCan stolen traffic or files be read?TLS (REQUIRE SSL), InnoDB tablespace encryption, encrypted backups
Audit and monitoringWho changed what, and when?Audit triggers or plugins, slow and general logs, alerts
BackupsCan we recover if everything else fails?mysqldump, physical backups, binary logs (Part 3)

No single layer is enough on its own. Each one catches some of what the others miss.

Part 3: Backup and Recovery

A database is the memory of an organisation. For a school it holds admissions, attendance, fee receipts and exam results; lose it, and fees cannot be reconciled, parents cannot be contacted and reports cannot be produced. A backup is a copy of that data you can use when something goes wrong, the same way your phone's photos are backed up because phones get lost, broken or stolen.

Most data loss does not come from hackers. It comes from everyday events:

CauseExample
Human errorDELETE or UPDATE without a WHERE, a migration run on the wrong server
Software bugsA release that overwrites the wrong column for every row
Hardware failureA disk dies; a server won't boot
CorruptionPower cut during writes on unreliable storage
Cyber attacksRansomware encrypts the server and any backups it can reach
Disasters and outagesFire, flood, or a whole cloud region going down

Back to that evening. After the DELETE, Deepak opens the backup folder. The nightly cron job has been creating a file every night, on the same server as the database. The newest file is 0 bytes: the job has been failing silently since a password change three months ago, and nobody ever tried to restore one. The last good dump is from before the term started. Every payment recorded since then exists only in paper receipts. The sections below are what Deepak sets up afterwards, and the walkthrough near the end shows how the same mistake could be undone in minutes.

Logical vs Physical Backups

There are two families of backup. A logical backup stores the data as SQL statements (CREATE TABLE ..., INSERT INTO ... VALUES ...) that rebuild the database when replayed. It is like keeping the recipe instead of the cake. A physical backup copies the actual InnoDB data files, which is like moving the whole house rather than photographing it and rebuilding from the photos.

LogicalPhysical
What it containsSQL text (or CSV/JSON-like chunks)Raw data files and logs
Toolsmysqldump, MySQL Shell dump utilitiesPercona XtraBackup (open source), MySQL Enterprise Backup, file-system or cloud volume snapshots, a copy of the data directory with the server stopped
Speed on large databasesSlow to create, much slower to restore (every row is re-inserted and indexes rebuilt)Fast to create and restore
Readable and editableYes, you can open it and restore one tableNo
PortabilityWorks across versions, operating systems and even other serversTied to MySQL version and configuration
Good forSmall and medium databases, migrations, copying one schema to stagingLarge databases where restore time matters

A note on tools that older articles mention. mysqlpump, a parallel version of mysqldump, was deprecated in MySQL 8.0.34 and removed in MySQL 8.4; it was not in the 8.4.9 binaries used here. For faster logical backups, use the MySQL Shell dump utilities (util.dumpInstance(), util.dumpSchemas(), util.dumpTables() and util.loadDump()), which dump and load in parallel. MySQL Shell was not installed on the test machine, so those were not run here.

A Real mysqldump and Restore

mysqldump ships with MySQL, and it was run here against the test server. The options matter more than the command itself:

# run from a shell, not inside mysql
mysqldump --host=127.0.0.1 --port=3306 --user=backup_user -p \
  --single-transaction --routines --triggers --events --source-data=2 \
  --databases school > school_full.sql
  • --single-transaction takes a consistent snapshot of InnoDB tables without locking them, so the school app keeps working during the backup.
  • --routines --triggers --events include stored procedures, triggers (like the audit triggers) and scheduled events. Forgetting these is a common reason a "restored" app behaves differently.
  • --source-data=2 writes the binary-log position at the moment of the snapshot as a comment. You need it for point-in-time recovery below. (Older articles call this --master-data; that name is deprecated.)
  • --databases school adds CREATE DATABASE and USE statements, so the file restores into a database of the same name.

These are the interesting lines from the 2,624-byte dump file created in the test:

-- MySQL dump 10.13  Distrib 8.4.9, for Win64 (x86_64)
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=1191;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
INSERT INTO `fee_payments` VALUES (1,1,2500.00,'2026-06-05'),(2,2,2500.00,'2026-06-07'),(3,3,3000.00,'2026-06-09');
-- Dump completed on 2026-11-03 10:41:07

Restoring is the reverse: feed the file to the mysql client. In the test the database was dropped and rebuilt from the file, and all three rows came back:

mysql --host=127.0.0.1 --port=3306 --user=root -p < school_full.sql

SELECT COUNT(*) FROM school.fee_payments;   -- 3

In real use, compress and encrypt the dump on its way to disk, for example by piping it through gzip and then an encryption tool such as gpg or openssl, and copy it off the server. Those pipelines were not run here because the exact commands depend on your key set-up.

Full, Incremental and Differential Backups

A full backup copies everything: every table, every row. It is the simplest to restore and the biggest to store. An incremental backup stores only what changed since the previous backup of any kind; it is small and fast, but a restore needs the full backup plus every incremental in order. A differential backup stores everything changed since the last full backup; each one grows during the week, but a restore needs only the full backup plus the latest differential.

In MySQL, the most common "incremental" is the binary log. The binlog records every change made to the data, in order, and it is turned on by default in MySQL 8 (this test server had log_bin = ON and binlog_format = ROW). A nightly full backup plus the binary logs since then lets you rebuild the data to almost any moment. Physical tools like XtraBackup also offer true incremental backups of the data files.

System sizeA reasonable starting schedule
Small app, few changes per dayDaily full logical backup, kept 7–30 days, plus binary logs
Growing appDaily full backup, binary logs copied off the server every few minutes or hourly
Large or critical systemPhysical full backups (weekly or daily) with incrementals, continuous binary-log shipping, and a replica in another location

Binary logs are removed automatically after binlog_expire_logs_seconds (30 days by default). Copy them somewhere safe before then, and make sure your oldest full backup is not older than your oldest binary log, or there will be a gap you can't replay across.

Point-in-Time Recovery With the Binary Log: A Real Walkthrough

This is the exact sequence that would have saved Deepak's evening, run on the test server. A full dump was taken (it recorded binlog position 1191). Then two new payments were added, so they exist only in the binlog. Then the mistaken DELETE ran:

-- after the backup: new work that is NOT in the dump
INSERT INTO school.fee_payments (student_id, amount, paid_on)
VALUES (4,2500,'2026-06-10'), (5,1500,'2026-06-10');

-- the mistake
DELETE FROM school.fee_payments;          -- 5 rows affected
SELECT COUNT(*) FROM school.fee_payments; -- 0

Step 1: find the bad transaction in the binary log. SHOW BINLOG EVENTS lists events from the dump's position onwards. Each transaction starts with a GTID event (here Anonymous_Gtid, because GTIDs were off) and ends with an Xid commit:

SHOW BINLOG EVENTS IN 'binlog.000001' FROM 1191;
-- Pos  | Event_type     | End_log_pos | Info
-- 1191 | Anonymous_Gtid | 1270        | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
-- 1270 | Query          | 1341        | BEGIN
-- 1341 | Table_map      | 1406        | table_id: 134 (school.fee_payments)
-- 1406 | Write_rows     | 1475        | table_id: 134 flags: STMT_END_F    <- the two new payments
-- 1475 | Xid            | 1506        | COMMIT /* xid=51 */
-- 1506 | Anonymous_Gtid | 1585        | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
-- 1585 | Query          | 1656        | BEGIN
-- 1656 | Table_map      | 1721        | table_id: 134 (school.fee_payments)
-- 1721 | Delete_rows    | 1841        | table_id: 134 flags: STMT_END_F    <- the DELETE
-- 1841 | Xid            | 1872        | COMMIT /* xid=52 */

With row-based logging the binlog stores the changed rows, not the SQL text. mysqlbinlog can decode them into readable pseudo-SQL so you can confirm which transaction is the mistake (this output is from a second, smaller run, so its positions differ):

mysqlbinlog --read-from-remote-server --host=127.0.0.1 --user=root -p \
  --base64-output=DECODE-ROWS --verbose --start-position=1168 binlog.000001

# at 1383
#261103 10:41:29 server id 1  end_log_pos 1452 CRC32 0xd2e5dd35  Delete_rows: table id 106 flags: STMT_END_F
### DELETE FROM `school`.`fee_payments`
### WHERE
###   @1=1
###   @2=1
###   @3=2500.00
###   @4='2026:06:05'
### DELETE FROM `school`.`fee_payments`
### WHERE
###   @1=2
###   ...

The @1, @2 numbers are column positions (id, student_id, amount, paid_on).

Step 2: restore the full backup. In real life, restore into a separate server or a new database first, so you don't make things worse on production. Here the database was dropped and rebuilt from the dump: 3 rows.

Step 3: replay the binary log from the dump's position up to, but not including, the bad transaction. The DELETE transaction starts at position 1506, so that is the stop position:

mysqlbinlog --read-from-remote-server --host=127.0.0.1 --user=root -p \
  --start-position=1191 --stop-position=1506 binlog.000001 | mysql --host=127.0.0.1 --user=root -p

SELECT * FROM school.fee_payments ORDER BY id;
-- 1 | 1 | 2500.00 | 2026-06-05
-- 2 | 2 | 2500.00 | 2026-06-07
-- 3 | 3 | 3000.00 | 2026-06-09
-- 4 | 4 | 2500.00 | 2026-06-10     <- recovered from the binlog
-- 5 | 5 | 1500.00 | 2026-06-10     <- recovered from the binlog

All five payments are back: three from the dump and two replayed from the binary log, with the DELETE skipped. If you know the time of the mistake rather than the position, --stop-datetime works too, but positions (or GTIDs, on servers with gtid_mode=ON) are more precise when several transactions happen in the same second. If the binlogs had lived only on the same disk as the database, a disk failure would have taken them too, which is why they need to be copied off the server.

A side note for MySQL 8.4: SHOW MASTER STATUS has been replaced by SHOW BINARY LOG STATUS, which is what you use to see the current binlog file and position.

Where to Store Backups: The 3-2-1 Rule

A backup on the same server as the database dies with that server. Deepak's 0-byte files were on the same disk, so even a good file would have been lost in a disk failure. The widely used 3-2-1 rule says: keep 3 copies of your data (production plus two backups), on 2 different types of storage, with 1 copy off-site. For a small school app that could be the production database, a backup on a separate backup server or volume, and an encrypted copy in cloud object storage in another region. Many teams now add "one copy immutable", meaning it can't be changed or deleted for a set period, as protection against ransomware that hunts for backups.

RPO and RTO in Simple Words

Two numbers turn "we should have backups" into a plan. RPO (Recovery Point Objective) answers "how much data can we afford to lose?" RTO (Recovery Time Objective) answers "how long can we be down?"

Suppose the school only takes a backup at 10:00 every morning, and the server dies at 10:45. Everything entered between 10:00 and 10:45 is gone, so the effective RPO is up to 24 hours of data, which might mean a whole day of fee receipts. Adding binary logs copied off the server every five minutes brings the worst-case loss down to about five minutes. If the restore of a large logical dump takes three hours, the RTO is at least three hours; if the school needs the fee counter working within 30 minutes, it needs a faster method, such as a physical backup or a standby replica. Agree on these two numbers with the people who run the school, then choose tools that meet them.

Test Your Restores

A backup you have never restored is a hope, not a backup. Files can be empty, as Deepak found, or corrupted, missing a database, missing triggers, or encrypted with a key nobody can find. Schedule a restore test, at least monthly and after any backup change, into a separate server, and check:

  • Does the restore finish without errors, and how long does it take? (That is your real RTO.)
  • Are all databases and tables present? Compare row counts of key tables with production at backup time.
  • Are stored routines, triggers, events and users present?
  • Does the app start against the restored copy, and does a known record (a specific fee receipt) look right?
  • Can you replay binary logs on top, as in the walkthrough?

Automate the boring part. A script that restores last night's dump into a scratch server, compares row counts and alerts on failure turns "we think backups work" into "we checked this morning".

Disaster Scenarios and the Matching Recovery

What happensRecovery
Someone deletes a table or rows by mistakeRestore the latest full backup on a separate server, replay binlogs up to just before the mistake, then copy the lost rows back
The server's disk failsPromote a replica if you have one; otherwise build a new server from the latest backup plus off-server binlogs
Ransomware encrypts the serverDisconnect, rebuild a clean environment, restore from an off-site or immutable backup the attacker could not reach; change all credentials
A whole cloud region is unavailableFail over to a replica or restore off-site backups in another region; this only works if you planned and tested it beforehand

Automate and Monitor Backups

Manual backups get forgotten during exam season. Schedule them (cron on Linux, Task Scheduler on Windows, or your cloud provider's automated backups) and make the job loud when it fails. A cron entry like the one below runs a backup script at 2:00 AM every day; it was not run here, since cron is not part of the test set-up:

# m h dom mon dow  command
0 2 * * * /opt/school/backup.sh >> /var/log/school-backup.log 2>&1

Then monitor the result, not just the schedule: did the job run, did it exit with success, is the file larger than a sensible minimum (a 0-byte dump should raise an alarm), did the off-site copy arrive, and did this month's restore test pass? A backup system that fails silently is how a 0-byte file sits unnoticed for three months.

Common Mistakes (and the Fix)

MistakeWhat goes wrongFix
SELECT *, no pagination, deep OFFSET pagesExtra bytes and rows on every request; the last pages are the slowestSelect only needed columns; LIMIT every list; keyset pagination for large or scrolling lists
Guessing about indexes and never reading EXPLAINMissing indexes, or indexes disabled by YEAR(col), a number vs text comparison or LIKE '%x%'Turn on the slow query log, run EXPLAIN ANALYZE, fix the query, confirm with EXPLAIN
N+1 queries and a new connection per requestHundreds of round trips per page; "Too many connections" under loadOne JOIN or IN (...) query; one shared connection pool sized below max_connections
Expecting MySQL's query cache to helpIt was removed in MySQL 8.0; query_cache_size no longer existsCache in the app or in Redis, with expiry and invalidation on writes
App connects as root; shared admin passwords; port 3306 open to the internetOne leaked password or one wrong query can read or delete everything, and nobody knows who did itRoles with least privilege, named accounts, validate_password, TLS, private network only
Backups on the same server, unencrypted, never restored, not monitoredThe backup disappears with the server, leaks, or turns out to be empty on the day you need it3-2-1 with encryption, binlogs copied off-server, scheduled restore tests and alerts

My Opinion

Turn on the slow query log before you need it. Set long_query_time to around 0.5–1 second on every production server from day one. It costs almost nothing, and when the "it's slow" complaint arrives you will have evidence instead of guesses.

Read EXPLAIN by row counts, not by milliseconds. Timings change with hardware and cache; "reads 20,000 rows to return 417" does not. If rows is much larger than the result, the query needs work, whatever the stopwatch says today.

Never let an application connect as root. Create one role per job on the first day, even for a hobby project. It takes five minutes, and it turns "a bug deleted everything" into "a bug got a permission error".

Prefer keyset pagination for anything that scrolls. OFFSET is fine for small admin tables with page numbers. For feeds, exports and large lists, keyset pagination stays fast at any depth and doesn't repeat rows when new data arrives.

Judge your backups only by your last successful restore. Write down your RPO and RTO, keep binary logs off the server, and schedule a restore test. A backup strategy that has never been tested should be treated as no strategy at all.

Try It Yourself

1. EXPLAIN for a query shows: type ALL, key NULL, rows 19560, Extra "Using where;
   Using filesort". The query is:
     SELECT id, full_name FROM students WHERE class_section = '9-A' ORDER BY full_name;
   What single index fixes both problems?
2. A search box runs WHERE full_name = 'Rice Traders' on a 5,00,000-row table with
   no index on full_name. What should you add, and what will EXPLAIN's type become?
3. Why does WHERE YEAR(paid_on) = 2026 not use an index on paid_on? Rewrite it.
4. An API returns page 900 of a student list with LIMIT 20 OFFSET 17980. Roughly how
   many rows does MySQL read? Rewrite it as keyset pagination, assuming the previous
   page ended at id 17980 and the list is sorted by id.
5. A loop runs one query per order to fetch its customer. For 100 orders, how many
   queries run in total, and what are two ways to make it 1 or 2?
6. Your app logs in as school_app with SELECT, INSERT and UPDATE on school.*. What
   happens when it runs DELETE FROM students; and what error code does mysql2 give?
7. Which is safer for a user's login password: storing it encrypted, or storing a
   bcrypt hash? Why?
8. Full backup every Sunday at 2 AM, binary logs copied every 5 minutes. The database
   is lost on Wednesday at 3:12 PM. What do you restore, and about how much data
   could be lost (RPO)?
Show answers
1. CREATE INDEX idx_class_name ON students (class_section, full_name);
   class_section matches the WHERE and full_name matches the ORDER BY, so type
   becomes ref and "Using filesort" disappears.
2. An index on full_name. type changes from ALL to ref (an exact match on a
   non-unique index).
3. MySQL can't look up the result of a function in an index built on the raw column.
   WHERE paid_on >= '2026-01-01' AND paid_on < '2027-01-01'
4. About 18,000 rows (offset + limit = 17,980 + 20).
   SELECT ... WHERE id > 17980 ORDER BY id LIMIT 20;
5. 101 queries (1 + 100). Use one JOIN between orders and customers, or two queries:
   the orders, then customers WHERE id IN (...) and match them in code.
6. It fails with ERROR 1142 (42000): DELETE command denied to user ... for table
   'students'. mysql2 sets err.code to 'ER_TABLEACCESS_DENIED_ERROR' (errno 1142).
7. A bcrypt hash. A hash is one-way, so a stolen database does not reveal passwords;
   encrypted passwords can be decrypted by anyone who also gets the key.
8. Restore Sunday's full backup, then replay all binary logs from the backup's
   position up to the failure (or just before a bad statement). Worst case, about
   5 minutes of changes since the last binlog copy are lost.

Frequently Asked Questions

Should I add an index to every column used in a WHERE?

No. Add indexes for the queries that actually run often or show up in the slow query log, and prefer one well-designed composite index over several single-column ones. Every index slows down inserts and updates and uses memory. Check with EXPLAIN that each new index is used, and remove indexes that stay unused over a full business cycle.

Is EXPLAIN ANALYZE safe to run on production?

It runs the query for real, so it costs as much as the query itself. Plain EXPLAIN or EXPLAIN FORMAT=TREE is always safe because it only plans. Use EXPLAIN ANALYZE on a replica or a copy of production data when the query is heavy, and avoid it on statements that change data.

If I use a managed cloud database, do I still need to think about backups?

Yes, but less about the mechanics. Managed services usually take automatic snapshots and keep binary logs for point-in-time restore within a retention window. You still need to set that window to match your RPO, keep a copy outside the same account or region, protect who can delete backups, and test a restore. Accidental deletes are replicated to standby servers instantly, so high availability is not a backup.

Can't I just rely on a replica instead of backups?

No. A replica copies every change, including a mistaken DELETE, within seconds. Replicas protect you from hardware failure and help with read load; backups plus binary logs protect you from mistakes, bugs and attacks. You need both for a critical system.

Key Takeaways

  • Most slow databases are fixed with basics: select only needed columns, add the right composite index, paginate, remove N+1 loops and reuse connections through a pool.
  • Read EXPLAIN by type, key, rows, filtered and Extra; use EXPLAIN ANALYZE to compare estimated and actual rows. Functions on columns, number-vs-text comparisons, leading wildcards and skipping a composite index's first column all disable indexes.
  • Keyset pagination reads about one page of rows at any depth; OFFSET reads everything it skips.
  • MySQL 8 has no query cache. Cache in the application or Redis, and find slow queries with the slow query log, performance_schema and the sys views.
  • Security on the database side means least-privilege roles, named accounts, validate_password, TLS, no public port 3306, encryption at rest with the key kept away from the data, protected backups and an audit trail.
  • Use logical backups (mysqldump, MySQL Shell) for small and medium databases and physical backups for large ones; mysqlpump is gone in 8.4.
  • A full backup plus off-server binary logs gives point-in-time recovery; follow 3-2-1, define RPO and RTO, and test restores on a schedule.

Next, see how these database pieces fit into a complete backend in Backend Developer Interview Questions & Complete Node.js Backend Flow Explained.

References

Related Articles

Post a Comment

0 Comments