Home » Полезные советы » Бэкап всех баз mysql в отдельные файлы

Бэкап всех баз mysql в отдельные файлы

Небольшая рекомендация по быстрому бэкапу всех mysql баз в отдельные файлы. Возникла идея так сделать после того, как прилично повозился над тем, чтобы восстановить одну базу в отдельности из дампа, в котором были все базы данных сервера.

Теоретический курс по основам сетевых технологий. Позволит системным администраторам упорядочить и восполнить пробелы в знаниях. Цена очень доступная, есть бесплатный доступ. Все подробности по . Можно пройти тест на знание сетей, бесплатно и без регистрации.

Самый простой способ сделать бэкап всех базы с помощью mysqldump следующий:

# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases | /usr/bin/gzip -c > /backup/mysql/`date "+%Y-%m-%d"`.gz

На выходе будет один сжатый файл с именем вида 2018-09-27.gz, в котором будет дамп всех mysql баз сервера, в том числе служебных таблиц. Работать с таким файлом потом очень неудобно. Это подходит только для случая, когда вы на таком же сервере будете восстанавливать все базы данных.

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

# mysql -u root -p --one-database destdbname < alldatabases.sql

У меня она не заработала. Консольная mysql выдавала ошибку. Я не стал подробно разбираться. Затем попробовал с помощью sed выдернуть информацию только о нужной базе, но сходу не получилось, так как дамп был большой. Просто просмотреть его была проблема, а тренироваться на тестовых дампах - тратить лишнее время. Проще было пойти на исходный сервер и вытащить только нужную базу данных.

Заодно заменил скрипт бэкапа на следующий:

for i in `mysql -uroot -p'password' -e'show databases;' | grep -v information_schema | grep -v Database`; 
    do 
	/usr/bin/mysqldump -uroot -p'password' $i | /usr/bin/gzip -c > /backup/mysql/`date +%Y-%m-%d`-$i.sql.gz;
    done

Сначала кладем в массив список всех баз данных, а потом делаем отдельный дамп каждой из них и сжимаем.

Был бы рад, если бы кто-то подсказал простой и рабочий способ загрузить на сторонний сервер одну базу данных из общего дампа. Очень часто приходится видеть такие бэкапы от всяких панелей управления или самодельных скриптов.

Супер-интенсив "Tarantool"

Если у вас есть желание освоить платформу in-memory вычислений, востребованную в современных высоконагруженных приложениях, рекомендую пройти интенсив Tarantool в OTUS. Обучение длится 5 дней. Что даст вам этот интенсив:
  • Узнаете архитектуру и внутреннее устройство Tarantool.
  • Поймете сильные и слабые стороны Tarantool.
  • Сможете назвать сходства и отличия от других СУБД.
  • Увидите кейсы использования: куда брать, куда не брать.
  • Установите и запустите Tarantool.
  • Поднимете собственный кластер.
Смотрите подробнее программу по .

Помогла статья? Подписывайся на telegram канал автора

Анонсы всех статей, плюс много другой полезной и интересной информации, которая не попадает на сайт.

Автор Zerox

Zerox
Владимир, системный администратор, автор сайта. Люблю настраивать сервера, изучать что-то новое, делиться знаниями, писать интересные и полезные статьи. Открыт к диалогу и сотрудничеству. Если вам интересно узнать обо мне побольше, то можете послушать интервью. Запись на моем канале - https://t.me/srv_admin/425 или на сайте в контактах.

