Пълното ръководство за mysqldump: Архивиране, възстановяване и автоматизация на MySQL база данни

mysqldump е помощна програма за командния ред, включена в MySQL и MariaDB, която генерира логически резервни копия, като сериализира обектите и данните на базата данни като поредица от SQL изрази. Полученият dump файл може да пресъздаде идентична база данни на всеки съвместим сървър, което го прави стандартния за индустрията инструмент за резервни копия, миграции между сървъри, надстройки на версии и работни процеси за възстановяване след бедствие.

За разлика от инструментите за физическо архивиране като Percona XtraBackup или MySQL Enterprise Backup, mysqldump работи на SQL слоя — чете живи данни чрез MySQL протокола и записва преносим, четим от човека SQL. Тази преносимост е неговото най-голямо предимство и, в голям мащаб, основното му ограничение.

Какво всъщност прави mysqldump под капака

Когато извикате mysqldump, клиентът се свързва с MySQL сървъра, прави заявки към информационната схема и речника на данните, и изпраща поток от `CREATE DATABASE`, `CREATE TABLE`, `INSERT` и DDL изрази към стандартния изход. Вие пренасочвате този поток към файл, тръба или помощна програма за компресия.

За таблици с InnoDB с `–single-transaction`, mysqldump отваря транзакция с повторяемо четене преди да прочете каквито и да е данни. Това ви дава последователна моментна снимка без придобиване на глобални заключвания за четене — базата данни остава напълно записваема по време на dump-а. За таблици с MyISAM такъв механизъм не съществува; mysqldump се връща към `FLUSH TABLES WITH READ LOCK`, което за кратко блокира записванията.

Разбирането на тази разлика е от решаващо значение, преди да изберете mysqldump за производствени натоварвания. Ако вашата схема смесва таблици InnoDB и MyISAM, `–single-transaction` само по себе си е недостатъчно — ще ви трябва `–lock-all-tables` или прозорец за поддръжка.

Предпоставки и необходими привилегии

Преди да изпълните каквато и да е команда за dump, проверете следното:

• MySQL или MariaDB е инсталиран и достъпен (локален сокет или TCP/IP).
• Потребителят за архивиране притежава минимално необходимите привилегии:
• `SELECT` за всички целеви таблици
• `LOCK TABLES` (необходимо, освен ако `–single-transaction` не се използва изключително с InnoDB)
• `SHOW VIEW` за включване на изгледи
• `TRIGGER` за включване на тригери
• `PROCESS` при използване на `–single-transaction` в MySQL 8+
• `RELOAD` за `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` ако ви трябват координати на двоичния журнал за настройка на репликация

Създайте специален потребител за архивиране, вместо да изпълнявате dump-ове като root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Изпълнението на mysqldump като root с вградена парола в командата на обвивката излага идентификационните данни в списъците с процеси и историята на обвивката — значителен риск за сигурността на всяка споделена или многопотребителска система.

Основен синтаксис

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Пълна справка за опциите

Опции за връзка

Опции за обхват

Опции за последователност и заключване

Опции за изход и съдържание

mysqldump срещу алтернативни методи за архивиране

Изборът на правилната стратегия за архивиране зависи от размера на базата данни, изискванията за RTO/RPO и инфраструктурата. Ето как mysqldump се сравнява с най-честите алтернативи:

За бази данни под 10–20 GB в среда за VPS Хостинг, mysqldump остава най-практичното и преносимо решение. Над този праг, инструментите за физическо архивиране предлагат значително по-бързи прозорци за архивиране и възстановяване.

Практически примери за употреба

Пример 1: Архивиране на единична база данни

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Заместването на `$(date +%F)` добавя автоматично ISO датата (напр. `2025-07-15`) към името на файла, предотвратявайки презаписване.

Пример 2: Архивиране на множество конкретни бази данни

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Флагът `–databases` кара mysqldump да генерира изрази `CREATE DATABASE` и `USE`, правейки dump-а самостоятелен за възстановяване.

Пример 3: Архивиране на всички бази данни

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Винаги включвайте `–events`, `–routines` и `–triggers` в dump-ове на целия сървър. Тези обекти се пропускат мълчаливо без изрични флагове.

Пример 4: Последователно архивиране на InnoDB (безопасно за производство)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` ротира двоичния журнал в началото на dump-а. `–source-data=2` записва текущото име на файла на двоичния журнал и позицията като SQL коментар, позволявайки възстановяване до конкретен момент чрез повторно изпълнение на последващи binlog-ове от тази позиция.

Пример 5: Компресирано архивиране с gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

За сървъри с ограничен CPU, заменете с `pigz` (паралелен gzip) за използване на множество ядра:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Пример 6: Архивиране само на схема (структура без данни)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Полезно за управление на версиите на вашата схема в Git или разгръщане в тестова среда без копиране на производствени данни.

Пример 7: Архивиране само на данни (без схема)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Използвайте това, когато целевата схема вече съществува и трябва само да попълните или обновите данните.

Пример 8: Архивиране на единична таблица

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Пример 9: Експортиране на филтрирано подмножество от редове

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Опцията `–where` се използва рядко, но е изключително мощна за частични експорти, архивиране на данни и отстраняване на грешки в конкретни набори от записи.

Пример 10: Изключване на конкретни таблици

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Изключването на големи, временни таблици (кешове, хранилища за сесии, таблици с журнали) може да намали размера и продължителността на dump-а с порядък.

Пример 11: Включване на съхранени процедури, функции и тригери

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Пример 12: Архивиране на отдалечена база данни

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

При архивиране на отдалечен сървър, трафикът преминава по мрежата некриптиран по подразбиране. Добавете флаговете `–ssl-ca`, `–ssl-cert` и `–ssl-key` или тунелирайте през SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Възстановяване на архивно копие от mysqldump

Възстановяване на единична база данни

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Ако целевата база данни все още не съществува, създайте я първо:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Възстановяване на всички бази данни от пълен dump на сървъра

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Тъй като `–all-databases` вгражда изрази `CREATE DATABASE` и `USE`, не е необходим аргумент за целева база данни.

Възстановяване от компресирано архивно копие

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Или чрез заместване на процес:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Възстановяване на единична таблица от пълен dump на база данни

Това е честа оперативна ситуация, която оригиналният dump файл прави нетривиална. Използвайте `sed` или `grep` за извличане на съответния раздел:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Алтернативно, използвайте `mysql_extract_table.sh` или импортирайте във временна база данни и копирайте таблицата:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Възстановяване до конкретен момент с помощта на двоични журнали

Ако вашият dump е направен с `–source-data=2` и двоичното журналиране е активирано, можете да възстановите до всеки момент след dump-а:

1. Идентифицирайте позицията в двоичния журнал от коментара в заглавието на dump файла.
2. Възстановете базовия dump.
3. Приложете последващите събития от двоичния журнал до желания времеви печат:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Автоматизиране на архивирането с Cron

Основна задача за ежедневно архивиране

Съхранявайте идентификационните данни в `~/.my.cnf`, вместо да ги вграждате в cron команди:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Задайте строги разрешения:

“`bash

chmod 600 ~/.my.cnf

