Created
February 1, 2018 03:26
-
-
Save jason-idk/a025d6399fe952d8411e7d1c3b882104 to your computer and use it in GitHub Desktop.
Some notes I took on mysql over time...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Random MySQL Notes: | |
#### Login to MySQL on Plesk by issuing: | |
If you cannot access DB on Ubuntu, check /etc/mysql/debian.cnf it will be in clear text... Im not joking... | |
(only on debian based systems… this is definitely a hack and not supposed to be widely used.) | |
mysql -u admin -p`cat /etc/psa/.psa.shadow` | |
#### Lock tables before dumping database: (and unlock) | |
flush tables with read lock; | |
unlock tables; | |
#### Example of db dump: | |
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` databasename > /path/to/20160608-databasename.sql | |
mysqldump -u root -p [options] —all-databases > alldb.sql | |
After dumping the DB, you should gzip it and then scp it to the new system. | |
mysqldump -u root -p --all-databases --lock-tables --events --triggers | |
#### Dump only data, no schema: | |
mysqldump --no-create-db --no-create-info --skip-triggers <database_name> | |
#### Dump all grants in readable format: | |
unset HISTFILE (on command line to not save password in history.) | |
mysql -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;}' | |
#### Listing databases: | |
show databases; | |
+-------------------------+ | |
| Database | | |
+-------------------------+ | |
| information_schema | | |
| apsc | | |
| horde | | |
| mysql | | |
| phpmyadmin_SUCKS | | |
| phpmyadmin_SUCKS | | |
| phpmyadmin_SUCKS | | |
| phpmyadmin_SUCKS | | |
| psa | | |
| dbnsmasd | | |
| wordpress_6 | | |
+-------------------------+ | |
14 rows in set (0.00 sec) | |
#### Using database: | |
use db_name; | |
#### List tables for database: | |
show tables; | |
#### Show permissions for user: | |
mysql -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;}' | |
show grants for ‘user’@‘localhost'; | |
+---------------------------------------------------------------------+ | |
| Grants for user@localhost | | |
+---------------------------------------------------------------------+ | |
| GRANT USAGE ON *.* TO 'user'@'localhost' | | |
| GRANT ALL PRIVILEGES ON `database`.* TO ‘user’@‘localhost' | | |
| GRANT ALL PRIVILEGES ON `database_two`.* TO ‘user’@‘localhost' | | |
+---------------------------------------------------------------------+ | |
3 rows in set (0.00 sec) | |
#### Show database sizes: | |
SELECT 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; | |
#### Show engine for tables in particular DB: | |
SELECT ‘ENGINE’ FROM ‘information_schema’.’TABLES’ WHERE ‘TABLE_SCHEMA’=‘ctp’; | |
##### Order to bring in data involving triggers and stored procedures: | |
1) schema_only.sql --- This only creates tables and assumes that the database you are importing into exists (but does not necessarily have any data) | |
2) data_only.sql -- This is the insert of data only and assumes all required tables are already created | |
3) triggers_and_procedures.sql -- This should be only triggers and procedures with no actual data. | |
### Script to dump all views for a database specified on command line: | |
#!/bin/bash -e | |
mysql --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = database()" $* | | |
xargs --max-args 1 mysqldump $* | |
#### Show User info | |
mysql> select user, host, password from mysql.user; | |
+----------+-------------+-------------------------------------------+ | |
| user | host | password | | |
+----------+-------------+-------------------------------------------+ | |
| root | localhost | *26D5462B39EdsgfsdfgdfgfdsfE484E00EC9C61A | | |
| root | 127.0.0.1 | *26D5462sdfgdfgfdsfgsdfgggggg84E00EC9C61A | | |
| | localhost | | | |
| | skyi01vma01 | | | |
| user1sa | localhost | *70A893D8jhgfjhg698B90FE28D1911207879D2B4 | | |
| user1sa | % | *5650FFD13A51DCCBFD295D06jjhgjgj69584B1FD | | |
| user1sa | 127.0.0.1 | *5650FFD13A51Dyougfggayy6B9CCE7C69584B1FD | | |
+----------+-------------+-------------------------------------------+ | |
7 rows in set (0.01 sec) | |
##### Unset history for mysql (do this so the clear text password is not stored in the history) | |
mysql -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;}' | |
##### Create User and grant privileges | |
GRANT USAGE ON *.* TO 'user'@'localhost'; | |
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'P2SSw0d!@#$%@' WITH GRANT OPTION; | |
GRANT ALL PRIVILEGES ON *.* TO 'user'@'ip.ip.ip.ip' IDENTIFIED BY ‘P2SSw0d!@#$%@’ WITH GRANT OPTION; | |
#### Grant permissions for user Continued: IP Address or % for all | |
^ | |
GRANT USAGE ON *.* TO ‘user_admin'@'ip.ip.ip.ip' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325'; | |
GRANT ALL PRIVILEGES ON *.* TO ‘user’@‘100.0.0.0/255.0.0.0' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325'; | |
#### Show active connections to database; | |
show status like '%onn%'; | |
#### View data from table, (must use database first): | |
select * from table_name limit 10; | |
EXTRA ----| | |
| | |
#### Start secondary instance of MySQL: | |
-- make configuration file/log files. | |
mkdir /var/lib/mysql2 | |
chown -R mysql.mysql /var/lib/mysql2/ | |
mkdir /var/log/mysql2 | |
chown -R mysql.mysql /var/log/mysql2 | |
cp -R /etc/mysql/ /etc/mysql2 | |
cp /etc/my.cnf /etc/my2.cnf | |
-- Replace necessary directives in config file. | |
cd /etc/mysql2/ | |
sed -i 's/3306/3307/g' my.cnf | |
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf | |
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf | |
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf | |
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf | |
-- Initialize default DBs. | |
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/ | |
-- Start the new instance and connect to it. | |
mysqld_safe --defaults-file=/etc/mysql2/my.cnf & | |
mysql -S /var/run/mysqld/mysqld2.sock | |
-- To stop the secondary instance, do... | |
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment