MySQL Cheatsheet

Indexes

List indexes :

SHOW INDEXES FROM @table;

Drop index :

DROP INDEX @index_name ON @table;

Foreign Keys

List FK on a table :

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = @db
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = @name;

Performance / Investigation

List currently running processes :

SHOW PROCESSLIST;

List currently running processes and their corresponding queries :

SHOW FULL PROCESSLIST;

Kill a running process :

KILL @process_id;

Emergency

Diff between the same table in 2 DB :

INSERT INTO db_prod.`table`
SELECT A.* FROM db_backup.`table` A
LEFT JOIN db_prod.`table` B
ON A.id = B.id
WHERE B.id IS NULL;

Insert the diff into the DB (for example to restore deleted entities) :

INSERT INTO db_prod.`table`
SELECT A.* FROM db_backup.`table` A
LEFT JOIN db_prod.`table` B
ON A.id = B.id
WHERE B.id IS NULL;

Fresh install

Install MariaDB :

sudo apt install mariadb-server

Run setup script :

sudo mysql_secure_installation

Connect to DB :

mysql -u root -p

Create a new user :

CREATE USER '--USERNAME--'@'localhost' IDENTIFIED BY '--PASSWORD--';

Grant full access to a single DB :

GRANT ALL PRIVILEGES ON database.tables TO '--USERNAME--'@'localhost'; -- Replace 'database' and 'tables'

Misc

Export the result of a query into a CSV file :

SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n'