15 комментариев

  1. Аватар

    Для бэкапов больших баз (200-300Гб) используем Percona XtraBackup, мощный инструмент умеет в s3, но не умеет восстанавливать отдельные базы. Для отдельных баз многопоточно умеет https://github.com/maxbube/mydumper/

    • Zerox

      Разве не умеет отдельные базы? В документации есть описание этой процедуры. Я тоже пробовал, работает и по отдельности. Но нужно саму базу данных бэкапить, а не полный бэкап. Хотя из полного тоже по-моему можно восстановить бэкап отдельной базы и даже таблицы.

  2. Аватар

    Странно, но у меня все работает.
    Правда надо создать новую пустую БД с таким же именем, который в дампе.

    root@host:~# mysql
    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]> create database mycooldb;
    Query OK, 1 row affected (0.00 sec)

    MariaDB [(none)]> exit
    Bye
    root@host:~# zcat /path/to/mysqldump.sql.gz | mysql -u root --one-database mycooldb

  3. Leonid Mamchenkov

    Я пользуюсь вот этот утилитой - https://sourceforge.net/projects/automysqlbackup/ . Ставится и запускается за три секунды, а результат гораздо лучше, чем всё то, что я наскриптовал сам. Она умеет и ротацию бэкапов делать, и запускать комманды до и после бэкапа (например для включения /etc/my.cnf в бэкап), и криптовать/подписывать бэкапы, и даже отдельно бэкапить схему баз, что тоже быает полезно.

    Кстати для бэкапа и восстановления больших баз (от нескольких десятков гигов и более), лучше пользоваться специализированным софтом, типа MySQL Enterprise Backup. Там скорость совсем другая. Вот тут же есть и сравнительные графики по скорости на 73-гиговой базе:

    https://www.mysql.com/products/enterprise/backup.html

    • Zerox

      За утилиту спасибо, посмотрю. Я иногда вот этим пользуюсь - https://github.com/adegtyarev/mysqlbackup
      А для больших баз уже свои подходы нужны. Не очень представляю, как сдампить наживую 73гб базу. Для этого надо slave настраивать и бэкапить с него.

      • Leonid Mamchenkov

        Да, со slave-ом проще. Можно на нём во время бэкапа гасить mysql, и бэкапить /var/lib/mysql. Это значительно быстрее, чем mysqldump.

        • Zerox

          Бэкап сырых данных баз и дамп это разные вещи. Мне кажется, копировать файлы это менее надежный механизм, нежели полноценный дамп. Если где-то какие-то проблемы в базе, то дамп это выявит. А так можно скопировать битые таблицы и даже не узнаешь об этом.

  4. Аватар

    Например, если таблица mysam будет битая. Mysqldump отработает с ошибкой.

  5. Аватар

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

    В скрипте отсутствует проверка на успешность выполненной команды.

    Пример правильного скрипта:

    for i in `mysql -uroot -p'password' -e'show databases;' | grep -v information_schema | grep -v Database`;
    do
    /usr/bin/mysqldump -uroot -p'password' $i | /usr/bin/gzip -c > /backup/mysql/`date +%Y-%m-%d`-$i.sql.gz;
    if [ "$?" != "0" ]; then
    echo "Error: Dump DB $i"
    else
    echo "Successful: Dump DB $i"
    fi
    done

    Иначе ждут сюрпризы.

    • Zerox

      Спасибо, замечание по существу, но в целом в моей практике у меня никогда не возникало ситуаций, что dump не выполнялся. Что его может остановить? А сами бэкапы я мониторю не выводами от работы скрипта, а физически наличием дампа и контролем его размера.

  6. Аватар
    Аноним

    Примерно также делал. Была нужда.

    #!/bin/bash
    
    suffix=$(date +%F)
    dbs=$(sudo mysql --defaults-extra-file=/root/.my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema")
    tmp=$(mktemp -d)
    outDir="/var/backup/mysql"
    out="$outDir/$suffix.tar.bz2"
    
    if [ ! -d "$outDir" ];then
      mkdir -p "$outDir"
    fi
    
    for db in $dbs; do
      mysqldump --defaults-extra-file=/root/.my.cnf --databases "$db" > "$tmp/$db.sql"
    done
    
    cd $tmp
    tar -jcf "$out" * > "/dev/null"
    cd "/tmp/"
    sudo rm -rf "$tmp"
    • Zerox

      Вариантов тут много. У меня самого скриптов 10 разных наберется по бэкапу mysql. Иногда приходится работать с полными бэкапами, которые делал не сам и других нет. Интересно, как другие выкручиваются в подобных ситуациях.

      • Аватар
        Аноним

        На днях занимался корректным резервированием базы на виртуалке в Azure при создании резервной копии всего сервера. Нагуглил вот такое решение - https://github.com/MicrosoftAzureBackup/MySQL . Там вообще потабличный дамп делается.
        Ну а по поводу выцепить дамп отдельной базы из общего - самый простой вариант видится - экспортнуть дамп на mysql и сделать дамп уже как надо. Ну и переделать алгоритм резервирования, чтобы впредь одним файлом дамп не снимался.
        Да, решение примитивное и что называется "в лоб", но что называется тут и сейчас :)

        • Zerox

          Кстати, это тоже первое, что мне пришло в голову сразу. Это реально может оказаться самым быстрым способом.

    • Аватар

      Как в примере выше тоже задался вопросом как делать бэкап всех баз, кроме некоторых, при этом необязательно системных, в итоге список баз выбирается так:
      #!/bin/bash

      ...
      DBS="$(mysql -u $db_user -h $db_host -p$db_pass -Bse 'SELECT `SCHEMA_NAME` from `INFORMATION_SCHEMA`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT in ("test", "mysql", "information_schema");')"
      for db in $DBS; do
      ...

Добавить комментарий

Ваш адрес email не будет опубликован.

Нажимая кнопку "Отправить комментарий" Я даю согласие на обработку персональных данных.