Mariadb backup database linux

Как сделать дамп базы MySQL / MariaDB

Обновлено: 12.02.2023 Опубликовано: 15.07.2016

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

Синтаксис и базовая команда

Создание дампа выполняется из командной строки Linux или Microsoft с помощью утилиты mysqldump. Она идет в составе с пакетом mysql/mariadb (mysql-client) и может быть запущена как локально на сервере СУБД, так и с удаленного компьютера. Общий синтаксис:

* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе на сервере 127.0.0.1 происходит от пользователя root. Это самый простой пример создания дампа MySQL. Базовые параметры команды mysqldump:

Параметр	Описание
-h	Адрес сервера, к которому нужно подключиться.
-u	Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права.
-p	Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее).

* полный перечень параметров смотрите в официальном руководстве.

Примеры создания дампа MySQL

1. С последующим архивированием

* в данном примере мы сначала создали переменную DBNAME, в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump, результат выполнения которой по конвееру отдаем архиватору gzip. В результате мы получит дамп по пути /tmp/.sql.gz Или с подробным выводом информации на экран (дольше по времени):

2. Для одновременно нескольких баз

3. Для всех баз одной командой

4. Резервирование только структуры базы

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

5. Создание копии определенной таблицы

6. Резервирование прав доступа на СУБД

Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.

* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;

7. Проигнорировать определенную таблицу

Учетная запись

В наших примерах мы создаем дамп от пользователя root. Также можно задействовать любую учетную запись, у которой есть права на базу, дамп которой мы хотим сделать. Если же нам нужна отдельная запись с минимальными правами, достаточными для резервного копирования, создаем ее SQL-командой:

* в данном примере мы создадим пользователя backup с паролем backup123. Теперь дадим разрешение созданной учетной записи выполнять подключение и создание резервной копии:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost;

Также мы можем создать локальный конфигурационный файл для пользователя системы, из под которого будет запускаться утилита mysqldump. В нем мы можем указать некоторые опции, чтобы не вводить их каждый раз. Создаем файл:

Скрипт для резервного копирования

Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron. Подробнее процесс описан в статье Пример скрипта для создания резервной копии MySQL (для Linux).

Резервное копирование в phpMyAdmin

В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных. В верхней части меню кликаем по Экспорт: В разделе «Способ экспорта» ставим переключатель в положение Обычный: * обычный режим откроет дополнительные опции для резервного экспорта данных. Выбираем компрессию, например, zip: И в нижней части окна нажимаем OK. Начнется загрузка файла с резервной копией на компьютер.

Возможные проблемы

Incorrect key file for table

mysqldump: Error 1034: Incorrect key file for table ‘

‘; try to repair it when dumping table ` ` at row: xxxxxx

Решение: в зависимости от причины, решений будет несколько.

1. Проще всего сначала проверить место на диске. В конфигурационном файле СУБД (как правило, /etc/my.cnf) можно найти опцию tmpdir — она указывает на каталог, который используется под создание временных таблиц. Если опции нет, то используется путь /tmp.

Необходимо, чтобы для данного раздела было достаточно места. Проверить можно командой:

2. Если наша таблица повреждена, то пробуем ее востановить. Данный процесс зависит от типа таблицы, с которой возникла проблема.

В командной оболочке SQL вводим:

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

б) Если таблица типа INNODB.

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

В радел [mysqld] добавим опцию:

[mysqld]
.
innodb_force_recovery = 1

Перезапускаем сервер баз данных, например:

Пробуем сделать резервную копию. Если получим такую же ошибку, меняем значение innodb_force_recovery с 1 на 2:

[mysqld]
.
innodb_force_recovery = 2

И так по кругу, до значения 6, пока не получим положительный результат.

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

Читайте также

Для восстановления базы читайте инструкцию Как восстановить базу MySQL

Источник

MariaDB Dump Tutorial

Many websites are now using MariaDB server to store their valuable data. Data is the most important content of any website. So you need to keep regular backups of the database and this will help you to restore your data if your website crashes or the data is corrupted by any system failure. This tutorial is divided into two parts. In the first part, you will learn to take a backup of MariaDB using the dump tool and in the second part, you will learn to restore the data from backup.

Part-1: MariaDB backup/dump

You can take a backup of the MariaDB server in 2 ways. These are physical backups and logical backups. All necessary SQL statements which are required to restore the databases such as create, insert etc. are stored in a logical backup. On the other hand, specific data files and directories are stored in a physical backup. Many tools are available to take backups of your database server. The use of the common tools, mysqldump and mysqlhotcopy, are explained in this section.

This tool can be used to make a logical backup of the MariaDB server. It is the best backup tool for small sized databases. But if the database size is large then you will need long times for restoring the data from this backup. It dumps data in SQL format. So, the data can be easily imported on different database servers. Database procedures, functions, views and events are dumped at the time of backup. So you can automatically recreate these at the time of restore. The syntax for mysqldump is given below:

According to the syntax, the backup file will be created at the current location. You can create backup of single table or single database or all databases. These three types of backups are shown in the following examples.

Log-in to MariaDB server and run the commands to check all existing databases of server.

Single table backup:

Suppose, you have to keep backup of table ‘employee’ of database ‘cakedb’ .

Run the following command from the terminal to create the backup file.

$ mysqldump — u root — p —single-transaction —quick —lock-tables=false cakedb employee >
cakedb — employee . sql

Now, check backup file is created or not using ‘ls’ command.

Single database backup:

Run the following command to create backup file for an existing database named ‘company’.

$ mysqldump — u root — p company —single-transaction —quick —lock-tables=false >
company — backup . sql

Now, check backup file is created or not using ‘ls’ command.

All databases backup:

Run the following command to create backup of all existing databases of MariaDB server.

$ mysqldump — u root — p —all-databases —single-transaction —quick —lock-tables=false >
all — backup . sql

Now, check backup file is created or not using ‘ls’ command.

This tool is used for physical backup of database server. It is faster than other backup tools but it can only create a backup on the same machine where the database directory resides. It can also be used for single table or multiple databases or those databases which are match by particular regular expression. The syntax of mysqlhotcopy is given below.

Single Database Backup

You require root permission to execute mysqlhotcopy command. Run the following command with root previlege and mysql root password to create backup files of ‘company’ database in ‘backups’ folder.

By default, the backup file will create in /var/lib/mysql/backups.

Multiple Database Backup

You can also create a backup on a different location. Run the following command to create backup files of two databases named ‘company’ and ‘cakePHP’ in /home/ubuntu/backups.

$ sudo mysqlhotcopy — u root — p mynewpassword company cakedb / home / ubuntu / backups
—allowold —keepold

Part-2: MariaDB Restore

You can restore the data from the backup file by using mysql client. The general syntax for restoring database using mysql is given below.

Different types of restore examples are shown in the following examples.

Restore single table

You have to create a new database to restore table from a backup file. Run the command to create new database and select database.

The backup filename is cakedb-employee.sql. Run the command to restore the table in newdb database.

Now, check the table is imported or not.

Restore single database

You can restore all tables from the backup file of any particular database. In the following command. All tables of company database will be restored in newdb database.

Now, log-in to server and check the database is imported or not.

Restore all database

you can restore all databases of any MariaDB server to another MariaDB server from backup file. In that case, all existing databases will be overwritten by databases and tables of backup file. Run the following command to do this task.

Creating backup files and restoring data from backup files are very necessary tasks for developing any type of database related projects. You can keep regular backup of your database files and restore the data when require by following above steps.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.

Источник