Log in Sign up

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"
SystemMain 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 mysql

Important 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
SettingMeaning
bind-addresswhich IP the server listens on (127.0.0.1 = local only)
max_connectionsmaximum simultaneous connections
innodb_buffer_pool_sizeRAM 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_sizesize of the redo log; larger helps write-heavy loads
max_allowed_packetlargest single query/row; raise if imports fail with "packet too large"
slow_query_log, long_query_timelog queries slower than N seconds

Check your config for errors before restarting (MySQL 8.0.16+):

sudo mysqld --validate-config

Viewing 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 restarts

SET 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\G

From 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

LogPurposeWhere
Error logstart-up, shutdown, crashes, warnings/var/log/mysql/error.log, /var/log/mysqld.log or the journal
Slow query logslow queriesas configured
General logevery query – only for short debugging, it grows fastgeneral_log = 1
Binary logall 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

ErrorUsual causeFix
ERROR 2002 Can't connect through socketserver not running, or wrong socket pathsystemctl status mysql; check the error log; use -h 127.0.0.1 to test TCP
ERROR 1045 Access deniedwrong password, or the account exists for a different hostSELECT user, host FROM mysql.user; – remember 'u'@'localhost' ≠ 'u'@'%'
ERROR 1698 Access denied for user 'root'@'localhost'root uses socket authenticationlog in with sudo mysql
ERROR 2003 Can't connect to server on 'host'remote: bind-address, firewall, or server downcheck `ss -tlnp \grep 3306`, firewall rules
ERROR 1040 Too many connectionsconnection leak or too low limitcheck SHOW PROCESSLIST; fix the app; raise max_connections
ERROR 1205 Lock wait timeouta long-running or forgotten transactionfind and end it – see MySQL Transactions and InnoDB
ERROR 1213 Deadlocktwo transactions blocking each otherretry in the application
ERROR 1062 Duplicate entryUNIQUE/PRIMARY KEY violationcheck the data, or use an upsert
Server will not startconfig error, full disk, wrong permissionserror log; df -h; chown -R mysql:mysql /var/lib/mysql
"Out of memory" / killed by OOM killerbuffer pool too large for the RAMlower innodb_buffer_pool_size; `dmesg \grep -i oom`

Checking and repairing tables

mysqlcheck --all-databases --check
mysqlcheck shop --analyze

InnoDB 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-upgrade afterwards.
  • 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.