Использование утилит mysql, mysqldump через SSH

Периодически то там, то тут поднимается вопрос, как сделать резервную копию базы данных, или как выполнить SQL скрипт огромного размера.
Если у вас есть доступ к серверу только по FTP и из PHP скриптов запрещен запуск команд оболочки, то другого выхода нет, кроме как искать скрипты выполняющие нужные функции на чистом PHP. Однако, на большинстве хостинговых площадок предоставляется SSH доступ к серверу. И на большинстве *nix серверов, даже если они не являются серверами баз данных, уже установлены утилиты командной строки для работы с БД mysql.

Итак: нам понадобится SSH доступ к серверу, наличие на сервере утилит mysql и mysqldump и доступ к их запуску.
Если на домашнем компьютере стоит Windows, то работать с сервером удобно через WinSCP в качестве файлового менеджера и PuTTY в качестве SSH клиента. WinSCP и PuTTY могут быть связаны между собой. Если операции с базой разовые и не будут сохраняться в скриптах, то WinSCP — не нужен, достаточно консоли.

Чтобы сделать резервную копию всей базы нужно выполнить команду;

mysqldump --host=$host --user=$user -p$pass $db > $sqlfile

здесь:
$host — Сервер БД
$user — Пользователь БД
$pass — Пароль БД
$db — Имя БД
$sqlfile — Имя файла, в который будет сохранен дамп.

Для копирования определенных таблиц базы, можно использовать следующий синтаксис:
mysqldump --host=$host --user=$user -p$pass $db $table1 $table2 > $sqlfile

Подробнее — смотрите документацию mysql: http://mysql.ru/docs/man/mysqldump.html

Чтобы залить этот дамп в другую БД, нужно выполнить команду:
mysql --host=$host2 --user=$user2 -p$pass2 $db2 < $sqlfile

Чтобы автоматизировать выполнение этих команд создаем файл copydb.sh (обычно автоматизировать надо только создание резервных копий, а восстановление из базы — по необходимости).
# данные первой базы, из которой копируем:
pass='pass'
user='user'
db='db'
host='mysql1.ru'

# данные второй базы, в которую копируем:
pass2='pass2'
user2='user2'
db2='db2'
host2='mysql2.ru'

# имя файла формируем по маске: БД_год_месяц_день.sql
sqlfile=$db'_'`date +%Y_%m_%d`'.sql'

# Создаем файл копии первой базы
mysqldump --host=$host --user=$user -p$pass $db > $sqlfile

# Заливаем файл во вторую базу
mysql --host=$host2 --user=$user2 -p$pass2 $db2 < $sqlfile

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

sqldump.sh:
# путь до папки с резервными копиями
home='/home/web/back/dbname'
pass='пароль'
user='пользователь'
db='база'
host='сервер'

# выбираем подпапку: в первый день месяца складываем в папку 'm',
# каждое воскресенье - в папку 'w', все остальные - в папку 'd'
if [ `date +%d` = "01" ]
then
subf="m"
elif [ `date +%u` = "7" ]
then
subf="w"
else
subf="d"
fi

cd $home

# Задаем имена sql и tar.gz файлов
sqlfile=$db'_'`date +%Y_%m_%d`'.sql'
tgzfile='back/'$subf'/'$sqlfile'.tgz'

# создаем дамп
mysqldump --host=$host --user=$user -p$pass $db > $sqlfile
# архивируем
tar -czf $tgzfile $sqlfile
# удаляем дамп
rm $sqlfile

Если создается копия большой базы данных, с большим количеством таблиц, то может быть получена ошибка вида:
Got error: 1016: Can’t open file: ‘база/таблица’ (errno: 24) when using LOCK TABLES
тогда в опции mysqldump следует добавить опцию —single-transaction:

mysqldump --single-transaction --host=$host --user=$user -p$pass $db > $sqlfile

  • Dima

    А куда класть на сервере этот .sh файл скрипта?

    • VladimirDolgov

      Положить можно куда угодно.. главное настроить права доступа и выполнение по расписанию.. и каждый раз копировать на другой сервер или на свой компьютер…

  • Виталий

    что делать если база 95 гигов, а места на диске 100 Ггб?

    можно ли резервную копию сразу на другой сервер залить?

    • VladimirDolgov

      Вы хотите дамп сразу на другой сервер?

      Всегда можно примонтировать другой сервер как папку по различным протоколам (например FTP) и сохранять в эту папку,

      можно с удаленного сервера подключаться к базе данных, и сохранять в файловой системе этого удаленного сервера. (Необходимо, чтобы на этом сервере был доступен shell )

      можно на удаленном сервере поднять mysql и делать репликацию (тогда правда в случае модификации данных, испорченные данные окажутся сразу же на втором сервере)

      Вообще, при работе с базами такого размера, чтобы не сохранять ежедневно по сто гигов резервной копии желательно вести лог запросов. Если есть возможность модифицировать программу, которая работает с этой базой — то в ней выбирать запросы изменяющие данные и записывать в файл. И его периодически копировать на другой сервер и очищать. Если программу модифицировать нельзя, можно в mysql включить query log, его периодически парсить откидывая select запросы и на удаленный сервер..
      Простор для творчества практически неограничен.
      У меня самого таких проблем не возникало, все базы в пределах 50МБ и я просто ежедневно делаю новую копию.