# Домашнее задание к занятию "6.3. MySQL"
## Задача 1

docker run --name mysql8 -v "C:/Users/vah/docker/08-mssql/db:/var/lib/mysql" -v "C:/Users/vah/docker/08-mssql:/tmp/backup" -e MYSQL_ALLOW_EMPTY_PASSWORD=yes --rm -d mysql:8
docker exec -it mysql8 bash
mysql -e 'create database test_db;'
mysql test_db < /tmp/backup/test_dump.sql

mysql> \s
--------------
mysql  Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.22 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 1 min 58 sec

Threads: 2  Questions: 51  Slow queries: 0  Opens: 135  Flush tables: 3  Open tables: 55  Queries per second avg: 0.432
-------------- 

Ответ: 
Server version:         8.0.22 MySQL Community Server - GPL


mysql> connect test_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    12
Current database: test_db

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| orders            |
+-------------------+
1 row in set (0.00 sec)


mysql> select count(*) from orders where price>300;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

## Задача 2

mysql> CREATE USER 'test' IDENTIFIED WITH mysql_native_password BY 'testpass'
    -> WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE INTERVAL 180 DAY FAILED_LOGIN_ATTEMPTS 3
    -> ATTRIBUTE '{"surname": "Pretty", "name": "James"}';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT SELECT on test_db.* TO test;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from INFORMATION_SCHEMA.USER_ATTRIBUTES;
+------------------+-----------+----------------------------------------+
| USER             | HOST      | ATTRIBUTE                              |
+------------------+-----------+----------------------------------------+
| test             | %         | {"name": "James", "surname": "Pretty"} |
+------------------+-----------+----------------------------------------+

## Задача 3

mysql> show table status\G
*************************** 1. row ***************************
           Name: orders
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 6
    Create_time: 2020-11-06 08:32:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)


|       27 | 0.15893275 | alter table orders engine=MyISAM                                        |
|       28 | 0.11471000 | alter table orders engine=InnoDB                                        |

mysql> SHOW PROFILE FOR QUERY 27;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000114 |
| Executing hook on transaction  | 0.000008 |
| starting                       | 0.000037 |
| checking permissions           | 0.000010 |
| checking permissions           | 0.000006 |
| init                           | 0.000026 |
| Opening tables                 | 0.000578 |
| setup                          | 0.000088 |
| creating table                 | 0.015970 |
| waiting for handler commit     | 0.000036 |
| waiting for handler commit     | 0.005470 |
| After create                   | 0.031612 |
| System lock                    | 0.000018 |
| copy to tmp table              | 0.002113 |
| waiting for handler commit     | 0.000068 |
| waiting for handler commit     | 0.000021 |
| waiting for handler commit     | 0.020711 |
| rename result table            | 0.000220 |
| waiting for handler commit     | 0.029552 |
| waiting for handler commit     | 0.000016 |
| waiting for handler commit     | 0.022150 |
| waiting for handler commit     | 0.000018 |
| waiting for handler commit     | 0.010023 |
| waiting for handler commit     | 0.000015 |
| waiting for handler commit     | 0.001390 |
| end                            | 0.016963 |
| query end                      | 0.001529 |
| closing tables                 | 0.000010 |
| waiting for handler commit     | 0.000022 |
| freeing items                  | 0.000046 |
| cleaning up                    | 0.000097 |
+--------------------------------+----------+
31 rows in set, 1 warning (0.00 sec)


mysql> SHOW PROFILE FOR QUERY 28;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000122 |
| Executing hook on transaction  | 0.000007 |
| starting                       | 0.000026 |
| checking permissions           | 0.000006 |
| checking permissions           | 0.000005 |
| init                           | 0.000012 |
| Opening tables                 | 0.000290 |
| setup                          | 0.000066 |
| creating table                 | 0.000103 |
| After create                   | 0.046835 |
| System lock                    | 0.000021 |
| copy to tmp table              | 0.002361 |
| rename result table            | 0.001378 |
| waiting for handler commit     | 0.000017 |
| waiting for handler commit     | 0.029450 |
| waiting for handler commit     | 0.000021 |
| waiting for handler commit     | 0.016245 |
| waiting for handler commit     | 0.000016 |
| waiting for handler commit     | 0.014242 |
| waiting for handler commit     | 0.000015 |
| waiting for handler commit     | 0.001739 |
| end                            | 0.000459 |
| query end                      | 0.001135 |
| closing tables                 | 0.000008 |
| waiting for handler commit     | 0.000047 |
| freeing items                  | 0.000045 |
| cleaning up                    | 0.000042 |
+--------------------------------+----------+
27 rows in set, 1 warning (0.00 sec)

Не понял, что значит "приведите время выполнения и запрос на изменения из профайлера в ответе"
Время привел, остальное - не могу найти что такое ;)

## Задача 4

innodb_file_per_table=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=2,4G
innodb_log_file_size=100M
innodb_flush_method = O_DIRECT # Чтобы избежать двойного кэширования — самим MySQL и операционной системой
tmpdir = /dev/shm
table_cache = 4096
table_definition_cache = 4096

+ (в /etc/fstab -> `tmpfs /dev/shm tmpfs defaults 0 0`)