Скрипт для бэкапа mysql баз
Написал для себя простенький скрипт (linux bash) для бэкапа всех баз на одном сервере. Его отличительные особенности:
- Наличие списка исключений (т.е. бэкапим все кроме…)
- Получение списка всех БД из MySQL (не надо добавлять вновь созданные базы к бекапу)
- Создание директории под бэкап вида “…/YYYY/mm/dd/HH-MM/”
- Бэкап каждой базы в отдельный файл вида “YYYY-mm-dd.HH-MM.databasename.backup.sql” (mysqldump бэкапит все в один файл)
- Архивирование бэкапа в тарбол
- Зачистка .sql
Собственно к написанию скрипта меня сподвигло именно то что mysqldump бэкапит все что ему сказано в один файл (если требуется восстановить одну базу, то попробуй ее выцарапай из общего дампа…), а создавать отдельную строку для бэкапа всякой новой БД геморно (об этом как минимум надо вспомнить!).
В общем если интересно – прошу под кат:
Собственно сам скрипт
| #!/bin/bash ## # MySQL backup utility # @author [email protected] ## # current date DATE=`date +%Y-%M-%d` # y/m/d/h/m separately YEAR=`date +%Y` MONTH=`date +%m` DAY=`date +%d` HOURS=`date +%H` MINUTES=`date +%M` # database credentials DBUSER=”” DBPASS=”” DBHOST=”” # create list of databases mysql -h $DBHOST -u $DBUSER -p$DBPASS -e “show databases;” > /tmp/databases.list # create backup dir (e.g. ../2011/01/01/04-00) BACKUP_DIR=/home/backups/mysql/$YEAR/$MONTH/$DAY/$HOURS-$MINUTES mkdir –parents –verbose $BACKUP_DIR # excludes list (Database is a part of SHOW DATABASES output) EXCLUDES=( 'Database' 'information_schema' ) NUM_EXCLUDES=${#EXCLUDES[@]} for database in `cat /tmp/databases.list` do skip=0 let count=0 while [ $count -lt $NUM_EXCLUDES ] ; do # check if this name in excludes list if [ “$database” = ${EXCLUDES[$count]} ] ; then let skip=1 fi let count=$count+1 done if [ $skip -eq 0 ] ; then echo “++ $database” # now we can backup current database cd $BACKUP_DIR backup_name=”$YEAR-$MONTH-$DAY.$HOURS-$MINUTES.$database.backup.sql” backup_tarball_name=”$backup_name.tar.gz” `/usr/bin/mysqldump -h “$DBHOST” –databases “$database” -u “$DBUSER” –password=”$DBPASS” > “$backup_name”` echo ” backup $backup_name” `/bin/tar -zcf “$backup_tarball_name” “$backup_name”` echo ” compress $backup_tarball_name” `/bin/rm “$backup_name”` echo ” cleanup $backup_name” fi done `/bin/rm /tmp/databases.list` echo “done!” |
#!/bin/bash ## # MySQL backup utility # @author dmitry.bykadorov@gmail.
com ## # current date DATE=`date +%Y-%M-%d` # y/m/d/h/m separately YEAR=`date +%Y` MONTH=`date +%m` DAY=`date +%d` HOURS=`date +%H` MINUTES=`date +%M` # database credentials DBUSER=”” DBPASS=”” DBHOST=”” # create list of databases mysql -h $DBHOST -u $DBUSER -p$DBPASS -e “show databases;” > /tmp/databases.list # create backup dir (e.g. ..
/2011/01/01/04-00) BACKUP_DIR=/home/backups/mysql/$YEAR/$MONTH/$DAY/$HOURS-$MINUTES mkdir –parents –verbose $BACKUP_DIR # excludes list (Database is a part of SHOW DATABASES output) EXCLUDES=( 'Database' 'information_schema' ) NUM_EXCLUDES=${#EXCLUDES[@]} for database in `cat /tmp/databases.
list` do skip=0 let count=0 while [ $count -lt $NUM_EXCLUDES ] ; do # check if this name in excludes list if [ “$database” = ${EXCLUDES[$count]} ] ; then let skip=1 fi let count=$count+1 done if [ $skip -eq 0 ] ; then echo “++ $database” # now we can backup current database cd $BACKUP_DIR backup_name=”$YEAR-$MONTH-$DAY.$HOURS-$MINUTES.$database.backup.
sql” backup_tarball_name=”$backup_name.tar.gz” `/usr/bin/mysqldump -h “$DBHOST” –databases “$database” -u “$DBUSER” –password=”$DBPASS” > “$backup_name”` echo ” backup $backup_name” `/bin/tar -zcf “$backup_tarball_name” “$backup_name”` echo ” compress $backup_tarball_name” `/bin/rm “$backup_name”` echo ” cleanup $backup_name” fi done `/bin/rm /tmp/databases.list` echo “done!”
Можете его свободно:
- использовать (никаких гарантий я вам конечно же не даю – на свой страх и риск )))
- модифицировать (например добавить выгрузку бэкапа на другой сервер или на Amazon S3 )))
- критиковать ))
P.S. Если таки будете использовать – не забудьте подставить ваши mysql credentials ))
Источник: http://hudson.su/2011/03/03/mysql-backup-script/
Утилита mysqldump и шпаргалка по параметрам
Утилита 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
Примеры навеяны постом Александра Макарова –<\p>
Делаем дамп только триггеров, процедур и событий:
mysqldump –no-create-info –no-data –triggers –routines –events -uroot -p database | gzip > ~/database.sql.gz
Шпаргалка по параметрам
Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать “mysqlhotcopy”, которая скопирует файлы *.frm, *.MYD и *.MYI:
# mysqlhotcopy db_name /path/to/dir
Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Для InnoDB есть xtrabackup, рекомендую посмотреть!
UPD: XtraBackup – резервное копирование для innoDB
Бин-лог и репликации
Для репликации “mysqldump” не предназначена, для этого есть бин-лог (–log-bin):
# mysqlbinlog binlog.[0-9]* | mysql
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Резервирование данныс в MySQL 6.x
С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:
Источник: https://adw0rd.com/2009/mysqldump-and-cheat-sheet/
» Делаем резервные копии баз данных при помощи mysqldump
Делаем резервные копии баз данных при помощи mysqldump
Делать резервные копии (бэкапы) очень важно для серьезных проектов, в которых часто обновляются данные.
Для интернет магазинов это просто архиважно, ведь база может испортиться в любой момент по самым разным причинам.
А если в этой базе важные данные пользователей, разные статистики или данные по заказам? Лучше потратить пару часов драгоценного времени и настроить автоматическое создание резервных копий и спать спокойно.
Для создания бэкапов у mysql есть утилита mysqldump. Её то мы и будем использовать. Для тестирования можем зайти на сервер по SSH либо пробовать на локальной базе. Синтаксис команды прост:
Тут всё интуитивно понятно. LOGIN — имя пользователя базы, PASS — его пароль, а HOST — имя или айпи адрес сервера, в большинстве случаев это localhost, хотя зависит от хостинга. DBNAME — имя базы для которой делаем бэкап. mysqldump — непосредственно сама команда. backup.
sql — имя файла, в который будет помещен дамп, перед именем файла можно и даже нужно указывать папку, например /home/backups/backup.sql.
Иногда перед командой нужно дописать путь к папке в которой лежит утилита, на UNIX серверах это как правило /usr/bin/, в этом случае команда будет выглядеть так:
Допустим мы хотим сделать бэкап для базы testing и подключаемся мы обычно к ней под пользователем root с паролем 123456.
Иногда бывает необходимость сохранять только некоторые таблицы базы, например когда в базе есть большие неизменяющиеся таблицы, тогда чтобы не напрягать лишний раз сервер нужно указывать какие именно таблицы должны «забэкапится» . Таблицы указываются после имени базы и перечисляются через пробел
Если мы хотим забэкапить все базы на сервере добавляем ключ —all-databases
mysqldump —all-databases-uroot -hlocalhost -p123456 > backup.sql
Если хотим сэкономить место на диске, бэкап можно сразу архивировать
Очень удобно в названии файла помещать дату дату бэкапа
mysqldump -uroot -hlocalhost -p123456 testing | gzip> `date «+%Y-%m-%d»`_backup.sql
Я обычно пользуюсь такой схемой
Таким образом у меня в папке бэкапов хранится 31 файл и я могу восстановить базу любого дня последнего месяца. Название файла является числом.
Ну и теперь об автоматизации. В этом деле нам поможет планировщик, в UNIX системах за это отвечает утилита CRON. В панеле хостинга вы должны найти её без проблем, просто добавьте туда команду бэкапа с указанием частоты и времени исполнения. Я обычно устанавливаю ежесуточный бэкап в 3 ночи.
Если же найти утилиту не получается в интерфейса хостинга, попробуйте добавить команду крона через SSH. Выполните команду crontab -e и создайте приблизительно такое правило:
Эта запись говорит планировщику, что необходимо запускать команду «mysqldump -uroot -hlocalhost -p123456 testing > backup.sql» каждый день в 3 часа 0 минут.
Ну и напоследок дополнительные настройки, которые может принимать утилита 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’
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны: «—where=user=’test’» «-wuserid>1″ «-wuserid
Источник: http://denweb.org/programming/mysql/delaem-rezevrnye-kopii-baz-dannyx-pri-pomoshhi-mysqldump/
Резервное копирование mysql базы данных
Как вы знаете, все важные данные ваших сайтов, их настройки, статьи, комментарии и другая информация хранятся в базе данных. Потеря этой информации может иметь очень тяжелые последствия для проекта. Поэтому важно своевременно делать резервные копии базы данных mysql. Также эти копии могут быть очень полезными при переносе проекта на другой сервер.
В этой инструкции мы рассмотрим как выполняется резервное копирование mysql или mariadb базы данных, а также как восстановить информацию в базе из копии. Кроме того, мы разберем как настроить автоматическое создание копий через определенный промежуток времени.
Резервное копирование базы данных
Все что вам нужно для резервного копирования mysql – это доступ к серверу с операционной системой Linux, на котором установлен сервер баз данных, а также имя базы данных и параметры доступа к ней.
Для экспорта информации из базы данных в формате SQL можно использовать утилиту mysqldump. Вот ее синтаксис:
$ mysqldump опции имя_базы [имя_таблицы] > файл.sql
По умолчанию утилита будет выводить все в стандартный вывод, поэтому нам нужно перенаправить эти данные в файл, что мы и делаем с помощью оператора “>”. Опции указывают параметры аутентификации и работы, а имя базы и таблицы – данные которые нужно экспортировать. Теперь рассмотрим кратко опции, которые будем использовать:
- -A – копировать все таблицы из всех баз данных;
- -i – записывать дополнительную информацию в комментариях;
- -c – использовать имена колонок для инструкции INSERT;
- -a – включать все возможные опции в инструкцию CREATE TABLE;
- -k – отключает первичные ключи на время копирования;
- -e – использовать многострочный вариант инструкции INSERT;
- -f – продолжить даже после ошибки;
- -h – имя хоста, на котором расположен сервер баз данных, по умолчанию localhost;
- -n – не писать инструкции для создания базы данных;
- -t – не писать инструкции для создания таблиц;
- -d – не записывать данные таблиц, а только их структуру;
- -p – пароль базы данных;
- -P – порт сервера баз данных;
- -Q – брать все имена таблиц, баз данных, полей в кавычки;
- -X – использовать синтаксис XML вместо SQL;
- -u – пользователь, от имени которого нужно подключаться к базе данных.
В большинстве случаев нам достаточно задать имя пользоваться, пароль, а также имя базы данных. Дальше рассмотрим примеры работы с утилитой. Например самая простая команда экспорта базы данных:
mysqldump -u имя_пользователя -p имя_базы > data-dump.sql
Вам нужно будет ввести пароль пользователя базы данных и больше ничего команда не выведет, поскольку мы отправили все данные в файл, но вы можете посмотреть информацию о резервной копии с помощью такой команды:
head -n 5 data-dump.sql
Но если во время создания копии возникнут какие-либо ошибки, они будут выведены на экран и вы сразу о них узнаете. Более сложный вариант, это выполнить резервное копирование mysql с другого хоста, если у вас есть к нему доступ:
mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql
Копирование таблицы mysql может быть выполнено простым добавлением имени таблицы в конец строки:
mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql
Также, чтобы выполнять автоматическое резервное копирование базы mysql может понадобиться сразу задать пароль, для этого указывайте его сразу после опции -p, без пробела:
mysqldump -u имя_пользователя -pпароль имя_базы > data-dump.sql
Мы можем делать бэкап вручную время от времени, но это не совсем удобно, поскольку есть другие важные дела. Поэтому используем планировщик cron, чтобы автоматизировать процесс. Тут есть два способа более простой, и более сложный, но точный. Допустим, нам нужно создавать резервную копию каждый день, тогда просто создайте скрипт в папке /etc/cron.daily/ со следующим содержимым:
sudo vi /etc/cron.daily/mysql-backup
!/bin/bash
/usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql
Папку /backups/mysql-dump.sql нужно заменить на свою папку для резервных копий. Осталось дать скрипту права на выполнение:
chmod ugo+x /etc/cron.daily/mysql-backup
Дальше планировщик будет запускать его каждый день и делать копирование базы mysql. Но есть еще один, более точный способ, который позволяет указать точное время выполнения. Сначала выполните команду:
sudo crontab -e
Добавьте в открывшейся файл такую строку и сохраните изменения:
30 2 * * * /usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql
Команда будет выполняться каждый день, в 2:30, это удобно, поскольку ночью обычно меньше нагрузка на сервер. Как вы поняли, первое число – это минуты, второе – часы, третье день, дальше неделя и месяц. Звездочка значит, что этот параметр не имеет значения.
Восстановление из резервной копии
Восстановить резервную копию mysql или mariadb из существующего SQL файла тоже очень просто. Поскольку использовался синтаксис sql мы просто можем выполнить все команды с помощью стандартного клиента mysql.
Сначала нужно создать новую базу данных. Для этого авторизуйтесь на mysql сервере с правами суперпльзователя:
mysql -u root -p
Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:
mysql> CREATE DATABASE new_database;
Дальше закройте оболочку, нажав сочетание клавиш Ctrl+Q и импортируйте данные из файла командой:
mysql -u пользователь -p база_данных < data-dump.sql
Для экспорта мы направляли данные стандартного вывода в файл, а здесь происходит обратная операция, данные из файла направляются на стандартный ввод программы. Успешно выполненная команда ничего не выведет, и чтобы убедиться что все прошло успешно, просто посмотрите содержимое базы.
Выводы
Теперь вы знаете как выполняется копирование базы данных mysql, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных mysql это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!
Источник: https://losst.ru/rezervnoe-kopirovanie-mysql-bazy-dannyh
Дамп и восстановление базы данных MySQL
Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty.
Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd‘.
Ниже приведены примеры о том, как создавать дампы базы данных MySQL и затем восстанавливать их при необходимости, например для Вашего сайта, интернет-магазина или какого либо другого проекта.
Создание дампа базы данных MySQL
Для того, чтоб выполнять данные команды, подключитесь удаленно к Вашему серверу через SSH используя одну из перечисленных выше программ. После подключения и авторизации к серверу/хостингу, Вы можете вводить приведенные ниже команды.
| # Бекап одной базы данных в файл dump_file.sqlmysqldump -uroot -p your_base > dump_file.sql# На windows дамп лучше всего создавать немного другой командой, которая предотвращает# случайное затирание строк дампа из за конвертации символов перевода строки ' ' в ' 'mysqldump -uroot -p your_base -r dump_file_utf8.sql# Если Вам нужен бекап только отдельных таблиц, а не всей базы данных# (указываем наименования таблиц через пробел после названия базы данных)mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql# Если нужно создать бекап только структуры базы данных без самих данныхmysqldump -uroot -p –no-data your_base > dump_file.sql# Бекап всех баз данных в файл текущая_дата.gzmysqldump -uroot -p –all_databases | gzip -c > 'date “+%Y-%m-%d”'.gz# Бекап, где для каждой записи создается отдельный INSERT# и с явным указанием кодировки базы данных UTF-8mysqldump -uroot -p –default-character-set=utf8 your_base –extended-insert=FALSE | gzip -c > 'date “+%Y-%m-%d”'.gz |
В приведенном выше примере, для создания бекапа используется утилита mysqldump, которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:
- -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
- -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
- your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
- > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
- dump_file.sql – это название Вашего файла .slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:после этой строки в примере указывается расширение файла ‘.gz‘. В результате будет создан файл вида ‘2014-11-15.gz‘.Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде [root@dvs home]#, где root@dvs это логин и имя сервера, то файл будет создан в директории /home. Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql.
- Во втором примере, вместо оператора ‘>‘ используется оператор ‘|‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz‘, о чем сообщает оператор ‘>‘.
- Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
- Параметры –default-character-set=utf8 и –extended-insert=FALSE. Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251. Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.
Восстановление базы данных из файла дампа MySQL
Теперь рассмотрим с Вами обратный процесс восстановления базы данных из файла дампа. Данное действие выполняется при помощи программы mysql. Рассмотрим сразу же пример.
| # Восстанавливаем базу данных your_base из файла дампа dump_filemysql -uroot -p your_base |
Источник: http://aione.ru/bekap-i-vosstanovlenie-bazyi-dannyih-cherez-shell/
Резервное копирование баз MySQL
CodeNet / Приложения / Базы данных / MySQL
Резервное копирование баз MySQL. Гораздо более сложной представляется задача создания копии такой динамичной структуры, как база данных MySQL.
Вообще, почти все хостинг-провайдеры производят резервное копирование всех файлов пользователей. Однако, не стоит забывать о том, что провайдеры делают backup, в основном, для себя, на случай аварии у себя.
Именно по этой причине пользователи в условиях хостинга могут, конечно, рассчитывать на восстановление в случае удаления каких-то данных по вине самого пользователя, но вовсе не факт, что провайдер сделает восстановление MySQL-базы сразу по получению запроса. Лучше делать для себя копию и в случае чего ее использовать.
Можно даже периодически копировать этот свой backup на другую, не провайдерскую машину – так надежнее, на всякий случай.
Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически “откладывать в сторонку” и копии скриптов. Гораздо более сложной представляется задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:
- Отсутствие физического доступа к файлам базы данных. Как правило, провайдеры хостинга предоставляют возможность работы с базой данных только через скрипты или специальный mysql-клиент, но не дают прав на доступ непосредственно к файлам, в которых содержатся данные из MySQL-базы.
- Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда “клюнул жареный петух”. То есть, в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет…
- В случае, если веб-мастер не владеет в достаточной мере навыками работы со специализированными утилитами из пакета MySQL, могут возникать трудности, связанные с ограничениями, налагаемыми хостинг-провайдером на пользовательские аккаунты. Например, если база очень большая и ее размер превышает лимит на доступную пользователю память (RAM), backup сделать будет сложно. Нужно пользоваться тонкими настройками утилит резервного копирования, что иногда тоже вызывает трудности на практике.
Итак, данная статья предназначена для того, чтобы облегчить работу по созданию резервных копий MySQL-баз. Если Вы – веб-мастер, и работаете с MySQL, наверняка, информация, содержащаяся в данной статье, будет Вам полезна.
Как сделать копию базы MySQL
Существует программа mysqldump, позволяющая быстро и просто производить операции по созданию резервных копий баз MySQL.
Также mysqldump дает возможность делать очень тонкие настройки для управления процессом создания резервных копий баз данных или отдельных таблиц.
Можно сказать, что mysqldump – это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.
Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер.
Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS.
Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):
> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt
После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга.
Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае – ” > dump.txt “.
Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.
Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных.
То есть, вывод mysqldump – это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария.
Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись “1111”, то mysqldump создаст примерно такой сценарий:
# MySQL dump 8.14 # # Host: HOST Database: DBNAME #——————————————————– # Server version 3.23.39-log # # Table structure for table 'test' # CREATE TABLE test ( test2 int(11) default NULL ) TYPE=MyISAM; # # Dumping data for table 'test2' # INSERT INTO test2 VALUES ('1111');
Таким образом, mysqldump “опишет” все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс – воссоздание базы из резервной копии.
Для восстановления будем пользоваться стандартной программой mysql, которая входит в комплект поставки MySQL наряду с mysqldump. Допустим, у нас имеется backup в файле dump.txt. Нам нужно восстановить его в рабочую базу. Например, мы случайно удалили нашу базу данных, а теперь пытаемся исправить эту незадачу. Делаем так :
> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt
То есть, заставляем mysql-клиент соединиться с сервером и выполнить сценарий, который у нас имеется. После выполнения этой команды в Вашей базе появятся таблицы и данные из резервной копии. Учитывайте то, что данные будут просто восстанавливаться по сценарию из dump.txt.
То есть, если таблицы, которые упоминаются в дампе базы, уже существуют и имеют другую структуру, тут явно возникнет ошибка. Просто посмотрите на сценарий и на рабочую базу и представьте, что Вы вручную выполняете команды из сценария.
Если уверены, что все будет хорошо – смело восстанавливайте.
Рассмотрим более тонкие настройки mysqldump:
–databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы “с нуля”.
То есть, без использования –databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные.
Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;
–all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);
Ключ –help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;
–add-drop-table – ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии.
Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;
–no-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;
–result-file=… – этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой “>”, а можно – вот этот ключ. Кому что нравится;
Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.
Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск.
Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка – mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы “заставить” mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ –quick.
Это решит проблему.
Автоматизация резервного копирования
Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа – cron. Она позволяет запускать процессы в указанное пользователем время или с определенной периодичностью.
Сразу оговоримся – cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время.
Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.
В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:
0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date “+%Y-%m-%d”`.gz
Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz.
Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали – date “+%Y-%m-%d”.
Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.
Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то, что испортилось, например.
Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find ~/каталог-с-архивами -name “*.gz” -mtime +7, Вы будете удалять архивы, которые “старше” семи дней.
Прочитайте документацию по find – она доступна по команде man find в unix shell.
Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина – это очень надежная штука. Однако, как говорится, “береженого Бог бережет”. Старая как мир истина в определенных условиях может и Вам помочь.
Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron. Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов – scp. Читайте документацию по этой команде в man-странице man scp. Примерный запуск: scp 2002-01-03.gz : – закачиваем файл 2002-01-03.
gz на машину your.host.ru авторизовавшись там под логином login.
Дополнительные возможности
Скрипт mysqlhotcopy, написанный на языке Perl, поможет Вам упростить создание копий баз данных и отдельных таблиц. Использование этого скрипта позволяет производить резервное копирование еще более гибко. Описание mysqlhotcopy читайте тут.
Источник: http://www.codenet.ru/db/mysql/MySql-Backup.php
Разбираемся с утилитами для бэкапа баз данных – «Хакер»
Содержание статьи
Серверы баз данных — одни из ключевых в любой организации. Именно они хранят информацию и предоставляют выдачу по запросу, и крайне важно сохранить БД в любой ситуации. Базовая поставка обычно включает нужные утилиты, но админу, не сталкивавшемуся до этого с БД, придется некоторое время разбираться с особенностями работы, чтобы обеспечить автоматизацию.
Для начала разберемся с тем, какие вообще бывают бэкапы.
Сервер баз данных не является обычным десктопным приложением, и, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable), используется ряд технологий, а поэтому создание и восстановление БД из архива имеет свои особенности. Существуют три различных подхода к резервному копированию данных, каждый из которых имеет свои плюсы и минусы.
При логическом, или SQL, бэкапе (pg_dump, mysqldump, SQLCMD) создается мгновенный снимок содержимого базы с учетом транзакционной целостности и сохраняется в виде файла с SQL-командами (можно выбрать всю базу или отдельные таблицы), при помощи которого можно воссоздать базу данных на другом сервере.
На это требуется время (особенно для больших баз) для сохранения и восстановления, поэтому очень часто эту операцию выполнять нельзя и ее производят во время минимальной нагрузки (например, ночью).
При восстановлении администратору необходимо будет выполнить несколько команд, чтобы подготовить все необходимое (создать пустую базу данных, учетные записи и прочее).
Физический бэкап (уровня файловой системы) — копирование файлов, которые СУБД использует для хранения данных в базе данных. Но при простом копировании игнорируются блокировки и транзакции, которые, скорее всего, будут неправильно сохранены и нарушены.
При попытке присоединить этот файл он будет в несогласованном состоянии и приведет к ошибкам. Чтобы получить актуальный бэкап, базу данных нужно остановить (можно уменьшить время простоя, использовав два раза rsync — вначале на работающей, потом на остановленной).
Недостаток этого метода очевиден — нельзя восстановить определенные данные, только всю базу данных. При старте БД, восстановленной из архива файловой системы, нужно будет провести проверку на целостность. Здесь используются разные вспомогательные технологии.
Например, в PostgreSQL логи упреждающей журнализации WAL (Write Ahead Logs) и специальная функция (Point in Time Recovery — PITR), позволяющая вернуться к определенному состоянию базы. С их помощью легко реализуется третий сценарий, когда бэкап уровня файловой системы объединяется с резервной копией WAL-файлов.
Вначале восстанавливаем файлы резервной копии файловой системы, а затем при помощи WAL база приводится к актуальному состоянию. Это чуть более сложный подход для администрирования, но зато нет проблем с целостностью БД и восстановлением баз до определенного времени.
Логический бэкап используется в тех случаях, когда необходимо одноразово сделать полную копию базы или в повседневной эксплуатации для создания копии не потребуется много времени или места. Когда же выгрузка баз занимает много времени, следует обратить внимание на физическое архивирование.
Сайт: pgbarman.org, sf.net/projects/pgbarman
Лицензия: GNU GPL
Поддерживаемые СУБД: PostgreSQL
PostgreSQL поддерживает возможности физического и логического бэкапа, добавляя к ним еще один уровень WAL (см. врезку), который можно назвать непрерывным копированием. Но управлять при помощи штатных инструментов несколькими серверами не очень удобно даже админу со стажем, а в случае сбоя счет идет на секунды.
Barman (backup and recovery manager) — внутренняя разработка компании 2ndQuadrant, предоставляющей услуги на базе PostgreSQL. Предназначен для физического бэкапа PostgreSQL (логический не поддерживает), архивирования WAL и быстрого восстановления после сбоев.
Поддерживаются удаленный бэкап и восстановление нескольких серверов, функции point-in-time-recovery (PITR), управление WAL. Для копирования и подачи команд на удаленный узел используется SSH, синхронизация и бэкап при помощи rsync позволяет сократить трафик.
Также Barman интегрируется со стандартными утилитами bzip2, gzip, tar и подобными. В принципе, можно использовать любую программу сжатия и архивирования, интеграция не займет много времени.
Реализованы различные сервисные и диагностические функции, позволяющие контролировать состояние сервисов и регулировать полосу пропускания. Поддерживаются Pre/Post-скрипты.
Конфигурационный файл Barman
Barman написан на Python, управление политиками резервного копирования производится при помощи понятного INI-файла barman.conf, который может находиться в /etc или домашнем каталоге пользователя.
В поставке идет готовый шаблон с подробными комментариями внутри. Работает только на *nix-системах. Для установки в RHEL, CentOS и Scientific Linux следует подключить EPEL — репозиторий, в котором содержатся дополнительные пакеты.
В распоряжении пользователей Debian/Ubuntu официальный репозиторий:
$ sudo apt-get install barman
В репозитории не всегда последняя версия, для ее установки придется обратиться к исходным текстам. Зависимостей немного, и разобраться в процессе несложно.
Сайт: sypex.net/ru/products/dumper
Лицензия: BSD
Поддерживаемые СУБД: MySQL
Вместе с MySQL поставляются утилиты mysqldump, mysqlhotcopy, позволяющие легко создать дамп базы данных, они хорошо документированы, и в интернете можно найти большое количество готовых примеров и фронтендов. Последние позволяют новичку быстро приступить к работе.
Sypex Dumper представляет собой PHP-скрипт, позволяющий легко создать и восстановить копию базы данных MySQL. Создавался для работы с большими базами данных, работает очень быстро, понятен и удобен в использовании.
Умеет работать с объектами MySQL — представлениями, процедурами, функциями, триггерами и событиями.
Еще один плюс, в отличие от других инструментов, при экспорте производящих перекодирование в UTF-8, — в Dumper экспорт производится в родной кодировке. Результирующий файл занимает меньше места, а сам процесс происходит быстрее.
В одном дампе могут быть объекты с разными кодировками. Причем легко импорт/экспорт произвести в несколько этапов, останавливая процесс во время нагрузки. При возобновлении процедура начнется с места остановки.
При восстановлении поддерживается четыре варианта:
- CREATE + INSERT — стандартный режим восстановления;
- TRUNCATE + INSERT — меньше времени на создание таблиц;
- REPLACE — восстанавливаем в рабочей базе старые данные, не затирая новые;
- INSERT IGNORE — добавляем в базу удаленные или новые данные, не трогая существующие.
Поддерживается сжатие копии (gzip или bzip2), автоудаление старых бэкапов, реализован просмотр содержимого дамп-файла, восстановление только структуры таблиц. Имеются и сервисные функции по управлению БД (создание, удаление, проверка, восстановление БД, оптимизация, очистка таблиц, работа с индексами и другое), а также файл-менеджер, позволяющий копировать файлы на сервер.
Интерфейс Dumper
Управление производится при помощи веб-браузера, интерфейс с использование AJAX локализован из коробки и создает впечатление работы с настольным приложением. Также возможно запускать задания из консоли и по расписанию (через cron).
Для работы Dumper понадобится классический L|WAMP-сервер, установка обычная для всех приложений, написанных на PHP (копируем файлы и устанавливаем права), и не будет сложной даже для новичка. Проект предоставляет подробную документацию и видеоуроки, демонстрирующие работу с Sypex Dumper.
Есть две редакции: Sypex Dumper (бесплатно) и Pro (10 долларов). Вторая имеет больше возможностей, все отличия приведены на сайте.
Сайт: sqlbackupandftp.com
Лицензия:коммерческая, есть версия Free
Поддерживаемые СУБД: MS SQL Server
MS SQL Server — одно из популярных решений, а потому встречается достаточно часто. Задание резервного копирования создается при помощи среды SQL Server Management Studio, собственно Transact-SQL и командлетов модуля SQL PowerShell (Backup-SqlDatabase).
На сайте MS можно найти просто огромное количество документации, которая позволяет разобраться с процессом. Документация хотя и полная, но очень специфическая, а информация в интернете часто противоречит друг другу.
Новичку действительно вначале потребуется потренироваться, «набив руку», поэтому, даже несмотря на все сказанное, сторонним разработчикам есть где развернуться. К тому же бесплатная версия SQL Server Express не может похвастаться встроенными инструментами для резервного копирования.
Для более ранних версий MS SQL (до 2008) можно найти бесплатные утилиты, например SQL Server backup, но в большинстве подобные проекты уже коммерциализировались, хотя и предлагают всю функциональность часто за символическую сумму.
Источник: https://xakep.ru/2014/10/16/database-backup-servers/
Загрузка...
