Difference between revisions of "MySQL Cheat Sheet"
Jump to navigation
Jump to search
Michael.mast (talk | contribs) |
Michael.mast (talk | contribs) |
||
| Line 1: | Line 1: | ||
==General use== | ==General use== | ||
<ref>http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database</ref> | <ref>http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database</ref> | ||
| + | |||
| + | ===Advanced Queries=== | ||
| + | ====Sub Query=== | ||
| + | Select from an "array" created from another query. You can only pull from a single column. In this example I am looking for users that are members of a group that has access to a directory. | ||
| + | <pre> | ||
| + | select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%'); | ||
| + | </pre> | ||
| + | |||
===Alter Table=== | ===Alter Table=== | ||
====Add columnn==== | ====Add columnn==== | ||
| Line 9: | Line 17: | ||
alter table <table name> modify column <column name> DECIMAL(10, 2); | alter table <table name> modify column <column name> DECIMAL(10, 2); | ||
| − | === | + | ===Delete rows older than certain date=== |
| − | + | <ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref> | |
| − | <ref> | + | <pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre> |
| + | |||
===Remove history=== | ===Remove history=== | ||
(this is kept in your home directory) | (this is kept in your home directory) | ||
| Line 27: | Line 36: | ||
===Show granted privileges=== | ===Show granted privileges=== | ||
<pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | <pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | ||
| − | === | + | |
| − | <ref> | + | ===View users=== |
| − | + | select host, user, password from mysql.user; | |
| + | <ref>http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql</ref> | ||
==AWS RDS== | ==AWS RDS== | ||
Revision as of 06:32, 25 July 2018
Contents
General use
Advanced Queries
=Sub Query
Select from an "array" created from another query. You can only pull from a single column. In this example I am looking for users that are members of a group that has access to a directory.
select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%');
Alter Table
Add columnn
alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;
Alter column
alter table
modify column <column name> DECIMAL(10, 2);Delete rows older than certain date
DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);
Remove history
(this is kept in your home directory)
rm -rf ~/.mysql_history
Remove user
drop user <username>@<host>;
Remove database
drop database <databasename>;
Grant privileges
grant all on <database>.<table> to <user>@<host> identified by '<password>'; grant all on test.* to 'michael'@'172.17.0.34' identified by 'mypassword';
Show granted privileges
SHOW GRANTS FOR 'root'@'localhost';
View users
select host, user, password from mysql.user;
AWS RDS
After creating the RDS instance, download the CA public key[7].
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
At this point you can log into the instance using TLS.
mysql -u<root user> -p -h xxxxxxx.xxxxxxxxxx.us-east-2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem
Then, as you create users makes sure to require the use of TLS[8]
grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl;
- ↑ http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database
- ↑ http://www.mysqltutorial.org/mysql-add-column/
- ↑ https://razorsql.com/features/mysql_change_column_type.html
- ↑ https://benperove.com/delete-mysql-rows-older-than-date/
- ↑ http://dev.mysql.com/doc/refman/5.7/en/show-grants.html
- ↑ http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql
- ↑ https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Security.html
- ↑ https://dev.mysql.com/doc/refman/5.7/en/alter-user.html#alter-user-tls
