Connect to MySQL
mysql -u username -p
Connect to specific database
mysql -u username -p database_name
Connect to remote MySQL
mysql -h hostname -u username -p database_name
Show all databases
SHOW DATABASES;
Create database
CREATE DATABASE mydb;
Use database
USE mydb;
Show all tables
SHOW TABLES;
Describe table structure
DESCRIBE table_name;
Or:
SHOW COLUMNS FROM table_name;
Show users
SELECT user, host FROM mysql.user;
Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Grant privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
Backup database
mysqldump -u username -p database_name > backup.sql
Restore database
mysql -u username -p database_name < backup.sql
Show running queries
SHOW PROCESSLIST;
Kill query
KILL query_id;
Show database size
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;
Show table sizes
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;
Execute SQL file
mysql -u username -p database_name < script.sql
Exit MySQL
EXIT;