Создание урезанной копии базы данных MySQL

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

Классический резервную копию делают следующей командой:

mysqldump -uroot -p your_database > dump.sql

Где -p — опция позволяет вводить пароль в скрытом виде,
your_database — название выгружаемой в дамп базы данных.

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

mysqldump -uroot -p your_database table1 table2 table3 > dump.sql

В данном случае, из базы данных your_database будут выгружены таблицы (со структурой и данными): table1, table2 и table3. Остальные таблицы не будет выгружены, даже структура.

Для начала получим список всех таблиц в базе данных:

SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database'

Где information_schema.tables — системная таблица, содержащая информацию о таблицах,
your_database — название вашей базы данных

Полученные данные имеют вид стобца, нам же нужны список названий с пробелом в качестве разделителя.
Для этого выполните следующий запрос:

SELECT TABLE_NAME INTO OUTFILE 'c:\\tables.txt' 
LINES TERMINATED BY ' '
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database'

Данный запрос позволяет выгрузить данные в файл c:\tables.txt. Из-за экранирования вместо разделителя пути «\» нужно использовать «\\» (Для linux-систем разделителем является слэш «/», который не экранируется, т.е. путь может быть выглядеть следующим образом /tmp/tables.txt. )
Причем, если файл c:\tables.txt уже существует выйдет ошибка «Error: File ‘c:\tables.txt’ already exists». Нужно удалить этот файл или переименовать название файла для выгрузки и выполнить запрос заново.
Строка LINES TERMINATED BY ‘ ‘ говорит о том, что разделитель строк теперь пробел. Это позволяет значения из столбца записать в виде строки.

Теперь полученный список названий таблиц через пробел можно подставить в запрос:

mysqldump -uroot -p your_database таблицы_через_пробел > dump.sql

Но нам нужны ведь не все таблицы, поэтому вы можете из запроса удалить ненужные таблицы.

Теперь о том, как найти таблицы, которые будем игнорировать. Отсеивать будем по размеру и наименованию таблиц.
Для начала выведем список таблиц, отсортированный по размеру таблиц, используем всё ту же таблицу information_schema.tables.

SELECT
    table_name,    
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema='your_database'
ORDER BY total_size_mb desc

Где table_name — название таблицы,
total_size_mb — размер таблицы в мегабайтах,
table_rows — кол-во записей в таблице
Пример выполнения запроса:
sizes
Я не буду выгружать таблицы, которые занимают более 10 мегабайт и в названии которых присутствует слово «ATTACH». В таблицах ATTACH храняться файлы, поэтому они занимают много места.
Следующий запрос позволяет получить список таблиц, которые не будем выгружать.

SELECT
    table_name,    
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema='your_database' AND ROUND(data_length/1024/1024,2) > 10 AND TABLE_NAME LIKE '%attach%'
ORDER BY total_size_mb desc

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

SELECT TABLE_NAME INTO OUTFILE 'c:\\tables.txt' 
LINES TERMINATED BY ' '
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME NOT IN ('ignore_table_1','ignore_table_2','ignore_table_3')

Данный запрос выведет названия таблиц через пробел в файл, но не будет исключенных таблиц: ignore_table_1, ignore_table_2 и ignore_table_3.

Остается подставить полученный список таблиц в команду резервного копирования:

mysqldump -uroot -p your_database таблицы_через_пробел > dump.sql

В данным случае, это позволило получить урезанную резервную копию на 7 гигабайт меньше оригинальной.

p.s. Есть один минус — при восстановлении резервной копии нужно создать таблицы, которые мы проигнорировали. В моем проекте это не проблема, т.к. используется ORM(Object-relational mapping), который сам восстановит недостающие таблицы.
Также будьте внимательны при выполнении запросов в различных sql-клиентах. Например, Squirrel SQL Client по умолчанию выведет в файл первый 100 названий таблиц, поэтому нужно снять флаг Limit rows.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.