victorpendleton

#!/bin/bash
# This file contains the user/password combination to connect to the database
source creds.sh
DATE=`date +%Y%m%d`
instancedirectory="/var/lib/"
retval=-1
backupdirectory="${1}"
socket="${instancedirectory}/mysql.sock"
config="${instancedirectory}/mysql.cnf"
targetdirectory="${backupdirectory}/${DATE}/"

victorpendleton

SELECT p.ID 
, p.User 
, SUBSTRING(p.Host, 1, LOCATE(':', p.Host) -1) AS Host
, SUBSTRING(p.Host, LOCATE(':', p.Host) + 1) AS Port
, ROUND(p.Time/60, 2) Mins 
, p.command 
, p.State 
, p.rows_sent 
, p.rows_examined
, SUBSTRING(p.INFO, 0, 50) Query

victorpendleton

SELECT p.id 
, p.user 
, p.host
, p.command 
, p.time 
, p.time/60 as minutes 
, p.state 
, substring(p.info, 1, 80) as snippet 
FROM information_schema.processlist p 
WHERE p.user IN ('system user')

victorpendleton

SHOW SLAVE STATUS\G
-- Grab Primary Host from `Retrieved_Gtid_Set`
-- Retrieved_Gtid_Set: PRIMARY_GUID:1234567
-- Find what value has been executed from primary from Executed_Gtid_Set
-- Executed_Gtid_Set: PRIMARY_GUID:123455
STOP SLAVE;
-- Set the gtid to next value
SET GTID_NEXT ='PRIMARY_GUID:123456';
begin; commit;
set gtid_next = 'AUTOMATIC';

victorpendleton

#!/bin/bash

## Pull INSERTs
/usr/local/mysql/bin/mysqlbinlog server-bin.1 --base64-output=DECODE-ROWS
 --start-datetime="YYYY-MM-DD HH:MM:SS" 
--stop-datetime="YYYY-MM-DD HH:MM:SS" 
-vv | grep -iI "INSERT INTO \`database-name" | sort -n | uniq -c | sort -rn

## Pull UPDATEs
/usr/local/mysql/bin/mysqlbinlog server-bin.1 --base64-output=DECODE-ROWS

victorpendleton

SELECT p.user 
, substring(p.host, 1, IF( (locate(':', p.host)-1)<0, LENGTH(p.host), locate(':', p.host)-1 ) ) as host
, Substring(p.info, 1, 56) as snippet
, count(*) 
FROM information_schema.processlist p 
LEFT OUTER JOIN information_schema.innodb_trx t 
ON p.id = t.trx_mysql_thread_id 
WHERE p.command not in ('Binlog Dump GTID', 'Binlog Dump') 
AND p.user NOT IN ('system user') 
AND p.id <> connection_id() 

victorpendleton

-- View processes with transaction information
SELECT p.id
, p.user
, substring(p.host, 1, IF( (locate(':', p.host)-1)<0, LENGTH(p.host), locate(':', p.host)-1 ) ) as host
, SUBSTRING(p.Host, LOCATE(':', p.Host) + 1) AS Port
, p.command
, t.trx_started AS transaction_start_time
, p.time
, p.time/60 as minutes
, p.state

victorpendleton

# https://www.rabbitmq.com/rabbitmqctl.8.html

# Create virtual host
sudo rabbitmqctl add_vhost host_name

# Create user
sudo rabbitmqctl add_user user password

# Set tags for user
sudo rabbitmqctl set_user_tags user [administrator|management]

victorpendleton

SELECT n.nspname, c.relname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS total_bytes 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname NOT LIKE 'pg%' 
GROUP BY 1, 2
ORDER BY 2;

victorpendleton

SELECT n.nspname
, ROUND(SUM(pg_total_relation_size(c.oid))/1024/1024, 2) AS MB 
FROM pg_class c INNER 
JOIN  pg_namespace n 
ON c.relnamespace = n.oid 
WHERE relkind = 'r' 
AND n.nspname NOT LIKE 'pg%' 
GROUP BY 1 
ORDER BY 2;