Configuration files
MySQL reads several option files in order; later settings win. See the exact order on your system:
mysqld --verbose --help | grep -A1 "Default options"| System | Main server config |
|---|---|
| Ubuntu / Debian (MySQL) | /etc/mysql/mysql.conf.d/mysqld.cnf |
| Debian (MariaDB) | /etc/mysql/mariadb.conf.d/50-server.cnf |
| RHEL family | /etc/my.cnf, /etc/my.cnf.d/*.cnf |
Best practice: put your own changes in a separate file, e.g. /etc/mysql/conf.d/zz-custom.cnf (Debian/Ubuntu) or /etc/my.cnf.d/zz-custom.cnf (RHEL), in the [mysqld] section. Then restart:
sudo systemctl restart mysqlImportant settings
[mysqld]
bind-address = 127.0.0.1
max_connections = 200
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M # MySQL 8.0.30+: innodb_redo_log_capacity
character-set-server = utf8mb4
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
max_allowed_packet = 64M| Setting | Meaning |
|---|---|
bind-address | which IP the server listens on (127.0.0.1 = local only) |
max_connections | maximum simultaneous connections |
innodb_buffer_pool_size | RAM cache for data and indexes – the most important setting. On a dedicated database server about 50–70 % of RAM; on a shared server with web apps much less |
innodb_redo_log_capacity / innodb_log_file_size | size of the redo log; larger helps write-heavy loads |
max_allowed_packet | largest single query/row; raise if imports fail with "packet too large" |
slow_query_log, long_query_time | log queries slower than N seconds |
Check your config for errors before restarting (MySQL 8.0.16+):
sudo mysqld --validate-configViewing and changing variables
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE '%timeout%';
SELECT @@max_connections;
SET GLOBAL max_connections = 300; -- until the next restart
SET PERSIST max_connections = 300; -- MySQL 8: also survives restartsSET PERSIST writes to mysqld-auto.cnf in the data directory. MariaDB does not have it – edit the config file instead.
Monitoring
SHOW PROCESSLIST; -- running connections and queries
SHOW FULL PROCESSLIST; -- with complete query text
KILL 1234; -- end a connection (id from the processlist)
KILL QUERY 1234; -- only cancel its current query
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW ENGINE INNODB STATUS\GFrom the shell:
mysqladmin status
mysqladmin -i 5 extended-status | grep -E 'Threads_connected|Questions'Database sizes:
SELECT table_schema AS db,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;Logs
| Log | Purpose | Where |
|---|---|---|
| Error log | start-up, shutdown, crashes, warnings | /var/log/mysql/error.log, /var/log/mysqld.log or the journal |
| Slow query log | slow queries | as configured |
| General log | every query – only for short debugging, it grows fast | general_log = 1 |
| Binary log | all changes, for replication and recovery | /var/lib/mysql or as configured |
sudo tail -f /var/log/mysql/error.log
journalctl -u mysql -e
SHOW VARIABLES LIKE 'log_error';Common errors and fixes
| Error | Usual cause | Fix | |
|---|---|---|---|
| ERROR 2002 Can't connect through socket | server not running, or wrong socket path | systemctl status mysql; check the error log; use -h 127.0.0.1 to test TCP | |
| ERROR 1045 Access denied | wrong password, or the account exists for a different host | SELECT user, host FROM mysql.user; – remember 'u'@'localhost' ≠ 'u'@'%' | |
| ERROR 1698 Access denied for user 'root'@'localhost' | root uses socket authentication | log in with sudo mysql | |
| ERROR 2003 Can't connect to server on 'host' | remote: bind-address, firewall, or server down | check `ss -tlnp \ | grep 3306`, firewall rules |
| ERROR 1040 Too many connections | connection leak or too low limit | check SHOW PROCESSLIST; fix the app; raise max_connections | |
| ERROR 1205 Lock wait timeout | a long-running or forgotten transaction | find and end it – see MySQL Transactions and InnoDB | |
| ERROR 1213 Deadlock | two transactions blocking each other | retry in the application | |
| ERROR 1062 Duplicate entry | UNIQUE/PRIMARY KEY violation | check the data, or use an upsert | |
| Server will not start | config error, full disk, wrong permissions | error log; df -h; chown -R mysql:mysql /var/lib/mysql | |
| "Out of memory" / killed by OOM killer | buffer pool too large for the RAM | lower innodb_buffer_pool_size; `dmesg \ | grep -i oom` |
Checking and repairing tables
mysqlcheck --all-databases --check
mysqlcheck shop --analyzeInnoDB repairs itself after a crash during start-up. For MyISAM tables, mysqlcheck --repair can fix damage.
Upgrades
- Back up first (see MySQL Backup and Restore).
- Upgrade through the package manager (
apt upgrade/dnf upgrade). - Since MySQL 8.0.16 the server upgrades its system tables by itself at start; on MariaDB run
sudo mariadb-upgradeafterwards. - Do not skip major versions (e.g. go 5.7 → 8.0 → 8.4) and read the release notes for removed features.
Back to the start of the series: MySQL on Linux: Installation and First Steps
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.