One place for hosting & domains

      базы

      Создание резервных копий, восстановление и миграция базы данных MongoDB в Ubuntu 20.04


      Автор выбрал COVID-19 Relief Fund для получения пожертвования в рамках программы Write for DOnations.

      Введение

      MongoDB — одна из самых популярных систем управления базами данных NoSQL. Она отличается масштабируемостью, отказоустойчивостью, надежностью и удобством в использовании. В этом учебном модуле вы создадите резервную копию, восстановите и перенесете образец базы данных MongoDB.

      Импортирование и экспорт базы данных подразумевают работу с данными в формате, приемлемом для чтения, который также совместим с другими программными продуктами. Операции резервного копирования и восстановления MongoDB создают или используют специальные двоичные данные MongoDB, в результате чего не только обеспечиваются согласованность и целостность данных, но и сохраняются специальные атрибуты MongoDB. Таким образом, для переноса данных предпочтительнее использовать процесс резервного копирования и восстановления, если исходные и целевые системы совместимы друг с другом.

      Предварительные требования

      Перед выполнением этого учебного модуля необходимо следующее:

      Если не указано иное, все команды в этом учебном модуле, для которых требуются привилегии root, должны выполняться пользователем без привилегий root с привилегиями sudo.

      Шаг 1 — Использование JSON и BSON в MongoDB

      Прежде чем продолжить прохождение этого учебного модуля, важно понять некоторые базовые моменты. Если у вас есть опыт работы с другими системами баз данных NoSQL, такими как Redis, вы можете найти некоторое сходство с ними при работе с MongoDB.

      MongoDB использует для хранения информации форматы JSON и BSON (двоичный JSON). JSON – это удобный для прочтения человеком формат, идеально подходящий для экспорта и импорта данных. Вы сможете управлять экспортированными данными в этом формате с помощью любого инструмента, поддерживающего JSON, включая простой текстовый редактор.

      Пример документа JSON:

      Example of JSON Format

      {"address":[
          {"building":"1007", "street":"Park Ave"},
          {"building":"1008", "street":"New Ave"},
      ]}
      

      С JSON удобно работать, но этот формат поддерживает не все типы данных, доступные в BSON. Это означает, что при использовании JSON возможна так называемая потеря корректности информации. Для создания резервных копий и восстановления, лучше использовать двоичный формат BSON.

      Во-вторых, вам не нужно беспокоиться о явном создании базы данных MongoDB. Если база данных, которую вы указываете для импорта, еще не существует, она создается автоматически. Еще лучше обстоят дела со структурой коллекций (таблицы базы данных). В отличие от других СУБД, в MongoDB структура создается автоматически при вставке первого документа (строка базы данных).

      В-третьих, в MongoDB операции чтения или вставки больших объемов данных, таких как в задачах этого учебного модуля, могут потреблять много ресурсов процессора, памяти и дискового пространства. Это крайне важно, поскольку MongoDB часто используется для крупных баз данных и больших данных. Самое простое решение этой проблемы — выполнять экспорт и создавать резервные копии в ночное время или в периоды низкой нагрузки.

      В-четвертых, согласованность информации может представлять проблему, если у вас загруженный сервер MongoDB, где информация может изменяться при экспорте или резервном копировании базы данных. Одно из возможных решений этой проблемы — репликация, и вы можете использовать его, когда лучше освоитесь с MongoDB.

      Хотя вы можете использовать функции импорта и экспорта для резервного копирования и восстановления данных, существуют и лучшие способы обеспечения полной целостности данных в базах данных MongoDB. Чтобы создать резервную копию данных, вам следует использовать команду mongodump. Для восстановления используйте команду mongorestore. Давайте посмотрим, как они работают.

      Шаг 2 — Использование mongodump для резервного копирования базы данных MongoDB

      Вначале поговорим о резервном копировании базы данных MongoDB.

      Одним из самых важных аргументов команды mongodump является аргумент --db, указывающий имя базы данных, резервную копию которой вы хотите создать. Если вы не укажете имя базы данных, mongodump создаст резервные копии всех ваших баз данных. Второй важный аргумент — это аргумент --out, определяющий каталог, в котором будут сохранены данные. Давайте создадим резервную копию базы данных newdb и сохраним ее в каталоге /var/backups/mongobackups. В идеальной ситуации все наши резервные копии будут содержаться в каталоге с текущей датой /var/backups/mongobackups/10-29-20.

      Вначале создайте каталог /var/backups/mongobackups:

      • sudo mkdir /var/backups/mongobackups

      Затем запустите команду mongodump:

      • sudo mongodump --db newdb --out /var/backups/mongobackups/`date +"%m-%d-%y"`

      Результат должен будет выглядеть следующим образом:

      Output

      2020-10-29T19:22:36.886+0000 writing newdb.restaurants to 2020-10-29T19:22:36.969+0000 done dumping newdb.restaurants (25359 documents)

      Обратите внимание, что в вышеуказанном пути каталога мы использовали формат date +"%m-%d-%y", который автоматически получает текущую дату. Это позволяет нам размещать резервные копии в каталогах вида /var/backups/10-29-20/. Это особенно удобно для автоматизации резервного копирования.

      Теперь у вас имеется полная резервная копия базы данных newdb в каталоге /var/backups/mongobackups/10-29-20/newdb/. В этой резервной копии есть все необходимое для правильного восстановления newdb и сохранения так называемой «корректности».

      Как правило, резервное копирование выполняется регулярно, и обычно это делается в периоды наименьшей нагрузки на сервер. Вы можете задать команду mongodump как задачу планировщика (cron), чтобы она выполнялась регулярно, например, каждый день в 03:03.

      Для этого откройте редактор crontab:

      Обратите внимание, что при запуске sudo crontab вы будете редактировать кроны пользователя root. Рекомендуется сделать именно это, потому что если вы настроите кроны для своего пользователя, они могут выполняться неправильно, особенно если для вашего профиля sudo требуется проверка пароля.

      Вставьте в командную строку crontab следующую команду mongodump:

      crontab

      3 3 * * * mongodump --out /var/backups/mongobackups/`date +"%m-%d-%y"`
      

      В приведенной выше команде мы целенаправленно опускаем аргумент --db, потому что обычно мы хотим создать резервные копии всех наших баз данных.

      Если резервных копий будет слишком много, а базы данных MongoDB будут слишком большими, у вас быстро закончится место на диске. В связи с этим, также рекомендуется регулярно удалять старые резервные копии или сжимать их.

      Например, вы можете использовать следующую команду bash для удаления всех резервных копий старше семи дней:

      • find /var/backups/mongobackups/ -mtime +7 -exec rm -rf {} ;

      Аналогично предыдущей команде mongodump, вы можете добавить эту команду как задание cron. Его следует запускать непосредственно перед началом следующего резервного копирования, например, в 03:01. Для этого снова откройте crontab:

      Вставьте следующую строку:

      crontab

      1 3 * * * find /var/backups/mongobackups/ -mtime +7 -exec rm -rf {} ;
      

      Сохраните и закройте файл.

      Выполнение всех задач, описанных на этом шаге, обеспечит надлежащее решение резервного копирования для ваших баз данных MongoDB.

      Шаг 3 — Использование mongorestore для восстановления и переноса базы данных MongoDB

      При восстановлении базы данных MongoDB из предыдущей резервной копии вы получите точную копию информации MongoDB на определенный момент времени, включая все индексы и типы данных. Это особенно полезно, если вы хотите выполнить миграцию ваших баз данных MongoDB. Для восстановления MongoDB мы будем использовать команду mongorestore, которая работает с двоичными резервными копиями, которые производятся mongodump.

      Давайте продолжим наши примеры с базой данных newdb и посмотрим, как мы можем восстановить ее с предыдущей резервной копии. Вначале мы укажем имя базы данных с аргументом --nsInclude. Если использовать newdb.*, мы восстановим все коллекции. Для восстановления отдельной коллекции, например restaurants, следует использовать newdb.restaurants.

      Используя аргумент --drop мы обеспечим предварительное отбрасывание целевой базы данных так, чтобы резервная копия была восстановлена в чистой базе данных. Как последний аргумент мы зададим каталог последней резервной копии, который будет выглядеть примерно так: /var/backups/mongobackups/10-29-20/newdb/.

      Когда вы создадите резервную копию с временной меткой, вы можете восстановить ее с помощью следующей команды:

      • sudo mongorestore --db newdb --drop /var/backups/mongobackups/10-29-20/newdb/

      Результат должен будет выглядеть следующим образом:

      Output

      2020-10-29T19:25:45.825+0000 the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead 2020-10-29T19:25:45.826+0000 building a list of collections to restore from /var/backups/mongobackups/10-29-20/newdb dir 2020-10-29T19:25:45.829+0000 reading metadata for newdb.restaurants from /var/backups/mongobackups/10-29-20/newdb/restaurants.metadata.json 2020-10-29T19:25:45.834+0000 restoring newdb.restaurants from /var/backups/mongobackups/10-29-20/newdb/restaurants.bson 2020-10-29T19:25:46.130+0000 no indexes to restore 2020-10-29T19:25:46.130+0000 finished restoring newdb.restaurants (25359 documents) 2020-10-29T19:25:46.130+0000 done

      В примере выше мы восстанавливаем данные на том же сервере, на котором создали резервную копию. Если вы хотите перенести данные на другой сервер, используя эту же методику, вам следует скопировать на другой сервер каталог резервной копии, в нашем случае это /var/backups/mongobackups/10-29-20/newdb/.

      Заключение

      Вы выполнили ряд важных задач по резервному копированию, восстановлению и миграции баз данных MongoDB. Производственный сервер MongoDB нельзя использовать без надежной стратегии резервного копирования, такой как описана здесь.



      Source link

      Импорт и экспорт базы данных MongoDB в Ubuntu 20.04


      Автор выбрал COVID-19 Relief Fund для получения пожертвования в рамках программы Write for DOnations.

      Введение

      MongoDB — одна из самых популярных СУБД NoSQL. Она отличается масштабируемостью, мощностью и надежностью и удобством в использовании. В этом учебном модуле мы покажем, как импортировать и экспортировать ваши базы данных MongoDB.

      Следует отметить, что под импортом и экспортом мы подразумеваем операции с данными в удобном для чтения формате, совместимом с другими программными продуктами. В отличие от импорта и экспорта, операции резервного копирования и восстановления используют специальные двоичные данные MongoDB, обеспечивающие согласованность и целостность данных и сохраняющие специальные атрибуты MongoDB. Таким образом, для переноса данных предпочтительнее использовать процесс резервного копирования и восстановления, если исходные и целевые системы совместимы друг с другом.

      В этом учебном модуле мы не описываем задачи резервного копирования, восстановления и миграции. Более подробную информацию можно найти в учебном модуле Резервное копирование, восстановление и миграция баз данных MongoDB в Ubuntu 20.04.

      Предварительные требования

      Для этого учебного модуля вам потребуется следующее:

      Шаг 1 — Импортирование информации в MongoDB

      Чтобы узнать, как работает импорт информации в MongoDB, мы используем популярный образец базы данных MongoDB — базу о ресторанах. Эта база имеет формат json, и ее можно загрузить с помощью wget следующим образом:

      • wget https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json

      После завершения загрузки в текущем каталоге должен быть файл primer-dataset.json (размер 12 Мбайт). Импортируем данные из этого файла в новую базу данных newdb и в коллекцию restaurants.

      Используйте команду mongoimport следующим образом:

      • sudo mongoimport --db newdb --collection restaurants --file primer-dataset.json

      Результат будет выглядеть вот так:

      Output

      2020-11-11T19:37:55.607+0000 connected to: mongodb://localhost/ 2020-11-11T19:37:57.841+0000 25359 document(s) imported successfully. 0 document(s) failed to import

      Как показывает приведенная выше команда, были импортированы 25359 документов. Поскольку у нас не было базы данных с именем newdb, MongoDB создала ее автоматически.

      Давайте проверим импорт.

      Подключитесь к только что созданной базе данных newdb:

      Вы подключились к экземпляру базы данных newdb. Обратите внимание, что ваша командная строка изменилась. Это означает, что вы подключены к базе данных.

      Подсчитайте число документов в коллекции restaraunts с помощью команды:

      Будет выведен результат 25359, т. е. количество импортированных документов. Для еще более эффективной проверки вы можете выбрать первый документ из коллекции restaraunts следующим образом:

      Результат будет выглядеть вот так:

      [secondary label Output]
      {
          "_id" : ObjectId("5fac3d937f12c471b3f26733"),
          "address" : {
              "building" : "1007",
              "coord" : [
                  -73.856077,
                  40.848447
              ],
              "street" : "Morris Park Ave",
              "zipcode" : "10462"
          },
          "borough" : "Bronx",
          "cuisine" : "Bakery",
          "grades" : [
              {
                  "date" : ISODate("2014-03-03T00:00:00Z"),
                  "grade" : "A",
                  "score" : 2
              },
      ...
          ],
          "name" : "Morris Park Bake Shop",
          "restaurant_id" : "30075445"
      }
      

      Такая детальная проверка может выявить проблемы с содержанием документа, кодировкой и т. д. Формат json использует кодировку UTF-8, и ваши операции экспорта и импорта также должны использовать эту кодировку. Это необходимо помнить при редактировании файлов json вручную. В противном случае MongoDB автоматически сделает это за вас.

      Чтобы выйти из командной строки MongoDB, введите команду exit:

      Вы вернетесь в обычную командную строку как пользователь без привилегий root.

      Шаг 2 — Экспорт информации из MongoDB

      Как мы уже упоминали, при экспорте информации MongoDB вы можете получить удобный для чтения файлы с вашими данными. По умолчанию экспорт выполняется в формате json, но также вы можете использовать для экспорта формат csv (разделенные запятыми значения).

      Чтобы экспортировать информацию из MongoDB, используйте команду mongoexport. Это позволяет выполнять детализированный экспорт с указанием базы данных, коллекции, поля, или даже с использованием запроса для экспорта.

      Простой пример работы команды mongoexport — экспорт коллекции restaurants из базы данных newdb, куда мы ее до этого импортировали. Это можно сделать следующим образом:

      • sudo mongoexport --db newdb -c restaurants --out newdbexport.json

      В команде выше мы используем --db для определения базы данных, -c для определения коллекции и --out для определения файла, куда будут сохранены данные.

      Вывод успешной команды mongoexport должен выглядеть вот так:

      Output

      2020-11-11T19:39:57.595+0000 connected to: mongodb://localhost/ 2020-11-11T19:39:58.619+0000 [###############.........] newdb.restaurants 16000/25359 (63.1%) 2020-11-11T19:39:58.871+0000 [########################] newdb.restaurants 25359/25359 (100.0%) 2020-11-11T19:39:58.871+0000 exported 25359 records

      Этот вывод показывает, что было экспортировано 25359 документов, то есть, столько же документов, сколько мы импортировали.

      В некоторых случаях, вам может быть нужно экспортировать только часть коллекции. Учитывая структуру и содержание файла restaurants json, давайте экспортируем все рестораны китайской кухни, расположенные в Бронксе. Если мы хотим получить эту информацию напрямую при подключении к MongoDB, нам нужно снова подключиться к базе данных:

      Затем, используйте следующий запрос:

      • db.restaurants.find( { "borough": "Bronx", "cuisine": "Chinese" } )

      Результаты будут выведены на терминал:

      Output

      • 2020-12-03T01:35:25.366+0000 connected to: mongodb://localhost/
      • 2020-12-03T01:35:25.410+0000 exported 323 records

      Чтобы выйти из диалогового окна MongoDB, введите exit:

      Если мы хотим экспортировать данные из командной строки sudo, а не во время подключения к базе данных, предыдущий запрос нужно сделать частью команды mongoexport, указав его после аргумента -q:

      • sudo mongoexport --db newdb -c restaurants -q "{"borough": "Bronx", "cuisine": "Chinese"}" --out Bronx_Chinese_retaurants.json

      Обратите внимание, что после двойных кавычек в тексте запроса идет обратная косая черта (). Точно так же нужно выделять и другие специальные символы в запросе.

      Если экспорт выполнен успешно, результат будет выглядеть вот так:

      Output

      2020-11-11T19:49:21.727+0000 connected to: mongodb://localhost/ 2020-11-11T19:49:21.765+0000 exported 323 records

      Выше показано, что мы экспортировали 323 записи, которые можно будет найти в заданном нами файле Bronx_Chinese_retaurants.json.

      Используйте cat и less для сканирования данных:

      • cat Bronx_Chinese_retaurants.json | less

      Используйте пробел для прокрутки страниц данных:

      Output

      • date":{"$date":"2015-01-14T00:00:00Z"},"grade":"Z","score":36}],"na{"_id":{"$oid":"5fc8402d141f5e54f9054f8d"},"address":{"building":"1236","coord":[-73.8893654,40.81376179999999],"street":"238 Spofford Ave","zipcode":"10474"},"borough":"Bronx","cuisine":"Chinese","grades":[{"date":{"$date":"2013-12-30T00:00:00Z"},"grade":"A","score":8},{"date":{"$date":"2013-01-08T00:00:00Z"},"grade":"A","score":10},{"date":{"$date":"2012-06-12T00:00:00Z"},"grade":"B","score":15}],
      • . . .

      Нажмите q, чтобы выйти. Теперь вы можете импортировать и экспортировать базу данных MongoDB.

      Заключение

      В этом учебном модуле мы познакомили вас с основами экспорта информации из базы данных MongoDB и импорта информации в эту БД. Дополнительную информацию можно найти в учебном модуле Резервное копирование, восстановление и миграция базы данных MongoDB в Ubuntu 20.04.

      Также вы можете рассмотреть возможность использования репликации. Репликация позволяет продолжить выполнение службы MongoDB без перебоев на подчиненном сервере MongoDB во время восстановления главного сервера после неисправности. Частью процесса репликации является журнал операций (oplog), где записываются все операции, изменяющие данные. Вы можете использовать этот журнал, как если бы вы использовали двоичный журнал в MySQL для восстановления данных после последнего резервного копирования. Помните, что резервное копирование обычно выполняется ночью, и если вы решите восстановить данные вечером, вы потеряете все изменения, которые произошли с момента создания резервной копии.



      Source link

      Использование триггеров базы данных MySQL в Ubuntu 18.04


      Автор выбрал фонд Apache Software Foundation для получения пожертвования в рамках программы Write for DOnations.

      Введение

      Триггер в MySQL — это определяемая пользователем SQL-команда, которая автоматически вызывается во время операций INSERT, DELETE или UPDATE. Код триггера связан с таблицей и уничтожается после удаления таблицы. Вы можете определить время действия триггера и указать, когда его нужно активировать – до или после определенного события базы данных.

      Триггеры имеют несколько преимуществ Например, вы можете использовать их для генерации значения производного столбца во время выполнения INSERT. Еще один вариант использования триггера – обеспечение ссылочной целостности при сохранении записи в нескольких связанных таблицах. Также к преимуществам триггеров относятся регистрация действий пользователя для аудита таблиц и оперативное копирование данных в разных схемах баз данных для обеспечения избыточности и предотвращения единой точки отказа.

      Вы также можете использовать триггеры, чтобы сохранить правила проверки на уровне базы данных. Это помогает избежать нарушения бизнес-логики при совместном использовании одного источника данных несколькими приложениями. Это значительно уменьшает количество обращений к серверу базы данных, что, в свою очередь, улучшает время отклика приложений. Поскольку сервер базы данных выполняет триггеры, они могут воспользоваться улучшенными ресурсами сервера, такими как RAM и CPU.

      В этом обучающем модуле вы научитесь создавать, использовать и удалять различные типы триггеров в вашей базе данных MySQL.

      Предварительные требования

      Прежде чем начать, убедитесь в наличии следующего:

      Шаг 1 — Создание тестовой базы данных

      На этом этапе вы создадите тестовую клиентскую базу данных пользователя с несколькими таблицами для демонстрации работы триггеров MySQL.

      Более подробно о работе MySQL можно прочитать в инструкции Запросы в MySQL​​​.

      Вначале войдите на сервер MySQL как root:

      По запросу введите свой root пароль MySQL и нажмите ENTER для продолжения. Когда вы увидите mysql>, выполните следующую команду, чтобы создать базу данных test_db:

      Output

      Query OK, 1 row affected (0.00 sec)

      Далее переходите к test_db с помощью:

      Output

      Database changed

      Начинайте с создания таблицы customers. В этой таблице будут храниться записи клиентов, включая customer_id, customer_name и level. Будет два типа клиентов: BASIC и VIP.

      • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      Теперь, добавьте несколько записей в таблицу customers. Для этого выполните следующие команды одну за другой:

      • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

      После выполнения каждой команды INSERT вы увидите следующий вывод:

      Output

      Query OK, 1 row affected (0.01 sec)

      Чтобы убедиться, что тестовые записи были успешно вставлены, выполните команду SELECT:

      Output

      +-------------+---------------+-------+ | customer_id | customer_name | level | +-------------+---------------+-------+ | 1 | JOHN DOE | BASIC | | 2 | MARY ROE | BASIC | | 3 | JOHN DOE | VIP | +-------------+---------------+-------+ 3 rows in set (0.00 sec)

      Затем создайте другую таблицу customers для хранения соответствующей информации об учетной записи клиентов. Таблица будет содержать поля customer_id и status_notes.

      Запустите следующую команду:

      • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

      Далее создайте таблицу sales. В этой таблице будут храниться данные о продажах, имеющих отношение к разным клиентам в столбце customer_id:

      • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      Вы сможете добавить тестовые данные в колонку sales на следующих этапах во время тестирования триггеров. Далее создайте таблицу audit_log для регистрации обновлений, внесенных в таблицу sales при имплементации триггера AFTER UPDATE в шаге 5:

      • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.02 sec)

      Имея базу данных test_db и четыре таблицы, теперь вы можете перейти к работе с различными триггерами MySQL в вашей базе данных.

      Шаг 2 — Создание триггера Before Insert

      На этом этапе вы изучите синтаксис триггера MySQL перед тем, как применить эту логику для создания триггера BEFORE INSERT, который проверяет поле sales_amount перед вставкой данных в таблицу sales.

      Общий синтаксис для создания триггера MySQL показан в следующем примере:

      DELIMITER //
      CREATE TRIGGER [TRIGGER_NAME]
      [TRIGGER TIME] [TRIGGER EVENT]
      ON [TABLE]
      FOR EACH ROW
      [TRIGGER BODY]//
      DELIMITER ;
      

      Структура триггера включает:

      DELIMITER //: разделитель MySQL по умолчанию — это ;. Его нужно заменить на что-то другое, для того, чтобы MySQL рассматривал следующие строки, как одну команду, пока не достигнет пользовательского разделителя. В данном примере в качестве разделителя используется //, а стандартный разделитель ; стоит в конце.

      [TRIGGER_NAME]: триггер должен иметь имя, и вы можете указать его именно здесь.

      [TRIGGER TIME]​​​: триггер может быть вызван в разные моменты времени. MySQL позволяет определить, когда запускать триггер — до или после операции с базой данных.

      [TRIGGER EVENT]: триггеры могут быть вызваны только операциями INSERT, UPDATE и DELETE. Вы можете использовать любое из значений в зависимости от того, чего вы хотите достичь.

      [TABLE]: любой триггер, который вы создаете в своей базе данных MySQL, должен быть связан с таблицей.

      FOR EACH ROW: этот оператор позволяет MySQL выполнять код триггера для каждой строки, на которую влияет триггер.

      [TRIGGER BODY]: код, который выполняется при вызове триггера, называется trigger body​​. Это может быть один SQL-оператор или несколько команд. Обратите внимание, если вы выполняете несколько SQL-операторов в теле триггера, вы должны заключить их в блок BEGIN... END.

      Примечание: при создании тела триггера вы можете использовать ключевые слова OLD и NEW для доступа к старым и новым значениям колонки, введенным во время операции INSERT, UPDATE и DELETE. В триггере DELETE может быть использовано только ключевое слово OLD (подробнее об этом в шаге 4).

      Теперь вы можете создать свой первый триггер BEFORE INSERT. Триггер будет связан с таблицей sales и будет вызываться перед вставкой записи для проверки sales_amount. Функция триггера состоит в том, чтобы проверить, превышает ли значение sales_amount, вставляемое в таблицу продаж, величину 10000, и выдать ошибку, если это так.

      Убедитесь, что вы вошли на сервер MySQL. Затем введите следующие команды MySQL одну за другой:

      • DELIMITER //
      • CREATE TRIGGER validate_sales_amount
      • BEFORE INSERT
      • ON sales
      • FOR EACH ROW
      • IF NEW.sales_amount>10000 THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      • END IF//
      • DELIMITER ;

      Используйте IF... THEN... END IF​​​ для оценки того, находится ли сумма, указанная в операторе INSERT, в пределах вашего диапазона. Триггер может извлечь новое значение sales_amount, используя ключевое слово NEW.

      Чтобы вызвать общее сообщение об ошибке, используются следующие строки для информирования пользователя:

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      

      Далее вставьте запись sales_amount со значением 11000 в таблицу sales, чтобы проверить, остановит ли триггер операцию:

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

      Output

      ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

      Эта ошибка показывает, что код триггера работает должным образом.

      Теперь попробуйте новую запись со значением 7500, чтобы проверить правильность действия команды:

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');

      Поскольку значение находится в рекомендованном диапазоне, вы увидите следующий вывод:

      Output

      Query OK, 1 row affected (0.01 sec)

      Для подтверждения вставки данных запустите следующую команду:

      Вывод подтверждает вставку данных в таблицу:

      Output

      +----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec)

      На этом этапе вы протестировали способность триггеров проверять данные перед вставкой в базу данных.

      Теперь поработайте с триггером AFTER INSERT для сохранения связанной информации в разных таблицах.

      Шаг 3 — Создание триггера After Insert

      Триггеры AFTER INSERT выполняются после успешной вставки записей в таблицу. Эта функция может использоваться для автоматического запуска других бизнес-логик. Например, в банковских приложениях триггер AFTER INSERT может закрывать кредитный счет, когда клиент завершает выплату кредита. Триггер может отслеживать все платежи, внесенные в таблицу транзакций, и автоматически закрывать кредит, как только кредитный баланс будет равен нулю.

      На этом этапе вы поработаете с таблицей customer_status​​​, используя триггер AFTER INSERT для ввода связанных клиентских записей.

      Для создания триггера AFTER INSERT введите следующие команды:

      • DELIMITER //
      • CREATE TRIGGER customer_status_records
      • AFTER INSERT
      • ON customers
      • FOR EACH ROW
      • Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
      • DELIMITER ;

      Output

      Query OK, 0 rows affected (0.00 sec)

      Таким образом вы инструктируете MySQL сохранить еще одну запись в таблицу customer_status, как только происходит вставка новой клиентской записи в таблицу customers.

      Теперь вставьте новую запись в таблицу customers, чтобы убедиться, что код триггера вызывается:

      • Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

      Output

      Query OK, 1 row affected (0.01 sec)

      После успешной вставки записи убедитесь, что запись нового статуса была добавлена в таблицу customer_status:

      • Select * from customer_status;

      Output

      +-------------+-----------------------------+ | customer_id | status_notes | +-------------+-----------------------------+ | 4 | ACCOUNT OPENED SUCCESSFULLY | +-------------+-----------------------------+ 1 row in set (0.00 sec)

      Вывод подтверждает успешную работу триггера.

      Триггер AFTER INSERT​​​ полезен для мониторинга жизненного цикла клиента. В производственной среде учетные записи клиентов могут проходить различные этапы, например открытие, приостановка и закрытие счета.

      На следующем этапе вы будете работать с триггерами UPDATE.

      Шаг 4 — Создание триггера Before Update

      Триггер BEFORE UPDATE схож с триггером BEFORE INSERT, разница заключается в том, когда они вызываются. Вы можете использовать триггер BEFORE UPDATE для проверки бизнес-логики перед обновлением записи. Для проверки используйте таблицу customers, в которую вы уже вставили некоторые данные.

      В базе данных есть два типа клиентов. В этом примере после того, как учетная запись клиента будет обновлена до уровня VIP, она не сможет быть понижена до уровня BASIC. Чтобы применить такое правило, создайте триггер BEFORE UPDATE, который будет выполняться перед оператором UPDATE, как показано ниже. Если пользователь базы данных попытается понизить клиента до уровня BASIC с уровня VIP, будет активировано определяемое пользователем исключение.

      Введите следующие команды SQL одну за другой, чтобы создать триггер BEFORE UPDATE:

      • DELIMITER //
      • CREATE TRIGGER validate_customer_level
      • BEFORE UPDATE
      • ON customers
      • FOR EACH ROW
      • IF OLD.level='VIP' THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
      • END IF //
      • DELIMITER ;

      Используйте ключевое слово OLD для фиксации уровня, предоставленного пользователем при выполнении команды UPDATE. Опять же, вы используете IF... THEN... END IF, чтобы сообщить пользователю об общей ошибке.

      Далее выполните следующую SQL команду, которая попытается понизить учетную запись клиента, имеющую идентификатор customer_id, равный 3:

      • Update customers set level='BASIC' where customer_id='3';

      Вы увидите следующий вывод, предоставляющий SET MESSAGE_TEXT:

      Output

      ERROR 1644 (45000): A VIP customer can not be downgraded.

      Если вы выполните ту же команду для клиента уровня BASIC и попытаетесь повысить учетную запись до уровня VIP, команда выполнится успешно:

      • Update customers set level='VIP' where customer_id='1';

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      Вы использовали триггер BEFORE UPDATE для применения бизнес-правила. Теперь перейдем к использованию триггера AFTER UPDATE для ведения журнала аудита.

      Шаг 5 — Создание триггера After Update

      Триггер AFTER UPDATE вызывается после успешного обновления записи в базе данных. Такое поведение триггера подходит для ведения журнала аудита. В многопользовательской среде администратор с целью аудита может просмотреть историю пользователей, обновляющих записи в конкретной таблице.

      Вы создаете триггер, который регистрирует активность обновления таблицы sales. Наша таблица audit_log будет содержать информацию о пользователях MySQL, обновляющих таблицу sales, дату обновления date, а также новые new и старые old значения sales_amount.

      Для создания триггера, выполните следующие команды SQL:

      • DELIMITER //
      • CREATE TRIGGER log_sales_updates
      • AFTER UPDATE
      • ON sales
      • FOR EACH ROW
      • Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
      • DELIMITER ;

      Вы вставляете новую запись в таблицу audit_log. Вы используете ключевое слово NEW для получения значения sales_id и нового значения sales_amount. Также вы используете ключевое слово OLD для получения предыдущего значения sales_amount, если вы хотите зарегистрировать обе суммы для аудита.

      Команда SELECT USER() извлекает текущего пользователя, выполняющего операцию, а оператор NOW() извлекает значение текущей даты и времени с сервера MySQL.

      Теперь, если пользователь попытается обновить значение какой-либо записи в таблице sales, триггер log_sales_updates вставит новую запись в таблицу audit_log.

      Давайте создадим новую запись о продажах со случайным значением sales_id, равным 5, и попробуем обновить ее. Сначала вставьте запись о продажах:

      • Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

      Output

      Query OK, 1 row affected (0.00 sec)

      Затем обновите запись:

      • Update sales set sales_amount='9000' where sales_id='5';

      Вывод должен выглядеть так:

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      Теперь выполните следующую команду, чтобы проверить, смог ли триггер AFTER UPDATE зарегистрировать новую запись в таблице audit_log:

      Триггер зарегистрировал обновление. Ваш вывод должен показать предыдущую сумму sales_amount и новую сумму new amount​​, зарегистрированную пользователем, который обновил запись:

      Output

      +--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec)

      Также в таблице вы увидите дату и время, когда было выполнено обновление, что важно для аудита.

      Далее вы будете использовать триггер DELETE для обеспечения целостности ссылок на уровне базы данных.

      Шаг 2 — Создание триггера Before Delete

      Триггеры BEFORE DELETE вызываются до выполнения операции DELETE в таблице. Этот вид триггеров обычно используется для обеспечения целостности ссылок в разных связанных таблицах. Например, каждая запись в таблице sales связана с записью customer_id из таблицы customers. Если пользователь базы данных удалил из таблицы customers запись, у которой есть связанная запись в таблице sales, у вас не будет возможности узнать, какой клиент был связан с этой записью.

      Избежать подобных ситуаций и сделать логику более надежной позволит создание триггера BEFORE DELETE. Выполните следующие SQL команды одну за другой:

      • DELIMITER //
      • CREATE TRIGGER validate_related_records
      • BEFORE DELETE
      • ON customers
      • FOR EACH ROW
      • IF OLD.customer_id in (select customer_id from sales) THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'The customer has a related sales record.';
      • END IF//
      • DELIMITER ;

      Теперь попробуйте удалить клиента, у которого есть связанная запись в таблице sales:

      • Delete from customers where customer_id='2';

      В результате вы получите следующий вывод:

      Output

      ERROR 1644 (45000): The customer has a related sales record.

      Триггер BEFORE DELETE может предотвратить случайное удаление связанной информации в базе данных.

      В некоторых ситуациях может потребоваться удалить из разных связанных таблиц все записи, связанные с конкретной записью. В этой ситуации возможно использовать триггер AFTER DELETE, который вы протестируете в следующем шаге.

      Шаг 5 — Создание триггера After Delete

      Триггеры AFTER DELETE активируются, когда запись была успешно удалена. Примером использования триггера AFTER DELETE является ситуация, когда скидка, которую получает конкретный клиент, определяется количеством покупок, совершенных этим клиентом в течение определенного периода. Если некоторые из записей клиента будут удалены из таблицы sales, скидка для этого клиента должна уменьшиться.

      Еще один вариант использования триггера AFTER DELETE — удаление связанной информации из других таблиц после удаления записи из базовой таблицы. Например, вы можете установить триггер, который удаляет запись о клиенте, если записи о продажах с соответствующим customer_id будут удалены из таблицы sales. Запустите следующую команду для создания триггера:

      • DELIMITER //
      • CREATE TRIGGER delete_related_info
      • AFTER DELETE
      • ON sales
      • FOR EACH ROW
      • Delete from customers where customer_id=OLD.customer_id;//
      • DELIMITER ;

      Далее запустите следующую команду, чтобы удалить все записи о продажах, связанных с customer_id, равному 2:

      • Delete from sales where customer_id='2';

      Output

      Query OK, 1 row affected (0.00 sec)

      Теперь проверьте, удалились ли записи для этого клиента из таблицы sales:

      • Select * from customers where customer_id='2';

      Вы получите вывод Empty Set, поскольку запись клиента, связанная с customer_id 2, была удалена триггером:

      Output

      Empty set (0.00 sec)

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

      Шаг 8 — Удаление триггеров

      Как и любой другой объект базы данных, вы можете удалить триггеры с помощью команды DROP. Синтакс удаления триггера следующий:

      Drop trigger [TRIGGER NAME];
      

      Например, чтобы удалить последний созданный триггер AFTER DELETE, выполните следующую команду:

      • Drop trigger delete_related_info;

      Output

      Query OK, 0 rows affected (0.00 sec)

      Необходимость удаления триггеров возникает, когда вы хотите воссоздать его структуру. В таком случае вы можете сбросить триггер и создать новый с помощью разных команд для триггеров.

      Заключение

      В этом обучающем руководстве вы научились создавать, использовать и удалять различные триггеры из базы данных MySQL. На примере клиентской базы данных вы ознакомились с применением триггеров для различных целей, таких как проверка данных, применение бизнес-логики, ведение журнала аудита и обеспечение целостности ссылок.

      Дополнительную информацию по использованию вашей базы данных MySQL можно найти здесь:



      Source link