Бекапи MySQL бувають 2 основних різновидів це:
Логічний бекап
Створюється текстовий дамп з SQL-запитів, як у mysqldump або Sypex Dumper.
Фізичний бекап
Виконуються точні копії файлів таблиць, типовий представник mysqlhotcopy.
У процесі роботи над новою версією Sypex Dumper і Sypex Backuper, прийшов до ще одного цікавого варіанту гарячого бекапа MySQL. Який являє собою, щось середнє між двома цими варіантами.
Але для початку розглянемо основні переваги і недоліки. Хто замість теорії хоче відразу перейти до практики - внизу поста знайдете посилання на тестовий скрипт.
Логічний бекап
Переваги логічного бекапу:
- отриманий дамп може бути відновлений на будь-якій системі;
- бекап віддаленого MySQL-сервера;
- бекап будь-яких табличних движків (включаючи MEMORY);
- бекап створюється на працюючому сервері, не зупиняючи його роботу.
З основних недоліків:
- логічний бекап робиться значно повільніше фізичного, так як потрібно всі дані перетворити на людинопритульні SQL-запити;
- більший розмір файлу через текстовий формат бекапа.
Фізичний бекап
Переваги фізичного бекапу:
- максимальна швидкість бекапа, оскільки просто копіюються файли;
- невеликий розмір файла (оскільки використовується бінарний формат);
- можна бекапити лог-файли сервера.
З основних недоліків:
- бекап тільки локального сервера;
- можуть бути складнощі з перенесенням бекапа на іншу машину/систему.
- не можна створювати бекап таблиць MEMORY (оскільки немає фізичних файлів);
- не завжди можна відновити окремі таблиці (наприклад, таблиці InnoDB можуть зберігатися в одному файлі).
Sypex MySQL RAW бекап
При аналізі логічних способів бекапу було помічено, що основна втрата швидкості відбувається при отриманні пакетів даних від сервера, розборі їх і перетворенні до текстового формату. До того ж цей розбір зазвичай робиться libmysql або в разі нових PHP-версій mysqlnd, і призводить до додаткового оверхеду.
Тому вирішив спробувати позбутися зайвих перетворень, і написав тестовий скрипт який підключається безпосередньо до MySQL (за TCP або до UNIX-сокету) без використання стандартних MySQL-драйверів, використовуючи MySQL Client/Server Protocol. Скрипт зберігає у файл дані у вигляді бінарних пакетів отриманих від MySQL-сервера (ProtocolBinary::Resultset). Таким чином не витрачається час на розбір пакунків, полів, екранування даних. А розбір пакетів і формування SQL-запитів відбувається вже при відновленні бекапу.
У підсумку швидкість бекапа багаторазово збільшилася, залежно від структури таблиці. Також дампи досить компактні. Можна порівняти швидкість RAW бекапа, з бекапом за допомогою mysqldump і SELECT... INTO OUTFILE.
Декілька результатів виконання на стандартних таблицях форумів IPB та phpBB.
Основний недолік способу, природно, неможливість відновлення стандартними методами, тобто потрібен спеціальний скрипт для відновлення. Але в нашому випадку це не так важливо, оскільки в будь-якому випадку даний спосіб буде працювати зі спеціальним файлом-контейнером, що підтримує дедупликацію, інкрементальний бекап, шифрування та інші фішки.
Завантажити скрипт для тестування можна тут.
Скрипт є демонстратором технології, а не кінцевим продуктом.
Про результати відписуйтеся в коментах. Тільки врахуйте, що SELECT... INTO OUTFILE працює тільки на localhost, плюс у MySQL користувача повинні бути права доступу FILE і у каталогу backup повинні бути виставлені права доступу 777.
UPD. На прохання трудящих, ще кілька тестів з таблицями побільше:
Бекап однієї з таблиць wikipedia (categorylinks) близько 1,3 ГБ
Бекап таблиці GeoNames близько 1 ГБ
