Cheatsheets - MySQL

Last Updated: 2023-01-15

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