Перенос БД с SQLite на MySQL на примере MediaWiki

Рассматривая в этой статье перенос сайта на другую СУБД на примере MediaWiki, создаваемом для Википедии и распространяемом бесплатно, дадим также общие советы.

Обе бесплатные открытые СУБД SQLite и MySQL очень популярны, имеют свои преимущества, но предназначены для разных целей, потому если цели или условия нашего проекта поменялись, то возникает необходимость перехода от одной СУБД к другой. Возможно мы захотели получить удалённый доступ к базе данных, или разграничить права доступа к таблицам, или использовать какое-то конкретное средство администрирования – причин для перехода может быть несколько. К сожалению пока не обнаружено простого решения этой задачи выполняющего работу путем нажатия одной кнопки, но несложный алгоритм описанный в статье позволит выполнить всё самостоятельно, быстро и бесплатно.

Итак, у нас имеется сайт под управлением MediaWiki использующий SQLite, который мы решили перенести на MySQL. Во избежании проблем крайне рекомендуется при переходе использовать ту же самую версию движка, так как при смене версий может быть изменена структура базы данных. Можно вообще использовать этот же набор файлов MediaWiki, лишь изменяя в конфигурационном файле LocalSettings.php переменные доступа к базе данных (они имеют общее начало: $wgDB...). Используя ту же версию мы можем не заботиться о переносе структуры, всё что нам остаётся – это перенести содержимое таблиц. Самым простым решением было бы выгрузить содержимое SQLite в виде текстового файла с последовательностью SQL команд и затем выполнить эти команды на MySQL. Однако, существует

Разница между реализацией SQL языка в SQLite и MySQL

В нашей конкретной задаче не имеет значения в чём различается функционал SQLite и MySQL, для нас важно чем отличаются SQL команды в этих СУБД, так как в конечном счёте работа с любой СУБД сводится к выполнению последовательности этих команд в её среде. С их помощью создаются, изменяются и удаляются все объекты, а также записываются и считываются данные. Несмотря на существующие стандарты языка SQL, каждая СУБД вносит что-то своё в его реализацию. Рассматриваемые нами СУБД не соответствуют стандартам по-разному, поэтому при подготовке команд SQLite для MySQL нужно будет:

• Удалить строки (Некоторые парсеры удаляют ещё строки «CREATE UNIQUE INDEX» и «COMMIT;», но это не обязательно, так как обе команды есть в MySQL.):
  • PRAGMA
  • BEGIN TRANSACTION;
  • sqlite_sequence
  • DELETE FROM sqlite_sequence
  • INSERT INTO sqlite_sequence

• Заменить:
  • AUTOINCREMENT на AUTO_INCREMENT
  • Все значения логических полей с 't' на '1' и с 'f' на '0'
  • Символ " на ` – т.е. двойную кавычку заменить на апостроф (кроме как в текстовых полях, где эти символы могут использоваться как часть текста).

Всё это можно было бы доверить парсеру (тем более, что их для SQLite создано уже довольно много), преобразующему синтаксис по известным нам правилам, но последнее из перечисленных условий является самым проблемным. Оно делает невозможным корректное автоматическое преобразование синтаксиса, так как в текстах сайта вполне вероятно могут встретится оба упомянутых символа в непредсказуемых комбинациях. Кроме того, задача парсера осложняется ещё и тем, что в статьях переносимого сайта могут встречаться SQL выражения, которые, конечно, же надо обрабатывать, но объяснить это парсеру очень трудно. Такая ситуация вынуждает нас использовать более долгий

Алгоритм перехода сайта под управлением MediaWiki c SQLite на MySQL

Перед началом переноса сайта MediaWiki желательно прочитать общие рекомендации на официальном сайте.

Так как в движке MediaWiki реализован механизм резервного копирования БД в XML-файл, не зависящей от используемой базы данных, то переход значительно упрощается. Его использование решит нашу основную сложность: XML представление позволяет обойти проблемы с кавычками в текстовых полях и с использованием в них SQL команд. Однако в этот файл выгружается только содержимое вики страниц и история их изменений, он не включает в себя данные из других таблиц. Поэтому наш алгоритм будет состоять из трёх этапов, с тремя шагами в каждом. Первым этапом будет

1. Подготовка нового сайта

Пока новый сайт не будет полностью настроен и проверен старый должен находится в рабочем состоянии. Поэтому мы

1.1. Переключаем действующий сайт в режим только для чтения. Для этого достаточно в конфигурационном файле определить переменную $wgReadOnly, задав сообщение, которое будет появляться в случае, если кто-то захочет отредактировать статью. Например:

$wgReadOnly = 'Сайт обновляется и редактирование временно запрещено';

1.2. Дублируем файлы действующего сайта, кроме файла базы данных SQLite. Можно не копировать файлы и каталоги, а создать на них мягкие ссылки. Для автоматизации этого процесса можно взять скрипт create_wiki.sh, написанный bash для создания вики-фермы.

1.3. Запускаем инсталлятор MediaWiki через веб-интерфейс и проводим процедуру установки до конца. Это создаст необходимую структуры базы данных в MySQL.

Теперь можно начать

2. Перенос страниц сайта

2.1. Выгружаем из действующего сайта содержимое вики-страниц в XML-файл с помощью штатного скрипта резервного копирования DumpBackup.php.

2.2. Делаем импорт данных на новый сайт с помощью importDump.php(работает не быстро). Если вики очень большая, то можно использовать MWDumper – специально написанный инструмент на Java. Данные для подключения к базе данных как здесь так и в предыдущем шаге скрипты берут из конфигурационного файла LocalSettings.php, поэтому они должны запускаться из соответствующих каталогов старого и нового сайтов.

