Difference between revisions of "MySQL Cheat Sheet"

From Michael's Information Zone
Jump to navigation Jump to search
 
(10 intermediate revisions by the same user not shown)
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>
 +
====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>
 +
 
===Alter Table===
 
===Alter Table===
 
====Add columnn====
 
====Add columnn====
−
alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;
+
<ref>http://www.mysqltutorial.org/mysql-add-column/</ref>
 +
alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;
 +
====Alter column====
 +
<ref>https://razorsql.com/features/mysql_change_column_type.html</ref>
 +
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>
 +
<pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre>
  
−
===View users===
 
−
select host, user, password from mysql.user;
 
−
<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===
+
===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 23: Line 80:
 
===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===
+
===Show last X rows===
−
<ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref>
+
<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>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre>
+
<pre>
 +
MariaDB [<database>]> select * from <table> order by <column> desc limit 10;
 +
</pre>
 +
 
 +
===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==
Line 39: 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

General use

[1]

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

[5] 

alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;

Alter column

[6]

alter table

modify column <column name> DECIMAL(10, 2);

Delete rows older than certain date

[7] 

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

[8]

Show granted privileges

SHOW GRANTS FOR 'root'@'localhost';

Show last X rows

[9] 

MariaDB [<database>]> select * from <table> order by <column> desc limit 10;

View users

select host, user, password from mysql.user;

[10]

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

[13] 

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;
  1. ↑ http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database
  2. ↑ https://forums.mysql.com/read.php?10,532967,533420
  3. ↑ https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html
  4. ↑ https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-variable-tables.html
  5. ↑ http://www.mysqltutorial.org/mysql-add-column/
  6. ↑ https://razorsql.com/features/mysql_change_column_type.html
  7. ↑ https://benperove.com/delete-mysql-rows-older-than-date/
  8. ↑ http://dev.mysql.com/doc/refman/5.7/en/show-grants.html
  9. ↑ https://stackoverflow.com/questions/4714975/how-to-select-the-last-10-rows-of-an-sql-table-which-has-no-id-field/14057040
  10. ↑ http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql
  11. ↑ https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Security.html
  12. ↑ https://dev.mysql.com/doc/refman/5.7/en/alter-user.html#alter-user-tls
  13. ↑ https://stackoverflow.com/questions/24270733/automate-mysql-secure-installation-with-echo-command-via-a-shell-script/35004940
Retrieved from "https://wiki.1701technology.com/index.php?title=MySQL_Cheat_Sheet&oldid=1853"