Создание резервных копий баз данных с помощью Mysqldump

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

-u [ имя пользователя ] — имя пользователя с правами, достаточными для создания резервной копии.
-p [ пароль ] — пароль пользователя, указывается без пробела перед ним
—databases [ название базы данных ] — Короткий вариант -B, название одной или нескольких баз данных для дампа. При создании резервной копии одной базы данных, опцию —databases, писать не обязательно, здесь я использовал ее для большей наглядности и однозначности. То есть название базы данных можно прописать сразу за паролем пользователя.
> — перенаправление вывода команды mysqldump, в файл дампа.

 

Создадим резервную копию одной базы данных:

Обратите внимание на отсутствие пробела после опции -p, сразу указываем пароль. В данном случае будет создан файл резервной копии, содержащий структуру и данные, /backup/workdb.sql. Если вам не нужны данные из этой базы, а достаточно просто сохранить структуру таблиц, можно воспользоваться следующим вариантом:

Ключ —no-data ( сокращенный вариант -d ), указывает mysqldump, не скидывать данные в дамп.

Так-же есть возможность создавать дампы, только необходимых таблиц, определенной базы данных:

Вышеприведенная команда, создает резервную копию таблиц access и users, указанных вслед за опцией —tables, из базы данных drupal и сохраняет в файл /backup/drupal2tables.sql.

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

Для создания резервной копии нескольких баз данных, достаточно перечислить их названия после опции —databases ( -B ):

Что-бы создать резервную копию всех имеющихся баз данных, нужно использовать опцию —all-databases или ее сокращенный вариант: -A.

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

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

Восстановить базу данных или таблицу из сохраненного ранее дампа, еще проще, чем этот дамп создать. Для этого достаточно воспользоваться стандартной программой-клиентом mysql, перенаправив в нее файл резервной копии. Делается это так:

После выполнения команды, будут восстановлены все базы данных, из резервной копии, которую мы создали несколькими строками выше. Таким-же способом происходит и восстановление отдельных баз данных или таблиц. Обратите внимание, при восстановлении таблиц, нужно указать в какую именно базу данных вы ее хотите поместить.

В приведенном примере, мы восстанавливаем из резервной копии, таблицу test_table, помещая ее в базу test.

Вообще с помощью программы mysqldump, очень удобно копировать таблицы с данными или только структуры таблиц, из одной базы данных в другую. Небольшой пример:

Скидываем в дамп структуру таблицы, test_table из базы данных test.

Восстанавливаем структуру таблицы, из только что созданной, резервной копии и разворачиваем ее в базу данных other_testdb.

Параметры программы Mysqldump

—help, -?

Вывод справки по опциям и используемым переменным.

—add-drop-database

Добавляет команду, DROP DATABASE перед каждой командой CREATE DATABASE

—add-drop-table

Добавляет команду, DROP TABLE перед каждой командой CREATE TABLE

—add-locks

Обрамляет дамп командами LOCK TABLES и UNLOCK TABLES, для ускорения операций вставки.

—all-databases, -A

Создает полную резервную копию всех баз данных, текущего сервера.

—allow-keywords

Разрешить имена столбцов, совпадающие с зарезервированными ключевыми словами. К имени такого столбца, добавляется префикс таблицы.

—character-sets-dir= путь

Директории с установленными наборами символов кодировок

—comments, -i

Добавить в файл дампа, дополнительную информацию ( например, версию программы, версию MySQL, имя хоста ), отменяется опцией —skip-comments

—compact

Создает более компактный дамп. Использование данного параметра, автоматически включает опции: —skip-add-drop-table, —skip-add-locks, —skip-comments, —skip-disable-keys и —skip-set-charset.

—compatible= имя

Данная опция, пытается повысить совместимость создаваемого дампа, с базой данных другого типа или с более старой версией MySQL. Возможные значения: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options или no_field_options. Можно использовать несколько значений, разделенных запятыми.

—complete-insert, -c

Использовать полную форму оператора INSERT, включая имена столбцов.

—compress, -C

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

—create-options

Включать все MySQL опции при использовании оператора CREATE TABLE.

—databases, -B

Делать дамп нескольких баз данных, перечисленных после данной опции. Без этого параметра, mysqldump, воспринимает в качестве имени базы данных, первое значение, остальные интерпретируются как имена таблиц.

—default-character-set= кодировка

Донная опция устанавливает кодировку по-умолчанию. Если не определена, используется utf8, в ранних версиях latin1.

—delayed-insert

Вместо оператора INSERT, использовать INSERT DELAYED.

—delete-master-logs

Удалять бинарный лог на основном сервере репликаций после создания дампа. При использовании этой опции, автоматически включается опция —master-data.

