Difference between revisions of "MySQL Cheat Sheet"
Jump to navigation
Jump to search
Michael.mast (talk | contribs) |
Michael.mast (talk | contribs) |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
<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=== | + | ====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 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> | <pre> | ||
select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%'); | select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%'); | ||
| + | </pre> | ||
| + | ====Transactions per second==== | ||
| + | Something helpful for when you are looking to migrate to the cloud.<ref>https://forums.mysql.com/read.php?10,532967,533420</ref> | ||
| + | <pre> | ||
| + | [root@mysql01 ~]# mysql -uroot -p | ||
| + | Enter password: | ||
| + | Welcome to the MariaDB monitor. Commands end with ; or \g. | ||
| + | Your MariaDB connection id is 7841411 | ||
| + | Server version: 10.1.16-MariaDB MariaDB Server | ||
| + | |||
| + | Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. | ||
| + | |||
| + | Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. | ||
| + | |||
| + | MariaDB [(none)]> show global status like "Questions"; | ||
| + | +---------------+-----------+ | ||
| + | | Variable_name | Value | | ||
| + | +---------------+-----------+ | ||
| + | | Questions | 549355331 | | ||
| + | +---------------+-----------+ | ||
| + | 1 row in set (0.08 sec) | ||
| + | |||
| + | MariaDB [(none)]> show global status like "Uptime"; | ||
| + | +---------------+----------+ | ||
| + | | Variable_name | Value | | ||
| + | +---------------+----------+ | ||
| + | | Uptime | 27537171 | | ||
| + | +---------------+----------+ | ||
| + | 1 row in set (0.00 sec) | ||
| + | |||
| + | </pre> | ||
| + | To do this without a calculator<ref>https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html</ref><ref>https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-variable-tables.html</ref> | ||
| + | <pre> | ||
| + | MariaDB [(none)]> select (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME'); | ||
| + | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ||
| + | | (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME') | | ||
| + | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ||
| + | | 19 | | ||
| + | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ||
| + | 1 row in set (0.00 sec) | ||
</pre> | </pre> | ||
| Line 25: | Line 65: | ||
(this is kept in your home directory) | (this is kept in your home directory) | ||
rm -rf ~/.mysql_history | rm -rf ~/.mysql_history | ||
| − | ===Remove user=== | + | ===User Management=== |
| + | ====Add User==== | ||
| + | create user 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; | ||
| + | ====Remove user==== | ||
drop user <username>@<host>; | drop user <username>@<host>; | ||
===Remove database=== | ===Remove database=== | ||
| Line 37: | Line 80: | ||
===Show granted privileges=== | ===Show granted privileges=== | ||
<pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | <pre>SHOW GRANTS FOR 'root'@'localhost';</pre> | ||
| + | ===Show last X rows=== | ||
| + | <ref>https://stackoverflow.com/questions/4714975/how-to-select-the-last-10-rows-of-an-sql-table-which-has-no-id-field/14057040</ref> | ||
| + | <pre> | ||
| + | MariaDB [<database>]> select * from <table> order by <column> desc limit 10; | ||
| + | </pre> | ||
===View users=== | ===View users=== | ||
| Line 54: | Line 102: | ||
<pre> | <pre> | ||
grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl; | grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl; | ||
| + | </pre> | ||
| + | ==Scripted Secure Installation== | ||
| + | <ref>https://stackoverflow.com/questions/24270733/automate-mysql-secure-installation-with-echo-command-via-a-shell-script/35004940</ref> | ||
| + | <pre> | ||
| + | UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root'; | ||
| + | DELETE FROM mysql.user WHERE User=''; | ||
| + | DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); | ||
| + | DROP DATABASE IF EXISTS test; | ||
| + | DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'; | ||
| + | FLUSH PRIVILEGES; | ||
</pre> | </pre> | ||
Latest revision as of 08:56, 4 October 2022
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%');
Transactions per second
Something helpful for when you are looking to migrate to the cloud.[2]
[root@mysql01 ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7841411 Server version: 10.1.16-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global status like "Questions"; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Questions | 549355331 | +---------------+-----------+ 1 row in set (0.08 sec) MariaDB [(none)]> show global status like "Uptime"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Uptime | 27537171 | +---------------+----------+ 1 row in set (0.00 sec)
To do this without a calculator[3][4]
MariaDB [(none)]> select (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME'); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME') | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 19 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
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
User Management
Add User
create user 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
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';
Show last X rows
MariaDB [<database>]> select * from <table> order by <column> desc limit 10;
View users
select host, user, password from mysql.user;
AWS RDS
After creating the RDS instance, download the CA public key[11].
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[12]
grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl;
Scripted Secure Installation
UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
- ↑ http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database
- ↑ https://forums.mysql.com/read.php?10,532967,533420
- ↑ https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html
- ↑ https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-variable-tables.html
- ↑ 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
- ↑ https://stackoverflow.com/questions/4714975/how-to-select-the-last-10-rows-of-an-sql-table-which-has-no-id-field/14057040
- ↑ 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
- ↑ https://stackoverflow.com/questions/24270733/automate-mysql-secure-installation-with-echo-command-via-a-shell-script/35004940