“`

След това създайте cron задачата:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Скрипт за архивиране от производствен клас

За Dedicated Servers, хостващи множество бази данни, по-стабилен скрипт обработва регистрирането на грешки, проверките на дисковото пространство и отдалеченото разтоварване:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Укрепване на сигурността за операции с mysqldump

Управлението на идентификационни данни е най-често пренебрегваният аспект на сигурността при архивиране. Никога не предавайте `-pYourPassword` директно на командния ред — той е видим в изхода на `ps aux` и историята на обвивката. Вместо това използвайте един от следните подходи:

• `~/.my.cnf` с `chmod 600` (за конкретен потребител)
• `/etc/mysql/backup.cnf` с `chmod 640`, собственост на root, четим от групата за архивиране
• Променлива на средата `MYSQL_PWD` (видима в `/proc`, използвайте само в изолирани контейнери)
• MySQL Vault или HashiCorp Vault за корпоративни среди

Разрешенията за архивните файлове трябва да бъдат ограничителни:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Криптиране в покой: За чувствителни данни, криптирайте архивните файлове преди съхранение или прехвърляне:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Криптиране при пренос: При dump от отдалечен сървър, винаги използвайте SSL/TLS или SSH тунел. В среда VPS с cPanel, интерфейсът за архивиране на cPanel обработва това автоматично, но ръчните операции с mysqldump изискват изрични SSL флагове.

Чести грешки и как да ги избегнете

Несъответствия в набора от символи са най-честата причина за повредени възстановявания. Винаги посочвайте набора от символи изрично:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Липсващ `–column-statistics=0` причинява грешки, когато MySQL 8.0 клиент прави dump от MySQL 5.7 или MariaDB сървър. MySQL 8 клиентът се опитва да направи dump на статистики за колони, които по-старите сървъри не поддържат:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Забравяне на `–routines`, `–triggers` и `–events` мълчаливо пропуска критични обекти на базата данни. Тези флагове не са активирани по подразбиране (с изключение на `–triggers`) и често се забравят при ad-hoc dump-ове.

Dump-ове на големи таблици, причиняващи OOM: mysqldump буферира цели набори от резултати в паметта по подразбиране. За много големи таблици, добавете `–quick` (активирано по подразбиране в повечето версии, но си струва да се провери) за поточно предаване на редовете един по един, вместо буфериране:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Възстановяване в различна версия на MySQL: Dump-ове от MySQL 8.0 може да съдържат синтаксис, неподдържан в MySQL 5.7 (напр. функционални индекси, невидими колони). Винаги тествайте възстановяванията в среда с еднаква версия, преди да разчитате на миграции между версии.

Отклонение на стойността за автоматично нарастване: Ако възстановите таблица в съществуваща схема, която вече има редове, изразите `INSERT` ще се провалят при конфликти на първичен ключ, освен ако не включите `–add-drop-table` или ръчно не изтриете целевата таблица предварително.

Използване на mysqldump за миграции на бази данни

mysqldump е стандартният подход за мигриране на бази данни между сървъри — например при преместване на WordPress сайт от Споделен Уеб Хостинг към VPS, или преминаване към среда с VPS Контролни Панели с повече ресурси.

Препоръчителният работен процес за миграция:

1. Направете dump на изходната база данни с пълни опции:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Прехвърлете сигурно с rsync през SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Създайте целевата база данни със съответстващ набор от символи:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Възстановете и проверете:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Актуализирайте конфигурацията на приложението, за да сочи към новия хост на базата данни.

За приложения, които разчитат и на имейл инфраструктура, уверете се, че DNS записите и конфигурациите на Имейл Хостинг се актуализират паралелно с миграцията на базата данни, за да се избегне прекъсване на услугата.

Проверка на целостта на архивното копие

Архивно копие, което никога не е тествано, не е архивно копие — то е непроверено предположение. Внедрете рутина за проверка:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Изпълнявайте този скрипт за проверка седмично чрез cron и получавайте сигнал при неуспех.

Матрица за решения: Кога да използвате mysqldump

Контролен списък с технически ключови изводи

• Винаги използвайте `–single-transaction` за бази данни само с InnoDB, за да избегнете заключвания за запис по време на архивиране.
• Винаги включвайте `–routines –triggers –events` в dump, предназначен като пълно архивно копие.
• Съхранявайте идентификационните данни в `~/.my.cnf` или `/etc/mysql/backup.cnf` с `chmod 600/640` — никога директно в скриптове или cron команди.
• Добавете `–column-statistics=0` при използване на MySQL 8.0 клиент срещу MySQL 5.7 или MariaDB сървър.
• Винаги посочвайте `–default-character-set=utf8mb4` при dump и възстановяване, за да предотвратите повреда на кодирането на символи.
• Компресирайте всички архивни копия с gzip или pigz; криптирайте чувствителни dump-ове с AES-256 преди извънсайтово прехвърляне.
• Включвайте `–flush-logs –source-data=2` в производствените dump-ове, за да активирате възстановяване до конкретен момент чрез двоични журнали.
• Автоматизирайте почистването на задържаните копия с `find … -mtime +N -delete`, за да предотвратите изчерпване на диска.
• Тествайте възстановяванията по график — проверявайте броя на редовете и извадково проверявайте целостта на данните спрямо производството.
• За схеми със смесени машини, използвайте `–lock-all-tables` вместо `–single-transaction` за гарантиране на последователност.

Често задавани въпроси

Заключва ли mysqldump таблиците по време на архивиране?

С `–single-transaction` на чиста InnoDB база данни, не се придобиват заключвания на таблици освен кратко начално изчистване. Таблиците MyISAM винаги изискват заключване за четене (`LOCK TABLES`), тъй като нямат поддръжка на транзакции. Схемите със смесени машини изискват `–lock-all-tables` за последователна моментна снимка, което блокира записванията за продължителността на dump-а.

Как да архивирам само схемата на базата данни без никакви данни?

Използвайте флага `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Това експортира всички изрази `CREATE TABLE`, `CREATE VIEW`, съхранени процедури и тригери без никакви изрази `INSERT`.

Защо mysqldump се проваля с грешки за „column statistics”?

Това се случва, когато MySQL 8.0 клиент се свързва с MySQL 5.7 или MariaDB сървър. Добавете `–column-statistics=0` към вашата команда. Алтернативно, актуализирайте сървъра до MySQL 8.0 или използвайте клиентски двоичен файл, съответстващ на версията на сървъра.

Може ли mysqldump да извършва инкрементални архивирания?

Не. mysqldump винаги произвежда пълен логически dump на посочения обхват. Възможността за инкрементално архивиране изисква архивиране на двоичен журнал (`mysqlbinlog`) в комбинация с базов mysqldump, направен с `–flush-logs –source-data=2`. Истинските инкрементални физически архивирания изискват Percona XtraBackup или MySQL Enterprise Backup.

Какъв е най-безопасният начин за автоматизиране на mysqldump без излагане на пароли?

Създайте специален MySQL потребител за архивиране с минимално необходимите привилегии, съхранете неговите идентификационни данни в раздел `[mysqldump]` на `~/.my.cnf` или отделен файл с опции с `chmod 600`, и го реферирайте с `–defaults-file=/path/to/backup.cnf`. Този подход поддържа идентификационните данни изцяло извън списъците с процеси, историята на обвивката и дефинициите на cron задачите.