Как восстановить базу sql из файлов /var/lib/mysql/* на другом сервере mysql?

Question

[Антон Пащенко]

Задача сделать копию битой БД mysql.
При попытки создать mysqldump база выдаёт ошибку:
Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'locate'

Для создания копии этой базы, был остановлен сервер mysql и база была выкачана просто в виде папки с файлами таблиц из директивы /var/lib/mysql/
Теперь вопрос, как из файлов "холодного резерва" создать sql-файл или как эту базу развернуть на другом сервере mysql?

выкачать битую

Comments

[Дмитрий]

Попробуйте записать файлы которые скачали на другой сервер, кроме базы mysql.. Возможно придётся создавать сначала на сервере базу, потом подменять в папке файлы

[Антон Пащенко]

Дмитрий,
Делал так, путём слияния, с заменой файлов с разным размером, mysql не стартует((

[Дмитрий]

Антон Пащенко, там надо аккуратно, базу mysql не трогать, не заменять, там настройки, пользователи и прочее. Ставим чистую бд. Потом по одной таблице аккуратно подменять, например есть база site и таблица users, вот сначала на новой создаёте базу, и таблицу, потом подменяете файлы. По одной с проверкой старта. Занятие то ещё

[Антон Пащенко]

Дмитрий, Я пjнимю, что это решение, но таблиц почти 1000, это лютый головняк.

[Дмитрий]

Антон Пащенко, разберитесь с одной, будет проще понять как лечить

Answer 1

[Евгений Рес]

Здравствуйте!

Вижу, что вопрос был давно, но я столкнулся с этим сейчас.

Нашел следующее решение через докер:

docker run --name dump_mysql \
  -v "./mysql:/var/lib/mysql" \ # Отправляем локальную папку с настройками в контейнер
  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \    # Отключаем пароль
  -p 3306:3306 \ # Настраиваем порт
  -d mysql:5.7 --skip-grant-tables  # Пропускаем основные таблицы

# Затем запускаем команду
docker exec -i dump_mysql mysqldump -uroot -p some_bd > some_bd.sql

`some_bd` - ваша база, котрая лежит в локальной папке `./mysql`
`mysql:5.7` - важно, чтобы версия `mysql` билась с той на которой работала папка на сервере

Answer 2

[Drno]

Нужно сделать нормальный дамп.
Либо как сказали в коментах придется вручную вначале создавать все таблицы

Answer 3

[Борис Сёмов]

Вам нужно установить mysql такой же или выше версии на другом сервере, перенести файлы, удостовериться, что права выставлены правильно и запустить mysql.
Но это вряд-ли поможет вам "починить" базу. И скопируется не только одна база, но и все базы и пользователи.
Ну и снять дамп, точно также, не получится, как на исходном сервере.

Вам на самом деле, надо посмотреть где беда с collations и исправить её.

upd: На всякий случай: я пишу о копировании всей /var/lib/mysql/, не отдельной папки с базой.

Answer 4

[Akina]

При попытки создать mysqldump база выдаёт ошибку:
Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'locate'

Скорректируйте @@collation_connection.

Задача сделать копию битой БД mysql ... как из файлов "холодного резерва" создать sql-файл или как эту базу развернуть на другом сервере mysql?

А смысл? если подключить файлы данных в новом местоположении, ошибки никуда не денутся.

А так - создать таблицы в новом местоположении. Структура должна совпадать с точностью до запятых, и версия сервера тоже. Самое разумное - сделать бэкап чисто структуры, без данных, и развернуть. А потом DISCARD/IMPORT TABLESPACE, либо замена файлов при остановленном сервисе и последующий запуск с лечением и перезаписью метаданных и статистики.

Comments

[Антон Пащенко]

1. Копию делаю, чтобы на копии исправить ошибки, а затем повторить этот приём на "боевом" сервере.
2.

Скорректируйте @@collation_connection

вот тут можно подробнее?

[Akina]

Антон Пащенко,

вот тут можно подробнее?

mysqldump работает просто - тупо запрашивает у сервера сведения обо всех и вся, и конвертирует полученные ответы в воссоздающие SQL-запросы. То есть запрашивает список баз, список таблиц базы, список полей таблицы, и так далее...

Само собой погружаясь всё ниже по дереву зависимостей, для отбора использует те значения, что получены выше (например, при получении списка полей в качестве условия отбора указывается имя таблицы). Эти значения подставляются в посылаемые запросы как параметры, то есть как строковые литералы. А на сервере эти литералы сравниваются с полями соответствующих таблиц.

И вот тут - проблема. Любое значение обложено определёнными характеристиками. В том числе это charset (грубо - набор символов) и collation (грубо - набор правил сортировки и сравнения). одному charset может соответствовать куча разных collation. Но в сравнении используется два значения. В вышеописанном случае - переданный литерал и значение в таблице. Если у них разный charset - сравнение невозможно в принципе. А если charset одинаков, то имеем два разных collation. два разных набора правил. И не факт, что они совместимы.

Ошибка собственно об этом и говорит. Collation, определяемый литералом (а для литерала однозначно применяется collation текущего соединения), несовместим с collation, определяемым значением в таблице (берётся из свойств поля, если не задано - из свойств таблицы и т.п.). Для того, чтобы сделать их совместимыми, какой-то из них надо переопределить. И гораздо проще сделать это с collation соединения - благо этот параметр динамический, меняется "на лету" простым SET SESSION collation_connection := 'нужный collation';. А когда изменение сделает collation совместимыми, сравнение пройдёт нормально, и бэкап получится без проблем.

Подробнее см. тут: Character Sets, Collations, Unicode. Материалу, конечно, дохрена, но хотя бы один раз, хотя бы по диагонали, прочитать нужно.

[Антон Пащенко]

Немного не сходится у меня в голове.
Эту же команду

SET SESSION collation_connection := 'нужный collation';

вводить надо в самом mysql?
Если так как тогда воспользоваться mysqldump? Войти в mysql выполнить команду выйти и запустить дамп?

[Akina]

Эххх... mysqldump это самый обычный клиент. И, как и любой клиент, он получает кучу настроек из секции [client] текущего файла настроек сервера. Который можно переопределить опцией --defaults-file или дополнить опцией --defaults-extra-file.

[Антон Пащенко]

Ясно, значить буду делать дамп из интерфейса mysql.
Спасибо

[Антон Пащенко]

И, как и любой клиент, он получает кучу настроек из секции [client]

Получается мне в секции [client] файла /etc/my.cnf надо дописать:
сollation_connection = utf8_unicode_ci
верно?
У меня вот такие параметры битой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'sitemanager';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8                       | utf8_unicode_ci        |
+----------------------------+------------------------+

[Akina]

Да, всё верно. Такая установка должна убрать ЭТУ проблему.

Хотя я бы создал отдельный файл с такой установкой и подключил его через --defaults-extra-file.

[Антон Пащенко]

Akina,
типо так:

sudo mysqldump -u root -p'' --no-data --defaults-extra-file=/home/add_my.cnf -h localhost sitemanager > /home/bitrix/www/bitrix/backup\_structure.sql

и в /home/add_my.cnf:

[client]
сollation_connection = utf8_unicode_ci

[Akina]

Антон Пащенко, ну да, вроде всё правильно.

[Антон Пащенко]

Akina,
Не работает:

unknown variable 'сollation-connection=utf8_unicode_ci'.

и так пробовал:

unknown variable 'сollation_connection=utf8_unicode_ci'.