software:development:ps_pycharm:mysql:mysql
Различия
Показаны различия между двумя версиями страницы.
Предыдущая версия справа и слеваПредыдущая версияСледующая версия | Предыдущая версия | ||
software:development:ps_pycharm:mysql:mysql [2024/02/25 19:32] – [Установка] vladpolskiy | software:development:ps_pycharm:mysql:mysql [2024/02/25 20:10] (текущий) – [Ссылки и Примечания] vladpolskiy | ||
---|---|---|---|
Строка 11: | Строка 11: | ||
\\ | \\ | ||
Несмотря на отсутствие некоторого функционала, | Несмотря на отсутствие некоторого функционала, | ||
- | =====Установка===== | + | =====Установка |
====Шаг 1 — Обновление системы==== | ====Шаг 1 — Обновление системы==== | ||
Важно, чтобы вы обновили систему, | Важно, чтобы вы обновили систему, | ||
Строка 143: | Строка 143: | ||
sudo systemctl restart mysql.service | sudo systemctl restart mysql.service | ||
sudo systemctl status mysql.service | sudo systemctl status mysql.service | ||
- | < | + | </code> |
* **/ | * **/ | ||
* **/ | * **/ | ||
< | < | ||
+ | / | ||
+ | / | ||
</ | </ | ||
+ | * **Порт TCP/3306** — TCP/3306 сетевой порт по умолчанию для MySQL сервера и привязывается к **127.0.0.1** по соображениям безопасности. Однако вы можете его изменить, | ||
+ | ====Шаг 4 — Защита сервера MySQL 8==== | ||
+ | По умолчанию пароль не установлен, | ||
< | < | ||
+ | sudo mysql_secure_installation | ||
</ | </ | ||
+ | Программа запросит пароль для пользователя **root,** политику валидации пароля и предполагаемую надёжность пароля: | ||
+ | < | ||
+ | Securing the MySQL server deployment. | ||
+ | |||
+ | Enter password for user root: My7Pass@Word_9_8A_zE | ||
+ | |||
+ | VALIDATE PASSWORD COMPONENT can be used to test passwords | ||
+ | and improve security. It checks the strength of password | ||
+ | and allows the users to set only those passwords which are | ||
+ | secure enough. Would you like to setup VALIDATE PASSWORD component? | ||
+ | |||
+ | Press y|Y for Yes, any other key for No: Y | ||
+ | |||
+ | There are three levels of password validation policy: | ||
+ | |||
+ | LOW Length >= 8 | ||
+ | MEDIUM Length >= 8, numeric, mixed case, and special characters | ||
+ | STRONG Length >= 8, numeric, mixed case, special characters and dictionary file | ||
+ | |||
+ | Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 | ||
+ | Using existing password for root. | ||
+ | |||
+ | Estimated strength of the password: 100 | ||
+ | Change the password for root ? (Press y|Y for Yes, any other key for No) : Y | ||
+ | |||
+ | New password: My7Pass@Word_9_8A_zE | ||
+ | |||
+ | Re-enter new password: My7Pass@Word_9_8A_zE | ||
+ | |||
+ | Estimated strength of the password: 100 | ||
+ | Do you wish to continue with the password provided? | ||
+ | </ | ||
+ | Далее удалим анонимного пользователя, | ||
+ | < | ||
+ | By default, a MySQL installation has an anonymous user, | ||
+ | allowing anyone to log into MySQL without having to have | ||
+ | a user account created for them. This is intended only for | ||
+ | testing, and to make the installation go a bit smoother. | ||
+ | You should remove them before moving into a production | ||
+ | environment. | ||
+ | |||
+ | Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y | ||
+ | Success. | ||
+ | |||
+ | |||
+ | Normally, root should only be allowed to connect from | ||
+ | ' | ||
+ | the root password from the network. | ||
+ | |||
+ | Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y | ||
+ | Success. | ||
+ | |||
+ | By default, MySQL comes with a database named ' | ||
+ | anyone can access. This is also intended only for testing, | ||
+ | and should be removed before moving into a production | ||
+ | environment. | ||
+ | |||
+ | |||
+ | Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y | ||
+ | - Dropping test database... | ||
+ | Success. | ||
+ | |||
+ | - Removing privileges on test database... | ||
+ | Success. | ||
+ | |||
+ | Reloading the privilege tables will ensure that all changes | ||
+ | made so far will take effect immediately. | ||
+ | |||
+ | Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y | ||
+ | Success. | ||
+ | |||
+ | All done! | ||
+ | </ | ||
+ | ====Шаг 5 — Включение загрузки MySQL сервера вместе с загрузкой системы==== | ||
+ | Убедитесь, | ||
+ | < | ||
+ | sudo systemctl is-enabled mysql.service | ||
+ | </ | ||
+ | Если загрузка не включена, | ||
+ | < | ||
+ | sudo systemctl enable mysql.service | ||
+ | </ | ||
+ | Проверьте состояние MySQL сервера, | ||
+ | < | ||
+ | sudo systemctl status mysql.service | ||
+ | </ | ||
+ | Если всё в порядке, | ||
+ | < | ||
+ | ● mysql.service - MySQL Community Server | ||
+ | | ||
+ | | ||
+ | Process: 1498 ExecStartPre=/ | ||
+ | Main PID: 1506 (mysqld) | ||
+ | | ||
+ | Tasks: 39 (limit: 4575) | ||
+ | | ||
+ | CPU: 1.090s | ||
+ | | ||
+ | | ||
+ | |||
+ | Aug 10 23:46:30 nixcraft-mysql-8 systemd[1]: Starting MySQL Community Server... | ||
+ | Aug 10 23:46:30 nixcraft-mysql-8 systemd[1]: Started MySQL Community Server. | ||
+ | </ | ||
+ | ====Шаг 6 — Запуск/ | ||
+ | Используя параметры командной строки, | ||
+ | < | ||
+ | sudo systemctl start mysql.service | ||
+ | </ | ||
+ | Для остановки MySQL сервера выполните следующую команду: | ||
+ | < | ||
+ | sudo systemctl stop mysql.service | ||
+ | </ | ||
+ | Если MySQL сервер нужно перезапустить выполните: | ||
+ | < | ||
+ | sudo systemctl restart mysql.service | ||
+ | </ | ||
+ | Посмотреть журнал службы MySQL можно командной **journalctl**: | ||
+ | < | ||
+ | sudo journalctl -u mysql.service -xe | ||
+ | </ | ||
+ | Она выведет что-то вроде: | ||
+ | < | ||
+ | May 10 05:09:01 ubuntu-nixcraft systemd[1]: Starting MySQL Community Server... | ||
+ | ░░ Subject: A start job for unit mysql.service has begun execution | ||
+ | ░░ Defined-By: systemd | ||
+ | ░░ Support: http:// | ||
+ | ░░ | ||
+ | ░░ A start job for unit mysql.service has begun execution. | ||
+ | ░░ | ||
+ | ░░ The job identifier is 597. | ||
+ | May 10 05:09:01 ubuntu-nixcraft systemd[1]: Started MySQL Community Server. | ||
+ | ░░ Subject: A start job for unit mysql.service has finished successfully | ||
+ | ░░ Defined-By: systemd | ||
+ | ░░ Support: http:// | ||
+ | ░░ | ||
+ | ░░ A start job for unit mysql.service has finished successfully. | ||
+ | ░░ | ||
+ | ░░ The job identifier is 597. | ||
+ | </ | ||
+ | Файл журнала ошибок MySQL сервера по умолчанию расположен в **/ | ||
+ | < | ||
+ | sudo tail -f / | ||
+ | </ | ||
+ | < | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | 2022-05-10T05: | ||
+ | </ | ||
+ | ====Шаг 7 — Войдите на MySQL 8 сервер для тестирования==== | ||
+ | На данный момент мы узнали, | ||
+ | < | ||
+ | mysql -u {user} -p | ||
+ | mysql -u {user} -h {remote_server_ip} -p | ||
+ | mysql -u root -p | ||
+ | </ | ||
+ | Пример сессии: | ||
+ | < | ||
+ | Enter password: | ||
+ | Welcome to the MySQL monitor. | ||
+ | Your MySQL connection id is 14 | ||
+ | Server version: 8.0.30-0ubuntu0.22.04.1 (Ubuntu) | ||
+ | |||
+ | Copyright (c) 2000, 2022, Oracle and/or its affiliates. | ||
+ | |||
+ | Oracle is a registered trademark of Oracle Corporation and/or its | ||
+ | affiliates. Other names may be trademarks of their respective | ||
+ | owners. | ||
+ | |||
+ | Type ' | ||
+ | |||
+ | mysql> | ||
+ | </ | ||
+ | Выполните команду **STATUS**, которая отображает версию и другую информацию о MySQL сервере: | ||
+ | < | ||
+ | STATUS; | ||
+ | </ | ||
+ | < | ||
+ | mysql Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) | ||
+ | |||
+ | Connection id: 14 | ||
+ | Current database: | ||
+ | Current user: | ||
+ | SSL: Not in use | ||
+ | Current pager: | ||
+ | Using outfile: | ||
+ | Using delimiter: | ||
+ | Server version: | ||
+ | Protocol version: | ||
+ | Connection: | ||
+ | Server characterset: | ||
+ | Db | ||
+ | Client characterset: | ||
+ | Conn. characterset: | ||
+ | UNIX socket: | ||
+ | Binary data as: | ||
+ | Uptime: | ||
+ | |||
+ | Threads: 2 Questions: 20 Slow queries: 0 Opens: 142 Flush tables: 3 Open tables: 61 Queries per second avg: 0.058 | ||
+ | -------------- | ||
+ | </ | ||
+ | Также версию MySQL сервера, | ||
+ | < | ||
+ | SHOW VARIABLES LIKE " | ||
+ | </ | ||
+ | < | ||
+ | +--------------------------+-------------------------+ | ||
+ | | Variable_name | ||
+ | +--------------------------+-------------------------+ | ||
+ | | admin_tls_version | ||
+ | | immediate_server_version | 999999 | ||
+ | | innodb_version | ||
+ | | original_server_version | ||
+ | | protocol_version | ||
+ | | replica_type_conversions | | | ||
+ | | slave_type_conversions | ||
+ | | tls_version | ||
+ | | version | ||
+ | | version_comment | ||
+ | | version_compile_machine | ||
+ | | version_compile_os | ||
+ | | version_compile_zlib | ||
+ | +--------------------------+-------------------------+ | ||
+ | 13 rows in set (0.00 sec) | ||
+ | </ | ||
+ | ====Шаг 8 — Создание новой базы данных MySQL и пользователя/ | ||
+ | Создадим новую базу данных mydemodb: | ||
+ | < | ||
+ | CREATE DATABASE mydemodb; | ||
+ | </ | ||
+ | Затем создадим пользователя **vivekappusr** для базы данных **mydemodb** с паролем **aa09dd995C72_5355a598fc7D8ab1230a**: | ||
+ | < | ||
+ | CREATE USER ' | ||
+ | </ | ||
+ | Установим права: | ||
+ | < | ||
+ | GRANT SELECT, INSERT, UPDATE, DELETE ON mydemodb.* TO ' | ||
+ | </ | ||
+ | И конечно предоставим все привилегии на базу данных **mydemodb**: | ||
+ | < | ||
+ | GRANT ALL PRIVILEGES ON mydemodb.* TO ' | ||
+ | </ | ||
+ | Посмотрим пользователей MySQL и привилегии пользователя **vivekappusr** командами: | ||
+ | < | ||
+ | SELECT USER,host FROM mysql.user; | ||
+ | SHOW GRANTS FOR vivekappusr; | ||
+ | </ | ||
+ | Войдём под новым пользователем в MySQL: | ||
+ | < | ||
+ | mysql -u vivekappusr -p mydemodb | ||
+ | mysql -u vivekappusr -h localhost -p mydemodb | ||
+ | </ | ||
+ | Где, | ||
+ | |||
+ | * -u vivekappusr : Имя пользователя | ||
+ | * -h localhost : Соединение с хостом localhost | ||
+ | * -p : Запрос пароля | ||
+ | * mydemodb : Соединение с базой данных: | ||
+ | ====Шаг 9 — Конфигурация сервера MySQL 8==== | ||
+ | Откройте файл **/ | ||
+ | < | ||
+ | sudo vim / | ||
+ | </ | ||
+ | Отредактируйте раздел mysqld в соответствии с вашими потребностями. Детальную информацию по параметрам можно узнать из [[https:// | ||
+ | < | ||
+ | [mysqld] | ||
+ | pid-file | ||
+ | socket | ||
+ | datadir | ||
+ | log-error = / | ||
+ | </ | ||
+ | Далее, я добавляю доступ по сети: | ||
+ | < | ||
+ | # server LAN/VLAN IP and port | ||
+ | bind_address = 10.147.164.6 | ||
+ | port = 3306 | ||
+ | skip_external_locking | ||
+ | skip_name_resolve | ||
+ | max_allowed_packet | ||
+ | max_connect_errors | ||
+ | </ | ||
+ | Немного тонкой настройки баз данных для проекта | ||
+ | < | ||
+ | # InnoDB | ||
+ | default_storage_engine | ||
+ | innodb_buffer_pool_instances | ||
+ | innodb_buffer_pool_size | ||
+ | innodb_file_per_table | ||
+ | innodb_flush_log_at_trx_commit | ||
+ | innodb_flush_method | ||
+ | innodb_log_buffer_size | ||
+ | innodb_log_file_size | ||
+ | innodb_stats_on_metadata | ||
+ | innodb_read_io_threads | ||
+ | innodb_write_io_threads | ||
+ | |||
+ | # MyISAM Settings (set if you are using MyISAM) | ||
+ | key_buffer_size | ||
+ | |||
+ | low_priority_updates | ||
+ | concurrent_insert | ||
+ | |||
+ | # Connection Settings | ||
+ | max_connections | ||
+ | |||
+ | back_log | ||
+ | thread_cache_size | ||
+ | thread_stack | ||
+ | |||
+ | interactive_timeout | ||
+ | wait_timeout | ||
+ | |||
+ | # Buffer Settings | ||
+ | join_buffer_size | ||
+ | read_buffer_size | ||
+ | read_rnd_buffer_size | ||
+ | sort_buffer_size | ||
+ | </ | ||
+ | Настройки таблицы в соответствии с потребностями проекта: | ||
+ | < | ||
+ | # Table Settings (see below for open file limits) | ||
+ | table_definition_cache | ||
+ | table_open_cache | ||
+ | open_files_limit | ||
+ | |||
+ | max_heap_table_size | ||
+ | tmp_table_size | ||
+ | |||
+ | # Search Settings | ||
+ | ft_min_word_len | ||
+ | </ | ||
+ | Настройка ведения логов: | ||
+ | < | ||
+ | # Logging | ||
+ | log_error | ||
+ | log_queries_not_using_indexes | ||
+ | long_query_time | ||
+ | slow_query_log | ||
+ | slow_query_log_file | ||
+ | </ | ||
+ | Настройка **mysqldump** для бэкапов: | ||
+ | < | ||
+ | [mysqldump] | ||
+ | quick | ||
+ | quote_names | ||
+ | max_allowed_packet | ||
+ | </ | ||
+ | ===Настройка максимального количества отрытых файлов сервера MySQL(количество файловых дескрипторов)=== | ||
+ | Для загруженного MySQL 8 сервера вам необходимо настроить параметры максимального количества открытых файлов с помощью **systemd**. В противном случае вы получите сообщение об ошибке «Ну удалось увеличить количество max_open_files больше, | ||
+ | < | ||
+ | sudo systemctl edit mysql.service | ||
+ | </ | ||
+ | Вы увидите следующий текст: | ||
+ | < | ||
+ | ### Lines below this comment will be discarded | ||
+ | ### / | ||
+ | # # MySQL systemd service file | ||
+ | # | ||
+ | # [Unit] | ||
+ | # Description=MySQL Community Server | ||
+ | # After=network.target | ||
+ | # | ||
+ | # [Install] | ||
+ | # WantedBy=multi-user.target | ||
+ | # | ||
+ | # [Service] | ||
+ | # Type=notify | ||
+ | # User=mysql | ||
+ | # Group=mysql | ||
+ | # PIDFile=/ | ||
+ | # PermissionsStartOnly=true | ||
+ | # ExecStartPre=/ | ||
+ | # ExecStart=/ | ||
+ | # TimeoutSec=infinity | ||
+ | # Restart=on-failure | ||
+ | # RuntimeDirectory=mysqld | ||
+ | # RuntimeDirectoryMode=755 | ||
+ | # LimitNOFILE=10000 | ||
+ | # | ||
+ | # # Set enviroment variable MYSQLD_PARENT_PID. This is required for restart. | ||
+ | # Environment=MYSQLD_PARENT_PID=1 | ||
+ | </ | ||
+ | Добавьте свою конфигурацию между: | ||
+ | < | ||
+ | ### Anything between here and the comment below will become the new contents of the file | ||
+ | |||
+ | |||
+ | |||
+ | ### Lines below this comment will be discarded | ||
+ | </ | ||
+ | Например (замените **1800000** на желаемое значение. Для максимального поддерживаемого значения используйте **LimitNOFILE=infinity** вместо **LimitNOFILE=1800000**): | ||
+ | < | ||
+ | ### Editing / | ||
+ | ### Anything between here and the comment below will become the new contents of the file | ||
+ | [Service] | ||
+ | LimitNOFILE=1800000 | ||
+ | |||
+ | |||
+ | |||
+ | ### Lines below this comment will be discarded | ||
+ | |||
+ | ### / | ||
+ | # # MySQL systemd service file | ||
+ | # | ||
+ | # [Unit] | ||
+ | # Description=MySQL Community Server | ||
+ | # After=network.target | ||
+ | # | ||
+ | # [Install] | ||
+ | # WantedBy=multi-user.target | ||
+ | # | ||
+ | # [Service] | ||
+ | # Type=notify | ||
+ | # User=mysql | ||
+ | # Group=mysql | ||
+ | # PIDFile=/ | ||
+ | # PermissionsStartOnly=true | ||
+ | # ExecStartPre=/ | ||
+ | # ExecStart=/ | ||
+ | # TimeoutSec=infinity | ||
+ | # Restart=on-failure | ||
+ | # RuntimeDirectory=mysqld | ||
+ | # RuntimeDirectoryMode=755 | ||
+ | # LimitNOFILE=10000 | ||
+ | # | ||
+ | # # Set enviroment variable MYSQLD_PARENT_PID. This is required for restart. | ||
+ | # Environment=MYSQLD_PARENT_PID=1 | ||
+ | </ | ||
+ | Создайте или отредактируйте **/ | ||
+ | < | ||
+ | fs.nr_open=1800000 | ||
+ | </ | ||
+ | Обновите изменения: | ||
+ | < | ||
+ | sudo sysctl -p / | ||
+ | </ | ||
+ | Затем перезагрузите и перезапустите службу mysql: | ||
+ | < | ||
+ | sudo systemctl daemon-reload | ||
+ | sudo systemctl restart mysql | ||
+ | </ | ||
+ | Проверьте: | ||
+ | < | ||
+ | mysql -u root -p -e 'SHOW GLOBAL VARIABLES LIKE " | ||
+ | </ | ||
+ | Пример вывода: | ||
+ | < | ||
+ | +------------------+---------+ | ||
+ | | Variable_name | ||
+ | +------------------+---------+ | ||
+ | | open_files_limit | 1800000 | | ||
+ | +------------------+---------+ | ||
+ | </ | ||
+ | Также можете воспользоваться следующей командой, | ||
+ | < | ||
+ | cat /proc/$(cat / | ||
+ | ## ИЛИ ## | ||
+ | grep 'open files' /proc/$(cat / | ||
+ | </ | ||
+ | Выведет в консоль: | ||
+ | < | ||
+ | Max open files 1800000 | ||
+ | </ | ||
+ | |||
=====Достоинства и недостатки===== | =====Достоинства и недостатки===== | ||
Переходим к обзору сильных и слабых сторон MySQL от Oracle. | Переходим к обзору сильных и слабых сторон MySQL от Oracle. | ||
Строка 174: | Строка 650: | ||
\\ | \\ | ||
[[https:// | [[https:// | ||
+ | \\ | ||
+ | [[https:// | ||
+ | \\ | ||
+ | [[https:// | ||
+ | \\ | ||
+ | [[software: | ||
software/development/ps_pycharm/mysql/mysql.1708878737.txt.gz · Последнее изменение: 2024/02/25 19:32 — vladpolskiy