Утилита mysqldump и шпаргалка по параметрам (для лентяев)

[versus]

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Создание дампа
Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

mysqldump -uroot -h82.82.82.82 -p database > database.sql
где:

-u или -–user=... — имя пользователя
-h или --host=... — удаленный хост (для локального хоста можно опустить этот параметр)
-p или --password — запросить пароль
database — имя базы данных
database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр --databases (или сокращенно -B), пример:

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql
А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр --all-databases (или сокращенно -A), пример:

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql
Развертывание дампа
Перенаправляем поток в обратную сторону и развертываем базу данных:

mysql -uroot -h82.82.82.82 -p database < database.sql
Или через mysql-console:

mysql> use database;
mysql> source database.sql
Ну, а если у нас gz-архив к примеру, то:

zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database
Пример использование некоторых параметров
Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql
Или нам нужна только структура, без данных:

mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql
Примеры навеяны постом Александра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Шпаргалка по параметрам
Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.

--add-drop-database
Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.
--add-drop-table
Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.
--add-locks
Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
--all-databases, -A
Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
--comments, -i
Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.
--compact
Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным --comments.
--compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.
--complete-insert, -c
Используется полная форма оператора INSERT (с именами столбцов).
--create-options
Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.
--databases, -B
Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.
--delayed
Использовать команду INSERT DELAYED при вставке строк.
--delete-master-logs
На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «--master-data».
--disable-keys, -K
Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.
--extended-insert, -e
Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
--flush-logs, -F
Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.
--force, -f
Продолжать даже если в процессе создания дампа произошла ошибка.
--hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263.
--ignore-table=db_name.tbl_name
Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «--ignore-table», указывая по одной таблице в каждом из параметров.
--insert-ignore
Добавляет ключевое слово IGNORE в оператор INSERT.
--lock-all-tables, -x
Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
--lock-tables, -l
Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
--no-autocommit
Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.
--no-create-db, -n
Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров --databases и --all-databases.
--no-data, -d
Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.
--opt
Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Начиная с MySQL 4.1, параметр --opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров --skip-opt
--order-by-primary
Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.
--port, -P
Номер TCP порта, используемого для подключения к хосту.
--protocol={TCP|SOCKET|PIPE|MEMORY}
Параметр позволяет задать протокол подключения к серверу.
--quick, -q
Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.
--quote-names, -Q
Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.
--replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
--result-file=/path/to/file, -r /path/to/file
Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.
--routines, -R
Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.
--single-transaction
Параметр создает дамп в виде одной транзакции.
--skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.
--socket=/path/to/socket, -S /path/to/socket
Файл сокета для подсоединения к localhost.
--tab=/path/, -T /path/
При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров --fields-xxx и --lines-xxx.
--tables
Перекрывает действия параметра --databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.
--triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр --skip-triggers.
--tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE='+00:00', который позволит обмениваться дампа в различных временных зонах.
--verbose, -v
Расширенный режим вывода. Вывод более детальной информации о работе программы.
--version, -V
Вывести информацию о версии программы.
--where='where-condition', -w 'where-condition'
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.
--xml, -X
Представляет дамп базы данных в виде XML.
--first-slave, -x
Блокирует все таблицы во всех базах данных.
--debug=..., -#
Отслеживать прохождение программы (для отладки).
--help
Вывести справочную информацию и выйти из программы.

[Rico-X]

Может кому пригодится, мой скрипт бэкапа баз без привязки к биллингу

Код:

#!/bin/sh 

# Переменные
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin #перечисляем пути для нахождения подпрограмм использованных в скрипте
bckpdir='/hdd/backup/mysqlbackup' #директория в которую будут делаться бэкапы баз
bckpdirsite='/hdd/backup/sitebackup' #директория в которую будут делаться бэкапы локальных файлов с сайтов 
bckplist='mysql_databases_list.txt' #список баз мускула
bckppath=$bckpdir/$bckplist #полный путь к файлу со списком баз для бэкапа 
passwd_root_mysql='3265476' #пароль рута для доступа к базе
#adminemail=' admin@ ' #адрес, на который будут приходить отчеты об архивации

# Указываем формат даты для бэкапов
todaydate=`date "+%d-%m-%Y"`

# Проверка, существует ли папка для бэкапов. Если да,то печатает "Dir exist", если нет, то создает $bckpdir (см. первый параграф)
if test -d $bckpdir 
then 
echo "Dir exists" > /dev/null 
else 
mkdir $bckpdir  
fi

if test -d $bckpdirsite 
then 
echo "Dir exists" > /dev/null 
else 
mkdir $bckpdirsite
fi


# В общем, заходим в базу под рутом "/bin/mysql -u root", 
#печатаем "show databases;" и вытаскиваем "sed '1,1d'" названия баз мускула. Вывод перенаправляем в файл $bckppath. 
echo "show databases;" | /usr/bin/mysql -u root --password=${passwd_root_mysql} | sed '1,1d' > $bckppath 

# Переменной parse ставим значение "cat $bckppath", она будет перечислять построчно в цикле каждую строчку файла $bckppath 
#Далее делаем цикл, с присвоением переменной i значений из $parse и подстановкой в 
#/bin/mysqldump --databases $i > $bckpdir/$i.sql 
#То есть под $i подразумевается название базы данных из строчки $bckppath. 
parse=`cat $bckppath` 

# Этой командой делаем бэкап всех баз данных, на случай того, если крякнет весь мускул. 
#Далее делаем отдельно бэкап каждой базы, на случай, если крякнет одна из баз. 
/usr/bin/mysqldump --skip-opt -R -Q --add-locks -u root --password=${passwd_root_mysql} --all-databases > $bckpdir/all-databases.sql 

# А вот и цикл создания дампа указанных баз, описание см. выше 
for i in $parse 
do /usr/bin/mysqldump --skip-opt -R -Q --add-locks -u root --password=${passwd_root_mysql} --databases $i > $bckpdir/$i.sql 
done 

# Создаем архив с дампами мускульных баз. Проще говоря, архивируем полученные файлы.
# Использовать "tar -xzf archivename.tar" для распаковки 
cd $bckpdir
tar -czf $todaydate.mysqldump.tar *.sql 
#Бэкапим локальные файлы сайтов
cd $bckpdirsite
#Media.lan
tar -czf $todaydate.media.lan.tar /var/www/media/public_html/www
#torrent.lan
tar -czf $todaydate.torrent.lan.tar /var/www/torrent.lan/public_html/www
#retracker
tar -czf $todaydate.xbtt.tar /var/www/xbtt/public_html/www

# Отсылка на почту админу списка файлов в $bckpdir. Тут используется переменная $adminemail 
#ls -l $bckpdir/*.sql $bckpdir/*.tar | awk '{print ($5,$8,$9)}' | sort -n -r | mail -s "BACKUP.GW.MySQL" $adminemail 

# Очищаем директорию с бэкапами от незаархивированных баз
rm -Rf $bckpdir/*.sql
rm -Rf $bckpdir/$bckplist
# Удаляем архивы старше месяца 
find $bckpdir -name \*.tar -mtime +31 -delete
find $bckpdirsite -name \*.tar -mtime +31 -delete
# (C)Rico-X

[smallcms]

А ещё если совсем надоели перебои с питанием и как следствие некислый дэмэйдж ФС - можно перейти на ZFS и соответственно выполнять снапшот и уводить его на стороннюю бэкап-машину (толку бэкапов на том же HDD, где биллинг примерно 0).
mysqldump - это конечно модно, но если БД весит более тера - работа становится такой утомительной и кропотливой, что хочется бросить всё и заняться земледелием...

[Rico-X]

Вариант, для огромных баз каждый делает свой велосипед, я выполняю репликацию на другой сервер master-slave а уже на том сервере делаю периодический бэкап через mysqldump. С ZFS в продакшине не работал и переносить живые базы как-то страшновато, да и лень. Схема с репликацией достаточно надежна при этом при сбое позволяет сделать очень быстрое переключение всего проекта на работу с slave базой тем самым минимизировать простой в работе.

толку бэкапов на том же HDD, где биллинг примерно 0

100% - это давно уже стало аксиомой.

[stix]

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

[poxy.]

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

У нас тоже локальный и удаленный.