mysql cheatsheet

mysql cheatsheet.md

MySQL Cheatsheet

This is a collection of the most common commands I run while administering Mysql databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Mysql has multiple shortcut functions, starting with a forward slash, "". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.

Setup

installation, Ubuntu 14.04

https://help.ubuntu.com/14.04/serverguide/mysql.html

sudo apt-get update
sudo apt-get install -y mysql-server
sudo mysql_secure_installation
sudo mysql_install_db

connect

mysql -u <username> -p<password>

connect, over SSH tunnel

ssh -L <local_mysql_port>:<local_addr>:<remote_mysql_port> \
<username>@<remote_addr>

ssh -L 3306:127.0.0.1:3306 \
[email protected]

mysql -h 127.0.0.1 -u <username> -p<password> <databbase_name>

disconnect

mysql> exit
mysql> quit
mysql> \q

clear screen

<CTRL + L>

connection info

mysql> status;

configure

Ubuntu 14.04 / Debian

sudo nano $(locate -l 1 my.cnf)
sudo service mysqld restart

Redhat / Fedora / CentOS

yum -y install mlocate
updatedb
sudo nano $(locate -l 1 my.cnf)
sudo service mysqld restart

debug logs

https://dev.mysql.com/doc/refman/5.6/en/server-logs.html

Ubuntu 14.04 / Debian

sudo tail -24 /var/log/mysql.err
sudo tail -24 /var/log/mysql.log

Redhat / Fedora / Centos

sudo tail -24 /var/log/mysqld.log

Recon

show version

SHOW VARIABLES LIKE "%version%";

show system status

SHOW STATUS;

show environmental variables

SHOW VARIABLES;

show current user

SELECT USER();

show current user's permissions

SHOW GRANTS FOR CURRENT_USER;

list databases

SHOW DATABASES;

show current database

SELECT DATABASE();

show all tables in database

SHOW TABLES;

list functions

SELECT * FROM mysql.func;

Databases

list databases

https://dev.mysql.com/doc/refman/5.6/en/show-databases.html

SHOW DATABASES;

connect to database

http://dev.mysql.com/doc/refman/5.6/en/use.html

USE <database_name>;

show current database

SELECT DATABASE();

create database

https://dev.mysql.com/doc/refman/5.6/en/create-database.html

CREATE DATABASE <database_name> IF NOT EXISTS;

delete database

http://dev.mysql.com/doc/refman/5.6/en/drop-database.html

DROP DATABASE IF EXISTS <database_name>;

Users

list all users name, host, password

SELECT User,Host,Password,Grant_priv,Super_priv FROM mysql.user;

list all users

SELECT * FROM mysql.user;

create user

http://dev.mysql.com/doc/refman/5.6/en/create-user.html

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

drop user

http://dev.mysql.com/doc/refman/5.6/en/drop-user.html

DROP USER <username>;

alter user's password

http://dev.mysql.com/doc/refman/5.6/en/set-password.html

SET PASSWORD FOR '<username>'@'localhost' = PASSWORD('<password>');

Permissions

http://dev.mysql.com/doc/refman/5.6/en/grant.html

show current user's permissions

SHOW GRANTS FOR CURRENT_USER;

reload permissions

FLUSH PRIVILEGES;

grant all permissions to user DANGEROUS!!!

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

grant permissions to select, update, insert, delete, on all tables

GRANT SELECT,UPDATE,INSERT,DELETE ON <database_name>.* TO '<username>'@'localhost';

grant permissions to select, insert, on all tables

GRANT SELECT,INSERT ON <database_name>.* TO '<username>'@'localhost';

grant permissions to select, on all tables

GRANT SELECT ON <database_name>.* TO '<username>'@'localhost';

Tables

list all tables

http://dev.mysql.com/doc/refman/5.6/en/show-tables.html

SHOW TABLES;

list tables schema

DESCRIBE <table_name>;
SHOW COLUMNS FROM <table_name>;

create table

http://dev.mysql.com/doc/refman/5.6/en/create-table.html

CREATE TABLE <table_name> (
	<col_name> INT,
	<col_name> VARCHAR(128)
);

delete table

http://dev.mysql.com/doc/refman/5.6/en/drop-table.html

DROP TABLE <table_name>;

rename table

http://dev.mysql.com/doc/refman/5.6/en/rename-table.html

RENAME TABLE <old_name> TO <new_name>;

Columns

add column

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

ALTER TABLE <table_name> IF EXISTS
ADD COLUMN <column_name> <column_type>;

update column

ALTER TABLE <table_name> IF EXISTS
MODIFY COLUMN <column_name> <column_type>;

delete column

ALTER TABLE <table_name> IF EXISTS
DROP COLUMN <column_name>;

update column to be an auto-increment primary key

ALTER TABLE <table_name> IF EXISTS
MODIFY COLUMN <column_name> INT auto_increment;

Data

read all data

http://dev.mysql.com/doc/refman/5.6/en/select.html

SELECT * FROM <table_name>;

read first row of data

SELECT * FROM <table_name> LIMIT 1;

search for data

SELECT * FROM <table_name> WHERE <column_name> = <value>;

insert data

http://dev.mysql.com/doc/refman/5.6/en/insert.html

INSERT INTO <table_name> VALUES();

edit data

http://dev.mysql.com/doc/refman/5.6/en/update.html

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value_1>;

delete all data

http://dev.mysql.com/doc/refman/5.6/en/delete.html

DELETE FROM <table_name>;

delete specific data

DELETE FROM <table_name>
WHERE <column_name> = <value>;

Functions

list functions

SELECT * FROM mysql.func;

Scripting

run local script, on remote server

https://dev.mysql.com/doc/refman/5.6/en/mysql-batch-commands.html

mysql -u <username> -p<password> <database_name> < mydb.sql

backup database, everything

https://dev.mysql.com/doc/mysql-backup-excerpt/5.6/en/mysqldump-sql-format.html

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#mysqldump-option-summary

mysqldump -u <username> -p<password> <database_name> > <sql_file>

backup database, only data

mysqldump --opt -u <username> -p<password> --no-create-info <database_name> > <sql_file>

backup database, only schema

mysqldump --opt -u <username> -p<password> --no-data <database_name> > <sql_file>

export table into CSV file

SELECT *
FROM <table_name>
INTO OUTFILE '<file_pathway>'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

export table, only specific columns, to CSV file

SELECT <column_name1>, <column_name2>
FROM <table_name>
INTO OUTFILE '<file_pathway>'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

import CSV file into table, only specific columns

http://www.mysqltutorial.org/mysql-export-table-to-csv/

Debugging

http://dev.mysql.com/doc/refman/5.6/en/explain.html

http://dev.mysql.com/doc/refman/5.6/en/debugging-server.html

Advanced

http://dev.mysql.com/doc/refman/5.6/en/constraints.html