Как ускорить Mysqldump?

Возможно, вы пробовали дампить более-менее большую таблицу. Если да — вы знаете, что это очень медленный процесс. К тому же, это
блокирует таблицы, что влияет на работающие БД.

Если же дамп после этого загружать в новую базу данных, выйдет еще медленнее.

Оптимизация настроек Mysqldump

По умолчанию, Mysqldump будет использовать блокировку таблиц во время сохранения дампа. Поэтому для снижения влияния на работающее приложение, стоит использовать такой ключ:

mysqldump --single-transaction database > dump.sql

# Отключение блокировки таблицы будет работать только для InnoDB

Кроме этого обязательно проверьте настройку innodb_flush_log_at_trx_commit. Она не повлияет на скорость работы дампа, зато повлияет на загрузку данных из дампа:

innodb_flush_log_at_trx_commit=2

# Изменение этой настройки может значительно ускорить загрузку данных с дампа

Mysqlimport и “SELECT INTO OUTFILE”

В качестве альтернативного решения, можно использовать экспорт данных в файл:

SELECT * INTO OUTFILE '/tmp/users.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users;

# Выгружаем данные в CSV файл из таблиц test

И сделать дамп только структуры таблицы:

mysqldump --no-data > dump.sql

Теперь можно перенести эту таблицу на другую БД. Сначала создадим ее структуру:

mysql new_database < dump.sql

Теперь загрузим данные из CSV файла в эту таблицу:

mysqlimport --local --fields-enclosed-by '"' --fields-terminated-by '\n' new_database /tmp/users.txt

# Загрузит данные в таблицу с названием users

Использование Xtrabackup

Xtrabackup стоит использовать для бекапов работающих Mysql серверов. Но этот инструмент также может пригодиться чтобы двигать (очень большие) таблицы между серверами. Устройство InnoDB позволяет перенести физические файлы таблиц ibd (подготовленные с помощью Xtrabackup) на другой сервер и включить их прямо на работающем сервере.

Что нужно сделать:

1. Настроить сервер назначения

На сервере, на который вы собираетесь переносить таблицу, необходимо добавить такую настройку в my.cnf:

innodb_import_table_from_xtrabackup = 1
innodb_file_per_table = 1

2. Сделать бекап нужных таблиц

Xtrabackup позволяет передать условие – для каких именно таблиц нужно сделать бекап.

innobackupex --include='^database[.]users' /root/backup

# Забекапит только таблицу users из БД database

Применим лог операций для финализирования файлов:

innobackupex --apply-log --export /root/backup/*

3. Делаем дамп структуры таблицы

Нужно для того, чтобы создать пустую таблицу на новом сервере:

mysqldump database users --no-data > dump.sql

4. Создаем таблицу на новом сервере

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

mysql new_database < dump.sql

5. Копируем и заменяем файл данных таблицы

После того, как таблица создана, необходимо остановить работу движка на новом сервере:

ALTER TABLE new_database.users DISCARD TABLESPACE;

6. Заменяем файлы данных

Нужно скопировать два файла – users.ibd и users.exp из папки с бекапом в папку данных mysql:

cd /root/backup/*/database/users/
cp users.ibd users.exp /var/lib/mysql/new_database/
chown mysql:mysql /var/lib/mysql/new_database/users*

# Не забываем поменять права на файл

7. Запускаем движок

Осталось запустить движок с новыми файлами данных.

ALTER TABLE new_database.users IMPORT TABLESPACE;

После этого таблица с данными будет доступна на новом сервере:

SELECT count(*) FROM new_database.users

+----------+
| count(*) |
+----------+
|     1554 |
+----------+

Возможные проблемы

Проблема возникнет если вы используете репликацию на сервере, на который переносится таблица. Слейв поломается и его придется восстановить заново с мастера.