2.3. Проверяем насколько удачно страницы перемещены: заходим на новый сайт, пробегаем глазами пару каких-нибудь сложных по форматированию статей, убеждаемся, что текст не искажён.

Теперь самое трудное позади, на очереди

3. Перенос оставшихся таблиц

Данные из остальных таблиц переносить уже гораздо проще, т. к. в них нет проблемного текста (с одной оговоркой, о которой скажем попозже). Будем брать SQL команды SQLite, конвертировать их в команды MySQL и запускать на последнем. Рассмотрим этот процесс на примере переноса таблицы user (пользователи вики). Вообще переноса одной только этой таблицы уже будет достаточно, чтобы проверить работоспособность нового сайта. Далее можно будет перенести таблицы user_properties (пользовательские настройки), category (категории статей), interwiki (короткие ссылки на другие вики сайты) и т.д. на усмотрение администратора. Все таблицы переносить смысла нет. С помощью phpMyAdmin можно легко просмотреть какие таблицы на действующем сайте у нас заняты и чем. Итак,

3.1. Получаем таблицы SQLite в виде SQL команд. Для этого можно использовать, например, утилиту с графическим интерфейсом sqliteman, взяв таблицы по отдельности – так будет проще всего. Также можно выполнить команду dump в командном интерпретаторе sqlite для получения всей базы данных:

sqlite3 sample.db .dump > sqlite_dump.sql

А из полученного дампа выбрать команды для интересующих нас таблиц и перенести их в один файл (скажем sqlite_tables.sql) для последующего преобразования.

3.2. Преобразуем SQL команды SQLite в MySQL с помощью парсера, например, parse_sqlite_sql.py:

python parse_sqlite_sql.py sqlite_tables.sql

Этот питоновский парсер на выходе создаёт два файла, группируя отдельно sql команды для данных и для структуры БД. Нас интересуют только данные (о совпадении структур мы уже позаботились). После преобразования команды имеют вид:

insert into user ("user_id", "user_name", "user_real_name", "user_password", "user_newpassword", "user_newpass_time", "user_email", "user_touched", "user_token", "user_email_authenticated", "user_email_token", "user_email_token_expires", "user_registration", "user_editcount", "user_password_expires") values ('28', 'SysopJ', 'User Name', ':B:9c5753aa:3b3ac9baf34afb0a67467bf7f038b590', '', NULL, 'e-mail@domain.org', '20150527205309', '2068c2567b61edf35199bdb2d395f229', '20111210155445', '85e61feb0a1ba4ee3b8e6253a5e300f7', '20111217024350', '20111210024350', '2806', NULL);''

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

insert into user values ('28', 'SysopJ', 'User Name', ':B:9c5753aa:3b3ac9baf34afb0a67467bf7f038b590', '', NULL, 'e-mail@domain.org', '20150527205309', '2068c2567b61edf35199bdb2d395f229', '20111210155445', '85e61feb0a1ba4ee3b8e6253a5e300f7', '20111217024350', '20111210024350', '2806', NULL);''

3.3. Выполняем команды в MySQL. Можно небольшими порциями, чтобы отслеживать результат или всё сразу, в веб интерфейсе phpMyAdmin или в консоли:

$ mysql -u UserName -p DBName < mysql_db.sql

где UserName и DBName лучше указать те же, что и в LocalSettings.php нового сайта.

Остальные таблицы переносятся по этому же алгоритму. Если при переносе некоторых таблиц возникли проблемы, то обратите внимание какие именно это таблицы: возможно данные в них и не нужны. Некоторые таблицы можно восстановить с помощью скриптов сопровождения, находящихся в каталоге maintenance. Например, recentchanges восстанавливается скриптом rebuildrecentchanges.php.

После окончания переноса не забываем проверить базовый функционал: регистрация пользователей, ограничение прав на редактирование (если было), загрузка файлов и общий вид страниц. Также можно просмотреть спец страницы: Версия MediaWiki (Служебная:Version), Статистика (Служебная:Statistics), Список участников (Служебная:ListUsers) и др. Если в вики использовались прямые URL ссылки на локальные ресурсы, а новый сайт располагается в другой папке, то это нужно учесть и либо исправить URL, либо перенести все такие файлы в прежнее место.

Если мы использовали стороннее программное обеспечение, кроме самого движка MediaWiki, например, поисковый движок Sphinx, то его тоже нужно не забыть перенастроить или даже переустановить, как в случае со Sphinx.

В заключении можно добавить ещё несколько слов для тех, кто использует

Сайты не MediaWiki

Если перед вами стоит схожая задача, но для другого движка, то суть решения и сложности с которыми столкнетесь остаются теми же. Поэтому предложенные здесь решения во многом вам помогут. Пожалуй, единственное отличие и основная трудность будет оставаться в переносе текста статей. Однако, скорее всего, у вашего движка также есть способы осуществлять перенос содержимого через промежуточные структуры путём создания и загрузки дампа базы данных. Первым делом нужно проверить такую возможность. Если этого нет, то можно написать небольшую программу с подключением к обоим СУБД. Так как SQLite и MySQL очень распространены, то думаю не будет ошибкой сказать, что во всех широко используемых языках программирования существуют библиотеки для работы с ними. По крайней мере для PHP, С, С#, Perl, Python, Java это так.

В случае с MediaWiki, как мы увидели, переход с СУБД SQLite на MySQL не такой уж сложный и долгий.

!	Основано на одноимённой статье, опубликованной в журнале "Системный Администратор" №10 (155) 2015г.