Итак, коротко посмотрим, как сделать бэкап базы данных mysql.

Создание бэкапа базы или таблицы MySQL с помощью mysqldump

Базы данных обычно разворачиваются, чтобы хранить и обрабатывать какие-либо важные данные. Потеря таковых может быть очень болезненная. Поэтому проведение бэкапов вообще - это очень важное мероприятие. Ну а если это бэкап баз данных, то вообще за отсутствие такового надо вводить смертную казнь. Лучше всего бэкапы делать периодически и, желательно, автоматизировав. Чтобы периодически дампить базу данных MySQL вручную или с помощью cron, можно использовать такой скрипт резервного копирования. Разумеется, такой вариант лучше всего проводить на относительно небольших базах и при минимальных запросах, так как нагрузка будет большая. Время минимальных запросов можно просто определить графиком MRTG или любым другим средством, которое у вас есть. Обычно это 4-5 часов утра.

Копируем в файл:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 #!/bin/bash
 # Эти данные нужно сменить, если какой-либо из параметров поменяется. Например при плановой замене пароля.
 export DB_BACKUP="/home/backups/mysql" #Указать каталог для баз
 export DB_USER="root" # Указать пользователя, от которого будет идти бэкап. Лучше не root, а имеющий возможность только чтения
 export DB_PASSWD="qweqwe" # Указать пароль этого пользователя
 # название и версии бэкапов. Тут же - глубина бэкапов. Чем больше строк "mv $DB_BACK..." тем больше бэкапов сохранится. При каждом
 # срабатывании скрипта бэкапы перемещаются в сл. папку, а последний - удаляется. То есть такая достаточно примитивная ротация,
 # которая избавит от необходимости постоянно чистить диски от старых и уже ненужных бэкапов
 echo "* Идет бэкап, можете покурить, попить чаю или поиграть в косынку :)"
 rm -rf $DB_BACKUP/04
 mv $DB_BACKUP/03 $DB_BACKUP/04
 mv $DB_BACKUP/02 $DB_BACKUP/03
 mv $DB_BACKUP/01 $DB_BACKUP/02
 mkdir $DB_BACKUP/01
 echo "* Самый ответственный, нужный и необходимый момент. Спорцменк и комсомолк mysqldump делает свое черное дело..."
 echo "----------------------"
 mysqldump --user=$DB_USER --password=$DB_PASSWD -R имя_базы >$DB_BACKUP/01/имя базы-wi-`date +%Y-%m-%d-%H-%M-%S`.sql
 mysqldump --user=$DB_USER --password=$DB_PASSWD -R имя_еще одной базы >$DB_BACKUP/01/имя_еще_одной_базы-`date +%Y-%m-%d-%H-%M-%S`.sql
 echo "ready!"
 # строчку с ls я добавляю специально, чтобы в письме, которое мне придёт, можно было увидеть
 # размер бэкапа. Если он меньше вчерашнего - есть повод призадуматься. Если вообще нулевой - бэкап не прошёл точно.
 ls -lah $DB_BACKUP/01/
 exit 0

Если необходимо сжать архив (а текстовые архивы жмутся действительно сильно), то просто нужно добавить

| gzip -9 -c

Например:

1
mysqldump --user=$DB_USER --password=$DB_PASSWD -R имя_базы | gzip -9 -c >$DB_BACKUP/01/имя базы-wi-`date +%Y-%m-%d-%H-%M-%S`.sql.gz

Сохраните данный скрипт в файл, например в backup.sh. Далее стоит сделать этот скрипт исполняемым:

chmod +x backup.sh

Имя дампа базы создается с датой и временем, чтобы можно было не только знать, когда сделан бэкап, но и сколько он делался. Если cron запустил выполнение в 4.00, а в имени файла 4.02 - значит 2 минуты бэкапилась база.


С созданием бэкапа отдельных таблиц все просто - нужно добавить название таблицы. Например, в вышеуказанно скрипте добавим “имятаблицы” после “имябазы” и в название файла:

1
mysqldump --user=$DB_USER --password=$DB_PASSWD имя_базы имя_таблицы >$DB_BACKUP/01/имя базы-имя_таблицы-wi-`date +%Y-%m-%d-%H-%M-%S`.sql

Восстановление базы данных

Для восстановления какой-либо из баз или таблицы нужно задать команду

mysql -u root -p имябазы < имябазы.sql

имя_базы.sql - это имя дампа, сделанного ранее. Учтите, что данные, которые есть, будут удалены, поэтому такие заливки следует продумывать как следует. К тому же на момент заливки базы будут заблокированы, так что постарайтесь максимально снизить нагрузку на базу на момент восстановления.

Как обойти ошибку Fatal error: Allowed memory size of……phpmyadmin/libraries/import.lib.php

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

Как известно, есть жесткие ограничения в php и phpmyadmin на размер загружаемого файла при импорте дампа в базу данных. Не рекомендуется поднимать значение максимально разрешенных передач, лучше эту операцию провести в командной строке. К тому же дампы баз могут быть очень большими, исчисляясь гигабайтами. И такие импорты просто уничтожат всю свободную оперативную память и есть шанс отправить систему в SWAP. Лучше поступим так:

  • Загрузите нужный дамп-файл на сервер, например по ftp в любую удобную вам папку. В качестве примера используем /tmp

  • В командной строке задайте вышеуказанную команду:

mysql -u root -p имябазы < /tmp/имябазы.sql

  • Обратите внимание, что указан полный путь к файлу. Если вы выполняете данную команду из директории с нужным дампом, то полный путь прописывать не обязательно.

  • Система запросит пароль и пойдет подлив дамп-файла.


Как сохранить все базы

Скрипт бэкапит все базы сразу по циклу.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 #!/bin/sh

#исходные данные:
 USER=database_user
 PASSWORD=database_user_passwoed
 HOST=database_host
 DUMPDIR=/way/to/dumps

#Подготовка и получение массива названий БД:
 NOW=$(date +"%b%d")
 RM=$(which rm)
 MYSQL=$(which mysql)
 MYSQLDUMP=$(which mysqldump)
 DBS=$($MYSQL -u $USER -h $HOST -p$PASSWORD -Bse 'show databases')

#Удаление старых дампов:
 echo Removing old dumps...
 rm -rf $DUMPDIR/*

#Делаем новые:
 for db in $DBS
 do
        echo Dumping database $db
        $MYSQLDUMP -u $USER -p$PASSWORD -R $db > $DUMPDIR/`/bin/date +BACKUP--%Y%m%d-$db.sql`
        echo Database $db backuped successful!
 done

Запускаем сие чудо по CRON’у раз в сутки (как это сделать, читаем cron) и будет счастье….


Разумеется, стоит учитывать, что на момент создания дампов, нагрузка будет достаточно большая и в час-пик такое организовывать точно не стоит. Если же условия не позволяют, настраиваем репликацию и бэкапим slave сервер.

Как делать бэкап базы данных не создавая нагрузку

Если есть необходимость делать резервное копирование баз данных, но нагрузка достаточно большая в любое время, можно нагрузить не основной сервер, а дополнительный. Для этого нужно: