Очень часто встречается ситуация, когда нужно иметь копию реальной базы данных на своем компьютере. Например, для тестирования программы на реальных данных, для поиска ошибок или просто иметь более-менее заполненные данными таблицы и т.д. Если в базе хранятся файлы, и они интенсивно добавляются в базу, то размер такой базы может быть очень большим. Чтобы сделать копию такой базы нужно время, свободное место, также затруднительно скачать такую базу удаленно через интернет. Как выход можно сделать урезанную копию — выгружать только таблицы, в которых не храняться файлы. При условии, конечно, что на данные таблиц не ссылаются другие таблицы.
Классический резервную копию делают следующей командой:
mysqldump -uroot -p your_database > dump.sql |
mysqldump -uroot -p your_database > dump.sql
Где -p — опция позволяет вводить пароль в скрытом виде,
your_database — название выгружаемой в дамп базы данных.
Также можно после наименования базы данных указать названия таблицы через пробел, которые должны быть выгружены, остальные таблицы не будет выгружены:
mysqldump -uroot -p your_database table1 table2 table3 > dump.sql |
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' |
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' |
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 |
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 |
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 — кол-во записей в таблице
Пример выполнения запроса:
Я не буду выгружать таблицы, которые занимают более 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,
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') |
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 |
mysqldump -uroot -p your_database таблицы_через_пробел > dump.sql
В данным случае, это позволило получить урезанную резервную копию на 7 гигабайт меньше оригинальной.
p.s. Есть один минус — при восстановлении резервной копии нужно создать таблицы, которые мы проигнорировали. В моем проекте это не проблема, т.к. используется ORM(Object-relational mapping), который сам восстановит недостающие таблицы.
Также будьте внимательны при выполнении запросов в различных sql-клиентах. Например, Squirrel SQL Client по умолчанию выведет в файл первый 100 названий таблиц, поэтому нужно снять флаг Limit rows.
