MENU
Administration, Backup, Replication, and Partitioning
Most administration acts on the whole server, so this section runs two MySQL 9.7.2 containers of its own (Section 3.1.6): a source on port 3317 and a replica on 3417, with global transaction identifiers (GTIDs) on. Credentials sit in ~/.my.cnf ([client] with user, password and host=127.0.0.1, mode 600), so commands pass only the port. shop is loaded on the source.docker network create shop-net
docker run -d --name source --hostname source --network shop-net \
-v source-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=secret -p 3317:3306 \
mysql:9.7 --server-id=1 --gtid-mode=ON --enforce-gtid-consistency=ON
docker run -d --name replica --hostname replica --network shop-net \
-v replica-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=secret -p 3417:3306 \
mysql:9.7 --server-id=2 --gtid-mode=ON --enforce-gtid-consistency=ON --read-only=ON
3.17.1 SHOW, DESCRIBE, and the Information Schema
Since MySQL 8.0 every definition lives in a transactional data dictionary of hidden InnoDB tables. INFORMATION_SCHEMA puts standard SQL views over it, and SHOW TABLES, SHOW CREATE TABLE, SHOW INDEX, SHOW GRANTS, SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS are shortcuts over the same data. DESCRIBE (or DESC) is short for SHOW COLUMNS:+------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| email | varchar(255) | NO | UNI | NULL | |
| name | varchar(100) | NO | | NULL | |
| country | char(2) | NO | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
3.17.2 Table Maintenance: ANALYZE, CHECK, OPTIMIZE, and REPAIR
Four statements look after a table's physical storage, and InnoDB changes what three of them do:ANALYZE TABLE orders;
OPTIMIZE TABLE orders\G
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| shop.orders | analyze | status | OK |
+-------------+---------+----------+----------+
*************************** 1. row ***************************
Table: shop.orders
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
...
ANALYZE TABLE re-samples index statistics cheaply; UPDATE HISTOGRAM ON col builds a histogram (Section 3.11.6).
CHECK TABLE verifies pages and indexes; its MEDIUM, EXTENDED and CHANGED options apply only to MyISAM. CHECKSUM TABLE hashes the contents, which proves a restore (Section 3.17.5).
OPTIMIZE TABLE on InnoDB means ALTER TABLE ... FORCE, an online rebuild that reclaims the space of deleted rows (a second row reports OK). Keep it for tables that lost many rows.
REPAIR TABLE serves MyISAM, ARCHIVE and CSV only; on orders it answers "The storage engine for the table doesn't support repair". InnoDB corruption means restoring a backup.
3.17.3 Server Commands: FLUSH, RESET, KILL, and CLONE
FLUSH reopens server resources: FLUSH BINARY LOGS starts a new binary log (here binlog.000003), FLUSH ERROR LOGS reopens a file after logrotate (Section 1.9.6), and FLUSH TABLES WITH READ LOCK is the global read lock (Section 3.14.7).RESET discards state. MySQL 8.4 removed the master/slave spellings, which fail with ERROR 1064 on 9.7.2: RESET MASTER is now RESET BINARY LOGS AND GTIDS (it deletes every binary log and empties gtid_executed), SHOW MASTER STATUS is SHOW BINARY LOG STATUS, SHOW SLAVE STATUS is SHOW REPLICA STATUS, and CHANGE MASTER TO is CHANGE REPLICATION SOURCE TO.KILL ends a connection, or with QUERY just its statement; locking an account (Section 3.16.3) leaves its sessions running until you kill them. While a first session ran SELECT SLEEP(60):SELECT id, user, command, time, state, info FROM performance_schema.processlist
WHERE info LIKE 'SELECT SLEEP%';
KILL QUERY 20;
+----+------+---------+------+------------+------------------+
| id | user | command | time | state | info |
+----+------+---------+------+------------+------------------+
| 20 | root | Query | 2 | User sleep | SELECT SLEEP(60) |
+----+------+---------+------+------------+------------------+
3.17.4 The Error, General, Slow, and Binary Logs
MySQL keeps four logs you manage (redo and undo logs are InnoDB internals, Section 3.13.3):The error log is always on: startups, crashes, replication errors. The Docker image sends it to stderr (docker logs), Ubuntu to /var/log/mysql/error.log; SQL can read performance_schema.error_log.
The general query log records every statement. Too heavy to leave on, it is off by default, but a minute of general_log = ON shows exactly what an application sends.
The slow query log keeps statements slower than long_query_time (Section 3.13.8).
The binary log records every commit for recovery and replication (Sections 3.17.7-3.17.8). binlog_expire_logs_seconds purges files after 30 days, PURGE BINARY LOGS BEFORE ... sooner. A replica keeps received events in a relay log.
docker run -d --name old84 -v upgrade-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=secret mysql:8.4
docker stop old84 && docker rm old84 # once it has initialized
docker run -d --name new97 -v upgrade-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=secret mysql:9.7
docker logs new97 2>&1 | grep -i upgrad | cut -c 29-
...
1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80300' to '90200'.
1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80300' to '90200'
completed.
4 [System] [MY-013381] [Server] Server upgrade from '80411' to '90702' started.
4 [System] [MY-013381] [Server] Server upgrade from '80411' to '90702' completed.
3.17.5 Logical Backups with mysqldump and MySQL Shell
A logical backup is SQL or text that re-creates the data: portable across versions, slow to restore at scale. A physical backup copies InnoDB's files: fast, but only to a compatible server.mysqldump -P 3317 --single-transaction --source-data=2 --routines --events \
--databases shop > full.sql 2> dump.err; echo "exit status: $?"
grep -n -E "GTID_PURGED|CHANGE REPLICATION|^-- Dump completed" full.sql | cut -c 1-90
exit status: 0
24:SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'f8b4dc41-b72a-11f1-9c16-a282e0d51493:1-25';
30:-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=762;
260:-- Dump completed on 2026-09-23 16:56:44
...
1 out of 5 schemas will be dumped and within them 7 tables, 0 views.
...
Running data dump using 4 threads.
3.17.6 Loading Data with LOAD DATA and SELECT INTO OUTFILE
SELECT ... INTO OUTFILE writes rows to a delimited file on the server's disk, and LOAD DATA INFILE reads one back: the fastest bulk path. The caller needs the FILE privilege (never grant it to an application), and secure_file_priv confines both to one directory, /var/lib/mysql-files/ here; INTO OUTFILE '/tmp/p.csv' fails with ERROR 1290. The export wrote lines such as 7,"AC-MUG-01","Semicolon Coffee Mug",12.50, and the load computes a column with SET:SELECT id, sku, title, price FROM products WHERE price < 20
INTO OUTFILE '/var/lib/mysql-files/cheap.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
CREATE TABLE price_updates (sku VARCHAR(20) PRIMARY KEY, new_price DECIMAL(8,2));
LOAD DATA INFILE '/var/lib/mysql-files/cheap.csv' INTO TABLE price_updates
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(@id, sku, @title, @price) SET new_price = ROUND(@price * 1.05, 2);
SELECT * FROM price_updates;
LOAD DATA LOCAL INFILE '/tmp/new.csv' INTO TABLE price_updates FIELDS TERMINATED BY ',';
+-----------+-----------+
| sku | new_price |
+-----------+-----------+
| AC-MUG-01 | 13.12 |
| AC-STK-01 | 5.24 |
+-----------+-----------+
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the
client and server sides
3.17.7 Point-in-Time Recovery from the Binary Log
Point-in-time recovery restores the last full backup, then replays the binary log up to just before the damage. After the dump of Section 3.17.5 (GTIDs 1-25), the source took an order (26, 27), someone ran DROP TABLE reviews (28), and an order was shipped (29). Find the bad transaction:mysqlbinlog -P 3317 --read-from-remote-server --base64-output=DECODE-ROWS -v \
binlog.000003 > b3.txt
grep -B5 "DROP TABLE" b3.txt | cut -c 1-90 | expand
SET @@SESSION.GTID_NEXT= 'f8b4dc41-b72a-11f1-9c16-a282e0d51493:28'/*!*/;
# at 1441
#260923 16:56:47 server id 1 end_log_pos 1573 CRC32 0xb3c1539b Query thread_id=43
exec_t
SET TIMESTAMP=1790153807/*!*/;
SET @@session.pseudo_thread_id=43/*!*/;
DROP TABLE `reviews` /* generated by server */
mysqlbinlog -P 3317 --read-from-remote-server \
--exclude-gtids='f8b4dc41-b72a-11f1-9c16-a282e0d51493:28' binlog.000003 | mysql -P 3417
echo "exit status: $?"
mysql -P 3417 -t shop -e "SELECT id, status, ordered_at FROM orders WHERE id >= 9;
SELECT COUNT(*) AS reviews FROM reviews;"
exit status: 0
+----+---------+---------------------+
| id | status | ordered_at |
+----+---------+---------------------+
| 9 | shipped | 2026-09-02 08:45:00 |
| 10 | paid | 2026-09-23 09:10:00 |
+----+---------+---------------------+
+---------+
| reviews |
+---------+
| 7 |
+---------+
3.17.8 Setting Up Replication and GTIDs
In asynchronous replication the replica's I/O thread copies the source's binary log events into its relay log, and applier threads replay them. A GTID (server_uuid:number) names every transaction, so with SOURCE_AUTO_POSITION = 1 the replica tells the source which GTIDs it has and receives the rest. On the source: CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl-2026-pass' and GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'. The spare server was emptied with RESET BINARY LOGS AND GTIDS and seeded with util load-dump /backup/seed --updateGtidSet=replace from a fresh util dump-instance of the source (the loader needs local_infile = ON).CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'source', SOURCE_PORT = 3306,
SOURCE_USER = 'repl', SOURCE_PASSWORD = 'Repl-2026-pass',
SOURCE_AUTO_POSITION = 1, SOURCE_SSL = 1;
START REPLICA;
SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
...
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
...
Seconds_Behind_Source: 0
...
Executed_Gtid_Set: f8b4dc41-b72a-11f1-9c16-a282e0d51493:1-36,
f8f7601e-b72a-11f1-baf7-3a8d15598219:1-15
SELECT worker_id, last_applied_transaction,
TIMESTAMPDIFF(MICROSECOND, last_applied_transaction_original_commit_timestamp,
last_applied_transaction_end_apply_timestamp) / 1000000 AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker
ORDER BY last_applied_transaction_end_apply_timestamp DESC LIMIT 1;
+-----------+-----------------------------------------+-------------+
| worker_id | last_applied_transaction | lag_seconds |
+-----------+-----------------------------------------+-------------+
| 1 | f8b4dc41-b72a-11f1-9c16-a282e0d51493:39 | 3.0240 |
+-----------+-----------------------------------------+-------------+
3.17.9 Group Replication and InnoDB Cluster
Asynchronous replication loses commits the source had not yet sent when it dies, and failover is manual. Semisynchronous replication makes a commit wait for a replica's acknowledgment; Group Replication goes further: a Paxos-based protocol orders every member's transactions, a transaction commits once a majority agrees, certification rejects conflicting writes, and survivors elect a new primary within seconds. It needs InnoDB, a primary key on every table, GTIDs and row logging. A group has at most 9 members, and 3 is the smallest that survives losing one. Single-primary mode, the default, has one writable member.InnoDB Cluster packages it: MySQL Shell's AdminAPI builds and repairs the group (dba.createCluster('shop'), cluster.addInstance(...), cluster.status()), and MySQL Router, beside PHP-FPM, sends writes to the current primary and reads to the secondaries, so the application does not change during a failover. InnoDB ReplicaSet is the same tooling over asynchronous replication. A cluster needs three hosts and was not built here. For most LAMP sites a source, a replica and tested backups are enough; move to InnoDB Cluster or a managed service (Section 3.18) when downtime costs more than a third server.3.17.10 Partitioning by RANGE, LIST, HASH, and KEY
A partitioned table is one logical table stored as several InnoDB tablespaces; a function of each row picks its partition. RANGE (and RANGE COLUMNS) assigns value bands with VALUES LESS THAN, the usual choice for dates; LIST assigns explicit sets with VALUES IN, such as regions; HASH spreads rows by an integer expression modulo N; KEY does the same with MySQL's own hash of any columns. Every unique key must contain every column the function uses (otherwise ERROR 1503), and InnoDB refuses foreign keys on partitioned tables (ERROR 1506):CREATE TABLE order_history (
id INT UNSIGNED NOT NULL,
customer_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
ordered_at DATETIME NOT NULL,
PRIMARY KEY (id, ordered_at),
KEY (customer_id)
)
PARTITION BY RANGE (YEAR(ordered_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
Q="SELECT COUNT(*), SUM(total) FROM order_history WHERE"
for W in "ordered_at >= '2025-01-01' AND ordered_at < '2025-07-01'" "YEAR(ordered_at) = 2025"
do mysql -P 3317 -E shop -e "EXPLAIN FORMAT=TRADITIONAL $Q $W" | grep -E "partitions|rows"
done
partitions: p2025
rows: 58656
partitions: p2024,p2025,p2026,p2027,pmax
rows: 83106
ALTER TABLE order_history REORGANIZE PARTITION pmax INTO (
PARTITION p2027 VALUES LESS THAN (2028),
PARTITION pmax VALUES LESS THAN MAXVALUE);
ALTER TABLE order_history DROP PARTITION p2023;
ALTER TABLE order_history TRUNCATE PARTITION p2024;