Cheatsheets - MySQL
Users and Privileges
# Show all the users
mysql> select host, user, password from mysql.user;
# Create a new user
mysql> CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
# Grant privileges
mysql> GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
# Show grants:
mysql> show grants FOR 'admin'@'localhost';
Database
# Show databases
mysql> show databases;
# Create databases
mysql> create database publications;
# Choose databases
mysql> use publications;
# Grant permission:
mysql> grant all on publications.* to 'root' identified by 'mypasswd';
Table
# Show Table List:
mysql> show tables;
# Describe a Table:
mysql> describe classics;
# Create a Table:
mysql> create table classics (
-> author varchar(128),
-> title varchar(128),
-> type varchar(16),
-> year char(4)) ENGINE MyISAM;
# Rename a Table:
mysql> alter table pre1900 rename classics;
# Drop table:
mysql> drop table classics;
# Change Engine:
ALTER TABLE tablename ENGINE = MyISAM;
Column
# Add Column:
mysql> alter table classics add id int unsigned not null auto_increment key;
# Remove Column:
mysql> alter table classics drop id;
# Change Column Data Type:
mysql> alter table classics modify year smallint;
# Rename Column(change from 'type' to 'category', need to specify the data type):
mysql> alter table classics change type category varchar(16);
Row
# Add Row:
mysql> insert into classics(author, title, type, year)
-> values('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
# Update Row:
mysql> UPDATE classics SET isbn='9781598184891' WHERE year='1876';
# Select:
mysql> select * from classics;
Index
mysql> alter table classics add index(author(20));
mysql> create index author on classics (author(20));
(CREATE INDEX cannot be used to create a PRIMARY KEY)
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year)) ENGINE MyISAM;
Add FULLTEXT Index
ALTER TABLE classics ADD FULLTEXT(author,title);
Transactions
use ENGINE InnoDB
BEGIN;
UPDATE accounts SET balance=balance+25.11 WHERE number=12345;
COMMIT;
ROLLBACK;
Explain
EXPLAIN SELECT * FROM accounts WHERE number='12345';
Back up
LOCK TABLES tablename1 tablename2 ... READ
mysqldump -u user -p password database
UNLOCK TABLES;
mysqldump
$ mysqldump -u user -ppassword publications > publications.sql
Example
To add primary key(not null), edit each row then add primary key.
ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
...
ALTER TABLE classics ADD PRIMARY KEY(isbn);
Calculate Sizes
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) \"Size in MB\" FROM information_schema.TABLES
WHERE table_schema='${a}';
To call it in a bash script
#!/bin/bash
for db in `mysql -h db0 -u username -e "show databases;"`
do
mysql -h db0 -u username -e "SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) \"Size in MB\" FROM information_schema.TABLES WHERE table_schema='${db}';"
done
Print Tables
#!/bin/bash
DBS=`mysql -uroot -e"show databases"`
for b in $DBS ;
do
mysql -uroot -e"show tables from $b"
done
MySQLAdmin
$ mysqladmin -h host -u root -p variables
Install MySQL Server
$ sudo yum install mysql-server
Start mysqld
$ sudo /sbin/service mysqld start
Install MySQL
$ sudo yum install mysql
Login
$ mysql -u <user_name> -p