Comprehensive MySQL and Linux Operations Interview Guide

This document collects typical interview questions and concise answers for operations engineers, focusing on MySQL security, replication, and administration.

MySQL security after a fresh installation : change the default port, restrict access with iptables, enforce complex passwords for all users, limit root privileges to local login, enable binary and slow‑query logs, control permissions on the installation and data directories, and remove unused accounts and the default test database.

Master‑slave replication principle : the master writes changes to the binary log, the slave copies those events to its relay log, and the slave’s SQL thread replays the events to keep data identical. The basic process involves four steps – the slave IO thread fetches logs, the master provides log file name and position, the slave writes to the relay log, and the SQL thread executes the statements.

Advantages of replication : fast failover to a slave, load reduction on the master by off‑loading reads, and safe backup on the slave without affecting the master.

Basic configuration and user management : add a MySQL user with appropriate privileges, e.g.

mysql> grant select,insert,update,delete on book.* to test2@localhost identified by "abc";

General GRANT syntax:

mysql> GRANT <权限> ON <库>.<表> TO '用户'@'主机名' identified by "密码";

Backup script example (mount remote share, dump database, compress):

mount 192.168.1.1:/backup /mnt && /usr/local/mysql/bin/mysqldump -h localhost -uroot test > test.sql && tar czf test.sql.tar.gz test.sql && rm -f test.sql

Nginx log statistics script to obtain the top 10 IPs:

awk '{a[$1]++} END {for (j in a) print a[j], j}' /home/logs/nginx/default/access.log | sort -nr | head -10

Linux boot process : BIOS → GRUB (loads kernel and initrd) → kernel initialization → sysinit scripts.

Common ports and services : 21 FTP, 22 SSH, 23 Telnet, 25 SMTP, 110 POP3, 143 IMAP, 873 rsync, 3306 MySQL.

Virus handling on Linux : locate malicious files, use iftop to monitor traffic, netstat for connections, top or ps aux to find suspicious processes, delete with rm -f, check crontab and /etc/rc.local for persistence.

Monitoring tools experience : Nagios (state monitoring via NRPE, SNMP, alerts), Cacti (performance graphs via SNMP), Zabbix (open‑source, integrates Nagios and Cacti features).

Nginx optimization points : minimize HTTP usage, use CDN, add Expires/Cache‑Control headers, enable gzip compression, reduce redirects (prefer rewrite over return), cache AJAX responses, limit DNS queries, and tune the TCP stack.

InnoDB lock troubleshooting : run SHOW ENGINE INNODB STATUS, inspect information_schema tables innodb_trx, innodb_locks, and innodb_lock_waits.

Reducing replication lag : ensure slave hardware is not weaker than master, enable multi‑threaded replication, eliminate slow SQL, improve network latency, balance load, and adjust parameters such as --slave-net-timeout=seconds and --master-connect-retry=seconds.

High‑availability components and implementations : configuration services (Zookeeper, Consul, Etcd+Confd), front‑end load balancers (nginx, HAProxy, Keepalived, LVS), message queues (ActiveMQ, Kafka), read/write splitting middleware (Atlas), log analysis (ELK).

Data migration tools and services : mysqldump for MySQL, Redis master‑slave sync via slaveof master and slaveof no one, Elasticsearch‑Exporter for Elasticsearch.

Automation configuration management : Ansible (SSH‑based, agent‑less, limited Windows support) and SaltStack (ZeroMQ transport, requires agents, faster on large fleets).