==== MySQL RELATED ====

Function: MySQL Tuner
Usage: perl <( curl -Lk4 https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl)
Usage: perl <( curl -Lk4 https://hoshisato.com/tools/code/mysqltuner.pl)

Function: MySQL Tuner for Cloud DB
Usage:  perl <( curl -Lk4 https://hoshisato.com/tools/code/mysqltuner.pl) \
--host 000000000000000.rackspaceclouddb.com --user user --pass "PASSWORD" --forcemem 1024

Function: MySQL Primer
Usage: curl -k4 https://hoshisato.com/tools/code/tuning-primer.sh | bash

Function: MySQL Show User Host Passwords
Usage: select user,host,password from mysql.user;

Function: MySQL Show User Host/DB Relationship
Usage: SELECT user,host,db from mysql.db;

Function: Show MySQL Processlist Updated Every Second
Usage: mysqladmin -u root -p -i 1 processlist

Function: Show MySQL Thread and Connection Count
Usage: mysqladmin extended-status | grep -wi 'threads_connected\|threads_running' | awk '{ print $2,$4}'

Function: Skip Bad Transaction on Slave (Not recommended !)

Function: MySQL .my.cnf 
Usage: Place into /root/.my.cnf 

Function: **DANGEROUS** Convert all MyISAM to innodb (Do not do to tables using FULLTEXT)
# for table in $(mysql -N -e "SELECT CONCAT('\`',table_schema,'\`.\`',table_name,'\`') 
FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') 
AND engine = 'MyISAM' AND table_name NOT IN (SELECT TABLE_name FROM information_schema.statistics 
WHERE index_type LIKE 'FULLTEXT%');"); do echo ${table}; mysql -e "ALTER TABLE ${table} ENGINE = InnoDB"; done

Function: Update Password 
Usage: UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

Function: Update Password
Usage: UPDATE mysql.user SET authentication_string=PASSWORD("myinsecurepasswd") where user="root" and host="localhost";

Function: MyISAM or INNODB
Usage: SHOW TABLE STATUS FROM `database`;

Function: Grant All Privileges / Create New User
Usage: GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password";

Function: Rename/Update MySQL User
Usage: RENAME USER 'magentouser'@'' to 'magentouser'@'%';

Function: Purge Binary Logs
PURGE BINARY LOGS TO 'mysql-bin.010'; 
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Function: Check Size of All Databases
SELECT table_schema 'database',
concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
GROUP BY table_schema;

Function: Check Size of of all Tables (Detailed)
SELECT CONCAT(engine, ':  ',table_schema, '.', table_name),
   CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
   CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
   CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') indx,
   CONCAT(ROUND(data_free / ( 1024 * 1024 * 1024 ), 2), 'G') free_space,
   CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
   ROUND(index_length / data_length, 2) idxfrac
FROM   information_schema.TABLES 
# WHERE engine != 'InnoDB'
ORDER  BY (data_length + index_length) DESC
LIMIT  10;

Function: Check Size of of all Tables (Hint: Replace MY_DATABASE_NAME Value)
SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = "MY_DATABASE_NAME" ORDER BY (data_length + index_length) DESC;

Function: Optimize Tables
mysqlcheck -o db_schema_name
mysqlcheck -o --all-databases

Function: Watch MySQL Processlist
Usage: mysqladmin -u root -p -i 1 processlist

Function: Show all MyISAM Tables using FULLTEXT
Usage: SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE index_type LIKE 'FULLTEXT%';

Function: Show all MyISAM Tables 
Usage: select table_name,table_schema,data_length/1024/1024 AS size_in_MB,engine from information_schema.tables where table_schema not in ('mysql','information_schema') and engine='MyISAM' order by data_length ASC;

Function: Import MySQL Database - Works great for large datasets, best done in a screen session.
Usage:zcat allDB.sql.gz | mysql MY-DataBase --debug-check --force --reconnect --sigint-ignore --verbose --wait

Function: TBD
Usage: TBD
mysql> SELECT COUNT(1) ReplicationThreadCount FROM information_schema.processlist
    -> WHERE user = 'replicant';
| ReplicationThreadCount |
|                      0 |

mysql -Bse 'show status;' | grep -i max_used