-
Notifications
You must be signed in to change notification settings - Fork 0
/
mysql.txt
95 lines (73 loc) · 5.73 KB
/
mysql.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
# which config files come from where?
$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
# the most common file/location is /etc/my.cnf
# data files:
/var/lib/mysql/<dbname>/files.[frm/MYD/MYI]
# === SQL ===
# basic administration stuff:
CREATE USER 'joe'@'localhost' IDENTIFIED BY 'supersekrit' -- create user account
GRANT SELECT, INSERT, UPDATE, DELETE ON theCornerShop.* TO 'joe'@'localhost' -- grant permissions example
ALL [PRIVILEGES] -- every permission applicable to {target} (except for GRANT)
SELECT, INSERT, UPDATE, DELETE -- a.k.a. CRUD
CREATE, ALTER, INDEX, DROP -- CRUD for tables
# to also allow GRANT itself to a user:
GRANT ALL [PRIVILEGES] ON *.* TO root@123.456.789.123 WITH GRANT OPTION;
# view and edit:
SHOW GRANTS [FOR 'username'@'host'] -- shows your or specified user's permissions
REVOKE DELETE, UPDATE ON theCornerShop.localhost FROM 'jim'@'localhost' -- remove specific permissions
REVOKE ALL PRIVILEGES ON theCornerShop.* FROM user@host; -- remove bunch of permissions
DROP USER 'leetHax0r1994'@'localhost' -- delete user/host pair. all related privileges should
be gone too.
# update password for existing users
SET PASSWORD FOR <username>@<host> = PASSWORD('evensekriter') -- update password <= 5.7.5
ALTER USER <user>@<host> IDENTIFIED BY 'soopersekrit'; -- update password >= 5.7.6
UPDATE user SET ssl_type = 'ANY' WHERE user = '<username>' AND host = '<host-address>'; -- old force encryption
ALTER USER 'user'@'host' REQUIRE SSL -- new force encryption
# list keys and stuff
SHOW INDEX FROM <table>;
# remove duplicate records
ALTER IGNORE TABLE \e[32m<table>\e[0m ADD UNIQUE INDEX \e[32m<foo>\e[0m (\e[32m<foo>\e[0m);
# and then optionally..
ALTER TABLE <table> DROP INDEX \e[32m<foo>\e[0m;
# if you can't drop a database because "a foreign key constraint fails" (I couldn't make this up) tell it to sod off
SET FOREIGN_KEY_CHECKS=0;
# set them back to 1 afterwards I guess.
# find storage engine of tables
select table_name, engine from information_schema.tables where table_schema='\e[32m<db_name>\e[0m' and table_name in ('\e[32m<table_1>\e[0m', '\e[32m<table_2>\e[0m', ..)
\e[38;5;201m# === CLI ===\e[0m
# export/import
mysqldump <creds/host> dbname > dbname.sql
mysqldump <creds/host> dbname tablename > table.sql
mysql dbname < dbname.sql # etc
# general query result display (running queries straight from the bash prompt with -e):
-s # no grid-lines for query output
-N # no headers/column names for query output
-B # tab separated according to chatgpt?
# \e[38;5;201mNB:\e[0m when the output is piped to another process, mysql uses batch mode (tab-separated fields) and removes column names, making it
# easier to parse
## In the client
# ctrl-W doesn't work, ctrl-H or ctrl-backspace does and deletes the last word/space. fiik. Ctrl-cursor keys might jump by word?
# MySQL has a few flags/toggles that you can run interactively, '\h' will bring up a list.
# useful ones are \s for status, \! for system commands and \\e for editing large multiline queries in a real editor
# The pager only works under 'unix', which apparently can mean 'centos but not wsl/ubuntu'. NB: when connecting to a remote host,
# it's the abilities of the local client that count.
# While running you can enter \e[32m\P less\e[0m; when launching you can pass --pager=less or have pager=less in the config file
# under [mysql].
# 'select foo bar bazz \G' = vertical result (more useful with a WORKING pager >.>) \G and others imply ';' and will run the query
# To connect to a Docker/Podman container from localhost, use -h 127.0.0.1; if you use localhost mysql client tries to use a unix
# socket. Unless it works regardless because wsl magic or somesuch.
\e[38;5;201m# == "Recipes" or some shit ==\e[0m
# All these are similar and are some basic blocks chained together.
# \e[33mExport a database from localhost to remote server, compressed.\e[0m Mind the quotes and the pipes and such.
$ mysqldump --default-character-set=utf8 -u\e[32m<user>\e[0m -p\e[32m<pass?>\e[0m \e[32m<database>\e[0m | gzip -c | ssh \e[32m<host>\e[0m 'gunzip | mysql --default-character-set=utf8 -u\e[32m<remote_user>\e[0m -p\e[32m<remote_password>\e[0m \e[32m<remote_database>\e[0m'
# basically dump the local database into gzip, pass that on to ssh which pours it in the quoted command pipeline it's given
# \e[33mDrop a remote database into a local file
$ ssh \e[32m<host>\e[0m 'mysqldump -u\e[32m<user>\e[0m -p\e[32m<pass>\e[0m --default-character-set=utf8 \e[32m<database>\e[0m | gzip -c' > db_dump.sql.gz
# \e[33mDrop the local file into a remote server
$ cat db_dump.sql.gz | ssh \e[32m<host>\e[0m 'gunzip | mysql --default-character-set=utf8 -u\e[32m<user>\e[0m -p\e[32m<pass>\e[0m \e[32m<database>\e[0m'
# maybe run 'history -c' after stuffing the commandlines with passwords.
# \e[33mGrab a database from one remote mysql server and put it in another remote mysql server (because we can!)
ssh \e[32m<source_host>\e[0m 'mysqldump -u\e[32m<user>\e[0m -p\e[32m<pass>\e[0m \e[32m<database>\e[0m | gzip -c ' | ssh \e[32m<target_host>\e[0m 'gunzip | mysql -u\e[32m<user>\e[0m -p\e[32m<pass>\e[0m \e[32m<database>\e[0m'
# \e[33mExtract a table structure and its data from a Default MySQL data dump:
$ sed -n -e '/-- Table structure for table `\e[32;1mmy_table\e[0m`/,/-- Table structure for/p' \e[32;1mdatabase.sql\e[0m > \e[32;1mmy_table.sql\e[0m
# \e[32;1m-n\e[0m suppress default output \e[32;1m-e\e[0m add expression, then start/end regexes. \e[32;1mp\e[0m for print matching lines.