Сжатие данные в MySQL

В БД MySQL есть особенность — при удалении данных из таблиц файловое пространство не освобождается. Есть два способа сжать эти данные:

• Выполнить команду OPTIMIZE TABLE:

  % Для таблицы cdr OPTIMIZE TABLE cdr; % Для таблицы tolltickets OPTIMIZE TABLE tolltickets;

  Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных, можно воспользоваться командой OPTIMIZE TABLE.

  В данном способе есть особенность: при модификации таблицы MySQL делает копию таблицы с новыми данными (те, что реально есть) в разделе /var/lib/mysql. В пределе может потребоваться данных столько же, сколько и в таблице до сжатия. Надо убедиться заранее, что свободного места будет достаточно. Данная операция может занять длительное время (например таблица tolltickets с 50 млн записями конвертируется около 3 часов), на протяжении этого времени таблица не доступна на запись (запросы выполняются после миграции данных).
  Поэтому для кластерной версии лучше выполнять изменении таблицы на slave хосте (тот хост, на котором НЕ поднят виртуальный IP для MySQL). Когда миграция завершится и автоматически запустится на втором хосте, необходимо погасить виртуальный IP на master хосте, чтобы он переместился на slave).

• Сделать dump данных в отдельный файл, затем сделать truncate table. После заново импортировать данные.
  

  Преимущество данного способа в том, что dump таблицы можно сделать в произвольном месте на хосте (по умолчанию можно сделать dump только в папку /var/lib/mysql-files), таким образом на разделе с MySQL не обязательно иметь запас свободной памяти.

  Вариант 1:

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

  % Экспорт таблицы tolltickets SELECT * FROM tolltickets INTO OUTFILE '/var/lib/mysql-files/tolltickets.dump' CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; % Полная отчистка таблицы tolltickets TRUNCATE TABLE tolltickets; % Импорт данных из файла LOAD DATA INFILE "/var/lib/mysql-files/tolltickets.dump" IGNORE INTO TABLE tolltickets CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

  Необходимо иметь в виду два момента:

  • После того, как выполнена операция TRUNCATE TABLE, в кластерной версии данные удалятся на обоих хостах; LOAD DATA INFILE — данные загрузятся на оба хоста;
  • Если между SELECT и TRUNCATE будут добавлены новые данные, то эти строки будут потеряны.

  Вариант 2 (только если MySQL в кластере):

  Если нет возможность снять нагрузку с системы и данные нельзя потерять. В этом случае процесс сжатия таблицы будет более сложным. Далее расписаны этапы на примере таблицы tolltickets. Считаем что виртуальный IP для MySQL находится на ecss1:

  1. Убедиться что на обоих хостах репликация работает (ecss1, ecss2):

     show slave status \G;

     Seconds_Behind_Master = 0, Slave_IO_Running: Yes, Slave_SQL_Running: Yes.

  2. Остановить репликацию (ecss1, ecss2)

     stop slave;

     
     

  3. Сделать backup данных с ecss2 в файл (ecss2):

     % Экспорт таблицы tolltickets SELECT * FROM tolltickets INTO OUTFILE '/var/lib/mysql-files/tolltickets.dump' CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

     Если на данном этапе нужны не все данные (например записи, начиная с определенной даты), то в запросе SELECT ... INTO OUTFILE можно добавить условие WHERE.

     Если backup делается в нестандартную папку (/var/lib/mysql-files/) то доступ mysql надо разрешить в apparmor: /var/lib/ecss/restfs/backup/*.* rw, 

  4. Выяснить текущее положение master-а ecss2 (ecss2):

     show master status \G; *************************** 1. row *************************** File: server-mysql-bin.002123 Position: 532 Binlog_Do_DB: Binlog_Ignore_DB: information_schema,mysql,performance_schema Executed_Gtid_Set: 1 row in set (0.00 sec)

     Тут необходимо запомнить параметры File, Position. Они потребуются далее.

  5. Провести полную очистку таблицы tolltickets на ecss2 (ecss2):

     TRUNCATE TABLE tolltickets;

     
     

  6. Импортировать данные из файла (ecss2):

     LOAD DATA INFILE "/var/lib/mysql-files/tolltickets.dump" IGNORE INTO TABLE tolltickets CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

     На данном этапе на ecss2 есть отчищенная таблица tolltickets с данными на момент создания dump. Запустить slave на ecss2, чтобы он перенес новые записи, которые появились с момента остановки slave в таблицу tolltickets.

  7. Запустить slave на ecss2 (ecss2):

     start slave;

     
     

  8. Дождаться, когда на ecss2 перенесутся все данные на ecss1 (ecss2):

     show slave status \G;

     Дождаться, когда параметр Seconds_Behind_Master станет равен нулю.

  9. Перенести виртуальные IP для MySQL с ecss1 на ecss2 (ecss2).
     Для этого на ecss2 в настройках keepalived (/etc/keepalived/keepalived.conf) в секции "vrrp_instance VI53" поднять приоритет (по умолчанию на ecss2 он равен 50, выставляем 101), и перезапустить keepalived.

     sudo systemctl restart keepalived

     
     

  10. Дождаться, когда сетевой интерфейс переедет на ecss2 (ecss2).

      После того, как IP переехал, подождать ещё 1 мин, чтобы все записи на ecss1 завершились.

  11. Проверить статус slave на ecss2 (ecss2):

      show slave status \G;

      Дождаться, когда параметр Seconds_Behind_Master станет равен нулю.

  12. Остановить slave на ecss2 (ecss2):

      stop slave;

      
      

  13. Полностью отчистить таблицы tolltickets на ecss1 (ecss1):

      TRUNCATE TABLE tolltickets;

      
      

  14. Выставить положение slave на ecss1 в позицию, полученную на этапе 5 (ecss1):

      CHANGE MASTER TO MASTER_LOG_FILE='server-mysql-bin.002123', MASTER_LOG_POS=532;

      
      

  15. Запустить slave на ecss1 (ecss1):

      start slave;

      
      

  16. Дождаться, когда на ecss1 перенесутся все данные с ecss2 (ecss1):

      show slave status \G;

      Дождаться, когда параметр Seconds_Behind_Master станет равен нулю. Эта операция может занять продолжительно время, т.к. надо перенести все данные из таблицы tolltickets с ecss2.

  17. Узнать текущее положение master ecss1 (ecss1):

      show master status \G; *************************** 1. row *************************** File: server-mysql-bin.000087 Position: 142612 Binlog_Do_DB: Binlog_Ignore_DB: information_schema,mysql,performance_schema Executed_Gtid_Set: 1 row in set (0.00 sec)

      Необходимо запомнить параметры File, Position. Они потребуются далее.

  18. Выставить положение slave на ecss2 в позицию, полученную на этапе 18 (ecss2):

      CHANGE MASTER TO MASTER_LOG_FILE='server-mysql-bin.000087', MASTER_LOG_POS=142612;

      
      

  19. Запустить slave на ecss2:

      start slave;

      
      

  20. Убедиться, что на обоих хостах репликация работает (ecss1, ecss2):

      show slave status \G;

      Seconds_Behind_Master = 0, Slave_IO_Running: Yes, Slave_SQL_Running: Yes.

  21. Восстановить настройки keepalived на ecss2 и перезапустить его (ecss2).

      Во время импорта данных из файла и переноса проимпортированных данных с одного хоста на другой MySQL кроме своего раздела (/var/lib/mysql) так же использует дисковое пространство папки /tmp. Поэтому во время длительных операций необходимо смотреть, чтобы на разделе с папкой /tmp так же было свободное место.