Персональный сайт Вячеслава Горчилина
Все статьи

Удалённое управление таблицами в MySQL

1. Введение в проблему
Проблема безопасности данных занимает уже давно одно из первых мест. В это понятие включается много составляющих, но здесь мы затронем те из них, которые связаны с безопасной передачей данных по сети Интернет. А именно, удалённым управлением таблицами в БД MySQL. Сюда входит: создание и удаление таблиц, добавление, изменение и удаление строк этих таблиц по SQL запросам, а также апгрейд таблиц по определённым критериям. И всё это - через безопасный канал связи, который обеспечивает модуль UAcoder.

Особенностью этого модуля является то, что передавать можно все символы стандарта ANSI, куда входят все необходимые для нашего удалённого управления символы. Кроме того, для удалённого управления, мы используем обычный протокол HTTP (HTTPS отдыхает), т.к. модуль обеспечивает достаточную криптостойкость для открытых каналов связи.

Все рассуждения и выкладки (в виде реальных скриптов), будем вести для следующей ситуации. У меня имеется:

  • Домашний ПК, на котором установлены серверы Apache, MySQL и Perl. Далее называем всё это - локальный хост
  • Удалённый компьютер, на котором есть всё то же. Это может быть самый обычный хостинг с поддержкой Perl+MySQL. В дальнейшем именуем это - удалённый хост
Задача. С локального хоста обеспечить управление таблицами MySQL на удалённом хосте. При этом, желательно, чтобы сами таблицы, и их содержимое, на локальном и удалённом хостах были идентичные. Другими словами, нужно, чтобы я мог создавать, изменять, удалять и апгрейдить таблицы на моём хостинге, с моего домашнего компьютера. Разумеется, такую возможность должен иметь только я, и никто другой без моего ведома:-)

2. Что для этого нужно
Для выполнения поставленной задачи нам понадобятся файлы, которые можно скачать здесь. В архиве собраны файлы, и для локального, и для удалённого хоста. Некоторые из них универсальны, т.е. применяются и там, и там. Как, и где они должны быть установлены, описано ниже.

На локальном хосте

  • dbm/
    • select
    • tables
  • module/
    • Tie.pm
    • UAcoder.pm
  • password/
    • password
  • create.cgi
  • insert.cgi1
  • select.cgi
  • update.cgi1

На удаленном хосте

  • module/
    • Tie.pm
    • UAcoder.pm
  • password/
    • password
  • tables.cgi

3. Настройка и работа файлов
3.1 Tie.pm
Это универсальный модуль2, обеспечивающий интерфейс подключения, передачи и приёма данных с удалённого хоста. А также, в нём прописаны общие для всех файлов, данные настройки.

Пути:

  • $HOST- нужно указать домен, к которому будет осуществляться подключение, и на котором расположена моя БД
  • $PATH- указывается путь к файлу tables.cgi на удалённом хосте, отсчитанный от корня
    Например, на моём хостинге файл tables.cgi расположен так: http://www.moyhosting.ru/cgi-bin/ua/tables.cgi, то: $HOST='www.moyhosting.ru', $PATH='/cgi-bin/ua/tables.cgi'.

    Подключение к MySQL (стандартное):

  • db_name - имя БД
  • user - логин юзера
  • password - пароль юзера
    ПРИМ. Не забывайте, что эти данные на локальном и удалённом хосте могут отличаться.

    Пароль:

  • здесь нужно указать путь к файлу с паролем. По умолчанию этот путь './password/password', но этот файл желательно разместить на удалённом хосте так, чтобы он был недоступен по FTP, и затем, соответственно, изменить этот путь. Файл содержит всего одну строку - пароль к кодированию и декодированию данных. Сам же пароль можно сгенерировать по известной методике (п.7). Само собой разумеется, что пароли на локальном и удалённом хосте должны быть одинаковые.

    3.2 create.cgi
    Этот скрипт отвечает за создание и удаление таблиц как на локальном, так и на удалённом хосте. Скрипт получает информацию о таблицах из реестра, который находится в файле './dbm/tables'. В реестре описание каждой новой таблицы начинается с символа '=' и названия таблицы. Ниже следуют поля и их типы также, как они распологаются в команде CREATE TABLE. Каждое поле должно располагаться в новой строке. В строке возможны комментарии, которые начинаются со знака '#'.

    После задания всех полей можно задать критерий, по которой будет производится перенос данных таблицы с удалённого на локальный хост (апгрейд таблицы). Критерий представляет собой SQL-запрос, который может занимать несколько строк, а строки должны начинаться с символа '>'. Если команда не задана - таблица будет переноситься целиком. Критерий можно задавать для таблиц, записи или обновления в которых сопровождаются установкой текущей даты. Пример: таблица регистрации клиентов; в этом случае переносится не вся таблица, а только строки, в которых поле 'дата' больше, чем дата последнего переноса. Для больших таблиц - это просто огромная экономия как времени, так и трафика!

    Сразу же после создания реестра, при помощи этого скрипта, можно приступать к созданию таблиц на локальном и удалённом хостах.

    3.3 select.cgi
    Работа этого скрипта состит из нескольких этапов. Вначале вам предлагается ввести команду SELECT, которая будет передана на удалённый хост, после чего будет возвращена таблица с первыми 20-ю строками.

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

    Особенностью этого скрипта является наличие файла './dbm/select', в котором можно записывать наиболее распостранённые запросы. В скачиваемом архиве есть этот файл с примерами запросов. Каждый запрос можно для удобства назвать и прокомментировать. Всё это позволяет огранизовать админ-интерфейс даже к большому интернет-проекту. Другими словами, с помощью этой технологии можно управлять целым проектом с домашнего компьютера в защищённом режиме передачи данных, не передавая при каждом запросе пароли из кукисов, или аутентификаций, и не испольуя медленный и дорогостоящий протокол HTTPS.

    3.4 insert.cgi
    Скрипт позволяет добавить строку в выбранную таблицу. Если поле строки не заполнено, в него добавляется значение по умолчанию. Строки добавляются в таблицу как на локальном, так и на удалённом хостах, что обеспечивает синхронность и идентичность данных.

    3.5 update.cgi
    Многофункциональный скрипт, который позволяет оценить количество строк перед апргейдом таблицы3, произвести сам апргейд, а затем установить в комментариях к таблице дату апгрейда. Пользователю нужно выбрать талицу, после чего скрипт предварительно запросит на удалённом хосте количество строк для апгрейда, и, если пользователь согласен с этим, начнёт сам апгрейд.

    Скрипт построен так, что если количество строк для апгрейда более 1000, то он разбивает весь процесс на несколько частей. Каждая такая часть делает свой собственный цикл запрос-ответ и апгрейдит только полученные в этой части строки. После апгрейда скрипт выдаст число обновлённых строк.

    ПРИМ. Строки вставляются в таблицу командой REPLACE, поэтому нужно помнить о свойстве этой команды: если строка заменяется на новую, то команда возвращает число обновлённых строк =2, если строка просто добавляется в таблицу, то команда возвращает число обновлённых строк =1

    -----------------------------------------------------------
    [1] Универсальный файл - файл, задействованный как на локальном, так и на удалённом хостах
    [2] Апгрейд таблицы - здесь будем называть этим термином перенос таблицы с удалённого хоста на локальный, причём, если задан критерий апгрейда, то переносятся только строки, соответствующие этому критерию
  • Горчилин Вячеслав, 2004 г.
    * Перепечатка статьи и применение модуля возможны с условием ссылки на сайт и соблюдением авторских прав

    « Назад
    2009-2017 © Vyacheslav Gorchilin