MySQL Commands

MySQL tells you what went wrong with a number. That is genuinely useful once you know the map: 1044 and 1045 are access, 1064 is your SQL, 1050 and 1054 are schema drift, 2003 is the network, 1205 and 1213 are locks, and 1366 and 1406 are almost always the character set. This reference is organized by operational task, and every section ends where those numbers actually come from.

Connecting and the client

# Store credentials in a file, not in shell history or process listings
cat > ~/.my.cnf <<'EOF'
[client]
user=appuser
password=secret
host=db.internal
EOF
chmod 600 ~/.my.cnf

Users, grants and access

MySQL identities are user@host — both halves. 'app'@'%' and 'app'@'localhost' are two different accounts that can have different passwords and different privileges. Almost every access-denied puzzle starts here.

The access errors map cleanly onto this section:

• 1045 access denied — no grant row matched user@host, or the password/plugin is wrong.
• 1044 access denied for database — the account authenticated but has no privilege on that schema.
• 1142 command denied — connected and in the right database, but not allowed that operation on that table.
• 1130 host not allowed — no account exists for the host you connected from.
• 1129 host blocked — too many failed connects from one host; FLUSH HOSTS clears it, then find what is retrying.
• 1698 access denied for auth_socket — the account authenticates by OS user, not password.

Inspecting the server and its schema

-- Largest tables, with data and index split out
SELECT table_schema, table_name,
       ROUND(data_length /1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY data_length + index_length DESC
LIMIT 20;

-- Indexes on a table, with cardinality
SHOW INDEX FROM appdb.orders;

-- What the server is doing right now
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW ENGINE INNODB STATUS\G

table_rows from information_schema is an estimate for InnoDB, sometimes off by a wide margin. Use it for relative sizing, never for a count that matters.

Schema changes without locking the table

MySQL 8.0 can perform many ALTER TABLE operations instantly or in place; the ones it cannot will rebuild the whole table while blocking writes. Always state your intent explicitly rather than discovering it in production.

Even an instant ALTER needs a brief metadata lock, and it will queue behind any long-running transaction touching the table — with every subsequent query queuing behind it. Check for open transactions before starting.

Schema drift shows up as 1050 table already exists, 1054 unknown column, 1146 table doesn’t exist and 1215 cannot add foreign key — the last is almost always mismatched column types, charsets or a missing index on the referenced column.

Diagnosing slow queries

-- The plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

-- The plan WITH real execution counts (MySQL 8.0.18+, MariaDB 10.9+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- What is slow overall (needs performance_schema enabled)
SELECT digest_text, count_star,
       ROUND(sum_timer_wait/1e12, 2)  AS total_s,
       ROUND(avg_timer_wait/1e9,  2)  AS avg_ms,
       sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;

Sort by total time, not average: a 30ms query run three million times costs far more than a nightly report, and is usually much easier to fix.

The single most useful ratio is rows examined versus rows sent. A query returning 10 rows after examining 400,000 is doing the work in the wrong place, and no amount of hardware fixes it.

ANALYZE TABLE orders;   -- refresh statistics after a bulk load or restore

Connections, locks and deadlocks

The section you open during an incident.

-- Who is connected and what are they doing
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

-- Open InnoDB transactions, oldest first — the usual culprit
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_mysql_thread_id AS thread, LEFT(trx_query, 60) AS query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Who blocks whom (MySQL 8.0)
SELECT r.trx_mysql_thread_id AS waiting_thread,
       b.trx_mysql_thread_id AS blocking_thread,
       LEFT(r.trx_query, 60) AS waiting_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

Use KILL QUERY first. A KILL on a large uncommitted transaction triggers a rollback that can take longer than letting it finish, and the table stays locked throughout.

See 1205 lock wait timeout and 1213 deadlock found.

Connection exhaustion is the other half. MySQL uses a thread per connection, so raising max_connections trades one problem for memory:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SELECT @@max_connections, @@wait_timeout, @@interactive_timeout;

Hitting the ceiling gives 1040 too many connections; the answer is a pooler (ProxySQL, or the application’s own pool sized sanely) rather than a bigger number. Idle connections dropped by wait_timeout surface later as 2006 server has gone away or 2013 lost connection.

Character sets, collations and truncation

This is the MySQL-specific trap that costs the most time, and it has one root cause.

-- Where is the charset actually set?
SELECT table_name, table_collation
FROM information_schema.tables WHERE table_schema = 'appdb';

SELECT column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'appdb' AND character_set_name IS NOT NULL;

SHOW VARIABLES LIKE 'character_set_%';   -- server + connection side

-- Converting (rebuilds the table — treat as a migration, not a tweak)
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

sql_mode decides whether bad data is an error or a silent truncation. Strict mode is the right default — a loud 1406 data too long or 1292 incorrect datetime is far better than discovering months later that a column quietly lost half its values:

SELECT @@sql_mode;   -- expect STRICT_TRANS_TABLES among others

Related: 1264 out of range, 1062 duplicate entry, and 1055 only_full_group_by — the last is MySQL 8.0 correctly rejecting an ambiguous GROUP BY that older versions allowed.

Backups and recovery

Replication and Galera

-- MySQL 8.0.22+ (older: SHOW SLAVE STATUS)
SHOW REPLICA STATUS\G
-- Watch: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, Last_Error

-- MariaDB
SHOW SLAVE STATUS\G

-- Galera / wsrep cluster health
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_local_state_comment';   -- expect: Synced
SHOW STATUS LIKE 'wsrep_flow_control_paused';   -- >0 means writes are being throttled

Replication failures worth knowing: 1032 can’t find record means the replica’s data already diverged, and 1236 fatal error from source usually means the binlog the replica wants has been purged. Read-only replicas reject writes with 1290 read-only — which is the safety net working, not a bug. Deeper coverage: replication setup and lag debugging and Galera and group replication.

Configuration that matters

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- Persist across restart (MySQL 8.0):
SET PERSIST long_query_time = 1;

SET GLOBAL alone does not survive a restart. MySQL 8.0’s SET PERSIST writes to mysqld-auto.cnf so the change actually sticks — on MariaDB, edit the config file.

1153 max_allowed_packet and 1114 table is full are both configuration limits masquerading as data problems, and error 28 is simply a full disk.

Troubleshooting specific errors

The MySQL failures engineers hit most often, each with a dedicated guide:

• 1044 access denied for database — authenticated, but no privilege on that schema.
• 1064 SQL syntax error — read the text right after near; usually a reserved word or a smart quote.
• 1050 table already exists — a partially applied migration or a re-run import.
• 2003 can’t connect via TCP — bind-address, the port, or a firewall.
• 1062 duplicate entry — a unique key collision; check for an out-of-sync AUTO_INCREMENT.
• 1054 unknown column — schema drift, or an alias used before it exists.
• 1146 table doesn’t exist — wrong database, or case sensitivity on Linux.
• 1292 incorrect datetime value — strict mode rejecting a malformed date.
• 1406 data too long — the column is narrower than the value, often a charset byte-length surprise.
• 1366 incorrect string value — utf8 where utf8mb4 was needed.
• 1205 lock wait timeout and 1213 deadlock found — different problems, different fixes.
• 1129 host is blocked — too many failed connects; FLUSH HOSTS, then find the retrier.

For anything else, browse the MySQL error cluster or paste the message into the Incident Assistant.

Production checklist

• Check user@host before debugging credentials. 'app'@'%' and 'app'@'localhost' are different accounts.
• Know whether you are on the socket or TCP. localhost and 127.0.0.1 behave differently.
• Name the ALTER algorithm. ALGORITHM=INSTANT fails fast instead of rebuilding silently.
• Check for open transactions before any DDL. A metadata lock queues everything behind it.
• utf8mb4 everywhere — table, column, connection and client.
• Keep STRICT_TRANS_TABLES. A loud error beats silent truncation.
• KILL QUERY before KILL. Rolling back a large transaction can take longer than finishing it.
• --single-transaction for dumps, and confirm every table is InnoDB first.
• Check both replication threads, not just Seconds_Behind_Source.
• SET PERSIST, not SET GLOBAL, or the change dies at the next restart.
• Turn on the slow query log. It costs almost nothing and answers most questions.

Frequently asked questions

Why does access denied happen when the password is definitely right?

Because MySQL matched a different account than you expected. Identities are user@host, and the server picks the most specific matching row — so 'app'@'localhost' can be selected instead of 'app'@'%', with a different password entirely. Run SELECT CURRENT_USER(), USER(); on the failing connection: USER() shows what you supplied, CURRENT_USER() shows what actually matched. If they differ, that is your answer. The second cause is the authentication plugin — MySQL 8.0 defaults to caching_sha2_password, which older clients cannot negotiate.

What is the difference between lock wait timeout and a deadlock?

A lock wait timeout (1205) is one transaction waiting too long for a lock another transaction holds — nothing is broken, someone is just slow, and the fix is to find the holder. A deadlock (1213) is a genuine cycle where two transactions each hold what the other needs; InnoDB detects it and rolls one back immediately. Deadlocks are expected under concurrency, so applications should retry the failed transaction; the durable fix is to make transactions acquire locks in a consistent order.

Is utf8 in MySQL not UTF-8?

Correct, and it catches almost everyone. MySQL’s historical utf8 is an alias for utf8mb3, which uses at most three bytes per character and therefore cannot store emoji or some CJK characters. Real UTF-8 is utf8mb4. MySQL 8.0 defaults to utf8mb4 for new schemas, but databases upgraded in place keep the old charset, which is why the failure appears years later the first time a user pastes an emoji.

Should I raise max_connections?

Almost never as the first move. MySQL runs a thread per connection, so each one costs memory and scheduler time whether it is working or idle, and a large max_connections turns a connection problem into a memory problem. The usual causes of exhaustion are a missing or mis-sized application pool, and idle connections held open far longer than they are used. Fix the pool, tune wait_timeout, and consider ProxySQL before changing the limit.

How do I change a big table without an outage?

First try the cheapest path and let MySQL refuse if it cannot: ALTER TABLE … ALGORITHM=INSTANT, then ALGORITHM=INPLACE, LOCK=NONE. If neither is possible the operation is a full table rebuild, and you should run it through pt-online-schema-change or gh-ost rather than a maintenance window — both build a shadow copy, keep it in sync with triggers or the binlog, and swap it in at the end. Whatever the method, confirm there are no long-running transactions first, because even an instant ALTER needs a metadata lock.

Does mysqldump lock my tables?

Not with --single-transaction, which takes a consistent snapshot using InnoDB’s MVCC and lets writes continue. The important caveat is that this guarantee applies to InnoDB only — any MyISAM table in the dump breaks consistency silently, and DDL running concurrently can also break it. Verify every table’s engine before relying on it, and for large databases prefer a physical backup such as XtraBackup, which restores far faster than replaying a logical dump.

Related resources

• Guide: Postgres Commands — the same operational ground for PostgreSQL, and a useful contrast on locking and vacuum.
• Guide: Linux Commands — the host-level diagnosis underneath every database incident.
• Stack hub: MySQL command centre — the top MySQL errors, tools and runbook in one place.
• Troubleshooting hub: MySQL database errors — the full error cluster.
• Tool: Incident Assistant — paste a symptom, get an ordered triage plan.