###### tags: `netology` `devops`

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

## Задача 1

- *Найдите команду для выдачи статуса БД и приведите в ответе из ее вывода версию сервера БД.*

mysql> status
--------------
mysql  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          19
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.23 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:                 13 min 51 sec

Threads: 2  Questions: 106  Slow queries: 0  Opens: 137  Flush tables: 3  Open tables: 55  Queries per second avg: 0.127
--------------

- *Приведите в ответе количество записей с price > 300.*

mysql> SELECT COUNT(*) FROM orders WHERE price > 300;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

## Задача 2

- *Используя таблицу INFORMATION_SCHEMA.USER_ATTRIBUTES получите данные по пользователю test и приведите в ответе к задаче.*

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'test' AND HOST = 'localhost';
+------+-----------+---------------------------------------+
| USER | HOST      | ATTRIBUTE                             |
+------+-----------+---------------------------------------+
| test | localhost | {"fname": "James", "lname": "Pretty"} |
+------+-----------+---------------------------------------+
1 row in set (0.00 sec)

## Задача 3

- *Исследуйте, какой engine используется в таблице БД test_db и приведите в ответе.*

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: 2021-02-06 12:19:35
    Update_time: 2021-02-06 12:19:35
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

- *Измените engine и приведите время выполнения и запрос на изменения из профайлера в ответе:*
- на MyISAM
- на InnoDB

SET profiling = 1;
ALTER TABLE test_db.orders ENGINE = MyISAM;
ALTER TABLE test_db.orders ENGINE = InnoDB;

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.08565350 | ALTER TABLE test_db.orders ENGINE = MyISAM |
|        2 | 0.07302175 | ALTER TABLE test_db.orders ENGINE = InnoDB |
+----------+------------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

## Задача 4

**my.cnf**:

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = ON
innodb_log_buffer_size = 1M
innodb_buffer_pool_size = 30
innodb_log_file_size = 100M

# Custom config should go here
!includedir /etc/mysql/conf.d/