Как сделать резервную копию большой базы данных MySQL в Linux

Существует ряд возможных методов резервного копирования, описанных в Документы MySQL. В вашем случае я однозначно рекомендую метод, описанный в разделе «Создание резервных копий с помощью снимка файловой системы», где вы запускаете FLUSH TABLES WITH READ LOCK, делаете снимок файловой системы (LVM или иначе) и РАЗБЛОКИРУЕТ ТАБЛИЦЫ. После этого вы можете просто скопировать файлы базы данных на резервный носитель.

Остальные варианты явно уступают. Mysqldump будет создавать длительные всплески нагрузки и блокировки таблиц, которые будут препятствовать вашим INSERT в течение длительного времени, особенно с очень большими таблицами.

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

Лучше всего создать другой сервер MySQL и настроить его в режиме master / master. Это не только предоставит вам решение для резервного копирования в реальном времени, но также и аварийное переключение.

Понимание того, как выполнять резервное копирование с помощью mysqldump

IMHO Создание резервных копий стало скорее видом искусства, если вы знаете, как к этому подойти.

У вас есть варианты

Вариант 1: mysqldump весь экземпляр mysql

Это самый простой вариант !!!

mysqldump -h... -u... -p... --routines --triggers --all-databases | gzip > MySQLData.sql.gz

Все записано в одном файле: структуры таблиц, индексы, триггеры, хранимые процедуры, пользователи, зашифрованные пароли. Другие параметры mysqldump также могут экспортировать различные стили команд INSERT, файл журнала и координаты положения из двоичных журналов, параметры создания базы данных, частичные данные (параметр --where) и т. Д.

Вариант 2: mysqldump разделяет базы данных в отдельные файлы данных

Начните с создания списка баз данных (для этого 2 метода)

Техника 1.

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Техника 2.

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Методика 1 - самый быстрый способ. Техника 2 - самая надежная и безопасная. Метод 2 лучше, потому что иногда пользователи создают папки общего назначения в / var / lib / mysql (datadir), которые не связаны с базой данных. Information_schema зарегистрирует папку как базу данных в таблице information_schema.schemata. Метод 2 позволяет обойти папки, не содержащие данных mysql.

После того, как вы скомпилировали список баз данных, вы можете переходить к циклическому просмотру списка и mysqldump их, даже параллельно, если хотите.

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

Если существует слишком много баз данных для одновременного запуска, параллельный дамп 10 за раз:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Вариант 3: mysqldump отдельные таблицы в отдельные файлы данных

Начните с создания списка таблиц

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

Затем выгрузите все таблицы группами по 10

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump -h... -u... -p... --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Вариант 4: ИСПОЛЬЗУЙТЕ ВООБРАЖЕНИЕ

Попробуйте варианты вышеупомянутых опций плюс методы для чистых снимков

Примеры

1. Упорядочивайте список таблиц по размеру каждой таблицы по возрастанию или убыванию.
2. Используя отдельный процесс, запустите «FLUSH TABLES WITH READ LOCK; SELECT SLEEP (86400)» перед запуском mysqldumps. Завершите этот процесс после завершения mysqldumps.
3. Сохраните mysqldump в устаревших папках и переверните старые резервные копии.
4. Загрузите весь экземпляр mysqldumps на автономные серверы.

ПРЕДОСТЕРЕЖЕНИЕ

Только Вариант 1 приносит все. Недостатком является то, что mysqldump, созданный таким образом, может быть перезагружен только в ту же самую большую версию mysql, что и mysqldump. Другими словами, mysqldump из базы данных MySQL 5.0 не может быть загружен в 5.1 или 5.5. Причина ? Схема mysql полностью отличается от основных выпусков.

Варианты 2 и 3 не включают сохранение имен пользователей и паролей.

Вот общий способ сбросить гранты SQL для пользователей, которые читабельны и более переносимы.

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Вариант 3 не сохраняет хранимые процедуры, поэтому вы можете сделать следующее

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

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

Примерно за 1 час до выполнения резервного копирования запустите эту команду SQL

SET GLOBAL innodb_max_dirty_pages_pct = 0;

В MySQL 5.5 значение innodb_max_dirty_pages_pct по умолчанию равно 75. В MySQL 5.1 и более ранних версиях значение innodb_max_dirty_pages_pct по умолчанию равно 90. Установка innodb_max_dirty_pages_pct на 0 ускоряет сброс грязных страниц на диск. Это предотвратит или, по крайней мере, уменьшит влияние очистки любых неполных двухфазных коммитов данных InnoDB перед выполнением любого mysqldump для любых таблиц InnoDB.

ЗАКЛЮЧИТЕЛЬНОЕ СЛОВО НА mysqldump

Большинство людей избегают mysqldump в пользу других инструментов, и эти инструменты действительно хороши.

К таким инструментам относятся

1. MAATKIT (параллельно свалка/восстановить скрипты от Percona [устарело, но отлично])
2. XtraBackup (Резервное копирование снимков TopNotch от Percona)
3. CDP R1Soft (Вариант модуля MySQL который делает моментальные снимки на определенный момент времени)
4. MySQL Enterprise Резервное копирование (ранее InnoDB Hot Backups [коммерческое])

Если у вас есть дух настоящего администратора баз данных MySQL, вы можете принять mysqldump и полностью овладеть им. Пусть все ваши резервные копии будут отражением ваших навыков администратора баз данных MySQL.

Если все ваши таблицы InnoDB, вы должны использовать Xtrabackup который обеспечивает онлайн-резервную копию. Вы также можете делать снимки LVM, но проблемы с производительностью снимков LVM по-прежнему предполагают, что Xtrabackup является рекомендуемым решением.

Ура