Форум пользователей MySQL

klow · 15.03.2018 17:29:38

Добрый день!
Как правильно сделать полный бекап для переноса данных с сервера MySql 5.5 на другой сервер MariaDB 10.2.13
Планирую так
mysqldump.exe -u backup -p***** -A -R -E --triggers --single-transaction > full_backup.sql
Я так понимаю, что пользователи тоже будут перенесены?

Отредактированно klow (15.03.2018 17:43:57)

paulus · 16.03.2018 12:11:10

Да, -A бэкапит все базы, включая базу mysql, в которой живут пользователи.

При переносе я бы, наверное, просто скопировал данные бинарно (innobackupex или просто остановить-скопировать, если есть возможность): это сильно быстрее для больших баз, чем перегонять через текст.

klow · 16.03.2018 12:13:13

Нужно через текст для уменьшения бинарного файла. Он и так уже сильно вырос.

klow · 17.03.2018 11:08:14

и не получилось

sql написал:
ERROR 1911 (HY000) at line 8097: Unknown option 'date_format'

То есть, нужно исключить MySql из бекапа. А как перенести пользователей?

Отредактированно klow (17.03.2018 11:54:20)

vasya · 17.03.2018 13:59:31

странно, дока говорит что date_format в марии есть и не используется также как и в mysql 5.5
Похоже на багу - возможно в марии 10.2 уже удалили эту переменную (как планируют сделать в mysql 8), но в доке не отобразили.

Что в этой строке? Можно закомментировать?

klow · 17.03.2018 14:56:19

значение
date_format = %Y-%m-%d
есть еще
time_format = %H:%i:%s
datetime_format = %Y-%m-%d %H:%i:%s
Переменные не используются, но они есть. Вот поэтому меня смущает полный перенос ДБ MySql/
По поводу закомментировать - не понял.

Отредактированно klow (17.03.2018 15:03:32)

klow · 17.03.2018 15:18:41

мда. Оказывается к переменным это не имеет отношение.
ругается на подключаемую таблицу

DROP TABLE IF EXISTS `iis_log`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `iis_log` (

  `date` datetime NOT NULL `date_format`='YYYY-MM-DD hh:mm:ss',

  `ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,

  `username` varchar(250) COLLATE utf8_unicode_ci NOT NULL,

  `method` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

  `uri-stem` varchar(250) COLLATE utf8_unicode_ci NOT NULL,

  `uri-query` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,

  `status` int(11) NOT NULL,

  `sc-bytes` int(11) NOT NULL,

  `cs-bytes` int(11) NOT NULL,

  `host` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `Referer` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL

) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `TABLE_TYPE`=CSV `FILE_NAME`='c:\\inetpub\\logs\\LogFiles\\W3SVC1\\Output.csv' `HEADER`=1 `SEP_CHAR`=',' `QUOTED`=1;

/*!40101 SET character_set_client = @saved_cs_client */;

И действительно, похоже на баг.

А вот код самой таблицы:

CREATE TABLE `iis_log` (

    `date` DATETIME NOT NULL,

    `ip` VARCHAR(15) NOT NULL COLLATE 'utf8_unicode_ci',

    `username` VARCHAR(250) NOT NULL COLLATE 'utf8_unicode_ci',

    `method` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',

    `uri-stem` VARCHAR(250) NOT NULL COLLATE 'utf8_unicode_ci',

    `uri-query` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',

    `status` INT(11) NOT NULL,

    `sc-bytes` INT(11) NOT NULL,

    `cs-bytes` INT(11) NOT NULL,

    `host` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',

    `Referer` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci'

)

COLLATE='utf8_unicode_ci'

ENGINE=CONNECT;

Отредактированно klow (17.03.2018 15:24:06)

klow · 17.03.2018 15:34:24

действительно баг
Если убрать кавычки то отрабатывает без ошибок

`date` datetime NOT NULL date_format='YYYY-MM-DD hh:mm:ss'

klow · 18.03.2018 11:16:31

Еще проблему обнаружил. Пользователи перенеслись, но пароли недействительные.
Это я что-то не так делаю или баг?

klow · 18.03.2018 13:06:13

так, странности продолжаются.
Если попытаться выполнить команду

SET PASSWORD FOR 'backup'@'%' = PASSWORD('1234');

Получаю ошибку

SQL Ошибка (1133): Can't find any matching row in the user table

Но если попытаться изменить пароль через интерфейс HeidiSQL то это срабатывает и после все остальные пользователи нормально могут работать, то есть, их пароли становятся рабочими. Запуск

SET PASSWORD FOR 'backup'@'%' = PASSWORD('1234');

уже отрабатывает без ошибки.
В логах HeidiSQL к северу идет обычная команда

SET PASSWORD FOR 'backup'@'%' = PASSWORD('1234');

Ничего не понимаю.
Чем дальше в лес, тем больше дров.

ЗЫ
В качестве теста переношу (backup, restore) только БД MySQL

Отредактированно klow (18.03.2018 13:32:27)

paulus · 19.03.2018 14:52:32

Для бэкапа при mysqldump можно поиграть с ключом --compatible, он автоматически вырежет все дополнительные свойства полей типа datetime_format. Что касается паролей, то Мария может хранить их в другом формате, поэтому бинарный перенос может не полечить, это правда. Как вариант — можно не дампить базу mysql вообще, снять гранты через pt-show-grants и накатить их текстом на целевую машинку.

Правильным «каноническим» способом я всё еще считаю запуск нового кода над старыми данными, после этого можно сделать mysqldump / mysql для уменьшения размера и преобразования форматов (в рамках одной версии кода все ключи должны быть совместимы).

klow · 19.03.2018 15:41:49

paulus написал:
Для бэкапа при mysqldump можно поиграть с ключом --compatible, он автоматически вырежет все дополнительные свойства полей типа datetime_format.

Вырезать datetime_format не нужно - без него работать не будет.
В данном случае это явный баг. Кавычек в datetime_format не должно быть.
По поводу паролей, похоже тоже на баг, он он проявляется только если установить чистую MariaDB и залить БД MySql.

rgbeast · 19.03.2018 16:21:11

Баг с кавычками стоит на багтрекер запостить, если существует простая возможность его воспроизвести.

klow · 19.03.2018 16:22:13

уже запостил. Воспроизводится легко

klow · 21.03.2018 15:18:19

По поводу ошибки с пользователями. Кто может протестировать?
Воспроизведение ошибки:
1. Делаем бекап БД MySql.
Внимание!
Должны быть пользователи отличные от root.
Достаточно только базы mysql, например: mysqldump.exe --user=root -p MySQL > MySql.sql
2. Устанавливаем MySql (MariaDB) на "чистую" машину (на другой сервер или предварительно сносим MySql)!
3. Заливаем бекап из п.1.
4. Проверяем возможность зайти пользователем отличным от root или изменить пользователя

SET PASSWORD FOR 'test'@'%' = PASSWORD ('test');

Отредактированно klow (21.03.2018 15:44:43)

paulus · 22.03.2018 10:36:11

Попробовал — у MariaDB 10 структура таблицы user отлична от структуры MySQL 5.7, поэтому просто так пользователей заливать нельзя, придется воспользоваться pt-show-grants таки.

Если восстанавливать текстом, то получится вот так:
180322 7:30:33 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

Если восстанавливать бинарно, то получится вот так:
180322 7:31:47 [ERROR] Unknown/unsupported storage engine: InnoDB

Всё-таки, системные таблицы — это системные таблицы. И плохо, что разломали бинарную совместимость, отстреливают себе ноги

klow · 22.03.2018 10:40:56

Спасибо за инфу. Но в данном случае я все делаю на одной версии MariaDB 10.2.13. Ошибка у меня легко воспроизводится. Уже передал баг разработчику. Но интересно, это характерно только для MariaDB или для MySql тоже? А может этот баг проявляться только у меня?

Форум пользователей MySQL

#1 15.03.2018 17:29:38

Backup

#2 16.03.2018 12:11:10

Re: Backup

#3 16.03.2018 12:13:13

Re: Backup

#4 17.03.2018 11:08:14

Re: Backup

sql написал:

#5 17.03.2018 13:59:31

Re: Backup

#6 17.03.2018 14:56:19

Re: Backup

#7 17.03.2018 15:18:41

Re: Backup

#8 17.03.2018 15:34:24

Re: Backup

#9 18.03.2018 11:16:31

Re: Backup

#10 18.03.2018 13:06:13

Re: Backup

#11 19.03.2018 14:52:32

Re: Backup

#12 19.03.2018 15:41:49

Re: Backup

paulus написал:

#13 19.03.2018 16:21:11

Re: Backup

#14 19.03.2018 16:22:13

Re: Backup

#15 21.03.2018 15:18:19

Re: Backup

#16 22.03.2018 10:36:11

Re: Backup

#17 22.03.2018 10:40:56

Re: Backup

Board footer