mysqldump database: бэкап mysql как сделать бэкап базы данных

Резервное копирование базы данных MySQL с помощью утилиты mysqldump. В данной инструкции будут рассмотрены далеко не все параметры данной утилиты.

Для данных экспериментов использовалась версии mysql 5.7.25, 5.7.44

Для начала создадим тестовую базу данных:

CREATE DATABASE IF NOT EXISTS `testdb`;

Выбираем базу:

USE `testdb`;

Создадим тестовую таблицу в тестовой базе данных:

CREATE TABLE IF NOT EXISTS `customers`
(
	 Id INT NOT NULL AUTO_INCREMENT
	, name VARCHAR(64) NOT NULL
	, lastname VARCHAR(64) NOT NULL
	, dateAdd DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	, CONSTRAINT PK_ID PRIMARY KEY (Id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Заполним данными: 

I N S E R T INTO customers (name, lastname) VALUES 
('Иван','Иванов')
,('Пётр','Петров')
,('Александр','Сидоров');

В слове insert уберите все лишние пробелы, тогда запрос заработает. Вынужден был прибегнуть к такому методу, т.к. платформа сайта видимо считает данный запрос SQL-инекцией и не пропускает его.

Создадим храниму процедуру (routine) чисто для потестить:

 

DELIMITER ;;
CREATE PROCEDURE `sp_select_customers`(IN ID int)
BEGIN	

    SET @id = ID;
    
    SET @SQL = 'SELECT name
		, lastname
	FROM customers cst
	WHERE Id = COALESCE(?, cst.Id);';

    PREPARE mttt FROM @SQL;
    EXECUTE mttt USING @id;	
    DEALLOCATE PREPARE mttt;

 
END ;;
DELIMITER ;

Вызываем процедуру либо так:

call sp_select_customers(NULL);

либо так:

 call sp_select_customers(1);

В случае передачи NULL в процедуру - выбираем все значения из таблицы, если же передаём цифирьку - то выбираем строку с данным идентификатором. В данном случае пример "притянут за уши". Можно и без процедур обойтись, но нам нужно посмотреть, как её быкапить.

Выходим из mysql командой \q

---

Приступим к созданию быкапа тестовой базы testdb:

mysqldump -uroot -p123 -h localhost --databases testdb > D:\mysql\backup\backup_tempdb1.sql

т.к. данный эксперимент проводится не на реальном сервере, а на простом компьютере, то всё это делается из под пользователя root, у которого пароль 123 - в продакшене это естественно недопустимо! Данная иснтрукция не является инструкцией по безопасности, только по использованию mysqldump. Каждый должен использовать безопасные логины и пароли!

Продолжим.

Создался файл backup_tempdb1.sql по указанному пути:

Примерно с таким содержимым:

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

mysqldump -uroot -p123 -h localhost --routines --databases testdb > D:\mysql\backup\backup_tempdb2.sql

Смотрим файл backup_tempdb2.sql:

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

Если необходимо для каждой строки свой собственный INSERT то в этом поможет параметр skip-extended-insert :

mysqldump -uroot -p123 -h localhost --skip-extended-insert --routines --databases testdb > D:\mysql\backup\backup_tempdb3.sql

Если какая-либо строка уже присутствует в базы, то можно параметр IGNORE в INSERT, тогда при восстановлении ошибоная строка будет пропущена, в этом поможет параметр insert-ignore :

mysqldump -uroot -p123 -h localhost --insert-ignore --skip-extended-insert --routines --databases testdb > D:\mysql\backup\backup_tempdb4.sql

Если необходим backup, в котором недопустимо создание базы данных и таблиц, т.к. при восстановлении база с таблицами уже создана, то в этом помогут параметры:

--no-create-db - не добавляет в быкап оператор CREATE DATABASE

--no-create-info - не добавляет в быка оператор CREATE TABLE

--skip-add-drop-table - запрещает оператор DROP TABLE перед каждым CREATE TABLE

mysqldump -uroot -p123 -h localhost --no-create-db --no-create-info --skip-add-drop-table --insert-ignore --skip-extended-insert --databases testdb > D:\mysql\backup\backup_tempdb5.sql

в данном примере убрал параметр --routines ибо очень уж длинная строка получается:

Если необходимо сделать backup структуры базы данных без самих данных, то нужно использовать параметр no-data ну и на всякий случай routines :

mysqldump -uroot -p123 -h localhost --no-data --routines --databases testdb > D:\mysql\backup\backup_tempdb6.sql

При использовании параметра no-data в backup-файл не попадают INSERT'ы.

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