Быстрый экспорт/импорт данных базы MySQL/MariaDB
Я до сих пор пользовался стандартным механизмом для экспорта/импорта данных между базами данных, условно он выглядит вот так: mysqldump | mysql. Первая команда генерит текстовое представление базы, все эти CREATE/INSERT-ы, а вторая команда строит и заполняет базу.
Работает mysqldump | mysql отлично, универсально, он гибко настраивается (хотите только схему или вам данные без процедур?), но работает не быстро, вставку в базу быстрее 70 тысяч строк в секунду я не видел, а значит импорт 1 млрд записей займёт часы, плюс время на экспорт, плюс на копирование текстовых SQL-файлов.
Нашёл более прогрессивный механизм экспорта/импорта данных без промежуточного SQL-файла! Это самый простой и логичный вариант - взять непосредственно файл с данными и перенести! На деле это означает:
а) ibd бинарный файл представляющий таблицу-данных,
б) файл cfg - описание таблицы данных,
скопировать файлы на destination хост и использовать в качестве таблицы-данных! Механизм можно использовать, если выполняется следующее условие - для базы включена опция file-per-table.
Итак, алгоритм следующий:
На source сервере (экспорт):
1) FLUSH TABLES tt FOR EXPORT; - выполняем для всех таблиц, которые экспортируем. При этом сервер создаст файлы cfg описания таблиц, а сами таблицы станут не доступны для чтения/записи.
2) Копируем файл tt.cfg, tt.ibd и все остальные из директории /var/lib/mysql/your_db/ на удалённый сервер в /temporary_dir/
3) UNLOCK TABLES; - при этом удалятся cfg файлы, а таблицы вернутся в работу (доступны для чтения/записи).
Вопрос как копировать файлы в пункте 2 может быть решён использованием shell-команд прямо из MySQL/MariaDB - команда \!.
Destination сервер (импорт):
1) Здесь у нас должны быть созданы все таблицы, которые мы будем импортировать.
2) ALTER TABLE tt DISCARD TABLESPACE; - делаем для всех таблиц, этим мы отвязываем таблицу от её файла-контейнера
3) Перемещаем соответствующие файлы из /temporary_dir/ в /var/lib/mysql/your_db/
4) Делаем chown mysql:mysql для всех этих файлов.
5) ALTER TABLE tt IMPORT TABLESPACE; - для всех таблиц, этим мы привязываем таблицы к данным на диске
Работает mysqldump | mysql отлично, универсально, он гибко настраивается (хотите только схему или вам данные без процедур?), но работает не быстро, вставку в базу быстрее 70 тысяч строк в секунду я не видел, а значит импорт 1 млрд записей займёт часы, плюс время на экспорт, плюс на копирование текстовых SQL-файлов.
Нашёл более прогрессивный механизм экспорта/импорта данных без промежуточного SQL-файла! Это самый простой и логичный вариант - взять непосредственно файл с данными и перенести! На деле это означает:
а) ibd бинарный файл представляющий таблицу-данных,
б) файл cfg - описание таблицы данных,
скопировать файлы на destination хост и использовать в качестве таблицы-данных! Механизм можно использовать, если выполняется следующее условие - для базы включена опция file-per-table.
Итак, алгоритм следующий:
На source сервере (экспорт):
1) FLUSH TABLES tt FOR EXPORT; - выполняем для всех таблиц, которые экспортируем. При этом сервер создаст файлы cfg описания таблиц, а сами таблицы станут не доступны для чтения/записи.
2) Копируем файл tt.cfg, tt.ibd и все остальные из директории /var/lib/mysql/your_db/ на удалённый сервер в /temporary_dir/
3) UNLOCK TABLES; - при этом удалятся cfg файлы, а таблицы вернутся в работу (доступны для чтения/записи).
Вопрос как копировать файлы в пункте 2 может быть решён использованием shell-команд прямо из MySQL/MariaDB - команда \!.
Destination сервер (импорт):
1) Здесь у нас должны быть созданы все таблицы, которые мы будем импортировать.
2) ALTER TABLE tt DISCARD TABLESPACE; - делаем для всех таблиц, этим мы отвязываем таблицу от её файла-контейнера
3) Перемещаем соответствующие файлы из /temporary_dir/ в /var/lib/mysql/your_db/
4) Делаем chown mysql:mysql для всех этих файлов.
5) ALTER TABLE tt IMPORT TABLESPACE; - для всех таблиц, этим мы привязываем таблицы к данным на диске