Все привыкли использовать восстановление бд следующим способом (отсутствие пробела после -p очень важно в данном случае):
mysql -h $(DB_HOST) -u $(DB_USER) -p$(DB_PASS) $(DB_NAME) > $(FILE_PATH) < C:\my\backup.sql
Описание некоторых опций:
-f, --force - продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка
--database=$(DB_NAME) указывает имя базы данных, в которую восстанавливаются данные (если не указать, то в файле backup.sql должна находится SQL-инструкция USE $(DB_NAME)
--default-character-set=utf8 набор символов (если не ошибаюсь, то по-умолчанию latin1)
Но, есть более быстрый вариант восстановления бд - написать backup-script.sql и для начала короткий, рабочий вариант, а ниже него описание:
SET NAMES utf8;
DROP DATABASE `mydb`;
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
USE `mydb`;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SOURCE /path/to/db_dump.sql;
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;SET NAMES utf8; говорит, что обмен данными будет происходить в кодировке UTF-8 и именно в ней находится исходный файл дампа базы и именно её будет иметь конечная база данных.
DROP DATABASE `mydb`; и CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; выполняют уничтожение базы данных и её последующее создание с кодировкой по умолчанию UTF-8. Понятное дело, что это сотрет все таблицы и данные в них, но это и даст нам уверенность в том, что полученная БД будет полностью соответствовать данным в файле дампа.
USE `mydb`; указывает, что надо использовать именно эту базу данных для всех команд из файла дампа (проверьте файл дампа на наличие этой директивы с указанием другой базы, дабы избежать конфуза).
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; отключает увеличение счетчика авто-инкремента при записи в поле авто-инкремента значения 0. По умолчанию, при записи в такое поле значения NULL или 0 оно автоматически инкрементируется. И хотя иметь значение 0 в авто-инкрементном поле – не лучшая практика, иногда все-таки случается что в дампе есть такие строки (я встретился с подобным в дампе от magento). Стоит также заменить, что mysqldump с некоторых пор включает данную директиву по умолчанию в своих дампах.
SET autocommit=0; перед заливкой дампа и COMMIT; после него - ускоряют выполнение INSERT'ов для InnoDB таблиц, поскольку при включенном режиме авто-коммита логи сохраняются на диск при каждом INSERT'е. Может помочь при накатывании больших дампов.
SET foreign_key_checks=0; наиболее очевидная опция, без которой большая часть дампов вообще не могла бы быть применена. Отключает проверки внешних ключей, что позволяет записывать данные в любом порядке и в том числе с циклическими ссылками по внешним ключам. Эта опция также ускоряет импорт дампа.
SET unique_checks=0; отключает проверки на уникальность для вторичных ключей, что опять таки может ускорить процесс импорта.
Ну и наконец, основная команда, ради которой все это было задумано – SOURCE /path/to/db_dump.sql; непосредственно импортирует дамп базы mysql из указанного файла. Путь до файла может быть абсолютными, либо относительным от той директории, откуда вы зашли в консоль mysql.
А после выполнения импорта – восстанавливаем значение флагов, хотя этого можно и не делать, ибо команда SET в mysql по умолчанию изменяет значения переменных только в контексте текущей сессии.
Для ускорения импорта больших дампов MyISAM-таблиц в файле my.cnf советуют применять следующие настройки:
[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M
не забудьте, после как прописали настройки, нужно перезапустить MySQL.
Что ж это за настройки такие:
delay_key_write - Данная переменная влияет на способ обработки ключевого слова DELAY_KEY_WRITE, оператора CREATE TABLES. Касается только таблиц MyISAM. Если при создании таблицы использовалось ключевое слово DELAY_KEY_WRITE, очистка буфера ключей производится не при каждом обновлении индекса, а только при закрытии таблицы, что позволяет увеличить скорость выполнения запросов.
max_allowed_packet - Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.
bulk_insert_buffer_size - В MySQL существует синтаксис, позволяющий делать добавление множества записей одним SQL-запросом.
Т.е. например, вместо n запросов
insert into table_name (id, val) values(1,1);
insert into table_name (id, val) values(1,2);
insert into table_name (id, val) values(1,3);
...........................................
insert into table_name (id, val) values(1,n);
можно выполнить один запрос:
insert into table_name (id, val) values(1,1),(1,2),(1,3),…(1,n)
Для таблиц типа MyISAM используется кэш, ускоряющий выполнение таких запросов - переменная bulk_insert_buffer_size устанавливает размер этого кэша.
По умолчанию – 8Мб. Установка bulk_insert_buffer_size в 0 отключит оптимизацию.
Если размер запроса превысит размер кэша, то Вы получите сообщение об ошибке.
Например, CodeIgniter выдал мне такое:
A Database Error Occurred
Error Number: 2006
MySQL server has gone away
Интересный способ ускорения, правда еще мной не опробованный это файл-обертка для дамп-файла:
SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;START TRANSACTION;
SET NAMES utf8;< содержимое дамп-файла >
COMIIT;
Еще можно поиграть с "LOAD DATA INFILE" и с такими настройками
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
skip-innodb_doublewrite
skip-innodb_checksums
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
если меняем innodb_log_file_size, то погасив mysql, удаляем лог файлы innodb (обычно /var/lib/mysql/ib_logfile0, /var/lib/mysql/ib_logfile1)
И еще, интересная статейка »
Делаем бэкап:
#!/bin/bash
# Удаляем данные в каталоге бекапа
rm -rf /mysql/backup/*
# xtrabackup копирует данные из /var/lib/mysql в /mysql/backup
xtrabackup --user=xtrabackup \
--password=xxxx_SECRET_xxxx \
--backup \
--target-dir=/mysql/backup
# Данные в каталоге /mysql/backup не консистентны до тех пор, пока они не будут подготовлены.
# Дело в том, что во время копирования файлов могли произойти изменения.
# Данная команда делает данные резервной копии идеально согласованными во времени
xtrabackup --prepare --target-dir=/mysql/backup
# Создаём архив
tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-"$(date +%F-%H:%M:%S)".gz /mysql/backupВосстановление
Вариант 1:
/var/lib/mysql директории содержимым директории /mysql/backup Вариант 2:
xtrabackup --copy-back --target-dir=/mysql/backupВ MySQL 6 появился более простой способ резервирования и восстановления: