Difference between revisions of "MySQL Cheat Sheet"

From Michael's Information Zone
Jump to navigation Jump to search
 
(7 intermediate revisions by the same user not shown)
Line 3: Line 3:
  
 
===Advanced Queries===
 
===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 24: 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 36: 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 53: 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;