Экстренное восстановление данных в таблице mysql с минимальной потерей времени

Доброго времени суток хабрачитатели. Совсем не давно на работе произошел инцидент который подпортил нервы и привел к длительным рассуждениям. Суть: при обновлении записи в БД mysql забыл прописать условие where и в результате изменились все записи в таблице.
Как этого можно было избежать:


  1. Всегда внимательно перечитывать запрос на рабочем сервере перед запуском (может спасти… но… может и не спасти)
  2. В конце обновляющих запросов всегда ставить limit 1, если не требуется обратное (Спасет, но дело привычки, к тому же все равно потом искать правленную строку).
  3. Использовать специальную утилиту типа phpmyadmin (пожалуй самое рационально, но не всегда доступное)
  4. Всегда делать дамп. (А это по идее обязательно даже при соблюдении остальных пунктов).

Вот если остался дамп, то мы спасены, и можем восстановить таблицу. Вот тут и начинается основная часть моей статьи- заметки.
Итак что у нас есть:

  1. Работающая база с неправильными данными
  2. Дамп правильной базы, без данных за определенный период (прошедший с момента создания дампа)
  3. Время в течении которого с базой продолжают работать, тем самым плодя ошибки и добавляя данные, которых нет в дампе.

Все омрачается еще и тем что база содержит внешние ключи, т.е. и ее id используются в других базах и она использует.
И вот наблюдая эту ситуацию я начинаю тихо впадать в истерику, ибо времени нет, а решение займет уйму времени, чтоб все корректно проделать и в итоге я могу потерять часть новых данных (это была таблица с заказами)
Но в итоге я собрался, подумал некоторое время и нашел решение, которое может помочь в подобных ситуациях.
Суть: Вместо перезаливки таблицы мы обновим ее значения используя старые данные из дампа.

Конкретно:

  1. Делаем копию дампа
  2. Редактируем копию дампа, удаляем данные о всех таблицах кроме нужной
  3. Заменяем все вхождения имени старой таблицы на новое (например temp_имя_старой таблицы)
  4. Загружаем исправленную копию дампа
  5. Выполняем запрос:
    update имя_старой_таблицы t1 join имя_новой таблицы t2 on (t1.id=t2.id) set t1.имя_поля_некорректными данными=t2.имя_этого_самого поля во второй таблице;
  6. Удаляем новую таблицу с измененным именем.

Итог:

Ключи не нарушены, старые данные восстановлены, новые данные если и повреждены, то имеют хотя бы часть информации, работа не была прервана, все выполнено довольно быстро.
Надеюсь эта история поможет кому-нибудь в подобной ситуации
Поделиться публикацией

Похожие публикации

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 11

    +4
    " Суть: при обновлении записи в БД mysql забыл прописать условие where и в результате изменились все записи в таблице."
    Суть: обновления и прочее обкатывать на тестовых серверах, тогда и проблем не будет таких.

    Решение:
    1) Закрываем временно систему на «тех. обслуживание».
    2) Разворачиваем бэкап.
    3) Отрываем систему.
      0
      Это была рядовая правка значения в заказе. Надо было скопировать запись заказа на тестовый сервер, там поправить, а потом скопировать обратно? Кстати, все равно update бы получился :-)

      2) Разворачиваем бэкап — нельзя, в базе новые заказы
      точнее можно скопировать текущую Таблицу, восстановить старую, перенести новые значения из старой, но опять же время.
      +1
      > Суть: при обновлении записи в БД mysql забыл прописать условие where и в результате изменились все записи в таблиц

      По-моему решение проблемы раскрывается в ее постановке — сначала внимательно напишите where, а потом допишите первую часть запроса.
        0
        Никто не застрахован ни от ошибок, ни от невнимательности.
          +1
          Вы ответили настолько общей фразой, что не понятно — одобрили или возразили :)
          Просто странно, что вы сами пишете «забыл where» из чего делаете выводы о том, что нужно проверять запрос, добавлять limit и использовать phpMyAdmin. Вот и удивляет, почему из ошибки «забыл where» не сделать вывод «писать where первично». :)

          А вообще в описанной вами ситуации плохо абсолютно всё, что вы упомянали.

          Более похожая на суровые промышленные реалии ситуация обновления данных должна выглядеть вот так:

          1. У вас должна быть копия продакшновских данных где-то в стороне. Для вас это вполне реализуемая ситуация, ведь вы предлагаете делать дамп перед запросом. Почему именно перед запросом? Настройте постоянно работающую репликацию на пару других серверов. Эти сервера кроме возможности проводить на одном из них эксперименты с данными, будут вам и источником свежайшего бекапа и подпоркой в случае падения основного сервера.

          2. Вы сначала пишете запрос с SELECT тех данных, которые хотите обновить. Запускаете его с EXPLAIN, чтобы оценить насколько данная выборка будет тяжелой для сервера, каковы вероятности залочить таблицы надолго. Исходя из результатов, оптимизируете.
          После оптимизации, запускаете запрос на селект на реплике (чтобы не мучать продакшн). И внимательно изучаете то, действительно ли это те данные, которые вам нужны. Если данных много, можно не каждую строку конечно сверять, а прикинуть кол-во записей, отдельные их признаки и пару тройку строк-таки проверить.

          3. По результатам пункта 2 вы пишете не запрос, а скрипт на любом удобном ЯП, желательно на том же, на котором работает проект. Почему именно скрипт?
          Потому что операция обновления данных слишком рискованная, вы это сами поняли, она должна быть выполнена аккуратно и с предварительными проверками. От апдейта строк одним запросом лучше отказаться. Лучше выполнить селект из пункта 2, потом пройтись по всем строкам в цикле и для каждой строки выполнить проверку на то, что она действительно должна быть обновлена. И если строка подходит — обновить. И, разумеется вся работа скрипта должна внутри транзакции. с BEGIN первым запросом и COMMIT последним. Перед последним COMMIT желательно также поместить проверку на адекватность данных. Для оптимальной работы транзакций без блокировки всей таблицы, у таблиц должен быть engine=InnoDb. MyISAM (стандартный движок) позволяет только лочить таблицу целиком.

          4. Полученный скрипт запустить на одной из реплик, проверить результат на адекватность.

          5. Если пункт 4 успешен, перейти к обновлению продакшна. При чем заметьте, подготовленный выше скрипт написан так, что чтобы его выполнить не нужно разбираться в структуре БД и выполнять какие-то дополнительные проверки. Этот скрипт может быть передан как часть нового релиза системы различным клиентам, которые используют ваш софт, или вашим коллегам, которые накатывают обновления.

        +1
        =)) ну и еще… можно несколько запросов в открытой транзакции в консоли делать… и SELECT'ом проверять что все правильно заапдейтили…
        а потом уже делать COMMIT или ROLLBACK ;)

        собственно говоря, теперь же вы просто будете внимательнее работать с консолью…
        все когда то делают свой первый «неправильный UPDATE» ;)
          +2
          Делайте правильно дампы:
          mysqldump --replace --set-charset --skip-add-drop-table --skip-disable-keys --skip-add-locks -uuser -ppasswd database_name | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/' > database.sql

          И тогда сможете накатывать свои дампы на работающую базу :)
          Но перед этим

          man mysqldump
            0
            --replace — нельзя, он удаляет, а затем добавляет строки, а в базе внешние ключи, которые потащат за собой другие таблицы.
            +3
            Правило №0 — не лезть руками или запросами в production базу.
              0
              >>2. Редактируем копию дампа, удаляем данные о всех таблицах кроме нужной

              Чисто практический интерес: пусть у меня есть дамп на 5-10 GB. Как с наименьшими затратами удалить из него все таблицы кроме нужной?
                0
                Ну как я понимаю, тут уже не идет речь об оперативном восстановлении данных и сайт точно надо закрывать на профилактику.
                Ну на вскидку я попробовал cat файл_дампа | grep -o -n ''Имя таблицы"
                На 3гб дампе:
                real 0m5.659s
                user 0m5.040s
                sys 0m2.212s
                А дальше vim со скриптами.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое