Создание дампа базы и восстановление данных

Для создания дампа (резервной копии базы данных) и восстановления данных вы можете воспользоваться phpMyAdmin либо консольными командами при подключении по SSH.

Создание дампа базы через PMA

Экспортирование базы данных — это создание резервной копии базы данных и ее загрузка на локальный компьютер.

Чтобы осуществить быстрый экспорт базы данных, выполните следующее:

1. В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).
2. Перейдите на вкладку «Экспорт» в меню PMA.
3. Нажмите кнопку «Вперед», и файл дампа базы данных сохранится на локальный компьютер.

Экспорт отдельных таблиц

1. В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).
2. Отметьте галочками нужные таблицы для экспорта.
3. Внизу страницы в выпадающем списке «С отмеченными:» выберите пункт «Экспорт».
4. На следующей странице убедитесь, что выбран формат SQL, и нажмите на кнопку «Вперед». Дамп будет сохранен на ваш компьютер.

Создание дампа базы через консоль

Подключитесь к серверу по SSH и выполните команду, указав корректные данные:

mysqldump -u'имя_пользователя' база_данных -p'пароль_базы_данных' > имя_дампа.sql

Восстановление данных через PMA

Импортирование базы данных — это загрузка сохраненной на локальном компьютере базы данных на сервер.

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

1. В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).
2. Перейдите на закладку «Импорт» в меню PMA.
3. Загрузите импортируемый файл (дамп базы данных), нажав на кнопку «Выберите файл» и указав файл на локальном компьютере.
4. Выберите кодировку, формат и проверьте другие настройки импорта (можно оставить по умолчанию).
5. Нажмите кнопку «Вперед», и дамп будет импортирован в базу данных.

При загрузке дампов баз данных размером более 1 Гб могут наблюдаться проблемы. В случае, если ваш дамп имеет больший размер, вы можете выполнить импорт через командную строку с помощью подключения по SSH.

Восстановление данных через консоль

Импортировать дамп в созданную базу данных можно с помощью команды:

mysql -u'имя_пользователя' база_данных -p'пароль_базы_данных' < имя_дампа.sql

Summary: in this tutorial, you will learn how to use the mysqldump tool to make a backup one or more databases in a MySQL Server.

Introduction to mysqldump tool

The mysqldump tool allows you to make a backup of one or more databases by generating a text file that contains SQL statements which can re-create the databases from scratch.

The mysqldump tool is located in the root/bin directory of the MySQL installation directory.

Here are the common mysqldump options:

add-drop-table

Includes a DROP TABLE statement for each table in the database.

add-locks

Includes LOCK TABLES and UNLOCK TABLES statements before and after each INSERT statement. It improves the data restoration speed from the dump file.

all-databases

Creates a dump of all databases on the MySQL server.

create-options

Includes ENGINE and CHARSET options in the CREATE TABLE statement for each table.

databases

Creates a dump of one or more databases.

disable-keys

Instructs MySQL to disable index updates during data load for MyISAM tables. MySQL will create indexes after mysqldump completes loading data. This option improves the speed of restoration.

extended-insert

Combines single-row INSERT statements into a single statement that insert multiple table rows; This option also helps speed up data restoration.

flush-logs

Flushes the server logs before dumping the data. This is useful in conjunction with incremental backups.

lock-tables

Ensures that the dump is a consistent snapshot by locking all the tables in a database during the dump.

no-data

opt

The mysqldump tool uses the opt by default.

To disable this option, you use skip-opt. If you want to skip each individual option, you use skip-<option_name>. For example, to skip the disable-keys option, you use skip-disable-keys option.

quick

Instructs mysqldump to not buffer tables in memory before writing to the file. This option speeds up dumps from big tables.

result-file

Specifies the path to the output dump file.

set-charset

Specifies the character set such as latin1 or utf8 of the database.

tables

Creates a dump of one or more tables.

where

Dumps only rows which satisfies a condition in the WHERE clause.

Читайте также:  Почему не работает PHP на хостинге? - Hostings.info

Using the mysqldump tool to make a backup of databases

Let’s take some examples of using the mysqldump tool to backup database examples.

1) Using the mysqldump tool to make a backup of a single database

 SQL (Structured Query Language) 

In this syntax:

• The <username> is the user account that will login to the MySQL Server.
• The <password> is the password for the <username>.
• The <path_to_backup_file> is the path to the backup file.
• The --databases is an option that instructs the mysqldump tool to create a dump of the specified databases.
• The <database_name> is the name of the database that you want to back up.

2) Using the mysqldump tool to make a backup of multiple databases

To make a backup of multiple databases, you specify a list of the database names after the --database option:

3) Using the mysqldump tool to make a backup of all databases

To make a backup of all databases in a MySQL Server, you use the –all-database option:

4) Using the mysqldump tool to make a backup of specific tables from a database

You can also specify multiple tables after the database name, one after the other:

5) Using a mysqldump tool to make a backup of database structure only

To make a backup of the database structure only, you use the --no-data option:

The statement will generate a dump file that contains the SQL statement necessary to re-create the database structure. And the dump file does not contain INSERT statements.

6) Using the mysqldump tool to make a backup of data only

To make a backup of the database data only, you use the --no-create-info option:

The statement will generate a dump file that contains the SQL statements necessary to lock tables and insert data into the tables. It has no CREATE TABLE statements.

In this tutorial, you have learned how to use the mysqldump tool to make a backup of databases in MySQL Server.

Was this tutorial helpful?

• Резервное копирование MySQL: инструкция

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

Синтаксис команд

Создать дамп базы данных можно через командную строку с помощью утилиты mysqldump. Ее устанавливают вместе с пакетом MySQL (идет в стандартном комплекте). Возможности одинаковы на Linux и на Windows. Запуск приложения допускается как на локальном, так и на удаленном сервере СУБД. Общий синтаксис:

mysqldump -v -h127.0.0.1 -uroot base -p > /tmp/dump.sql

Здесь мы сделали резервный архив БД под названием base, разместили его в каталоге /tmp. Коннект осуществляется к локальной базе (IP 127.0.0.1) под аккаунтом root. Это был простейший вариант по созданию дампа для резервного копирования базы MySQL. Теперь перейдем к описанию базовых параметров утилиты mysqldump:

1. -h – адрес хоста, куда требуется подключаться для создания дампа.
2. -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
3. -p – пароль используемого аккаунта. При работе через скрипт его вводят в формате p12345, в остальных случаях лучше оставить только символ -p, это безопаснее.

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

Приведем варианты команд для нескольких частных случаев.

Мы сделали переменную DBNAME, где затем разместили значение «имя базы», подлежащей архивации. И только потом запустили утилиту mysqldump, чтобы по завершении передать очередь исполнения архиватору GZIP. 

Итог: дамп, размещенный в каталоге /tmp/<имя базы>.sql.gz. Если пользователю надо, чтобы процесс отображался на экране, команда примет вид:

Одновременная архивация двух и более баз

Задача простая, достаточно перечислить названия БД через пробел и использовать опцию -B. Вот так будет выглядеть команда:

mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiplе_bases.sql

Заархивировать все имеющиеся базы

Также можно запускать резервное копирование баз данных MySQL при помощи параметра —all-databases, указывая его вместо имени базы. Пример команды:

mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql

Резервировать только структуру БД

Важно учитывать, что в таком режиме в архив будут сохранены таблицы без каких-либо данных. Режим выбирается при помощи параметра —no-data:

mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql

Создать резервную копию указанной таблицы

В этом случае к имени базы добавляет наименование таблиц. Например, как в команде:

mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql

Перенос прав доступа к СУБД

Система способна выгрузить действующие учетки вместе с актуальными паролями. Такая функция нужна для экспорта СУБД на другой хост без риска утраты доступа к базе. Команда будет выглядеть так:

Чтобы обеспечить работоспособность после переноса при помощи такого дампа, нужно выполнить команду flush privileges (через SQL Shell).

При архивации игнорировать указанную таблицу

Подразумевается выполнение команды с ключом ignore-table:

mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql

Резервирование под любой учетной записью

Примеры, приведенные выше, были рассчитаны на подключение к БД при помощи пользователя root. На практике же процедуру выполняют и под другими учетками. Главное, чтобы у выбранного пользователя были права для доступа к базе, дамп которой планируется создать. Можно создать отдельный аккаунт с ограниченными правами для выполнения архивации. Выполним это командой SQL:

Здесь мы создали учетку backup, задали пароль backup123. Прав у пользователя будет достаточно, чтобы подключиться к БД на локальном сервере. Чтобы не вводить вручную при каждом запуске одни и те же параметры, можно записать файл конфигурации:

Теперь команда примет «облегченный» вид:

mysqldump base > /tmp/dump.sql

Резервирование БД через phpMyAdmin

Зайдем в пункт «Экспорт» верхнего меню и в разделе «Способ экспорта» поставим переключатель в положение «Обычный». После этого откроется ряд дополнительных опций. Из них нас интересует способ компрессии, например ZIP. Остается кликнуть ОК – запустится загрузка дампа с выполнением резервной копии и ее сохранением на локальном компьютере.

Типовые проблемы

Иногда можно столкнуться с ошибкой Incorrect key file for table, которая выглядит так:

mysqldump: Error 1034: Incorrect key file for table '<table name>'; try to repair it when dumping table `<table name>` at row: xxxxxx

Причин может быть несколько. Например, обнаружена логическая ошибка таблицы в БД или закончилось место на накопителе, где система сохраняет временные данные. Соответственно, для решения проблемы нужны разные подходы. Так, первым делом желательно проверить объем места на диске. В файле конфигурации, размещенном по пути /etc/my.cnf, указана опция tmpdir, которая определяет папку, куда система сохраняет временные таблицы.

Если ее нет, тогда по умолчанию будет использован путь /tmp. Проверить, достаточно ли места для создания дампа, можно командой:

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

MyISAM. Действия пользователя следующие. В командной строке SQL вводим команду:

> REPAIR TABLE <table name> USE_FRM;

После нее повторяем попытку создания резервной копии.

INNODB. Откроем файл конфигурации СУБД:

innodb_force_recovery = 1

Перезапустим сервер БД:

systemctl restart mysql

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

Выводы

Команда mysqldump обладает широким функционалом, позволяющим резервировать базы в разной комбинации. При желании ее легко заменить удобным инструментом с графическим интерфейсом phpMyAdmin. Эксперименты желательно проводить на пустой БД, где нет ценной информации. Например, можно арендовать соответствующие ресурсы у Timeweb Cloud.

I want to keep a backup of all my MySQL databases. I have more than 100 MySQL databases. I want to export all of them at the same time and again import all of them into my MySQL server at one time. How can I do that?

1 gold badge34 silver badges46 bronze badges

asked Feb 29, 2012 at 11:03

Export

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import

mysql -u root -p < alldb.sql

answered Feb 29, 2012 at 11:15

Shakti Singh

21 gold badges133 silver badges152 bronze badges

It backs up each database into a different file

#!/bin/bash
USER="zend"
PASSWORD=""
#OUTPUT="/Users/rabino/DBs"
#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql # gzip $OUTPUT/`date +%Y%m%d`.$db.sql fi
done

1 gold badge25 silver badges28 bronze badges

answered Sep 29, 2014 at 9:13

1 gold badge23 silver badges26 bronze badges

All the answers I see on this question can have problems with the character sets in some databases due to the problem of redirecting the exit of mysqldump to a file within the shell operator >.

To solve this problem you should do the backup with this command line. The -r flag does the same as the shell operator > but without the character-set problems.

mysqldump -u root -p --opt --all-databases -r backup.sql

To do a good BD restore without any problem with character sets use these commands (you can change the default-character-set as you need).

mysql -uroot -p --default-character-set=utf8
mysql> SET names 'utf8';
mysql> SOURCE backup.sql;

answered Jan 19, 2017 at 9:46

1 gold badge34 silver badges46 bronze badges

Based on these answers I’ve made script which backups all databases into separate files, but then compress them into one archive with date as name.

This will not ask for password, can be used in cron. To store password in .my.cnf check this answer https://serverfault.com/a/143587/62749

Made also with comments for those who are not very familiar with bash scripts.

