Импортиране и експортиране на MySQL бази данни чрез командния ред

Овладяването на операциите за импортиране и експортиране на MySQL бази данни от командния ред е задължително умение за всеки администратор на бази данни или бекенд инженер. Помощната програма `mysqldump` експортира база данни в преносим `.sql` файл, съдържащ всички DDL и DML изрази, необходими за пълно възстановяване на схемата и данните, докато клиентската команда `mysql` извършва обратното — предава поточно този файл обратно към работещ MySQL екземпляр.

Това ръководство обхваща всеки практически сценарий: експортиране на единична база данни, дъмпове на множество бази данни, резервни копия само на структурата, компресирани трансфери, обработка на набори от символи и безопасни работни процеси за импортиране — включително гранични случаи, които причиняват безшумно повреждане на данни или неуспешни възстановявания в производствени среди.

Предварителни изисквания

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

• MySQL Server (5.7, 8.0 или 8.4) е инсталиран и процесът `mysqld` работи
• Двоичните файлове `mysqldump` и `mysql` са в системния `PATH` (потвърдете с `which mysqldump`)
• Притежавате MySQL акаунт с минимум привилегии `SELECT`, `LOCK TABLES`, `SHOW VIEW` и `TRIGGER` за експортиране; `CREATE`, `INSERT`, `ALTER` и `DROP` за импортиране
• На дестинацията има достатъчно дисково пространство — компресиран дъмп може да се разшири 5–10 пъти при импортиране
• Имате достъп до обвивката на сървъра (локален терминал, SSH или управлявана среда за VPS Хостинг)

Експортиране на бази данни с mysqldump

`mysqldump` е каноничният инструмент за логическо архивиране, включен в MySQL. Той сериализира обектите на базата данни в четим от човека SQL скрипт. За разлика от инструментите за физическо архивиране като Percona XtraBackup, `mysqldump` е независим от механизма за съхранение и работи с различни версии на MySQL и дори с разклонения на MariaDB.

1. Експортиране на единична база данни

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Разбивка на параметрите:

• `-u [username]` — MySQL акаунт, използван за връзката
• `-p` — Задейства интерактивна подкана за парола (никога не предавайте паролата директно като `-p[password]` на споделени системи; тя е видима в изхода на `ps aux`)
• `[database_name]` — Целева схема за експортиране
• `> [filename].sql` — Пренасочва stdout към изходния файл

Пример:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

Това създава файл, съдържащ изрази `CREATE TABLE`, `INSERT` и `ALTER TABLE`, достатъчни за пресъздаване на `mydatabase` от нулата.

Критичен граничен случай: По подразбиране `mysqldump` придобива глобална заключване за четене (`FLUSH TABLES WITH READ LOCK`) в началото на дъмпа. На InnoDB сървъри с голям трафик използвайте вместо това `–single-transaction`, за да направите последователна снимка без блокиране на записите:

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` работи надеждно само с InnoDB таблици. Ако базата данни съдържа MyISAM таблици, заключването е неизбежно.

2. Експортиране на множество бази данни

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

Флагът `–databases` инструктира `mysqldump` да включи изрази `CREATE DATABASE` и `USE` в изхода, правейки дъмпа самостоятелен. Без този флаг тези изрази се пропускат и дъмпът предполага, че целевата база данни вече е избрана по време на импортиране.

Пример:

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Експортиране на всички бази данни

“`bash

mysqldump -u root -p –all-databases > all_databases_backup.sql

