Home › Cheat Sheets

MySQL cheat sheet

Backup and restore

Backing up tables:

mysqldump -p -c -Q databasename tablename1 tablename2 ... > whatever.sql

Restoring tables:

mysql -p databasename < whatever.sql

Creating a user and their own database

CREATE DATABASE dbname;
GRANT USAGE ON dbname.* TO username@'%';
GRANT ALL ON dbname.* TO username@'%';
SET PASSWORD FOR username@'%' = PASSWORD('blah');
FLUSH PRIVILEGES;

Changing passwords

SET PASSWORD = PASSWORD('blah');

Changing administrator password

mysql -u root
mysql> SET PASSWORD FOR root@localhost = PASSWORD('new_password');

Disabling network access

In my.cnf, add the keyword ==skip-networking== and the line ==bind-address=127.0.0.1== (for any applications that still require TCP networking) to the ==![mysqld]== section.

Flushing Initial Database and Users

drop database test;
use mysql;
delete from db;
delete from user where not (host="localhost" and user="root");
flush privileges;

Deleting users

REVOKE ALL PRIVILEGES ON *.* FROM username@'%';
REVOKE GRANT OPTION ON *.* FROM username@'%';
DELETE FROM mysql.user WHERE (User='username' and Host='%');

Notes

  • For specifying hosts users may access from, '%' means from any host. These have precedence over 'localhost'.