Mysql Cheatsheet

Mon 15 December 2014
By alex

Secure Operations

Configure securely

sudo mysql_secure_installation

Running securely

sudo mysqld_safe

Managing Users

List users

SELECT user FROM mysql.user;

Create a user and assign permissions for a database

GRANT ALL PRIVILEGES ON <database>.* TO <newuser>@<hostname> IDENTIFIED BY '<password>';

Reset a user's password

SET PASSWORD FOR '<user>'@'<hostname>'=PASSWORD('<password>');

Update permissions

FLUSH PRIVILEGES;

Managing databases and tables

List databases

SHOW databases;

Create a database

CREATE DATABASE <database>;

Select a database to use

USE <database>;

List database tables

SHOW tables;

List field formats

DESCRIBE <table>;

Display data (record)

SELECT <field> FROM <table>;

Delete data

DELETE FROM <table>;

Delete a table

DROP TABLE <table>;

Delete a database

DROP DATABASE <database>;

Disaster Recovery

Backup a database

mysqldump -u <username> -p <db name> > backup.sql

Backup multiple databases

for i in $(mysql -e 'show databases' | egrep -iv "(database|schema|mysql)"); do mysqldump $i > $i.sql ; done

Restore a database

mysql -u <username> -p <db name> < backup.sql

Restore multiple databases

for database in $(ls /backups/databases/); do mysql -e "create database ${database:0:-4};"; mysql "${database:0:-4}" < "$database"; done

Copy a database (target db must exist)

mysqldump -u <username> -p <source db> | mysql -u <username> -p <destination db>

Recover Mysql root password and set a new password

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD("newrootpassword") WHERE USER='root';
FLUSH PRIVILEGES;
quit
/etc/init.d/mysql stop
mysqld_safe

Query Tips

Working with time and dates

SELECT NOW();
SELECT DATE_ADD(NOW(), INTERVAL 30 day);
SELECT DATE_SUB(NOW(), INTERVAL 30 day);
SELCET DATEDIFF(expiration, NOW()) FROM user_accounts WHERE user_id = 2;