Задача: Подготовить шпаргалку по настройке mysql-server применительно к Ubuntu 22.04 Server, а то каждый раз приходится вспоминать что помимо установки нужно сделать
- Как задать пароль на системную учетную запись Login:root
- Как создать базу, пользователя, пароль на этого пользователя
- Как ограничить подключение из вне к этому сервису
- и многое другое что мне обычно в работе нужно.
Я такие документы на работе и для интересующих меня тем делаю постоянно, вот и сейчас есть время почему бы не упорядочить и собрать все воедино.
Шаг №1: Обновляем информацию по дефолтным репозитариям Ubuntu 22.04 Server
sudo rm -Rf /var/lib/apt/lists sudo apt-get update
Шаг №2: Устанавливаем mysql-Server-8.0 и mysql-client-8.0:
sudo apt-get install -y mysql-server mysql-client
Проверка какая версия установлена в системе сейчас:
ekzorchik@srv-home:~$ sudo mysqld -Version /usr/sbin/mysqld Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) ekzorchik@srv-home:~$
Шаг №3: Изменяю схему авторизации и назначаю пароль на системную учетной запись root сервиса MySQL:
ekzorchik@srv-home:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] default-authentication-plugin=mysql_native_password
Шаг №4: Задаем пароль на системную учетную запись Login:root сервиса MySQL:
ekzorchik@srv-home:~$ sudo mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '712mbddr@';"
Шаг №5: Выполнить базовую безопасность:
ekzorchik@srv-home:~$ sudo mysql_secure_installation
Шаг №6: Пробуем подключиться к сервису MySQL с указанием установленного пароля на системную учетную запись Login:root и выполнить команду:
ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e "use mysql;select version()\g" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------------+ | version() | +-------------------------+ | 8.0.35-0ubuntu0.22.04.1 | +-------------------------+ ekzorchik@srv-home:~$
Шаг №7: Смотрим какие схемы авторизации сейчас в Mysql доступны:
ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e " use mysql;SELECT user,authentication_string,plugin,host FROM mysql.user;" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ aT onN-I(6uvT+d/NHYR//54c3v894opBmnS0pZ/wvc9ZTUhOE34w6Yz0 | caching_sha2_password | localhost | | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | root | *06311EC3368F51D40BE586479970CB6B5CD288D8 | mysql_native_password | localhost | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ ekzorchik@srv-home:~$
из вывода видно, что из-под Login:root можно подключаться только как native, просто часто для всех сервисов которые я использовал только это требовалось чтобы было.
Шаг №8: Чтобы создать базу если не существует: (Лучше делать так)
sudo mysql -u root -p712mbddr@ -e "create database if not exists db_db2
Шаг №9: Чтобы создать учетную запись с указанием откуда (к примеру, из сети 192.168.1.0/24) под ней будут подключаться и каким паролем:
sudo mysql -u root -p712mbddr@ -e "create user 'us_us2'@'192.168.1.%' identified by 'Aa1234567';" ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e "use mysql;select user,host from mysql.user" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+-------------+ | user | host | +------------------+-------------+ | us_us2 | 192.168.1.% | | debian-sys-maint | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | us_db1 | localhost | +------------------+-------------+ ekzorchik@srv-home:~$
Шаг №10: Чтобы создать пароль, на пользователя, который включает в себя спецсимволы, пароль нужно взять в одинарные кавычки:
sudo mysql -u root -p712mbddr@ -e "alter user 'us_db1'@'localhost' identified with mysql_native_password by '712mbddr_@_@_E';"
Шаг №11: Чтобы создать базу исключительно в определенной кодировке:
sudo mysql -u root -p712mbddr@ -e "create database db_db1 character set utf8mb4 collate utf8mb4_unicode_ci" sudo mysql -u root -p712mbddr@ -e "create user 'us_db1'@'localhost' identified by '612mbddr@'" sudo mysql -u root -p712mbddr@ -e "grant all on db_db1.* to 'us_db1'@'localhost';" sudo mysql -u root -p712mbddr@ -e "flush privileges"
командами выше я создать базу с характеристиками, пользователя с паролем и назначаю полные права созданному пользователя на созданную базу. После чего данные данные
Database: db_db1
User: us_db1
Password: 612mbddr@
можно использовать при развертывании того или иного сервиса
к примеру, для Zabbix:
sudo mysql -u root -p712mbddr@ -e "create database db_zabbix character set utf8 collate utf8_bin"
к примеру, для phpbb:
sudo mysql -u root -p712mbddr@ -e "create database db_phpbb"
На заметку: или просто указываем create database db_db1 без дальнейших указаний.
Шаг №12: Чтобы к базе можно было подключиться с другой системы, я делаю следующее:
в /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
перезапускаю mysql: sudo systemctl restart mysql
и дабы не все подряд могли подключиться настраиваю firewalld для явно с определенных IP-адресов подключение
sudo firewall-cmd --new-zone=mysqlzone --permanent sudo firewall-cmd --permanent --zone=mysqlzone --add-source=999.999.999.999/32 sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcp sudo firewall-cmd –-reload sudo firewall-cmd --zone=mysqlzone --list-all
после подключение с другой системы к этой системы сервиса mysql будет выглядеть вот так:
sudo mysql -u us_db1 -p612mbddr@ -h 999.999.999.999 db_db1 -e "show tables"
где указываем Login, Pass, IP&DNS удаленной системы к которой подключается с той IP адрес которой прописали в firewalld
Шаг №13: Отобразить какие учетные записи созданы в mysql:
ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e "use mysql;select user,host from mysql.user" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | us_db1 | localhost | +------------------+-----------+ ekzorchik@srv-home:~$
Шаг №14: Чтобы отобразить какие базы вообще есть на сервере:
ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e "show databases" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | db_db1 | | db_db2 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ ekzorchik@srv-home:~$
Шаг №15: Отобразить какие права доступа у учетной записи сервиса mysql в базе:
ekzorchik@srv-home:~$ sudo mysql -u root -p712mbddr@ -e "use db_db1; show grants for 'us_db1'@'localhost';" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------------------------------------------------+ | Grants for us_db1@localhost | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO `us_db1`@`localhost` | | GRANT ALL PRIVILEGES ON `db_db1`.* TO `us_db1`@`localhost` | +------------------------------------------------------------+ ekzorchik@srv-home:~$
Шаг №16: Чтобы отозвать права у mysql учетной записи на базу, т.к. к примеру, дали Insert, а было Select и поэтому после вернуть на Select:
sudo mysql -u root -p712mbddr@ -e "use db_db1; grant insert on db_db1.* to 'us_db1'@'localhost';"
Отобрать все права:
sudo mysql -u root -p712mbddr@ -e "use db_db1; revoke all privileges on *.* from 'us_db1'@'localhost';"
Отобразить текущий права:
sudo mysql -u root -p712mbddr@ -e "use db_db1; show grants for 'us_db1'@'localhost';" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------------------------------+ | Grants for us_db1@localhost | +--------------------------------------------+ | GRANT USAGE ON *.* TO `us_db1`@`localhost` | +--------------------------------------------+
Дать права Select:
sudo mysql -u root -p712mbddr@ -e "use db_db1; grant select on db_db1.* to 'us_db1'@'localhost';"
Отобразить текущие права:
sudo mysql -u root -p712mbddr@ -e "use db_db1; show grants for 'us_db1'@'localhost';" mysql: [Warning] Using a password on the command line interface can be insecure. +----------------------------------------------------+ | Grants for us_db1@localhost | +----------------------------------------------------+ | GRANT USAGE ON *.* TO `us_db1`@`localhost` | | GRANT SELECT ON `db_db1`.* TO `us_db1`@`localhost` | +----------------------------------------------------+
Отлично, а если нужно дополнить права, к примеру, чтобы были: Select + Insert вместе взятые:
sudo mysql -u root -p712mbddr@ -e "use db_db1; grant select,insert on db_db1.* to 'us_db1'@'localhost';" sudo mysql -u root -p712mbddr@ -e "use db_db1; show grants for 'us_db1'@'localhost';" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------------------------------------------------+ | Grants for us_db1@localhost | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO `us_db1`@`localhost` | | GRANT SELECT, INSERT ON `db_db1`.* TO `us_db1`@`localhost` | +------------------------------------------------------------+
Отлично
Шаг №17: Чтобы сделать дамп базы и сжать ее:
mysqldump -u root -p712mbddr@ production | gzip -9 > 134012122023/Alldb-production$bfile.sql.gz
На заметку: Лучше делать дам таблицы так:
mysqldump -v --insert-ignore --skip-lock-tables --single-transaction=TRUE -u root -p712mbddr@ db_tablo | /bin/gzip -9 > $FOLDER/srv-tablo_$efile.sql.gz
или так:
mysqldump --single-transaction --set-gtid-purged=OFF -uroot -p712mbddr@ db_site | gzip -9 > /etc/dbackup/db_site_$bfile.sql.gz
Шаг №18: Чтобы из определенной базы сделать дам определенной таблицы внутри нее:
sudo mysqldump -u root -712mbddr@ db_dealers29052022 order_positions > order_positions.sql
Шаг №19: если вам нужна только структура данных и атрибуты столбцов исходной таблицы, без наполнения её данными из исходной.
sudo mysql -u root -712mbddr@ db_dealers -e "create table order_positions_temp like order_positions" sudo mysql -u root -712mbddr@ db_dealers -e "select * from order_positions_temp"
содержимого в новой таблице нет
Шаг №20: Если нужно из бекапа взять определенную таблицу и восстановить ее в другую таблицу под другим именем другой базы с которой работают разработчики:
План:
а) создаем базу (это база в которую я буду разворачивать бекап)
sudo mysql -u root -712mbddr@ -e «create database db_production»
б) восстанавливаем в созданную базу dump из бекапа
aollo@customizer:~$ sudo mkdir /backuprecovery
aollo@customizer:~$ sudo cp /backup/14-05-2023/Alldb-production140523_09_08_03.sql.gz /backuprecovery/
aollo@customizer:/backuprecovery$ sudo gzip -d Alldb-production140523_09_08_03.sql.gz
aollo@customizer:/backuprecovery$ ls
Alldb-production140523_09_08_03.sql
aollo@customizer:/backuprecovery$ sudo mysql -u root -712mbddr@ db_production < Alldb-production140523_09_08_03.sql
в) сохраняем из восстановленной базы определенную таблицу
aollo@customizer:/backuprecovery$ sudo mysql -u root -712mbddr@ db_production -e «show tables» | grep dealers_employees
mysql: [Warning] Using a password on the command line interface can be insecure.
dealers_employees
dealers_employees_permissions
dealers_employees_to_permissions
aollo@customizer:/backuprecovery$
aollo@customizer:/backuprecovery$ sudo su —
root@customizer:/backuprecovery# mysqldump -u root -712mbddr@ db_production dealers_employees > /backuprecovery/dealers_employees.sql
т.е. мне нужно таблицу dealers_employees из бекапа сохранить, как файл.
г) создаем новую базу или используем существующую
д) Клонируем в существующей базе таблицу в другое именование
е) восстанавливаем в другое именование таблицы экспортированную таблицу.
sudo mysql -u root -712mbddr@ development -e «create table dealers_employees_2 as select * from db_production.dealers_employees»
где База development — боевая тестовая база разработкиков
db_production — база в которой развернут бекап
т.е. я в базе development создаю таблицу dealers_employees_2 содержимое которой беру из бекап базы db_production таблицы dealers_employees
тем самым и не нужно сохранять таблицу в sql файл.
Шаг №21: Узнать размер SQL Базы через запрос:
Узнать размер базы Mysql-запросом
SELECT table_schema AS "<database_name>", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "<database_name" GROUP BY table_schema;
На заметку: Насчет использования плагина проверки сложности пароля советую ознакомиться с заметкой: "Плагин validate_password в mysql-server на Ubuntu 22.04"
Итого я пока частично собрал по всем своим наработкам что мне приходилось делать с mysql, если что еще по возникающим задачам будет интересного, то все это найдет себя, как продолжение в текущей заметке. С уважением, автор блога Олло Александр aka ekzorchik.