MySQL: Memento

The MySQL memo for admin commands that are not typed often enough to remember, but often enough to know they exist.

Connection

mysql -u root -p
mysql -u root -p -h localhost my_schema
mysql -u root -p < dump.sql

Users and grants

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON my_schema.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON my_schema.* TO 'readonly'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'user'@'localhost';
DROP USER 'user'@'localhost';

Backup and restore

# Full backup of one database
mysqldump -u root -p my_schema > backup.sql

# Backup of all databases
mysqldump -u root -p --all-databases > all.sql

# Backup with DROP TABLE
mysqldump -u root -p --add-drop-table my_schema > backup.sql

# Restore
mysql -u root -p my_schema < backup.sql

Admin queries

SHOW DATABASES;
SHOW TABLES;
SHOW TABLE STATUS;
SHOW PROCESSLIST;
SHOW VARIABLES;
SHOW ENGINE INNODB STATUS;
DESCRIBE table_name;

Optimization

OPTIMIZE TABLE table_name;
ANALYZE TABLE table_name;
REPAIR TABLE table_name;

The lost root password

If you have lost the root password, the procedure is:

# Stop MySQL
/etc/init.d/mysql stop

# Start without grant tables
mysqld_safe --skip-grant-tables &

# Connect without password
mysql -u root

# In mysql:
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

# Restart normally
/etc/init.d/mysql restart

See also the article on lost credentials for the detailed version.

[edit: 2026] MySQL has been replaced by MariaDB on most distros (same protocol, same commands) and the mysqld_safe --skip-grant-tables is still the documented recovery procedure. The memo survives because the SQL protocol survives.

[edit: 2026] The Ansible implementation of all this: the mariadb role .