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

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

-- 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

#!/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}"
exportconfig="${2}"
socket="${instancedirectory}/mysql.sock"
config="${instancedirectory}/mysql.cnf"

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

-- Get the unique PARTITION_EXPRESSION values
SELECT DISTINCT PARTITION_EXPRESSION
FROM information_schema.partitions;

-- Mappings
-- TO_DAYS() => FROM_DAYS()
-- UNIX_TIMESTAMP() => FROM_UNIXTIME()

SELECT TABLE_SCHEMA
, TABLE_NAME

victorpendleton

SELECT '00000001' || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM 1 FOR position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))-1), 8, '0') || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))+1 FOR 2), 8, '0') as last_wal
, SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))+3 ) as last_lsn
, '00000001' || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM 1 FOR position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))-1), 8, '0') || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))+1 FOR 2), 8, '0') as last_wal_rev
, SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))+3 ) as last_lsn_recv
, CASE WHE

victorpendleton

SELECT user
, total_connections
, concurrent_connections
, select_commands
, rows_fetched
, table_rows_read
, IF (table_rows_read >= rows_fetched
,ROUND((rows_fetched/table_rows_read), 5) * 100
, 100) as efficiency 
FROM information_schema.user_statistics 

victorpendleton

STOP SLAVE;
SET GTID_NEXT="";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
SHOW SLAVE STATUS\G

victorpendleton

SELECT t.trx_id 
, t.trx_mysql_thread_id 
, p.User 
, SUBSTRING(p.Host, 1, LOCATE(':', p.Host) -1) AS Host
, SUBSTRING(p.Host, LOCATE(':', p.Host) + 1) AS Port
, t.trx_state 
, t.trx_started 
, ROUND(p.Time/60, 2) Mins 
, p.command 
, p.State