Master‑Slave MySQL Replication & Query Optimization: A Step‑by‑Step Guide

1. Master‑Slave Replication Deployment

Concept : Master‑slave replication uses two or more MySQL servers; the master handles writes while slaves handle reads. The master writes all changes to the binary log (binlog) and streams it to slaves, which replay the log to stay in sync.

Enable binlog by editing /etc/my.cnf and restarting MySQL:

vim /etc/my.cnf
service mysql restart

After restart, the mysql-bin files appear in /usr/local/mysql/var. Use show master status; to view the latest binlog file and position: mysql> show master status; Inspect the binlog with:

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001

Typical commands for binlog management: flush logs – creates a new binlog file. reset master – removes all binlog files except the first. mysqlbinlog – reads a binlog file. show binlog events – lists events in a binlog.

Create a replication user on the master :

CREATE USER 'kang'@'192.168.206.132' IDENTIFIED BY 'smudge';
GRANT ALL PRIVILEGES ON *.* TO 'kang'@'192.168.206.132';

Master configuration (IP 192.168.206.128): edit /etc/my.cnf, then flush logs with a read lock to obtain a consistent snapshot, or create a dump with:

mysqldump -uroot -psmudge smudge -l -F > /home/smudge.sql

Transfer the dump to the slave using scp: scp /home/smudge.sql 192.168.206.132:/home Slave configuration : create the database, import the dump, and edit /etc/my.cnf with the same credentials. Restart MySQL, then start replication (MySQL 5.7 requires CHANGE MASTER TO):

CHANGE MASTER TO MASTER_HOST='192.168.206.128', MASTER_USER='kang', MASTER_PASSWORD='smudge', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

Verify with SHOW SLAVE STATUS\\G. Common slave commands include START SLAVE, STOP SLAVE, SHOW MASTER LOGS, CHANGE MASTER TO …, and SHOW PROCESSLIST. Troubleshoot by checking Slave_IO_Running and Slave_SQL_Running values; if they are NO or NULL, stop the slave, adjust MASTER_LOG_FILE and MASTER_LOG_POS, then restart.

2. Query Performance Optimization

Query execution flow :

Client sends query to server.

Server checks query cache; if hit, returns cached result.

Server parses, optimizes, and creates an execution plan.

Execution engine calls the storage engine API.

Server sends results back to client.

The MySQL client/server protocol is half‑duplex, which limits simultaneous send/receive and can cause latency; using LIMIT helps reduce data transferred.

Use SHOW FULL PROCESSLIST to see connection states (sleep, query, locked, etc.). The SHOW PROCESSLIST output helps identify long‑running queries.

MySQL records three key metrics in the slow‑query log: execution time, rows examined, and rows returned. Analyze them with tools like mysqlsla.

Typical scan types (ordered by cost): full table scan, index scan, range scan, unique index lookup, constant. Prefer covering indexes (type index) to avoid reading full rows.

Common causes of poor performance:

Fetching more rows or columns than needed.

Server examining unnecessary rows.

Mitigations include adding LIMIT, selecting only required columns, using covering indexes, redesigning schema with summary tables, and rewriting complex queries.

Break large queries into smaller batches, e.g. delete old records in chunks:

DELETE FROM message WHERE create < date_sub(now(), INTERVAL 3 MONTH) LIMIT 1000;

Decompose joins into separate queries and join results in application code to reduce lock contention, especially with MyISAM tables.

Use foreign keys (InnoDB only) to enforce referential integrity; example tables student and student_extend with a foreign key on student_extend.id referencing student.id. Deleting a row from student cascades to student_extend if defined.

Additional optimizations:

Optimize MAX() / MIN() queries by adding indexes on the target column.

Avoid mixing SELECT and UPDATE on the same table in a single transaction when possible.

These steps provide a practical roadmap for setting up reliable MySQL replication and improving query performance.