#!/bin/bash
# This script will backup all mysql databases into
# compressed file named after date, ie: /var/backup/mysql/2016-07-13.tar.bz2
# Setup variables used later
# Create date suffix with "F"ull date format
suffix=$(date +%F)
# Retrieve all database names except information schemas. Use sudo here to skip root password.
dbs=$(sudo mysql --defaults-extra-file=/root/.my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema")
# Create temporary directory with "-d" option
tmp=$(mktemp -d)
# Set output dir here. /var/backups/ is used by system,
# so intentionally used /var/backup/ for user backups.
outDir="/var/backup/mysql"
# Create output file name
out="$outDir/$suffix.tar.bz2"
# Actual script
# Check if output directory exists
if [ ! -d "$outDir" ];then # Create directory with parent ("-p" option) directories sudo mkdir -p "$outDir"
fi
# Loop through all databases
for db in $dbs; do # Dump database to temporary directory with file name same as database name + sql suffix sudo mysqldump --defaults-extra-file=/root/.my.cnf --databases "$db" > "$tmp/$db.sql"
done
# Go to tmp dir
cd $tmp
# Compress all dumps with bz2, discard any output to /dev/null
sudo tar -jcf "$out" * > "/dev/null"
# Cleanup
cd "/tmp/"
sudo rm -rf "$tmp"

answered Jul 13, 2016 at 7:44

1 gold badge25 silver badges28 bronze badges

I wrote this comment already more than 4 years ago and decided now to make it to an answer.

The script from jruzafa can be a bit simplified:

#!/bin/bash
USER="zend"
PASSWORD=""
ExcludeDatabases="Database|information_schema|performance_schema|mysql"
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases`
for db in $databases; do echo "Dumping database: $db" mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
done

1. The excluded databases — prevalently the system tables — are provided in the variable ExcludeDatabases
2. Please be aware that the password is provided in the command line. This is considered as insecure. Study this question.

10 gold badges84 silver badges98 bronze badges

answered Apr 9, 2019 at 18:29

Peter VARGA

3 gold badges37 silver badges73 bronze badges

Why parsing formatted output while the mysql command can do directly what you want?

databases=`mysql -u $USER -p$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema"`

Lists the database names and only this.

1 gold badge25 silver badges28 bronze badges

answered Jul 8, 2015 at 13:45

1 silver badge4 bronze badges

Be careful when exporting from and importing to different MySQL versions as the mysql tables may have different columns.
Grant privileges may fail to work if you’re out of luck.
I created this script (mysql_export_grants.sql ) to dump the grants for importing into the new database, just in case:

#!/bin/sh
stty -echo
printf 'Password: ' >&2
read PASSWORD
stty echo
printf "\n"
if [ -z "$PASSWORD" ]; then echo 'No password given!' exit 1
fi
MYSQL_CONN="-uroot -p$PASSWORD"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g'

1 gold badge25 silver badges28 bronze badges

answered Dec 16, 2015 at 3:26

When you are dumping all database. Obviously it is having large data. So you can prefer below for better:

mysqldump -u [user] -p[password]--single-transaction --quick --all-databases | gzip > alldb.sql.gz

— Warning: Skipping the data of table mysql.event. Specify the —events option explicitly.

mysqldump -u [user] -p --events --single-transaction --quick --all-databases | gzip > alldb.sql.gz

gunzip < alldb.sql.gz | mysql -u [user] -p[password]

Hope it will help 🙂

answered Sep 1, 2016 at 7:23

Export database:

for export you can go with this command:

mysqldump -u root -p --all-databases > alldb.sql ↓ ↓ (you user name) (file name will be save)

and actually, I wrote this post because there is no reason to back up all the databases in one file. it might cause an error while you’re importing it.
for example, there are some default databases and there is no reason to back up them.

therefore you should choose databases that you want to backup.

mysqldump -u root -p --databases YourDBName1 YourDBName2 YourDBName3 > tutorials_query1.sql ↓ ↓ ↓ (your databases name that you want to backup)

you can get all the databases name with this command:

sudo mysql -u root -p

and enter your password and then in front of mysql> run this command:

SHOW DATABASES;

Import database:

mysql -u root -p < alldb.sql ↓ ↓ (your username) (it can be the full path like "/home/yoursqlfile.sql")

answered Sep 13, 2022 at 20:50

8 silver badges24 bronze badges

mysqldump -uroot -proot —all-databases > allDB.sql

answered Sep 20, 2017 at 11:47

Export all databases in Ubuntu

1 — mysqldump -u root -p --databases database1 database2 > ~/Desktop/databases_1_2.sql

2 — mysqldump -u root -p --all_databases > ~/Desktop/all_databases.sql

30 gold badges95 silver badges140 bronze badges

answered Jun 25, 2019 at 9:28

answered May 23, 2021 at 6:06

I successfully exported all databases using this command.

.\mysqldump -u root -p --all-databases > D:\laragon\tmp\alldb.sql

My Tools: Xampp, Windows Powershell

answered Dec 18, 2022 at 12:49