Очень часто встречается ситуация, когда нужно иметь копию реальной базы данных на своем компьютере. Например, для тестирования программы на реальных данных, для поиска ошибок или просто иметь более-менее заполненные данными таблицы и т.д. Если в базе хранятся файлы, и они интенсивно добавляются в базу, то размер такой базы может быть очень большим. Чтобы сделать копию такой базы нужно время, свободное место, также затруднительно скачать такую базу удаленно через интернет. Как выход можно сделать урезанную копию - выгружать только таблицы, в которых не храняться файлы. При условии, конечно, что на данные таблиц не ссылаются другие таблицы.
Классический резервную копию делают следующей командой:
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.