alter table без лока таблицы

Представим себе проект, который должен работать 24/7, проект, использующий базу данных mysql и типичную ситуацию, приходит разработчик и говорит: В табличке, в которой сейчас 300 млн строк, я забыл добавить поле и индекс по нему, а остановка работы прода с базой недопустима, придумай что-нибудь.

Если попробовать погуглить на тему, то в основном встречаются советы по созданию второй таблички, постепенному копирования туда данных («insert into T select * from S limit OFFSET») и затем переименовыванию таблиц, но подобный механизм имеет существенные недостатки, например данные в исходной таблице могут измениться, сразу после копирования, а т.к. залочить таблицу на запись нельзя, то целостность данных будет под вопросом.

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

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

Дано — база mysql на 2 серверах с мастер-мастер репликацией и внешний механизм (haproxy, конфиг приложения и т.п.), который показывает какой из инстансов mysql активен в коде. Т.е. мы имеем возможность переключать запросы с одного сервера на другой.
Реплика двухсторонняя:

сервер1<==>сервер2
сервер1 — логический мастер, т.е. он получает запросы с кода приложения.

1. Отключаем слейва на сервере1 (mysql> stop slave;). Т.к. запросы на сервера2 не идет, то отключение реплики в этом направлении не вызовет негативных последствий для целостности данных на обоих серверах.
2. На сервер2 выполняем alter, данный запрос залочит таблицу только на сервер2, но к нему сейчас прод не обращается, это не вызывает проблем, а значит это безопасно.
3. Следим за отставанием от мастера на сервер2, команда show slave status; обращаем внимание на Seconds_Behind_Master. Отставание вызвано тем, что запросы к залоченной таблице во время alter не проигрываются на слейве сервер2, они накапливаются в replay-log и после выполнения alter они проигрываются.
4. Далее мы получает на сервер1 устаревшую таблицу, а на сервер2 таблицу с новым, требуемым нам, форматом, нам надо переключить логического мастера на сервер2, переключаем.
5. выключаем на сервер1 лейва, например через mysqladmin start. что приводит к тому, что alter приходит с сервер2 на сервер1 и модифицирует там таблицу.
6. На всякий случай дожидаемся что сервер1 догнал реплику. Если требуется, опять переключаем логического мастера (например бывает хардкод снятия бэкапов с сервера2 и чтобы логического мастера бэкапы не тормозили надо его переключить).

А как подобные задачи решаете вы?
Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 12

      0
      Руки потянулись к клавиатуре писать pt-online-sсhe… Oh shi! уже написали :)
      0
      То ли я от жизни отстал и в самом MySQL (не percona и не mariadb) появился нормальная Master — Master репликация, то ли статья изначально не правильно называется.
        0
        На данный момент MySQL master-master хорош для горячего переключения на второй мастер в случае если основной ложится. А для одновременного использования есть нюансы с синхронизацией и т.п.
          0
          Т.е. по сути в чистом MySQL репликация master-master это когда На всех репликах запущен и мастер и слейв процессы?
            0
            Да, мастер-мастер по кругу вполне норм, если писать только в одного.
              0
              Да, и они синкаются между собой через релей и бинлоги. В интернете есть статьи и опыт использования.
                0
                Я не вижу тогда смысла называть это "Мастер-Мастер" репликацией, это скорее прокачанный Мастер-Слейв. Нормальная Мастер-Мастер репликация например у Cassandra там ты пишешь в любую ноду и оно разольет данные по всем нодам, при определенных настройках.
          0
          п.3 самый сумбурно описанный. А ведь где-то должно быть slave start, после сделанного stop slave
            0
            Это в пятом пункте, mysqladmin start как раз аналог slave start, это можно делать только после переключения мастера.
            0
            Ну это еще ниче. У меня чуваки зашардировали базу чтобы альтеры на мелких базах быстрее проходили :D Продвигаю вот тоже pt-online-schema-change, которую опробовал на других проектах :) А что с шардингом делать… пригодится наверное :D
              +2
              А мы просто используем PostgreSQL.

              Only users with full accounts can post comments. Log in, please.