“`

Това експортира всяка схема, до която свързващият се потребител има достъп, включително системните бази данни `mysql`, `information_schema` и `performance_schema`. Избягвайте импортирането на системни бази данни между основни версии на MySQL — схемата на таблицата с привилегии се промени значително между MySQL 5.7 и 8.0, и импортирането на старата схема `mysql` в нов екземпляр 8.0 ще повреди удостоверяването.

За да изключите системните схеми:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Експортиране само на структурата на таблицата (без данни)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

Това е безценно за контрол на версиите на схемата, прегледи на кода или стартиране на празна staging среда. Изходът съдържа само DDL като `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` и подобни — без редове `INSERT`.

5. Експортиране на конкретни таблици

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Имайте предвид, че при експортиране на конкретни таблици по този начин, `mysqldump` не включва изрази `CREATE DATABASE` или `USE`. Трябва да се уверите, че целевата база данни съществува преди импортирането.

6. Експортиране със съхранени процедури, тригери и събития

По подразбиране `mysqldump` включва тригери, но пропуска съхранени процедури, функции и планирани събития. За пълно архивиране на приложение:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Забравянето на `–routines` е една от най-честите причини за неуспешни възстановявания на приложения — схемата и данните са налице, но бизнес логиката липсва.

7. Компресирано експортиране

Предайте изхода директно през `gzip`, за да намалите размера на файла с 60–80%:

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

За максимална компресия при големи бази данни (за сметка на процесорно време):

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Импортиране на бази данни с mysql клиента

Клиентът от командния ред `mysql` приема SQL файл чрез пренасочване на stdin и изпълнява всеки израз последователно спрямо целевия сървър.

1. Импортиране в съществуваща база данни

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Пример:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Важно: Ако `.sql` файлът е бил експортиран с `–databases` или `–all-databases`, той вече съдържа директиви `CREATE DATABASE` и `USE`. В такъв случай не указвайте име на база данни в командния ред — това създава конфликт:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Импортиране в нова база данни

Целевата база данни трябва да съществува преди да можете да импортирате в нея. MySQL няма да я създаде автоматично от обикновен дъмп на таблица.

Стъпка 1 — Създайте базата данни:

“`bash

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

“`

Стъпка 2 — Импортирайте дъмпа:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Винаги указвайте набора от символи и съпоставянето изрично при създаването на базата данни. Разчитането на сървърните настройки по подразбиране е честа причина за несъответствия в кодирането, особено при мигриране между сървъри с различни конфигурации `character_set_server`.

3. Импортиране на компресиран дъмп

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Или с `zcat` (еквивалентно в повечето Linux дистрибуции):

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Импортиране с наблюдение на напредъка

При големи дъмпове стандартният `mysql` клиент не дава обратна връзка. Използвайте `pv` (pipe viewer), за да покажете лента за напредък в реално време:

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Инсталирайте `pv` с `apt install pv` или `yum install pv`. На Dedicated Server, обработващ производствени бази данни с много гигабайти, тази видимост е оперативно критична.

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

За среди, работещи с множество производствени бази данни на управляван VPS с cPanel, `mysqldump` остава най-универсално поддържаната опция поради своята съвместимост и простота.

Разширена конфигурация и гранични случаи

Правилна обработка на набори от символи

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

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Забележка: `utf8` в MySQL е 3-байтово подмножество, което не може да съхранява 4-байтови Unicode символи (емоджи, определени CJK идеографи). Винаги използвайте `utf8mb4` за нови бази данни.

Ускоряване на големи импортирания

По подразбиране MySQL извършва пълен commit след всеки израз `INSERT` в дъмпа. При големи набори от данни това е катастрофално бавно. Добавете следното в началото на вашата сесия за импортиране:

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Алтернативно, експортирайте с `–extended-insert` (активирано по подразбиране) и `–disable-keys`, за да групирате вмъкванията и да отложите възстановяването на индексите до след зареждането на данните.

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

Запис за автоматизирано архивиране от производствен клас в `/etc/cron.d/mysql-backup`:

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Използвайте специален MySQL потребител с минимално необходимите привилегии вместо `root`. Създайте го с:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Защита на идентификационните данни с .my.cnf

Предаването на пароли в командния ред ги излага в историята на обвивката и списъците с процеси. Съхранявайте идентификационните данни в `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Задайте разрешенията незабавно:

“`bash

chmod 600 ~/.my.cnf

“`

С това на място всички команди `mysqldump` и `mysql` автоматично вземат идентификационните данни без флаговете `-u` и `-p`.

Експортиране на отдалечена база данни

За дъмп на база данни от отдалечен MySQL сървър:

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Уверете се, че отдалеченият MySQL екземпляр позволява връзки от вашия IP адрес и че порт 3306 е отворен в защитната стена. За криптирани трансфери, тунелирайте през SSH:

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Практическа матрица за вземане на решения

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

• Винаги използвайте `–single-transaction` за InnoDB бази данни, за да избегнете блокиране на записите на приложението по време на експортиране
• Винаги указвайте `utf8mb4` изрично — никога не разчитайте на предположенията за набора от символи по подразбиране на сървъра
• Включвайте `–routines`, `–triggers` и `–events` във всяко пълно архивиране на приложение, иначе рискувате да загубите бизнес логиката
• Никога не импортирайте таблици на системната схема (`mysql.*`) между основни версии на MySQL
• Съхранявайте идентификационните данни в `~/.my.cnf` с `chmod 600` — никога не предавайте пароли като вградени аргументи
• За бази данни, надвишаващи 10 GB, оценете `mysqlpump` или паралелните помощни програми за дъмп на MySQL Shell, тъй като `mysqldump` ще се превърне в тесно място
• Проверявайте всяко архивиране, като извършвате тестово възстановяване в изолирана среда, преди да разчитате на него за аварийно възстановяване
• При хостване на множество клиентски бази данни, изолирайте средите с помощта на отделни VPS Control Panels, за да предотвратите достъп между наематели по време на операции по възстановяване
• Съчетайте стратегията си за архивиране на бази данни с валиден SSL сертификат на всеки уеб-ориентиран слой на приложението, който се свързва с MySQL, за да предотвратите прихващане на идентификационни данни при пренос

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

Каква е разликата между mysqldump и mysqlpump?

`mysqldump` е еднонишков и създава единичен SQL файл — надежден и универсално съвместим. `mysqlpump`, въведен в MySQL 5.7.8, поддържа паралелно експортиране на множество бази данни и таблици едновременно, значително намалявайки времето за дъмп на многоядрени сървъри. Въпреки това `mysqlpump` има известни проблеми с последователните архиви на бази данни със смесени механизми и е по-малко подходящ за миграции между версии.

Мога ли да импортирам дъмп от MySQL 5.7 в MySQL 8.0?

Да, с уговорки. Потребителските данни и схемите на приложенията се импортират без проблеми. Въпреки това никога не импортирайте системната база данни `mysql` директно — плъгинът за удостоверяване се промени от `mysql_native_password` на `caching_sha2_password` в 8.0, и импортирането на стари таблици с привилегии ще наруши удостоверяването. Пресъздайте потребителите ръчно с изрази `CREATE USER` и `GRANT`.

Защо импортирането ми се проваля с "ERROR 1005: Can't create table" поради ограничения на външния ключ?

Това се случва, когато таблиците се импортират в ред, който нарушава зависимостите на външния ключ. Решението е да добавите `SET foreign_key_checks = 0;` в началото на вашата сесия за импортиране и да добавите `SET foreign_key_checks = 1;` след завършване. Алтернативно, експортирайте с `–single-transaction`, което запазва референтната цялост в самия дъмп файл.

Как да експортирам само данните без изразите CREATE TABLE?

Използвайте флага `–no-create-info`: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Това е полезно, когато трябва да презаредите данни в съществуваща схема, без да променяте нейната структура.

Какъв е най-безопасният начин за прехвърляне на MySQL дъмп между два отдалечени сървъра?

Предайте дъмпа директно през SSH без записване на междинен файл на диска: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. Това е едновременно по-бързо и по-сигурно от копирането на обикновен `.sql` файл, особено при работа в среда за Споделен уеб хостинг, където дисковите квоти са ограничение.