Author Archive

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

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 - кол-во записей в таблице Пример выполнения запроса: Я не буду выгружать таблицы, которые занимают более 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.

Допустим, у вас есть сервер на CentOS 5.5, на котором не установлен клиент MySQL, и вам нужно подключиться или снять бэкап с базы данных MySQL 5.5 на другом сервере, и в добавок ко всему на сервере нет доступа к интернету. Для начала нужно закачать на сервер установочный пакет клиента MySQL: MySQL-client-5.5.29-1.rhel5.x86_64.rpm Затем выполните команду:

sudo rpm -ivh MySQL-client-5.5.29-1.rhel5.x86_64.rpm 

Ключ -i означает установку пакета (install). Флаги v и h не являются обязательными, но нужны для включения вывода информации о ходе процесса установки пакета. Если у вас уже имеется старая версия пакета, вам нужно выполнить другую команду:

sudo rpm -Uvh MySQL-client-5.5.29-1.rhel5.x86_64.rpm 

Ключ -U означает обновление пакета (update). Последную команду можно использовать не только для обновления пакета, но и для установки. Другими словами, при наличии старой версии пакета, он будет обновлен, а при его отсутствии, пакет будет будет установлен. Если процесс установки прошел без ошибок, можно попробовать подключиться к удаленному MySQL:

mysql -h -u -p

host - адрес удаленного сервера user - пользователь password - пароль пользователя