—disable-keys, -K

Оператор INSERT для каждой таблицы, обрамляется выражением /*!40000 ALTER TABLE tbl_name DISABLE KEYS */ и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */. Данная опция ускоряет загрузку при восстановлении из дампа, для таблиц типа MyISAM, за счет того, что индексы создаются после вставки всех данных.

—dump-date

Если включена опция —comments, добавлять дату создания дампа.

—extended-insert, -e

Использовать многострочный синтаксис оператора INSERT. Уменьшает размер дампа и ускоряет последующую вставку данных.

—flush-logs, -F

Переоткрыть лог файлы, перед созданием резервной копии. Старый файл будет сохранен с суффиксом -old. При использовании с опцией —all-databases ( сокращенный вариант -A), будут переоткрыты лог-файлы каждой базы данных, для которой делается дамп.

—flush-privileges

Выполнять команду FLUSH PRIVILEGES после создания дампа базы данных.

—force, -f

Продолжать создание резервной копии даже в случае возникновения ошибки.

—host= имя_хоста, -h имя_хоста

Указывает хост MySQL сервера. По-умолчанию резервная копия делается для хоста localhost

—hex-blob

Представлять бинарные данные полей BINARY, VARBINARY, BLOB и BIT в шестнадцатиричном формате ( hex ).

—ignore-table= имя_базы.имя_таблицы

Не скидывать в дамп таблицу «имя_таблицы» из базы «имя_базы«. Опцию нужно использовать повторно, для каждой игнорируемой таблицы.

—insert-ignore

Дописывать в оператор INSERT, опцию IGNORE.

—lock-all-tables, -x Блокировка всех таблиц, во всех базах, на время создания резервной копии. Данная опция автоматически отключает —single-transaction и —lock-tables.

—lock-tables, -l

Блокировка таблиц базы данных, на время создания резервной копии. При дампе всех баз данных с этой опцией, таблицы каждой базы блокируются отдельно. Для транзакционных таблиц, типа InnoDB и BDB, предпочтительней использовать опцию —single-transaction.

—log-error= имя_файла

Писать ошибки и предупреждения в файл «имя_файла«.

—no-autocommit

Включает операторы INSERT для каждой таблицы, в операторы SET AUTOCOMMIT и COMMIT, для увеличения скорости выполнения большого количества запросов INSERT

—no-create-db, -n

Данная опция подавляет вывод в дамп оператора CREATE DATABASE, при использовании опций —databases и —all-databases.

—no-create-info, -t

Не писать оператор CREATE TABLE, для пересоздания каждой таблицы из резервной копии.

—no-data, -d

Не скидывать в дамп содержимое таблиц. Оставляет только операторы CREATE TABLE для создания структуры.

—opt

Групповая опция. Синоним включения опций, —add-drop-table, —add-locks, —create-options, —disable-keys, —extended-insert, —lock-tables, —quick, —set-charset. Ускоряет общий процесс создания резервной копии, включена по-умолчанию. Отключается опцией —skip-opt.

—order-by-primary

Сортировать ряды таблиц по первичному ключу или по первому уникальному индексу, если индекс существует. Полезна в случае создания дампа таблиц MyISAM с последующей вставкой в таблицы типа InnoDB.

—password[=password], -p[password]

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

—port= номкр_порта, -P номкр_порта

Номер порта, для подключения к серверу по протоколу TCP/IP.

—protocol={TCP|SOCKET|PIPE|MEMORY}

Использовать для подключения к серверу MySQL, указанный протокол.

—quick, -q

Данная опция вынуждает Mysqldump, восстанавливать строки, по одной за раз, вместо того что-бы скидывать весь объем строк в буфер памяти и выписывать их оттуда. Очень полезна при создании резервных копий больших таблиц.

—quote-names, -Q

Обрамлять имена баз данных, таблиц и колонок, ковычками. Включена по-умолчанию.

—replace

Использовать оператор REPLACE вместо INSERT. Начиная с версии MySQL 5.1.3.

—result-file= имя_файла, -r имя_файла

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

—routines, -R

Записывать в дамп хранимые процедуры и функции. Для использования данной опции, необходимы права SELECT на таблицу proc, системной базы данных mysql. Дамп будет содержать операторы CREATE PROCEDURE и CREATE FUNCTION. При использовании этой опции, эти операторы не будут содержать атрибутов времени создания и модификации хранимых процедур и функций и после восстановления дампа они будут равны времени восстановления. Если вам необходимо сохранить исходные атрибуты времени, вместо использования данной опции, сделайте отдельный дамп таблицы mysql.proc, под именем пользователя, который имеет на это достаточные права. Опция появилась с версии MySQL 5.1.2.

—set-charset

Добавляет в дамп оператор SET NAMES со значением кодировки по-умолчанию. По-умолчанию, данная опция включена, что-бы подавить, используйте —skip-set-charset.

—single-transaction

Выполняет оператор BEGIN SQL, перед началом создания резервной копии. Опция используется только для транзакционных таблиц, типа InnoDB. Только этот тип таблиц может быть сохранен в дамп в актуальном состоянии, после выполнения BEGIN SQL, и без блокирования приложения. Например таблицы типа MyISAM или MEMORY, могут измениться в процессе создания резервной копии с использованием данной опции, в итоге, информация в дампе будет противоречивой, неактуальной. Опции —single-transaction и —lock-tables, являются взаимоисключающими.

—socket= путь_к_файлу_сокета, -S путь_к_файлу_сокета

Использовать файл unix-сокета, для подключения к localhost.

—tables

Имена идущие за этой опцией, считаются именами таблиц.

—triggers

Включать в резервную копию триггеры, для каждой таблицы. Отменить действие опции можно с помощью —skip-triggers.

—user= имя_пользователя, -u имя_пользователя

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

—verbose, -v

Вывод служебной информации о ходе выполнения программы.

—where=’where_условие’, -w ‘where_условие’

Скидывать в дамп информацию, выбранную по условию WHERE.

—xml, -X

Создать дамп в формате XML

 

 

Источник: https://vds-admin.ru/backup/rezervnoe-kopirovanie-i-vosstanovlenie-baz-dannykh-s-pomoshchyu-programmy-mysqldump

Основное конфигурирование MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf

Указание кодировок и collation

В секцию [mysqld] добавим строки:

• character-set-server — кодировка для всего сервера;
• collation-server — порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
• init-connect — строка, выполняемая для каждого клиента при соединении.

Ограничение количества, таймауты и источники соединений

Увеличение числа открытых файлов

В большинстве Linix-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, для работы этого недостаточно.

Проверим текущие опции:

Внесем требуемые лимиты в /etc/security/limits.conf

Динамически изменим текущие лимиты:

Проверим soft limit:

и hard limit

Текущие лимиты в MySQL проверим SQL-запросом:

Оптимизация MySQL для MyISAM

Оптимизация параметров MySQL позволяет значительно увеличить производительность MyISAM.

Буферы

Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).

При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.

Кэши

Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.

Будьте внимательны при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 256M.

Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.

Перенос временных файлов MySQL в память

Проверяем наличие /dev/shm:

Настройки размещаются в /etc/fstab, рекомендуем указать размер, например, 1G:

Если внесли изменения, то перемонтируем:

В конфигурационном файле указываем:

В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:

Затем перезапустите:

Оптимизация MySQL для InnoDB

Стандартно все таблицы и индексы хранятся в одном файле, мы рекомендуем использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.

Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуем устанавливать обе опции в 4096 или 8192.

При использовании только InnoDB часть опций требует корректировки:

Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных.

Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Не забудьте провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-22G и разделяем его на 8 секций:

Размер памяти, выделяемый InnoDB для хранения различных внутренних структур, устанавливается 16M-32M

Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных.

При установке значения в 2 буфер не сбрасывается на диск, а только в кэш операционной системы. Установка значения в 0 увеличит быстродействие, однако возможна потеря последних данных при аварийном выключении mysql-сервера.

Количество потоков ввода/вывода файлов в InnoDB задается опцией innodb_file_io_threads, обычно этому параметру присваивается значение 4 или 8.

С версии MySQL 5.5 разделено на 2 опции:

Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:

После применения изменений перезагрузите MySQL:

 

Источник: https://www.hostcms.ru/documentation/server/mysql/

 

Строкий режим появился еще в MySQL 5.1, но стал обязательным в MySQL 5.7.

Строгий режим отключается в конфигурационных файлах:

Необходимо выставить такие значения:

 

 

 

 

Шаг 1. Перемещение данных на другой диск

Смотрим, где находится каталог MySQL:

Выполняем команду:

Видим:

Останавливаем MySQL:

Смотрим статус MySQL:

Должны увидеть нечто подобное:

Переносим папку MySQL на другой диск:

Переименуем каталог с базами MySQL (позже, можно его будет удалить вручную):

Шаг 2. Указываем в настройках MySQL новый каталог.

Редактируем файл: /etc/mysql/mysql.conf.d/mysqld.cnf

Исправляем строку:

Шаг 3. Настройка прав доступа.

Редактируем файл: /etc/apparmor.d/tunables/alias

Вставляем строку:

Перезагружаем:

Шаг 4. Запускаем MySQL.

 

Первоисточник: https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04