Handy MySQL commands

mysql.md

Kill all threads from a MySQL User

mysql -BNe "SELECT id FROM processlist WHERE user = 'redmine';" information_schema | while read id; do mysqladmin kill $id; done

Debug Tmp Disk Tables

SELECT * FROM performance_schema.events_statements_summary_by_digest order by SUM_CREATED_TMP_DISK_TABLES desc limit 10\G

Show processlist by IP

SELECT
    tmp.ipAddress,

    -- Calculate how many connections are being held by this IP address.
    COUNT( * ) AS ipAddressCount,

    -- For each connection, the TIME column represent how many SECONDS it has been in
    -- its current state. Running some aggregates will give us a fuzzy picture of what
    -- the connections from this IP address is doing.
    FLOOR( AVG( tmp.time ) ) AS timeAVG,
    MAX( tmp.time ) AS timeMAX
FROM
    -- Let's create an intermediary table that includes an additional column representing
    -- the client IP address without the port.
    (

        SELECT
            -- We don't actually need all of these columns for the demo. But, I'm
            -- including them here to demonstrate what fields COULD be used in the
            -- processlist system.
            pl.id,
            pl.user,
            pl.host,
            pl.db,
            pl.command,
            pl.time,
            pl.state,
            pl.info,

            -- The host column is in the format of "IP:PORT". We want to strip off
            -- the port number so that we can group the results by the IP alone.
            LEFT( pl.host, ( LOCATE( ':', pl.host ) - 1 ) ) AS ipAddress
        FROM
            INFORMATION_SCHEMA.PROCESSLIST pl

    ) AS tmp
GROUP BY
    tmp.ipAddress
ORDER BY
ipAddressCount DESC

Show performance schema memory usage

mysql -hxxxx -Pxxx -uxx -pxx -e "show engine performance_schema status"|grep memory|sort -nr -k3 |head

Get Table data and index size 1

SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 100;

Get Table data and index size 2

SELECT 
	table_schema,
	SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
	SUM(data_length)/1024/1024 AS data_mb,
	SUM(index_length)/1024/1024 AS index_mb,
	SUM(data_free)/1024/1024 AS free_mb,
	COUNT(*) AS tables,
	CURDATE() AS today 
FROM 
	information_schema.tables
	GROUP BY table_schema
	ORDER BY 2 DESC
;

SELECT 
	table_schema,
	table_name,
	SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
	SUM(data_length)/1024/1024 AS data_mb,
	SUM(index_length)/1024/1024 AS index_mb,
	SUM(data_free)/1024/1024 AS free_mb,
	COUNT(*) AS tables,
	CURDATE() AS today 
FROM 
	information_schema.tables
	GROUP BY table_schema, table_name
	ORDER BY 3 DESC
;

Get free disk space

SELECT
	table_name,
	data_length,
	max_data_length,
	index_length,
	data_free
FROM
	information_schema.tables 
WHERE table_name='schema_name'
;

Get size of each index

SELECT
       sum(stat_value) pages,
       index_name,
       sum(stat_value) * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
       table_name = 't'
       AND database_name = 'db'
       AND stat_description = 'Number of pages in the index'
GROUP BY
       index_name;

ProxySQL

Reset stats

select * from stats_mysql_query_digest_reset limit 1;

Finding most frequent SELECT query

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 15;

# aggregate across shards
SELECT digest,SUBSTR(digest_text,0,25),sum(count_star),sum(sum_time),sum(sum_time)/sum(count_star) avg_time, min(min_time), max(max_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' GROUP BY digest ORDER BY sum(count_star) DESC LIMIT 15;

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 15\G

Flush ProxySQL logs

sudo rm /var/lib/proxysql/proxysql.log
echo "PROXYSQL FLUSH LOGS;" | mysql -uadmin -padmin -h127.0.0.1 -P6032