Mysql tips

1
2
3
4
5
6
7

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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

1
2
3
4
5
6

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;

1
2
3
4

# RDS Mysql
ssh -t -N -L 3306:obmediadb-staging.cluster-c6fqne7ig0cv.us-east-1.rds.amazonaws.com:3306 -i ~/.ssh/obmedia-devops.pem ubuntu@3.221.61.85
# then in a new terminal
mysql -h 127.0.0.1 -u dbuser -p

1

SHOW VARIABLES LIKE '%collation%';

1
2

# check how to create an existing table
show create table serpTermStrategy