У меня есть несколько таблиц в MySQL 5.6, которые содержат большие двоичные данные в некоторых полях. Я хочу знать, могу ли я доверять дампам, созданным mysqldump
и быть уверенным, что эти двоичные поля не будут легко повреждены при передаче файлов дампов через такие системы, как FTP, SCP и тому подобное. Кроме того, я должен заставить такие системы обрабатывать файлы дампа как двоичные передачи вместо ascii?
Поддерживает ли mysqldump двоичные данные?
Ответ 1
Нет, это не всегда надежно, когда у вас есть бинарные капли. В этом случае вы должны использовать флаг - hex-blob, чтобы получить правильные результаты.
У меня есть случай, когда эти вызовы терпят неудачу (импортируются на другой сервер, но оба запускают Centos6/MariaDB 10):
mysqldump --single-transaction --routines --databases myalarm -uroot -p"PASSWORD" | gzip > /FILENAME.sql.gz
gunzip < FILENAME.sql.gz | mysql -p"PASSWORD" -uroot --comments
Он создает файл, который молча не импортирует. Добавление "-skip-extended-insert" дает мне файл, который намного легче отлаживать, и я обнаружил, что эта строка сгенерирована, но не может быть прочитана (но об ошибке не сообщается ни об экспорте, ни об импорте):
INSERT INTO `panels` VALUES (1003,1,257126,141,6562,1,88891,'??\\\?ŖeV???,NULL);
Обратите внимание, что конечная цитата в двоичных данных отсутствует в оригинале.
select hex(packet_key) from panels where id=1003;
--> DE77CF5C075CE002C596176556AAF9ED
Столбец представляет собой двоичные данные:
CREATE TABLE `panels` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`enabled` tinyint(1) NOT NULL DEFAULT '1',
`serial_number` int(10) unsigned NOT NULL,
`panel_types_id` int(11) NOT NULL,
`all_panels_id` int(11) NOT NULL,
`installers_id` int(11) DEFAULT NULL,
`users_id` int(11) DEFAULT NULL,
`packet_key` binary(16) NOT NULL,
`user_deleted` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
...
Итак, нет, не только вы не можете доверять mysqldump, вы даже не можете полагаться на него, чтобы сообщать об ошибке при возникновении.
Уродливое обходное решение, которое я использовал, состояло в том, чтобы mysqldump исключал обе страшные таблицы, добавив такие параметры в дамп:
--ignore-table=myalarm.panels
Затем этот BASH script взломать. В основном выполняйте SELECT, который производит значения INSERT, где обрабатываются столбцы NULL, а двоичный столбец превращается в вызов UNHEX(), например:
(123,45678,UNHEX("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"),"2014-03-17 00:00:00",NULL),
Вставьте его в свой редактор, чтобы играть с ним, если вам нужно.
echo "SET UNIQUE_CHECKS=0;SET FOREIGN_KEY_CHECKS=0;DELETE FROM panels;INSERT INTO panels VALUES " > all.sql
mysql -uroot -p"PASSWORD" databasename -e "SELECT CONCAT('(',id,',', enabled,',', serial_number,',', panel_types_id,',', all_panels_id,',', IFNULL(CONVERT(installers_id,CHAR(20)),'NULL'),',', IFNULL(CONVERT(users_id,CHAR(20)),'NULL'), ',UNHEX(\"',HEX(packet_key),'\"),', IF(ISNULL(user_deleted),'NULL',CONCAT('\"', user_deleted,'\"')),'),') FROM panels" >> all.sql
echo "SET UNIQUE_CHECKS=1;SET FOREIGN_KEY_CHECKS=1;" > all.sql
Это дает мне файл под названием "all.sql", который нуждается в последней запятой в INSERT, превращенном в точку с запятой, тогда ее можно запустить, как указано выше. Мне нужны "большие буферы импорта", установленные как в интерактивной оболочке mysql, так и в командной строке для обработки этого файла, потому что он большой.
mysql ... --max_allowed_packet=1GB
Когда я сообщил об ошибке, я в конце концов указал на флаг "-xx-blob", который делает то же самое, что и мой обходной путь, но в тривиальном виде с моего бокового пути. Добавьте эту опцию, blobs будут сбрасываться как hex, end.
Ответ 2
Дампы, созданные из mysqldump
, можно доверять.
Чтобы избежать проблем с кодировками, двоичными передачами и т.д., используйте параметр --hex-blob
, поэтому он переводит каждый байт в шестнадцатеричном номере (например, "abc" становится 0x616263). Это сделает дамп больше, но он будет самым совместимым и безопасным способом получения информации (так как это будет чистый текст, не странные неправильные интерпретации из-за специальных символов, сгенерированных с двоичными данными в текстовом файле).
Вы можете обеспечить целостность (и ускорить передачу) файлов дампа, упаковывающих его в rar или zip файл. Таким образом, вы можете легко обнаружить, что он не был поврежден передачей.
Когда вы пытаетесь загрузить его на свой сервер, проверьте, что вы установили в своем конфигурационном файле сервера my.cnf
[mysqld]
max_allowed_packet=600M
или больше, если необходимо.
Кстати, я просто выполнил миграцию и сбрасывал множество двоичных данных с помощью mysqldump
, и он отлично работал.
Ответ 3
Да, вы можете доверять дампам, сгенерированным mysqldump
.
Да, вы должны использовать двоичную передачу, чтобы избежать преобразования кодировки во время передачи. Дамп MySQL добавляет команды управления дампу, чтобы сервер интерпретировал файл в конкретной кодировке при реимпортации. Вы не хотите изменять эту кодировку.