jfriv

SELECT table_schema, table_name, (data_length / 1024 / 1024 / 1024) as data_size_gb, (index_length / 1024 / 1024 / 1024) as index_size_gb 
FROM information_schema.TABLES 
WHERE table_schema='mydatabase' 
GROUP BY table_name
ORDER BY index_length DESC LIMIT 10;

jfriv

SELECT 'TABLE_SCHEMA', 'TABLE_NAME', 'CONSTRAINT_NAME', 'REFERENCED_TABLE_NAME', 'UNIQUE_CONSTRAINT_NAME', 'UNIQUE_CONSTRAINT_SCHEMA', 'UPDATE_RULE', 'DELETE_RULE'
UNION ALL

SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.REFERENCED_TABLE_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_SCHEMA, rc.UPDATE_RULE, rc.DELETE_RULE

INTO OUTFILE '/tmp/fk_audit_mydatabase.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

jfriv

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'mytable'

jfriv

GRANT ALL PRIVILEGES ON mydatabase.* to 'myuser'@'10.%' IDENTIFIED BY 'mypassword';
GRANT SELECT, CREATE TEMPORARY TABLES, SHOW DATABASES ON *.* TO 'myuser'@'10.%' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

jfriv

------------------------------------------------------------------------------
--- select all tables which have FKs that reference a table
------------------------------------------------------------------------------
SELECT *
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE referenced_table_name LIKE '%{table}%';

jfriv

---------------------------------------------------------------------
---- build sql to drop all foreign keys for a table
---------------------------------------------------------------------

SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` DROP FOREIGN KEY ',constraint_name,';')
    FROM information_schema.table_constraints
    WHERE constraint_type='FOREIGN KEY'
    AND table_schema='{database}'
    AND table_name IN ('{table}');

jfriv

#!/bin/bash

# set up some variables

NOW_DATE=$(date '+%Y-%m-%d-%H-%M')
RESTORE_FROM_INSTANCE_ID=<source name>
TARGET_INSTANCE_ID=<target name>
TARGET_INSTANCE_CLASS=db.m4.large
VPC_ID=<vpc subnet id>
NEW_MASTER_PASS=<root password>

jfriv

server {
	listen 80 default_server;
	server_name _;

	root /Users/MacbookPro/Sites/$host/;

	autoindex on;		
	index index.html index.php;

	# include php settings