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 user== | ||
<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> | ||
| − | ==View users== | + | ===View users=== |
select host, user, password from mysql.user; | select host, user, password from mysql.user; | ||
<ref>http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql</ref> | <ref>http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql</ref> | ||
| − | ==Remove history== | + | ===Remove history=== |
(this is kept in your home directory) | (this is kept in your home directory) | ||
rm -rf ~/.mysql_history | rm -rf ~/.mysql_history | ||
| − | ==Remove user== | + | ===Remove user=== |
drop user <username>@<host>; | drop user <username>@<host>; | ||
| − | ==Remove database== | + | ===Remove database=== |
drop database <databasename>; | drop database <databasename>; | ||
| − | ==Grant privileges== | + | ===Grant privileges=== |
<pre> | <pre> | ||
grant all on <database>.<table> to <user>@<host> identified by '<password>'; | grant all on <database>.<table> to <user>@<host> identified by '<password>'; | ||
| Line 16: | Line 17: | ||
</pre> | </pre> | ||
<ref>http://dev.mysql.com/doc/refman/5.7/en/show-grants.html</ref> | <ref>http://dev.mysql.com/doc/refman/5.7/en/show-grants.html</ref> | ||
| − | ==Show granted privileges== | + | ===Show granted privileges=== |
<pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | <pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | ||
| − | ==Delete rows older than certain date== | + | ===Delete rows older than certain date=== |
<ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref> | <ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref> | ||
<pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre> | <pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre> | ||
| + | ==AWS RDS== | ||
Revision as of 12:40, 12 June 2018
Contents
General user
View users
select host, user, password from mysql.user;
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';
Delete rows older than certain date
DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);
