mysql [options] [database]
mysql -u root -p
MySQL is a popular open-source relational database. This sheet covers the command-line
client, everyday SQL, user and permission management, backups, replication basics,
performance checks and the admin tasks a sysadmin does most. It targets MySQL 8.x.
MariaDB is a compatible fork: nearly everything here works, and differences are noted.
Run SQL commands in the mysql> prompt and end each statement with a semicolon.
Be careful on production servers: DROP, DELETE and UPDATE without WHERE cannot be undone.
Install and service
sudo apt install mysql-server # Debian/Ubuntu (mariadb-server for MariaDB)
sudo dnf install mysql-server # Fedora/RHEL family
brew install mysql # macOS
sudo systemctl enable --now mysql # start now and at boot (service is "mysqld" on RHEL family, "mariadb" on MariaDB)
systemctl status mysql
sudo mysql_secure_installation # set a root password, remove test data, disable remote root
mysql --version
mysqladmin -u root -p version # server version and uptime
Connect
mysql -u root -p # ask for the password
sudo mysql # as root via the socket (default on Ubuntu, no password)
mysql -u alice -p mydb # connect straight into a database
mysql -h 192.168.1.20 -P 3306 -u alice -p # a remote server
mysql -u alice -p -e "SHOW DATABASES;" # run one statement and exit
mysql -u alice -p mydb < script.sql # run a file of SQL
mysql -u alice -p -N -B -e "SELECT id,name FROM users" > out.tsv # plain tab-separated output
mysql --ssl-mode=REQUIRED -h db.example.com -u alice -p # require TLS
mysql_config_editor set --login-path=local --host=localhost --user=alice --password # store credentials safely
mysql --login-path=local # connect using stored credentials
Do not put the password on the command line (-pSECRET). It shows up in ps and shell history.
Client commands (inside the mysql> prompt)
help or \h Help exit or quit or \q Leave
status or \s Connection and server info \G End a query with \G for vertical output
use mydb; or \u mydb Choose a database source file.sql; or \. file.sql Run a script
system ls Run a shell command \T out.txt Save a copy of the session
\c Cancel the statement you are typing
select @@version; select database(); select current_user();
Look around
SHOW DATABASES;
USE mydb;
SHOW TABLES;
SHOW FULL TABLES; -- tables and views
DESCRIBE users; -- columns (also DESC users; SHOW COLUMNS FROM users;)
SHOW CREATE TABLE users\G -- the full CREATE statement
SHOW INDEX FROM users;
SHOW TABLE STATUS LIKE 'users'\G -- rows, size, engine
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE '%buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST; -- running queries (SHOW FULL PROCESSLIST for full text)
SHOW ENGINE INNODB STATUS\G -- InnoDB internals and last deadlock
SHOW GRANTS FOR 'alice'@'localhost';
SHOW WARNINGS; -- warnings from the last statement
SELECT VERSION(), NOW(), CURRENT_USER();
Databases and tables
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- use utf8mb4, never plain utf8
DROP DATABASE shop; -- deletes everything in it
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_name (name)
) ENGINE=InnoDB;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD INDEX idx_created (created_at);
ALTER TABLE users DROP INDEX idx_created;
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
RENAME TABLE users TO customers;
TRUNCATE TABLE logs; -- remove all rows fast, resets AUTO_INCREMENT
DROP TABLE IF EXISTS temp_data;
CREATE TABLE users_copy LIKE users; -- same structure
CREATE TABLE users_backup AS SELECT * FROM users;
Common types: INT, BIGINT, DECIMAL(10,2), VARCHAR(n), TEXT, DATE, DATETIME, TIMESTAMP, BOOLEAN, JSON, ENUM(...)
Insert, update, delete
INSERT INTO users (email, name) VALUES ('a@example.com', 'Alice');
INSERT INTO users (email, name) VALUES ('b@example.com','Bob'), ('c@example.com','Cy');
INSERT INTO users (email, name) VALUES ('a@example.com','Alice') ON DUPLICATE KEY UPDATE name = VALUES(name);
INSERT IGNORE INTO users (email) VALUES ('a@example.com');
REPLACE INTO users (id, email) VALUES (1, 'new@example.com');
UPDATE users SET name = 'Alicia' WHERE id = 1; -- always include WHERE
DELETE FROM users WHERE id = 3; -- always include WHERE
SELECT LAST_INSERT_ID();
Safety net: mysql --safe-updates refuses UPDATE and DELETE without a WHERE that uses a key.
Wrap risky changes in a transaction: START TRANSACTION; ...; ROLLBACK; (or COMMIT;)
Query data
SELECT * FROM users LIMIT 10;
SELECT id, email FROM users WHERE name LIKE 'A%' ORDER BY created_at DESC LIMIT 20 OFFSET 40;
SELECT DISTINCT country FROM customers;
SELECT COUNT(*) FROM users;
SELECT country, COUNT(*) AS n FROM customers GROUP BY country HAVING n > 10 ORDER BY n DESC;
SELECT MIN(price), MAX(price), AVG(price), SUM(price) FROM products;
SELECT * FROM users WHERE id IN (1,2,3) AND created_at >= '2026-01-01' AND email IS NOT NULL;
SELECT * FROM users WHERE name REGEXP '^A.*e$';
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE o.total > 100;
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id; -- include users with no orders
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 500);
SELECT * FROM a UNION SELECT * FROM b; -- UNION ALL keeps duplicates
SELECT IFNULL(phone,'n/a'), COALESCE(phone, mobile, 'n/a') FROM users;
SELECT CASE WHEN total > 100 THEN 'big' ELSE 'small' END FROM orders;
SELECT DATE_FORMAT(created_at,'%Y-%m') AS month, COUNT(*) FROM orders GROUP BY month;
SELECT NOW(), CURDATE(), DATE_SUB(NOW(), INTERVAL 7 DAY), DATEDIFF('2026-12-31', CURDATE());
SELECT CONCAT(first,' ',last), UPPER(name), LENGTH(name), SUBSTRING(name,1,3), TRIM(name) FROM users;
SELECT JSON_EXTRACT(data,'$.name'), data->>'$.name' FROM events;
Users and privileges
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'a-strong-password';
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-strong-password'; -- from a subnet
CREATE USER 'app'@'%' IDENTIFIED BY '...'; -- from anywhere (be careful)
GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.0.%';
GRANT SELECT ON shop.orders TO 'report'@'%';
GRANT USAGE ON *.* TO 'nobody'@'localhost'; -- no privileges, can only connect
REVOKE DELETE ON shop.* FROM 'app'@'10.0.0.%';
SHOW GRANTS FOR 'alice'@'localhost';
SELECT user, host, plugin FROM mysql.user;
ALTER USER 'alice'@'localhost' IDENTIFIED BY 'new-password';
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;
RENAME USER 'alice'@'localhost' TO 'alicia'@'localhost';
DROP USER 'alice'@'localhost';
FLUSH PRIVILEGES; -- only needed if you edit the grant tables directly
Give applications the smallest set of privileges they need, and never use root in application code.
Roles (8.0): CREATE ROLE 'readonly'; GRANT SELECT ON shop.* TO 'readonly'; GRANT 'readonly' TO 'alice'@'localhost';
Backup with mysqldump
mysqldump -u root -p mydb > mydb.sql # one database
mysqldump -u root -p --single-transaction --routines --triggers --events mydb > mydb.sql # consistent InnoDB backup
mysqldump -u root -p --all-databases --single-transaction > all.sql
mysqldump -u root -p mydb users orders > two-tables.sql # specific tables
mysqldump -u root -p --no-data mydb > schema.sql # structure only
mysqldump -u root -p --no-create-info mydb > data.sql # data only
mysqldump -u root -p --databases db1 db2 > multi.sql # includes CREATE DATABASE
mysqldump -u root -p mydb | gzip > mydb-$(date +%F).sql.gz # compressed and dated
mysqldump -u root -p --master-data=2 --single-transaction mydb > repl.sql # with binlog position (older syntax; newer: --source-data)
mysqldump -h db.example.com -u alice -p --single-transaction mydb | ssh backup@host "cat > /backups/mydb.sql"
Restore
mysql -u root -p mydb < mydb.sql # into an existing database
mysql -u root -p -e "CREATE DATABASE mydb"; mysql -u root -p mydb < mydb.sql
gunzip < mydb.sql.gz | mysql -u root -p mydb
mysql -u root -p < all.sql # a full dump restores all databases
Test your restores regularly. A backup you have never restored is only a hope.
Other tools: mysqlpump, mydumper/myloader (parallel), Percona XtraBackup and MySQL Enterprise Backup (physical, hot).
Import and export data
SELECT * FROM users INTO OUTFILE '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/var/lib/mysql-files/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES; -- needs local_infile enabled
SHOW VARIABLES LIKE 'secure_file_priv'; -- the only folder the server may read and write files in
mysql -u root -p -B -e "SELECT * FROM mydb.users" | sed 's/\t/,/g' > users.csv # quick CSV (no quoting)
Indexes and performance
EXPLAIN SELECT * FROM users WHERE email = 'a@example.com';
EXPLAIN ANALYZE SELECT ...; -- runs it and shows real timings (8.0.18+)
Look at: type (ALL = full table scan, ref/range/const = good), key (index used), rows (estimate), Extra.
CREATE INDEX idx_email ON users (email);
CREATE INDEX idx_user_date ON orders (user_id, created_at); -- composite: leftmost column first
CREATE UNIQUE INDEX uq_email ON users (email);
DROP INDEX idx_email ON users;
ANALYZE TABLE users; -- refresh index statistics
OPTIMIZE TABLE users; -- rebuild and defragment (locks/rebuilds InnoDB tables)
CHECK TABLE users; REPAIR TABLE users; -- REPAIR is for MyISAM only
SHOW STATUS LIKE 'Slow_queries';
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10; -- the sys schema summarizes performance_schema
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
SELECT * FROM sys.innodb_lock_waits\G
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;
Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # top 10 by total time
pt-query-digest /var/log/mysql/mysql-slow.log # Percona Toolkit: much richer report
Processes and locks
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;
KILL 12345; -- end that connection
KILL QUERY 12345; -- stop the query, keep the connection
SHOW ENGINE INNODB STATUS\G -- LATEST DETECTED DEADLOCK section
SELECT * FROM performance_schema.data_locks; -- current locks (8.0)
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Aborted_connects';
SHOW VARIABLES LIKE 'max_connections'; SET GLOBAL max_connections = 500;
Server health checks
mysqladmin -u root -p status # uptime, threads, queries per second
mysqladmin -u root -p extended-status | grep -E 'Threads_connected|Questions|Slow_queries'
mysqladmin -u root -p processlist
mysqladmin -u root -p ping # is the server alive?
mysqladmin -u root -p variables | grep buffer_pool
mysqladmin -u root -p flush-logs
mysqladmin -u root -p shutdown
mysqlcheck -u root -p --all-databases --check # check every table (also --repair, --optimize)
mysqltuner.pl # a separate script that suggests tuning changes (read its advice with care)
Config (my.cnf)
/etc/mysql/my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf /etc/my.cnf ~/.my.cnf (mysql --help | grep -A1 "Default options")
[mysqld]
bind-address = 127.0.0.1 # listen only locally (0.0.0.0 = all interfaces)
port = 3306
max_connections = 200
innodb_buffer_pool_size = 2G # the most important setting: about 50-70% of RAM on a dedicated DB server
innodb_log_file_size = 512M # (8.0.30+: innodb_redo_log_capacity)
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
slow_query_log = 1
long_query_time = 1
log_error = /var/log/mysql/error.log
[client]
default-character-set = utf8mb4
Restart after editing: sudo systemctl restart mysql. Many variables can be changed live with SET GLOBAL and persisted with SET PERSIST.
SET PERSIST max_connections = 300; -- 8.0: survives a restart
Files and logs
/var/lib/mysql/ Data directory
/var/log/mysql/error.log or /var/log/mysqld.log Error log (first place to look when it will not start)
sudo tail -f /var/log/mysql/error.log
journalctl -u mysql -f
SHOW VARIABLES LIKE 'datadir'; SHOW VARIABLES LIKE 'log_error';
Never copy or edit files in the data directory while the server runs.
Replication (basics)
Source (primary) settings in my.cnf: server-id = 1 log_bin = mysql-bin binlog_format = ROW
Replica settings: server-id = 2 read_only = 1 relay_log = relay-bin
On the source:
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '...';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
SHOW MASTER STATUS; -- 8.4: SHOW BINARY LOG STATUS; file name and position
On the replica (MySQL 8.0.23+ syntax; older: CHANGE MASTER TO / START SLAVE):
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.1', SOURCE_USER='repl', SOURCE_PASSWORD='...', SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=157;
START REPLICA;
SHOW REPLICA STATUS\G -- 8.0.22 and older: SHOW SLAVE STATUS
Check: Replica_IO_Running = Yes, Replica_SQL_Running = Yes, Seconds_Behind_Source close to 0, and no Last_Error.
STOP REPLICA; RESET REPLICA ALL;
Prefer GTID replication (gtid_mode = ON) for easier failover. Take a consistent dump first to seed a new replica.
Binary logs also enable point-in-time recovery: mysqlbinlog mysql-bin.000003 --start-datetime="2026-09-26 10:00:00" | mysql -u root -p
Security checklist
- Run mysql_secure_installation, and use strong, unique passwords
- bind-address = 127.0.0.1 unless remote access is needed, then firewall port 3306 to trusted hosts only (ufw / security groups)
- No anonymous users and no remote root: SELECT user, host FROM mysql.user;
- One user per application with only the grants it needs
- Require TLS for remote connections: REQUIRE SSL on the user, ssl-mode=REQUIRED on the client
- Disable LOAD DATA LOCAL and keep secure_file_priv set
- Keep MySQL patched, back up regularly and test the restore
- Never store secrets in a world-readable ~/.my.cnf: chmod 600 it, or use mysql_config_editor
Practical one-liners
# Size of every database
mysql -u root -p -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS MB FROM information_schema.tables GROUP BY table_schema ORDER BY MB DESC;"
# The 10 largest tables
mysql -u root -p -e "SELECT table_schema, table_name, ROUND((data_length+index_length)/1024/1024,1) AS MB FROM information_schema.tables ORDER BY MB DESC LIMIT 10;"
# Nightly compressed backup of everything (cron)
mysqldump --defaults-extra-file=/root/.backup.cnf --all-databases --single-transaction --routines --events | gzip > /backups/all-$(date +\%F).sql.gz
# Keep the last 7 days of backups
find /backups -name 'all-*.sql.gz' -mtime +7 -delete
# Copy a database to another server
mysqldump -u root -p --single-transaction mydb | mysql -h other-host -u root -p mydb
# Who is connected right now, and from where?
mysql -u root -p -e "SELECT user, host, db, command, time FROM information_schema.processlist ORDER BY time DESC;"
# Kill every query running longer than 60 seconds (review first!)
mysql -u root -p -N -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE command='Query' AND time > 60;"
# Reset a forgotten root password (MySQL 8): stop the service, then
sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root -e "FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';"
sudo mysqladmin shutdown && sudo systemctl start mysql
# (skip-networking keeps it off the network while grants are disabled)
# Count rows in every table of a database
mysql -u root -p -N -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='mydb';"
# Watch queries per second
mysqladmin -u root -p -r -i 1 extended-status | grep Questions
# Find tables without a primary key
mysql -u root -p -e "SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON c.table_schema=t.table_schema AND c.table_name=t.table_name AND c.constraint_type='PRIMARY KEY' WHERE c.constraint_name IS NULL AND t.table_type='BASE TABLE' AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema');"
Common errors
ERROR 1045 (28000): Access denied for user Wrong user, password or host. Check SELECT user, host FROM mysql.user
ERROR 2002 (HY000): Can't connect through socket The server is not running or the socket path differs: systemctl status mysql
ERROR 2003: Can't connect to MySQL server on host Firewall, bind-address, wrong port, or the server is down
ERROR 1044 / 1142: command denied The user lacks that privilege: SHOW GRANTS
ERROR 1049: Unknown database Wrong name: SHOW DATABASES
ERROR 1146: Table doesn't exist Wrong database (USE it), or the table name case is wrong on Linux
ERROR 1062: Duplicate entry A UNIQUE or PRIMARY KEY conflict: use ON DUPLICATE KEY UPDATE or fix the data
ERROR 1064: You have an error in your SQL syntax Typo, a reserved word (quote it with backticks), or missing quote or comma
ERROR 1205: Lock wait timeout exceeded A long transaction holds a lock: find it in sys.innodb_lock_waits, commit or kill it
ERROR 1213: Deadlock found Retry the transaction, and keep transaction order consistent
ERROR 1040: Too many connections Raise max_connections, use connection pooling, and find leaking clients
ERROR 1153: Got a packet bigger than max_allowed_packet Raise max_allowed_packet (client and server), often on big restores
ERROR 1290: --secure-file-priv option Read and write files only in the folder shown by secure_file_priv
"Server has gone away" (2006) Timeout, crash or oversized packet: check the error log and wait_timeout
Server will not start Read /var/log/mysql/error.log: usually a bad my.cnf value, a full disk or permissions on the data directory
Disk full Purge binary logs: PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY; and set binlog_expire_logs_seconds
Tips
Use utf8mb4 everywhere. The old "utf8" is a 3-byte subset and cannot store emoji.
Use mysqldump --single-transaction for InnoDB backups so nothing is locked while it runs.
Restore a backup to a test server regularly. Untested backups fail when you need them.
Always include a WHERE on UPDATE and DELETE, run a SELECT with the same WHERE first, and use a transaction for risky changes.
Use EXPLAIN before you add indexes, and index the columns you filter, join and sort on. Every extra index slows writes.
Set innodb_buffer_pool_size to a large share of RAM on a dedicated server. It is the setting with the biggest effect.
Keep the server off the internet: bind to localhost or a private address and firewall port 3306.
Give each application its own user with the minimum privileges, and never use root in an app.
Watch the slow query log and SHOW PROCESSLIST when the server feels slow. Most problems are a few bad queries.
Use \G at the end of a query for readable, vertical output when rows are wide.
On MariaDB, some commands and status names differ (for example replication syntax and the sys schema). Check the MariaDB docs when a command is not recognized.
