Skip to content

Instantly share code, notes, and snippets.

@a1iraxa

a1iraxa/mysql cheatsheet.md

Forked from apolloclark/mysql cheatsheet.md
Created July 19, 2020 17:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save a1iraxa/679de840bbb44f8eb51235f44863130a to your computer and use it in GitHub Desktop.
Save a1iraxa/679de840bbb44f8eb51235f44863130a to your computer and use it in GitHub Desktop.
Download ZIP
mysql cheatsheet
Raw
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 \
admin@example.com

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment