Сжатие данные в 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 так же было свободное место.