Top MySQL Interview Q&A: Replication, InnoDB, Optimization & Backup

1. MySQL Replication Principles and Process

Basic principle and flow involve three threads and their relationships.

Master: binlog thread records all statements that modify data into the master’s binlog.

Slave IO thread pulls binlog content from the master after START SLAVE and stores it in the relay log.

Slave SQL thread executes statements from the relay log.

2. Differences Between MyISAM and InnoDB (at least 5 points)

InnoDB supports transactions; MyISAM does not.

InnoDB uses row‑level locking; MyISAM uses table‑level locking.

InnoDB supports MVCC; MyISAM does not.

InnoDB supports foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB does not.

InnoDB’s four major features: insert buffer, doublewrite, adaptive hash index, read‑ahead.

MyISAM is faster for SELECT COUNT(*) because it maintains an internal row counter.

3. VARCHAR vs CHAR and Meaning of VARCHAR(50)

CHAR is fixed‑length; VARCHAR is variable‑length.

VARCHAR(50) can store up to 50 characters; the storage size is the actual length plus one or two bytes for length information.

INT(20) defines display width, not storage size; it still occupies 4 bytes.

These designs are mainly for display formatting and have no effect on storage or calculation.

4. InnoDB Transaction and Log Implementation

Log types: error log, general query log, slow query log, binary log, relay log, transaction log.

Four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Transactions are implemented via redo logs and the InnoDB log buffer; on commit the log buffer is flushed to disk (pre‑write logging).

5. MySQL Binlog Formats and Their Differences

Statement format records each SQL statement that modifies data.

Row format records the actual row changes.

Mixed format combines both, using statement format when possible and row format for statements that require it.

Each format has trade‑offs in log size, performance, and compatibility with certain statements.

6. Handling CPU Spikes to 500% in MySQL

List processes with SHOW PROCESSLIST, identify long‑running or idle queries, and terminate them.

Check error and timeout logs; high CPU is often caused by large queries or batch inserts.

7. SQL Optimization

Explanation of EXPLAIN output items: select_type, type, possible_keys, key, key_len, ref, Extra.

Profiling shows execution time, CPU/memory usage, and lock wait times.

8. Backup Strategy, mysqldump and XtraBackup Principles

Backup plans vary by organization.

Typical backup times: 20 GB in 2 min (mysqldump), 80 GB in 30 min, 111 GB in 30 min, 288 GB in 3 h (xtrabackup), 3 TB in 4 h (xtrabackup).

XtraBackup works by replaying InnoDB redo logs and rolling back uncommitted transactions during recovery.

9. mysqldump Options for One‑Row‑Per‑INSERT and Including Master Position

mysqldump -uroot -p database_name --skip-extended-insert

Use --master-data to embed the binary log position.

10. Restarting 500 DB Instances Quickly

Tools such as Puppet or dsh can orchestrate parallel restarts.

11. InnoDB Read/Write Parameter Optimization

Read parameters: global and local buffer pools.

Write parameters: innodb_flush_log_at_trx_commit, innodb_buffer_pool_size.

IO‑related parameters: innodb_write_io_threads, innodb_read_io_threads, innodb_thread_concurrency.

Cache parameters: query cache settings, suitable for read‑heavy workloads.

12. Database Monitoring and Slow‑Query Log Analysis

Common tools include Zabbix and Lepus; slow‑query logs can be examined to identify performance bottlenecks.

13. Master‑Slave Consistency Verification

Tools such as checksum, mysqldiff, and pt‑table‑checksum are used.

14. Emoji Support in MySQL

UTF‑8 must be upgraded to utf8mb4 to store emoji characters.

15. Maintaining the Database Data Dictionary

Common practice is to add comments directly in production tables and export them for documentation.

16. Development Standards Enforcement

Many teams adopt publicly available coding standards and enforce them through code reviews.

17. Handling Large Text Columns (Read‑Heavy)

Splitting into a separate table reduces join overhead but increases storage and complexity; keeping it together simplifies queries but may affect performance.

18. InnoDB Row Lock Implementation

Row locks are based on indexed columns; without an index, InnoDB falls back to table locks.

19. Restoring a Single Database or Table from a mysqldump Backup

Use sed or grep to extract the desired statements, or restore the full dump and drop unwanted objects.

20. Querying a Subset of Rows from Two Large Tables

When the join key is indexed, use a range condition with LIMIT or a subquery to fetch the required offset range efficiently.