f9uPEK6R

1Random MySQL Notes:
2
3#### Login to MySQL on Plesk by issuing: 
4If you cannot access DB on Ubuntu, check /etc/mysql/debian.cnf it will be in clear text... Im not joking...
5
6(only on debian based systems… this is definitely a hack and not supposed to be widely used.)
7mysql -u admin -p`cat /etc/psa/.psa.shadow`
8
9
10
11#### Lock tables before dumping database: (and unlock)
12flush tables with read lock;
13
14unlock tables;
15
16
17
18#### Example of db dump:
19mysqldump -u admin -p`cat /etc/psa/.psa.shadow` databasename > /path/to/20160608-databasename.sql
20
21mysqldump -u root -p [options] —all-databases > alldb.sql
22
23After dumping the DB, you should gzip it and then scp it to the new system. 
24
25mysqldump -u root -p --all-databases --lock-tables --events --triggers
26
27
28#### Dump only data, no schema:
29mysqldump --no-create-db --no-create-info --skip-triggers <database_name>
30
31
32#### Dump all grants in readable format:
33unset HISTFILE (on command line to not save password in history.)
34
35mysql -pSuPerTot35s3cUr##! -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql -pSuPerTot35s3cUr##!  $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
36
37
38
39
40#### Listing databases:
41show databases;
42
43+-------------------------+
44| Database                |
45+-------------------------+
46| information_schema      |
47| apsc                    |
48| horde                   |
49| mysql                   |
50| phpmyadmin_SUCKS        |
51| phpmyadmin_SUCKS        |
52| phpmyadmin_SUCKS        |
53| phpmyadmin_SUCKS        |
54| psa                     |
55| dbnsmasd                |
56| wordpress_6             |
57+-------------------------+
5814 rows in set (0.00 sec)
59
60
61
62#### Using database:
63use db_name;
64
65
66
67#### List tables for database:
68show tables;
69
70
71
72#### Show permissions for user:
73mysql -uroot -p -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql -uroot -p $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
74
75
76show grants for ‘user’@‘localhost';
77
78+---------------------------------------------------------------------+
79| Grants for user@localhost                                         |
80+---------------------------------------------------------------------+
81| GRANT USAGE ON *.* TO 'user'@'localhost'                          |
82| GRANT ALL PRIVILEGES ON `database`.* TO ‘user’@‘localhost'          |
83| GRANT ALL PRIVILEGES ON `database_two`.* TO ‘user’@‘localhost' |
84+---------------------------------------------------------------------+
853 rows in set (0.00 sec)
86
87
88
89
90#### Show database sizes:
91SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
92
93
94
95
96#### Show engine for tables in particular DB:
97SELECT ‘ENGINE’ FROM ‘information_schema’.’TABLES’  WHERE ‘TABLE_SCHEMA’=‘ctp’;
98
99
100
101
102##### Order to bring in data involving triggers and stored procedures:
103
1041) schema_only.sql  --- This only creates tables and assumes that the database you are importing into exists (but does not necessarily have any data) 
1052) data_only.sql  -- This is the insert of data only and assumes all required tables are already created 
1063) triggers_and_procedures.sql -- This should be only triggers and procedures with no actual data.
107
108
109
110
111### Script to dump all views for a database specified on command line:
112#!/bin/bash -e
113
114mysql --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = database()" $* |
115xargs --max-args 1 mysqldump $*
116
117
118
119
120
121#### Show User info
122mysql> select user, host, password from mysql.user;
123+----------+-------------+-------------------------------------------+
124| user     | host        | password                                  |
125+----------+-------------+-------------------------------------------+
126| root     | localhost   | *26D5462B39EdsgfsdfgdfgfdsfE484E00EC9C61A |
127| root     | 127.0.0.1   | *26D5462sdfgdfgfdsfgsdfgggggg84E00EC9C61A |
128|          | localhost   |                                           |
129|          | skyi01vma01 |                                           |
130| user1sa  | localhost   | *70A893D8jhgfjhg698B90FE28D1911207879D2B4 |
131| user1sa  | %           | *5650FFD13A51DCCBFD295D06jjhgjgj69584B1FD |
132| user1sa  | 127.0.0.1   | *5650FFD13A51Dyougfggayy6B9CCE7C69584B1FD |
133+----------+-------------+-------------------------------------------+
1347 rows in set (0.01 sec)
135
136
137
138
139##### Unset history for mysql (do this so the clear text password is not stored in the history)
140mysql -p'passwd' -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | \mysql -p'passwd' $@ | \sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
141
142
143
144
145##### Create User and grant privileges
146GRANT USAGE ON *.* TO 'user'@'localhost';
147GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'P2SSw0d!@#$%@' WITH GRANT OPTION;
148GRANT ALL PRIVILEGES ON *.* TO 'user'@'ip.ip.ip.ip' IDENTIFIED BY ‘P2SSw0d!@#$%@’ WITH GRANT OPTION;
149
150
151
152#### Grant permissions for user Continued:    IP Address or % for all
153					^
154GRANT USAGE ON *.* TO ‘user_admin'@'ip.ip.ip.ip' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325';
155GRANT ALL PRIVILEGES ON *.* TO ‘user’@‘100.0.0.0/255.0.0.0' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325';
156
157
158
159#### Show active connections to database;
160show status like '%onn%';
161
162
163
164#### View data from table, (must use database first):
165select * from table_name limit 10;
166
167
168
169
170EXTRA ----|
171          |
172#### Start secondary instance of MySQL:
173-- make configuration file/log files.
174
175	mkdir /var/lib/mysql2
176	chown -R mysql.mysql /var/lib/mysql2/
177	mkdir /var/log/mysql2
178	chown -R mysql.mysql /var/log/mysql2
179	cp -R /etc/mysql/ /etc/mysql2
180	cp /etc/my.cnf /etc/my2.cnf
181
182-- Replace necessary directives in config file. 
183
184	cd /etc/mysql2/
185	sed -i 's/3306/3307/g' my.cnf
186	sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
187	sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
188	sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
189	sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf
190
191-- Initialize default DBs.
192
193	mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
194
195-- Start the new instance and connect to it.
196
197	mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
198	mysql -S /var/run/mysqld/mysqld2.sock
199
200-- To stop the secondary instance, do...
201
202	mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown