Підготуйте базу даних MySQL або MariaDB

Налаштувати сервер бази даних

Після встановлення набору служб ЛАМПИ, WAMP, MAMP o XAMPP Як пояснюється в першій статті серії або просто після встановлення на машині, яка виконуватиме роль сервера, суми програм, які реалізують відповідні служби, необхідно налаштувати деякі аспекти конфігурації сервера бази даних MySQL а також створення баз даних і таблиць, які використовуватимуться на веб-сервері для зберігання даних із пристроїв Інтернету речей.

Якщо використовується загальнодоступний сервер, провайдер напевно надасть певний тип панелі для посередництва між користувачем і «справжньою» конфігурацією бази даних з метою посилення безпеки в роботі. Якщо так, вам доведеться звернутися до інструкцій, які пропонує цей постачальник. У випадку реального або віртуального сервера, розміщеного на об’єктах третьої сторони, подібні обставини можуть виникнути, коли ним керує постачальник; Якщо було укладено контракт на некерований сервер (провайдером, керованим користувачем), застосовуватиметься така інформація.

Оскільки важливо знати інструкції, які потрібно надати менеджеру бази даних, і розуміється, що він використовується як засіб підтримки пристроїв для Інтернету речей, який буде об’єктом проекту, це може бути достатньо для використання консолі, оскільки робота на самому сервері бази даних буде періодичною. Якщо робоче навантаження на керування починає збільшуватися, можливо, варто використовувати менеджер на самому сервері, наприклад популярний PHPMYADMIN (з веб-браузера) або локально, хоча, звичайно, доступ до сервера бази даних, з адміністратором, таким як Робочий стіл MySQL з якого виконувати в розширеному графічний інтерфейс користувача операції, які будуть пояснені, і багато інших, які не входять у цю статтю і які заслуговують на цілу серію.

На додаток до більш-менш автоматичних операцій, які можна виконувати Робочий стіл MySQL на сервері, базах даних або таблицях також можна виконувати завдання «вручну», написавши та виконавши повні сценарії або незалежні команди в SQL. Так само, як і з інструментом Робочий стіл MySQL, SQL потребує окремого розділу, і який буде важливо знати детальніше, коли частина веб-роботи IoT, яка виконується, передбачає більш серйозну увагу до базова ніж тут пояснюється.

У будь-якому випадку, і, звичайно, для вирішення необхідної конфігурації бази даних IoT, яка обробляється, можна використовувати консоль.

Налаштувати root, головного адміністратора MySQL

Якщо сервер бази даних знаходиться на власній машині, перше, що потрібно зробити, це налаштувати дозволи адміністратора служби, щоб мати можливість створювати, використовуючи ці привілеї, інших користувачів (які, у свою чергу, можуть їх мати) і, якщо це можливо, бази даних і таблиці в якому зберігатиметься інформація, необхідна системі Інтернету речей.

Важливо приділити увагу процесу встановлення набору сервісів або сервера MySQL, залежно від подальшого процесу, оскільки у багатьох випадках інсталятор сам відповідатиме за виконання цього та інших кроків, і їх не потрібно буде повторювати, окрім випадків, коли ви хочете змінити конфігурацію.

Для виконання цих завдань ви зазвичай працюєте з консоллю. MySQL як адміністратор. Для доступу до цього режиму використовуйте команду mysql -u root -p o sudo mysql -u root якщо використовується вперше та під час інсталяції користувача не було налаштовано root

До замовлення mysql -u root -p позначений опцією -u ім'я користувача (root) і з опцією -p (пароль), що ключ потрібно запитувати під час доступу до консолі MySQL. Для повернення до системної консолі можна скористатися командою quit вийти

Як видно на скріншоті вище, перед виходом з консолі MySQL, щоб встановити привілеї користувача root була використана команда:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
в якому:

• надаються всі привілеї GRANT ALL PRIVILEGES
• у всіх таблицях у всіх базах даних ON *.*
• користувачеві root доступ із машини, на якій працює сервер MySQL TO 'root'@'localhost'
• використовуючи 1234 як пароль IDENTIFIED BY '1234'
• з дозволом авторизувати інших користувачів WITH GRANT OPTION

Для дистанційного керування базою даних необхідно надати привілеї відповідному користувачеві з інших місць. У попередньому прикладі це було дозволено root отримати доступ із машини, на якій працює сервер бази даних (localhost) з 'root'@'localhost', якщо використовується 'root'@'%' Доступ буде дозволено з будь-якої машини.

Якщо у вас є права адміністратора системи, ви можете повторно встановити пароль користувача root de MySQL. Для цього необхідно зупинити службу (mysql o mysqld, залежно від дистрибутива), завантажте його без ключа, встановіть новий ключ root і перезапустіть його. За допомогою доступу без пароля також можна надати привілеї, описані вище.

• припинити службу sudo /etc/init.d/mysql stop
• кинути MySQL без ключа та розблокувати консоль sudo mysqld_safe --skip-grant-tables &
• доступ як «root» mysql -u root
• змінити пароль:
  • отримати доступ до бази даних «mysql' USE mysql;
  • призначити "root» ключ «1234»: UPDATE user SET authentication_string=PASSWORD("1234") WHERE user="root";
  • застосувати нові привілеї: FLUSH PRIVILEGES;
  • вихід з консолі mysql і повернутися до системи: quit

• послуга перезапуску sudo /etc/init.d/mysql restart

Налаштувати віддалений доступ до бази даних

З міркувань безпеки початкова конфігурація менеджера баз даних MySQL не дозволяє віддалений доступ, щоб змінити його, необхідно відредагувати документ конфігурації, як правило my.cnf, який зазвичай зустрічається на маршруті /etc/mysql.

Якщо використовується MariaDB, яка зараз є «вільнішою» версією MySQL, звичайно my.cnf буде посилання на /etc/alternatives/my.cnf який, у свою чергу, завантажить конфігурацію, використовуючи !includedir, з маршрутних документів /etc/mysql/mariadb.conf.d, ймовірно, з документа 50-server.cnf

Ресурс, який сервер бази даних використовує, щоб не звертатися до віддалених запитів, складається з «прослуховування» лише запитів, зроблених із певного місця. IP-адреса який виражається як значення параметра bind-address (який замінює класичний skip-networking) Коли адреса є 127.0.0.1 (localhost) обробляються лише запити з локальної машини. Якщо прокоментовано с # або якщо значення є 0.0.0.0, обслуговуємо за будь-якою адресою. Щоб дозволити зовнішній доступ, найбезпечніше вказати IP-адреса (який може бути загальнодоступним або приватним) комп’ютера, з якого здійснюється доступ, зазвичай щось на зразок bind-address = 192.168.1.74. У вашому випадку це обладнання може виконувати роль тунель (переадресація с SSH) або як доріжка (з основні таблиці ip), щоб уникнути виходу сервера за межі контрольованої зони мережі.

На знімку екрана вище ви бачите, що його було відредаговано my.cnf з конфігурацією MySQL користуючись замовленням sudo nano /etc/mysql/my.cnf. У разі роботи з MariaDB При звичайній комплектації порядок був би приблизно таким sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf. Як згадувалося вище, коли є доступ із правами адміністратора, a графічний інтерфейс користувача як Робочий стіл MySQL для регулярного керування базами даних.

Створіть бази даних

Після налаштування сервера бази даних можна створювати бази даних і таблиці для зберігання даних IoT. Для цього можна використовувати команди SQL з консолі або з графічний інтерфейс користувача менеджера.

Замовлення CREATE DATABASE temperaturas; створює базу даних «температури». Щоб уникнути помилок, можна використовувати форму CREATE DATABASE IF NOT EXISTS base_datos_temperaturas; який би створив базу даних “temperature_database”, лише якщо вона наразі не існує.

Створіть таблиці

Оскільки в різних базах даних можуть існувати таблиці з однаковою назвою, ви можете посилатися на одну з них, поставивши перед її назвою назву бази даних і розділивши обидві крапкою, приблизно на кшталт base_datos.tabla Це позначення також можна розширити до назв полів base.tabla.campo

Якщо під час роботи з таблицею (в даному випадку під час її створення) ім’я бази даних опущено, вважається, що є посилання на останню вибрану. Щоб чітко вибрати певний, ви можете скористатися командою USE і вкажіть своє ім'я USE base_datos_temperaturas;

Під час створення таблиць у базах даних у тій самій операції зазвичай додається інформація про поля, які їх утворюють (хоча їх також можна змінити пізніше). Як і у випадку з базою даних, таблицю можна створити, лише якщо вона не існує додавання пропозиції IF NOT EXISTS або, навпаки, видаліть таблицю перед її створенням, лише якщо вона існує за допомогою DROP TABLE IF EXISTS valor_temperaturas;

Щоб дозволити імена таблиць або баз даних, які можуть конфліктувати з командами або процедурами, знак наголосу, який іноді називають лапкою, можна використовувати для оточення імені та уникнення конфліктів або помилок у командах. SQL.

Визначте поля

Поля, які додаються під час створення таблиці, позначаються в круглих дужках із зазначенням типу даних, які вони зберігають. Типи даних, які зазвичай використовуються в базі даних для зберігання інформації з пристроїв, підключених до Інтернету речей, є цілими числами (INT o BIGINT) десяткові (DECIMAL, DOUBLE o FLOAT) часу (DATE, TIME, DATETIME o TIMESTAMP) і текст (переважно VARCHAR хоча є й інші подібні TEXT але вони не використовуються для IoT) Після типу даних і в дужках додається довжина даних і, якщо застосовно, формат або точність.

Додаючи AUTO_INCREMENT Визначення поля вказує менеджеру бази даних, що, якщо нічого іншого не вказано, під час створення нового запису значення останнього плюс відповідне приріст має бути автоматично збережено (яке зазвичай дорівнює 1). Зазвичай воно включається в поля, які використовуються як числові індекси записів.

Код NOT NULL вказує, що поле в записі не може бути порожнім, воно має зберігати певне значення.

При використанні DEFAULT При створенні поля таблиці вказується MySQL значення за замовчуванням для поля за умови, що під час додавання нового запису або його модифікації інше значення явно не вказано. Значення за замовчуванням, яке часто використовується, у цьому випадку для моніторингу значень IoT CURRENT_TIMESTAMP що дозволяє автоматично записувати поточний час у полі під час створення нового запису, тому дані автоматично ідентифікуються в часі.

Щоб оптимізувати продуктивність бази даних, зручно включити в кінець визначення полів таблиці те, яке використовується як індекс із пунктом PRIMARY KEY (ID) Ідентифікатор — це поле, вибране як індекс.

Наприкінці визначення таблиці ви можете вказати механізм бази даних, за допомогою якого вона керується, як значення параметра ENGINE з форматом ENGINE=InnoDB; буття InnoDB двигун, обраний у цьому випадку.

Створюйте бази даних, таблиці та налаштовуйте поля під час сеансу SQL

Код у наступному прикладі об’єднує накази, кроки, які потрібно виконати, щоб створити базу даних із мінімальною таблицею.

Створення резервних копій баз даних

Після налаштування сервера та створення бази даних і її таблиць все буде готово для зберігання інформації, отриманої вузлами IoT. Хоча в центрі цієї серії статей не йдеться про технічне обслуговування, є одна критична адміністративна проблема, яку важливо вирішити: резервне копіювання. Від більшості керівників, в т.ч PHPMYADMIN y Робочий стіл MySQL, резервні копії можна створювати та відновлювати, але їх зазвичай включають у сценарій, який їх автоматизує (ймовірно, разом із конфігурацією та багатьма іншими документами), щоб було зручніше використовувати інструмент MySQLDump, клієнт, який зазвичай є частиною звичайної інсталяції MySQL, або якийсь альтернативний варіант Дамп MySQL Що входить до замовлення? mydumper зробити копію і myloader щоб відновити його

MySQLDump

Форма замовлення mysqldump Це дуже просто, потрібно лише вказати ім'я користувача user, Пароль password і базу даних, яку ви хочете скопіювати або --all-databases для резервного копіювання всіх баз даних, як у наступному прикладі:

mysqldump --opt --user=pelaez --password=1234 --all-databases > copia.sql

Попередній приклад створює документ copia.sql який містить накази SQL (тобто текст), необхідний для перебудови всіх баз даних. При збереженні в текстовому форматі копія займає набагато більше, ніж необхідно, тому її також зазвичай стискають, щоб заощадити місце на пристрої, на якому вона зберігається.

Резервну копію, згенеровану за допомогою команди прикладу, можна відновити, якщо інформацію було втрачено через помилку команди нижче, яка виконує операції SQL документа copia.sql

mysql --user=pelaez --password=1234 < copia.sql

Дамп MySQL

Хоча попередніх операцій було б достатньо, його дуже часто використовують Дамп MySQL, вже майже стандарт для його продуктивності, хоча він не є частиною базових інструментів, що входять до складу MySQL

Нижче наведено варіанти mydumper найбільш використовувані, які, за логікою, повинні бути записані як частина тієї самої команди, розділені пробілами, хоча в цьому тексті вони розділені на рядки для полегшення читання:

--host polaridad.es «polaridad.es» — це назва сервера бази даних
--port 3306 "3306" - це номер порту, який прослуховує сервер (за замовчуванням це 3306)
--user pelaez "pelaez" - це ім'я користувача, з яким здійснюється доступ до бази даних
--password 1234 «1234» — пароль користувача, з яким здійснюється доступ до бази даних
--database temperaturas «температури» — це назва бази даних, для якої створюється резервна копія
--no-locks не блокувати базу даних під час копіювання (за замовчуванням вона блокується)
--build-empty-files також скопіюйте порожні таблиці
--compress-input стиснути вхід
--compress стиснути вихід (отриману резервну копію)

Щоб відновити резервну копію, отриману за допомогою mydumper він використовується myloader який в основному використовується з такими параметрами, які мають бути включені в тому самому порядку, розділених пробілами:

--host polaridad.es «polaridad.es» — це назва сервера бази даних
--port 3306 "3306" - це номер порту, який прослуховує сервер (за замовчуванням це 3306)
--user pelaez "pelaez" - це ім'я користувача, з яким здійснюється доступ до бази даних
--password 1234 «1234» — пароль користувача, з яким здійснюється доступ до бази даних
--directory=copia_iot «iot_copy» — папка, в якій знаходиться копія, створена за допомогою mydumper
--overwrite-tables перезаписувати таблиці, навіть якщо вони вже існують
--verbose 3 «3» - ступінь інформованості процесу (вище, більше повідомлень)
-C використовувати стиснення потоку даних

Наступна (і остання) стаття цієї серії пояснює як використовувати мову програмування PHP для зберігання інформації, отриманої пристроями, підключеними до Інтернету речей, у базах даних про які йшлося в цьому тексті.