Mysql tips Link to heading
Create user in AWS RDS and grant permissions like a master
CREATE USER 'jacob'@'%' IDENTIFIED BY 'xxxxxxxx';
GRANT SELECT,INSERT,UPDATE,DELETE,DROP on synapbox_qa.* TO 'jacob'@'%';
or
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3, INVOKE LAMBDA ON synapbox_qa.* TO 'jacob'@'%' IDENTIFIED BY PASSWORD WITH GRANT OPTION
Need to check all the queries in live mode?: Link to heading
mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log = 'OFF';
Check size in disk on each table: Link to heading
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;
SSH Mysql tunnel Link to heading
# RDS Mysql
ssh -t -N -L 3306:acmeorgdb-staging.cluster-xxxxxx.us-east-1.rds.amazonaws.com:3306 -i ~/.ssh/acmeorg-devops.pem ubuntu@$HOST
# then in a new terminal
mysql -h 127.0.0.1 -u dbuser -p
Check collation Link to heading
SHOW VARIABLES LIKE '%collation%';
Others Link to heading
# check how to create an existing table
show create table serpTermStrategy
Good to know Link to heading
mysqldump
can dump data and compress it with different algorithms and levels of preset.