Some notes I took on mysql over time...

mysql-notes.txt

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