==== 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 !) Usage: MySQL> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Function: MySQL .my.cnf Usage: Place into /root/.my.cnf [client] user=root password="password" Function: **DANGEROUS** Convert all MyISAM to innodb (Do not do to tables using FULLTEXT) Usage: # 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'@'192.168.1.1' to 'magentouser'@'%'; Function: Purge Binary Logs Usage: SHOW MASTER LOGS PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; Function: Check Size of All Databases Usage: SELECT table_schema 'database', concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size FROM information_schema.TABLES WHERE ENGINE=('MyISAM' || 'InnoDB' ) GROUP BY table_schema; Function: Check Size of of all Tables (Detailed) Usage: 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) Usage: 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 Usage: 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 ---------------------------------------- UNSORTED --------------------------------------- mysql> SELECT COUNT(1) ReplicationThreadCount FROM information_schema.processlist -> WHERE user = 'replicant'; +------------------------+ | ReplicationThreadCount | +------------------------+ | 0 | +------------------------+ mysql -Bse 'show status;' | grep -i max_used