Cheet Sheet

Most of the commands i use at work which used for my reference

MYSQL

Show Database by Size

SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Show Size consumed by Tables in Database (Change database_name)

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Standard Operations

-- Get All Users
SELECT User FROM mysql.user;

-- Create User
CREATE USER 'john'@'%' IDENTIFIED BY 'secret';

-- Grant Access After User Creation
-- Readonly Access
GRANT SELECT ON *.* TO 'john'@'%' IDENTIFIED BY 'secret';

-- All Access
GRANT ALL PRIVILEGES ON *.* TO 'john'@'%' IDENTIFIED BY 'secret';

-- Delete User
DELETE FROM mysql.user WHERE user = 'john';

-- Get List of Active Connections
show processlist

Shell

Compression & Decompression

# Compression on Files
gzip -k -9 filename (9 is compression Ratio)

# Compression on Directories
GZIP=-9 tar cvzf log.tar.gz /path/to/directory
tar cv catalina.out-20180722 | gzip –best > file.tar.gz

# Decompression
tar -xvzf file.tar.gz

Encryption & Decryption

gpg --batch --passphrase-file key.txt -c data.txt
gpg --batch --passphrase-file key.txt -d data.txt.gpg > b.txt

Disk Space Usage

ncdu /
df -h

Copy, Move, Sync Files to S3 Bucket (Note – Attach S3 Write Access Role to EC2 Instance)

aws s3 cp database-backup.sql s3://bucketname
aws s3 mv database-backup.sql s3://bucketname
aws s3 sync database-backup.sql s3://bucketname