Бэкап отдельной таблицы или базы
Не всегда нужны архивные копии всего mysql сервера. Иногда достаточно отдельной базы данных или даже таблицы. Xtrabackup позволяет это сделать. Архивируем только одну базу данных sitemanager.
Для того, чтобы этот способ бэкапа отдельной базы mysql работал, необходим параметр innodb_file_per_table в настройка сервера баз данных.
# xtrabackup --backup --databases "sitemanager" --target-dir=/root/backupdb/sitemanager
Восстановление отдельной базы mysql будет выглядеть так.
# xtrabackup --prepare --databases "sitemanager" --target-dir=/root/backupdb/sitemanager # systemctl stop mysqld # mkdir -p /var/lib/mysql.old/sitemanager # mv /var/lib/mysql/sitemanager /var/lib/mysql.old # mv /root/backupdb/sitemanager/sitemanager /var/lib/mysql # chown -R mysql. /var/lib/mysql # systemctl start mysql
Мы по сути просто скопировали исходные файлы базы из бэкапа в рабочий каталог, предварительно сохранив предыдущую версию базы на всякий случай.
Теперь попробуем из полного бэкапа базы восстановить только одну таблицу. В моем примере я на работающем сервере сделаю восстановление существующей таблицы из полного бэкапа. Если будете восстанавливать на другой сервер, то перед этим вам нужно будет воссоздать исходную структуру таблицы перед тем, как будете восстанавливать данные по предложенному методу.
Готовим бэкап к восстановлению:
# xtrabackup --prepare --export --target-dir=/root/backupdb/full
Идем в консоль mysql и выбираем там таблицу для восстановления. В моем примере это будет таблица b_user_access из базы sitemanager. Смотрим, заполнена ли таблица данными.
mysql> SELECT COUNT(*) FROM sitemanager.b_user_access; +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)
Делаем DISCARD этой таблицы.
mysql> ALTER TABLE sitemanager.b_user_access DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec)
Discard означает, что будет удален ibd файл таблицы. Теперь нам его нужно скопировать из бэкапа и назначить права для mysql.
# cp /root/backupdb/full/sitemanager/b_user_access.* /var/lib/mysql/sitemanager # chown mysql. /var/lib/mysql/sitemanager/b_user_access.*
Возвращаемся в консоль mysql и импортируем данные.
mysql> ALTER TABLE sitemanager.b_user_access IMPORT TABLESPACE; Query OK, 0 rows affected (0.03 sec)
Смотрим, что получилось.
> SELECT COUNT(*) FROM sitemanager.b_user_access; +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)
Данные восстановлены. Вернулись те же 6 строк, что и были до этого.
Создайте SSH-туннель в Linux и macOS
Клиент предустановлен в большинстве систем на базе Linux и Unix.
Если вы используете Linux или macOS в качестве операционной системы, вы можете создать туннель SSH, используя следующую команду:
Используются следующие параметры:
- — Указывает SSH не выполнять удаленную команду.
- — Создает переадресацию локального порта. Локальный порт ( ), то IP — назначения ( ) и удаленный порт ( ) разделены двоеточием ( ).
- — удаленный пользователь SSH и IP-адрес сервера.
- Чтобы запустить команду в фоновом режиме, используйте параметр .
- Если SSH-сервер прослушивает порт, отличный от 22 (по умолчанию), укажите порт с помощью параметра .
После запуска команды вам будет предложено ввести пароль пользователя SSH. После его ввода вы войдете на сервер, и туннель SSH будет установлен. Рекомендуется настроить аутентификацию на основе ключей SSH и подключаться к серверу без ввода пароля.
Теперь вы можете указать клиенту MySQL на локальном компьютере адрес ввести учетные данные для входа в удаленную базу данных и получить доступ к серверу MySQL.
Например, чтобы подключиться к серверу MySQL с помощью клиента из командной строки, вы должны ввести:
Где — это удаленный пользователь MySQL, имеющий права доступа к базе данных.
При появлении запроса введите пароль пользователя MySQL.
Чтобы завершить туннель SSH, введите в консоли, на которой работает клиент ssh.
Как проверить конфигурационный файл сервера SSH без запуска службы
-t
Тестовый режим. Только проверяет правильность файла конфигурации и работоспособность ключей. Это полезно для надёжного обновления sshd, поскольку параметры конфигурации могут измениться.
-T
Расширенный тестовый режим. С этой опцией sshd проверит правильность файла конфигурации, выведет действующую конфигурацию в стандартный вывод и затем завершит работу. При желании, правила соответствия могут применяться путём указания параметров соединения с использованием одной или нескольких опций -C.
-C connection_spec
Укажите параметры соединения для использования в расширенном тестовом режиме -T. Если установлены, любые директивы Match в файле конфигурации, которые будут применяться, применяются до записи конфигурации в стандартный вывод. Параметры соединения предоставляются в виде пар «ключевое слово=значение» и могут предоставляться в любом порядке, с несколькими опциями -C или в виде списка через запятую. Ключевыми словами являются «addr», «user», «host», «laddr», «lport» и «rdomain», и они соответствуют адресу источника, пользователю, разрешённому имени хоста источника, локальному адресу, номеру локального порта и домену маршрутизации соответственно.
Как выбрать интерфейс (IP) для прослушивания
В системе может быть несколько сетевых интерфейсов с несколькими IP адресами, по умолчанию sshd прослушивает их все, в том числе IPv6 адреса:
ListenAddress 0.0.0.0 ListenAddress ::
Если вы хотите, чтобы компьютер в локальной сети был доступен только для устройств в локальной сети, то можно указать его локальный IP:
ListenAddress 192.168.1.20
Кстати, смотрите статью «Как настроить Kali Linux на использование статичного IP адреса».
Директиву ListenAddress можно использовать несколько раз в одном конфигурационном файле. Разрешены следующие форматы:
ListenAddress hostname|address ListenAddress hostname:port ListenAddress IPv4_address:port ListenAddress :port
Если порт не указан, то sshd будет прослушивать на указанном порту в соответствии со всеми установленными опциями Port в этом файле.
Квалификатор rdomain является необязательным, он имеет отношение к маршрутизации.
Также обратите внимание, что с ListenAddress обязательно должен быть указано имя хоста (адрес) ИЛИ порт. Можно указать отдельно имя хоста (адрес), можно указать отдельно порт, а также можно указать их вместе
Во всех вариантах rdomain может отсутствовать.
Опции командной строки (об их использовании далее) имеют приоритет над директивами конфигурационного файла. В том числе опция для установки используемого порта приводят к тому, что Port в конфигурационном файле игнорируется. Но порты указанные с ListenAddress перезаписывают порты в строке команды.
Вы можете указать конкретный IP, который будет прослушиваться в ожидании подключений. А опцией AddressFamily вы можете выбрать для прослушивания все адреса, только IPv4 или только IPv6:
AddressFamily any
Варианты:
- any (по умолчанию — любые адреса),
- inet (использовать только IPv4),
- inet6 (использовать только IPv6),
Завершение работы процессов
Завершить текущие процессы можно командой killall. Например, для завершения процессов веб-сервера Apache:
killall -9 apache2
Обратите внимание! После выполнения данной команды для перезапуска обработчиков Apache необходимо изменить версию обработчика php в разделе «Сайты». Аналогично для других сервисов, например:
Аналогично для других сервисов, например:
killall -9 vsftpd
Завершение процесса MySQL:
mysqladmin -u'база_данных' -p'пароль_базы_данных' kill id_запроса
Для просмотра процессов MySQL используйте:
mysqladmin -u'база_данных' -p'пароль_базы_данных' pr
Делаем резервную копию (он же дамп, он же бекап)
Все данные одной базы
mysqldump -u USER -pPASSWORD DATABASE > dumpname.sql
| 1 | mysqldump-uUSER-pPASSWORD DATABASE>dumpname.sql |
– логин пользователя,
– пароль
Обратите внимание, что между. и
и
нет пробела.
Все данные нескольких определенных баз
Добавляем параметр
или
mysqldump -u USER -pPASSWORD —databases DATABASE1 DATABASE2 DATABASE3 … > dumpname.sql
| 1 | mysqldump-uUSER-pPASSWORD—databases DATABASE1 DATABASE2 DATABASE3…>dumpname.sql |
– логин пользователя,
– пароль
Обратите внимание, что между. и
и
нет пробела.
Данные всех баз
Указываем ключ
или
.
mysqldump -u USER -pPASSWORD —all-databases > dumpname.sql
| 1 | mysqldump-uUSER-pPASSWORD—all-databases>dumpname.sql |
– логин пользователя,
– пароль
Обратите внимание, что между. и
и
нет пробела.
Дамп только структуры базы данных MySQL
За это это отвечает параметр
или
.
mysqldump —no-data -u USER -pPASSWORD DATABASE > dumpstruct.sql
| 1 | mysqldump—no-data-uUSER-pPASSWORD DATABASE>dumpstruct.sql |
Дамп определенных таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLENAME1 TABLENAME2 .. > dumpstruct.sql
| 1 | mysqldump-uUSER-pPASSWORD DATABASE TABLENAME1 TABLENAME2..>dumpstruct.sql |
Сразу архивируем дамп MySQL
mysqldump -u USER -pPASSWORD DATABASE | gzip > dumpname.sql.gz
| 1 | mysqldump-uUSER-pPASSWORD DATABASE|gzip>dumpname.sql.gz |
Добавляем дату и время к дампу
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +dumpname.%Y%m%d_%H%M%S.sql.gz`
| 1 | mysqldump-uUSER-pPASSWORD DATABASE|gzip>`date+dumpname.%Y%m%d_%H%M%S.sql.gz` |
Автоматизируйте резервное копирование с помощью Cron
Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.
Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:
-
Создайте файл с именем в домашнем каталоге пользователя:
Скопируйте и вставьте следующий текст в файл .my.cnf.
Не забудьте заменить и на пользователя базы данных и пароль пользователя.
-
Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ:
-
Создайте каталог для хранения резервных копий:
-
Откройте ваш пользовательский файл crontab:
Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем каждый день в 3 часа ночи:
Не забудьте заменить своим фактическим именем пользователя. Мы также избегаем знаков процента ( ), потому что они имеют особое значение в crontab.
Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:
Конечно, вам нужно настроить команду в соответствии с местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим руководством по поиску файлов в Linux с помощью командной строки .
Восстановление из бэкапа
Давайте теперь восстановим данные из сделанного бэкапа. Если это тот же сервер, то все очень просто. Нам достаточно подготовить бэкап с помощью ключа prepare, как я показал ранее и заменить содержимое рабочей директории mysql на то, что хранится в архиве.
# systemctl stop mysqld && rm -rf /var/lib/mysql/* # xtrabackup --copy-back --target-dir=/root/backupdb/full # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld
Разбираем, что я сделал.
- Остановил mysql сервер и удалил все из ее рабочей директории.
- Восстановил данные из архивной копии xtrabackup. По факту он просто скопировал данные в рабочую директорию mysql сервера.
- Назначил пользователя mysql владельцем рабочей директории и всего ее содержимого.
- Запустил mysql сервер с восстановленными данными.
После запуска mysql сервера проверяйте лог /var/log/mysql/error.log на предмет ошибок. Если увидите там такие ошибки:
InnoDB: Page log sequence number 17744745 is in the future! Current system log sequence number 9160744.
Значит забыли выполнить подготовку архива перед восстановлением. Я частенько это забываю и расстраиваюсь, когда вижу ошибки. В общем случае ошибок после восстановления быть не должно.
Если будете восстанавливать базу на другой сервер, то последовательность действий будет следующая. Подключаем репозиторий percona.
# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Ставим mysql server и xtrabackup нужной версии.
# yum install Percona-Server-server-57 percona-xtrabackup-24
Копируем на новый сервер архив сервера баз данных.
# scp root@10.20.1.5:/root/backupdb/full.tar.gz ~
Распаковываем его:
# tar xzvf full.tar.gz
Восстанавливаем данные и запускаем mysql сервер.
# xtrabackup --copy-back --target-dir=~/full # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld
Заходим в консоль mysql и проверяем список баз и пользователей.
# mysql -u root -p mysql> show databases; mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Все на месте, как и на исходном сервере.
Это я показал восстановление из полного бэкапа. Теперь показываю, как восстановиться из инкрементной копии. Для начала нам надо подготовить полную копию для накатывания на нее инкремента.
# xtrabackup --prepare --apply-log-only --target-dir=/root/backupdb/full
Теперь добавляем туда данные из инкрементного бэкапа.
# xtrabackup --prepare --apply-log-only --target-dir=/root/backupdb/full --incremental-dir=/root/backupdb/inc1
И так для всех остальных инкрементных копий, если у вас из них выстроена цепочка. Контролировать состояние полного архива и сопоставлять с инкрементами можно по содержимому файлов xtrabackup_checkpoints. После того, как восстановили все инкрементные архивы, на последнем из них не нужно использовать ключ apply-log-only. Так же он не нужен, если у вас только одна инкрементная копия. Завершающий этап подготовки полной копии должен быть без него.
После того, как восстановили всю цепочку инкрементов, с архивом можно работать как с обычным полным бэкапом.
Навигация
Вывод текущей рабочей директории
Для вывода информации о текущей рабочей директории используется команда pwd.
Пример использования:
username@server:~$ pwd /home/u/username
Вывод содержимого директории
Чтобы посмотреть содержимое директории, воспользуйтесь командой ls.
Вывод содержимого текущей директории в несколько колонок (только имена файлов и директорий):
ls .
Вывод содержимого текущей директории в одну колонку (только имена файлов и директорий):
ls -1
Вывод подробной информации о содержимом текущей директории, включая скрытые файлы (имя которых начинается с точки):
ls -la
Вывод содержимого конкретной директории:
ls имя_директории
Пример использования:
username@server:~$ ls -la total 16 drwx------ 4 username customers 4096 Mar 10 12:56 . drwx------ 14 username customers 4096 Mar 10 12:55 .. -rw------- 1 username customers 0 Mar 10 12:56 .htaccess drwx------ 2 username customers 4096 Mar 10 12:55 test drwx------ 2 username customers 4096 Mar 10 12:55 test1 -rw------- 1 username customers 0 Mar 10 12:55 test.txt где "." - текущий каталог, а ".." - родительский каталог.
Перемещение между директориями
Команда cd позволяет выполнить переход в другую директорию.
Основные способы применения:
Перейти в директорию, которая находится в текущей директории:
cd dirname
Перейти в родительский каталог (на уровень выше):
cd ..
Перейти в домашний каталог:
cd # Либо: cd ~
Перейти в домашний каталог по абсолютному пути (начиная с корня):
cd /home/u/username
Перейти в предыдущий каталог:
cd -
Примеры использования:
# Текущая директория отображается после двоеточия и до символа "$". # Перейти в каталог media username@server:~$ cd /home/u/username/public_html/media # Перейти в каталог cms username@server:~/public_html/media$ cd cms # Перейти в домашний каталог username@server:~/public_html/media/cms$ cd # Перейти в предыдущий каталог username@server:~$ cd - /home/u/username/public_html/media/cms # Перейти на уровень выше username@server:~/public_html/media/cms$ cd .. username@server:~/public_html/media$
Возможные ошибки
В процессе восстановления мы можем столкнуться с разными ошибками. Рассмотрим их примеры.
MySQL server has gone away
Во время восстановления базы может выскочить ошибка:
at line xxx: MySQL server has gone away.
Как правило, ее причина в низком значении параметра max_allowed_packet, который отвечает за ограничение выполнения команд из файла. Посмотреть текущее значение можно командой в mysql:
> SHOW VARIABLES LIKE ‘max_allowed_packet’;
Чтобы увеличить значение параметра, открываем конфигурационный файл my.cnf:
vi /etc/my.cnf
* в некоторых версиях СУБД конфиг может находится по пути /etc/my.cnf.d/server.cnf.
В разделе редактируем или добавляем:
…
max_allowed_packet = 512M
* значение для данного параметра не обязательно должно быть таким большим.
Перезапускаем mysql:
systemctl restart mariadb || systemctl restart mysql
Row size too large
Ошибка выскакивает после небольшого времени работы восстановления. Более полный текст выглядит, примерно, так:
ERROR 1118 (42000) at line 608: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
Причина: ошибка встречается, если в нашей базе есть большое количество текстовых полей и мы используем таблицы типа INNODB. По умолчанию, они имеют ограничение на объем данных, которые можно хранить в одной строке таблицы.
Решение:
Для решения проблемы мы можем добавить опцию innodb_strict_mode со значением . Данная опция регламентирует более строгий режим работы СУБД. Это грубое решение, которое позволит нам добиться результата, но мы можем выполнить настройку тонко — об этом можно прочитать на соответствующей странице блога mithrandir.ru.
Мы же сделаем все по-быстрому. Открываем конфигурационный файл СУБД — его местоположение зависит от версии и реализации, например:
vi /etc/mysql/mariadb.conf.d/50-server.cnf
* это пример расположения для базы MariaDB 10. Более точное расположение можно найти в файле /etc/my.cnf.
Приводим опцию innodb_strict_mode к виду:
…
innodb_strict_mode = 0
Перезапускаем сервис:
systemctl restart mariadb
* в данном примере мы перезапустили сервис для mariadb.
Как настроить SSH сервер (sshd)
Служба sshd считывает настройки из конфигурационного файла /etc/ssh/sshd_config. Этот файл содержит пары «ключевое слово — аргумент», одна пара на одной строке. Для каждого ключевого слова будет использовано первое полученное значение (исключение составляют несколько директив, которые можно использовать несколько раз, и каждого значение будет учтено, например это Port и ListenAddress).
Опционально аргументы можно заключить в двойные кавычки («), чтобы передать аргументы, содержащие пробелы.
Ключевые слова не чувствительны к регистру, а аргументы чувствительны к регистру.
Многие директивы закомментированы, но они указывают на значение по умолчанию, которое всё равно используется. Если вас устраивает значение по умолчанию, то не нужно ничего менять. Если же вы хотите другое значение, то нужно раскомментировать строку с соответствующей директивой (убрать символ #) и внести изменения.
Дополнительно можно указать опции командной строки — они имеют приоритет над настройками в конфигурационном файле.
Файлы/директории
Узнать абсолютный путь до текущего каталога
pwd
Удалить папку со всем ее содержимым
rm -R /path/to/dir
Создать символьную ссылку
ln -s /etc/apache2/sites-available/site.com.conf /etc/apache2/sites-enabled/site.com.conf
Подсчитать количество файлов в текущем каталоге (включая вложенные)
find . -type f | wc -l
Подсчитать занимаемый размер каталога
du -sh /var
Вывести на экран количество файлов в поддиректориях текущего каталога
for D in `ls -Fl | grep / | awk ‘{print $9}’` ; do echo $D `find -L $D -type f -print | wc -l` ; done
Удалить в директории все файлы старше N дней
find /home/user -type f -mtime +N -exec rm {} ;
Узнать информацию об использовании inodes (файловых дескрипторов):
df -i
Создать патч
diff -uN file.orig file.new > file.patch
Наложить патч
patch file.orig < file.patch
Найти определенные файлы и скопировать их с сохранением иерархии
find . -name «ru.po» -exec cp —parents «{}» /destination/dir/ «;»
Управление пользователями
Так как Linux заточена под использование большим количеством людей одновременно, разработчики придумали для нее продвинутую иерархию пользователей. У каждого свой набор прав и свои возможности. И есть целый набор команд для работы с ними. Рассмотрим главные.
useradd — создает на сервере новую учетную запись. По сути, нового пользователя. Синтаксис: useradd имя будущей учетной записи. Имя можно указать любое на свой вкус. Потом останется лишь добавить для нового аккаунта пароль.
passwd — задает пароль для учетной записи. Работает вкупе с предыдущей командой. То есть сразу после создания аккаунта, пишем: passwd имя новой учетной записи. После этого система попросит придумать и указать пароль для новой учетной записи.
Система безопасности в Linux не показывает во время ввода пароля даже звездочки, но это не значит, что он не вводится. Продолжайте набирать вслепую, а как закончите, нажмите Enter, и все сработает. И не бойтесь запутаться, вас попросят повторить придуманный пароль.
userdel — удаляет выбранную учетную запись. Синтаксис: userdel имя учетной записи, которую нужно стереть
usermod — вносит изменения в характеристики существующих учетных записей, лишает их контроля или вовсе приостанавливает работу. Делает все, что не связано с созданием и удалением аккаунтов. Используется только вместе с дополнительными опциями:
- -с — добавляет комментарий к аккаунту (можно вписать любой текст по желанию, чтобы запомнить для чего нужен выбранный пользователь).
- -d — меняет расположение домашней директории выбранной учетной записи.
- -e — указывает время, которое будет существовать аккаунт (после этого сработает автоматический userdel).
- -g — меняет группу, к которой принадлежит аккаунт.
- -G — привязывает аккаунт к выбранной группе.
- -L — блокирует пользователя.
- -m — перемещает контент из домашней папки пользователя в другую папку.
- -p — устанавливает незашифрованный пароль (лучше так не делать).
- -s — задает конкретную оболочку для нового аккаунта на усмотрение администратора компьютера.
- -U — снимает блокировку с выбранной учетной записи.
Принцип работы GTID
GTID появился с MySQL 5.6 и представляет собой уникальный 128-битный глобальный идентификационный номер (SERVER_UUID), который увеличивается с каждой новой транзакцией. Выглядит GTID примерно так:
Классическая репликация MySQL без GTID использует позицию в бинарном логе. Но благодаря GTID больше не нужно разбираться с вычислениями позиции бинлога. Из преимуществ GTID является согласованность данных, т.е. на сервере (как на мастере, так и на слейве) будет подтверждена одна и только одна транзакция с одним GTID, а любые другие транзакции, имеющие такой же UUID, будут проигнорированы. Подробную теорию можно дополнительно изучить на официальном сайте MySQL.
Использование GTID – это хорошая практика, т.к. данные между мастером и слейвом более консистентные с GTID, настройка ещё быстрее и проще.
В MySQL при использовании GTID есть две глобальные переменные, о которых необходимо знать:
- gtid_executed – содержит набор всех транзакций из бинарного лога;
- gtid_purged – содержит набор транзакций, которые были зафиксированы на сервере, но не содержащиеся в бинарном логе. gtid_purged является подмножеством gtid_executed.
Вышеописанные переменные получают свои значения при каждом запуске MySQL. На мастер-сервере это выглядит так:
Как разрешить или запретить пользователям входить через SSH
Всего имеется 4 директивы, которые разрешают или запрещают пользователям и группам подключаться к SSH. Эти директивы в порядке обработки: DenyUsers, AllowUsers, DenyGroups и наконец AllowGroups.
За ключевым словом AllowUsers может следовать список шаблонов имён пользователей, разделённых пробелами, например:
AllowUsers alice bob
Если директива используется, то вход разрешён только пользователям, имена которых соответствуют шаблонам. Принимаются только имена пользователей, цифровые идентификаторы пользователей не распознаются. По умолчанию вход разрешён для всех пользователей. Если шаблон имеет вид ПОЛЬЗОВАТЕЛЬ@ХОСТ, тогда раздельно проверяются ПОЛЬЗОВАТЕЛЬ и ХОСТ и вход разрешается только определённым пользователям с определённых хостов. В качестве ХОСТа могут быть адреса в формате CIDR, то есть в виде адрес/маска.
Далее о шаблонах, которые могут принимать директивы DenyUsers, AllowUsers, DenyGroups и AllowGroups.
Настройка mysqldump
Как я уже сказал, бэкап базы данных mysql я буду делать с помощью mysqldump. В общем случае выполнить его проще простого. Вот пример команды, которая сделает дамп базы данных:
# mysqldump --databases dbname -u'root' -p'password' > dbname.sql
Отдельно отмечу, что использовать выгрузку сырых данных из базы в виде текстового дампа имеет смысл для не очень больших баз. Думаю, что для баз размером до 10-15 Гб это можно делать. Сжатые дампы будут весить 500-1000 Мб. Если базы больше, лучше использовать другие способы. Например, бинарный бэкап с помощью Percona XtraBackup.
Мы выгрузили дамп базы dbname в отдельный файл. Дальше его можно сжать и отправить на сервер бэкапов. Но, как это обычно бывает, везде есть куча нюансов. Дефолтные настройки mysqldump очень часто не подходят. Например, наиболее распространенная проблема — блокировка таблиц во врем дампа с дефолтными настройками. Если в базу активно идёт запись, а тут вы запускаете mysqldump и блокируете таблицы, возникают проблемы с записью.
Все параметры mysqldump можно посмотреть в документации — https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html. Если вы не указываете никакие дополнительные ключи, то по умолчанию используется ключ —opt, который включает в себя следующие параметры:
- —add-drop-table — в дампе перед каждым созданием таблицы добавляется строка с её удалением. То есть при заливке дампа сначала удаляется таблица, потом создается пустая и в неё заливаются данные из дампа. И так для всех таблиц.
- —add-locks — в дампе в строке перед созданием таблицы ставится команда на ее блокировку, а после окончания заливки данных блокировка снимается. Это позволяет гарантировать успешную запись данных в таблицу при загрузке дампа.
- —create-options — в дамп добавляются команды на создание таблиц.
- —disable-keys — в дамп добавляются параметры, отключающие создание индексов рядом с каждой командой insert. Это позволяет ускорить загрузку дампа, а индексы создаются, когда все строки будут добавлены.
- —extended-insert — используется особый синтаксис multiple-row для команд insert.
- —lock-tables — блокировка таблиц перед созданием дампа. Этот параметр частенько мешает в движке innodb и его лучше не использовать (параметр, а не движок).
- —quick — ускоренный механизм получения строк таблицы.
- —set-charset — добавляет в дамп информацию о кодировках.
В целом, все дефолтные параметры можно считать полезными и удобными, кроме блокировки таблиц. Для дампа innodb баз, а это самый популярный движок хранения данных в mysql, рекомендуется не использовать lock-tables, а вместо этого включать механизм single-transaction. С этим параметром для обеспечения целостности данных в дампе используется не механизм блокировок, а учёт транзакций.
Я не буду сейчас подробно останавливаться на теории, так как все эти параметры очень хорошо описаны в документации. Можете сами зайти и почитать. Показываю свой набор ключей для создания дампов баз данных mysql с помощью mysqldump.
# mysqldump --add-drop-database --add-locks --create-options --disable-keys --extended-insert --single-transaction --quick --set-charset --routines --events --triggers --comments --quote-names --order-by-primary --hex-blob --databases dbname -u'root' -p'password' > dbname.sql
Это мой универсальный набор параметров mysqldump, которые я обычно использую, когда делаю выгрузку базы данных mysql. В целом, тут почти дефолтные настройки, только убраны блокировки, добавлены транзакции и некоторые другие сущности mysql типа events, triggers и т.д.
Инкрементный бэкап Mysql
Основное удобство XtraBackup как раз в простых, быстрых и удобных инкрементных бэкапах для mysql. Допустим, по примеру выше, вы сделали полный бэкап. Он должен быть не сжатый. Теперь на основе этого полного бэкапа, можно сделать инкрементный, где будут только изменения со времени полного бэкапа.
# xtrabackup --backup --target-dir=/root/backupdb/inc1 --incremental-basedir=/root/backupdb/full
Можно проверить, что конкретно в себя включает полный бэкап, а что инкремент. В директории с бэкапами есть файл xtrabackup_checkpoints примерно следующего содержания.
backup_type = full-backuped from_lsn = 0 to_lsn = 17687056 last_lsn = 17687065 compact = 0 recover_binlog_info = 1 flushed_lsn = 17687065
LSN — log sequence number. Это регистрационные номера транзакций. В данном случае полный бэкап начинается с нулевой транзакции и заканчивается 17687056. Теперь смотрим этот же файл в директории inc1.
backup_type = incremental from_lsn = 17687056 to_lsn = 17710039 last_lsn = 17710048 compact = 0 recover_binlog_info = 1 flushed_lsn = 17710048
Инкрементный бэкап базы начался с той транзакции, на которой закончился полный. Следующий инкрементный архив может быть создан опять на базе полного, либо уже инкрементного. То есть либо так:
# xtrabackup --backup --target-dir=/root/backupdb/inc2 --incremental-basedir=/root/backupdb/full
либо так:
# xtrabackup --backup --target-dir=/root/backupdb/inc2 --incremental-basedir=/root/backupdb/inc1
Можете сами проверить, какие номера транзакций будут в этих случаях. Я обычно делаю раз в день полный бэкап и потом инкрементные на основе полного. Цепочки инкрементных бэкапов делать не люблю, так как если возникнут проблемы с одним из архивов цепочки, возникнут трудности с восстановлением.
Предлагаю вот такой скрипт для инкрементных бэкапов — mysql-inc-backup.sh.
#!/bin/bash DATA1=`date +%Y-%m-%d` DATA2=`date +%H-%M-%S` xtrabackup --backup --target-dir=/root/backupdb/$DATA1/inc-$DATA2 --incremental-basedir=/root/backupdb/$DATA1/full
Можете запускать его раз в час. Тогда вместе со скриптом полного бэкапа, который выполняется раз в день, у вас будут вместе с ним в одной папке инкрементные бэкапы по часам. На следующий день можно запаковать всю директорию с бэкапами за прошлый день и отправить на сервер бэкапа.
Хотя лично я делаю не так. Я сервером бэкапа сам постоянно подключаюсь к целевым серверам и забираю данные, подчищаю за собой. А на сервере уже обрабатываю их, пакую и распределяю по директориям.
Как разрешить подключение только с определённого IP или группы IP. Как заблокировать определённые IP для SSH
С помощью директив (перечислены в порядке обработки) DenyUsers, AllowUsers, DenyGroups и AllowGroups и шаблонов можно настроить разрешения для доступа к SSH по IP.
Рассмотрим несколько примеров. Если в файл sshd_config добавить фильтрацию с AllowUsers:
AllowUsers johndoe@192.168.1.* admin2@192.168.1.* otherid1 otherid2
Это разрешит доступ для johndoe и admin2 только с адресов 192.168.1.*, а для otherid1, otherid2 доступ будет открыт с любого адреса.
Чтобы разрешить доступ к SSH любым пользователям, но только с определённых IP, в файле /etc/ssh/sshd_config используйте директиву AllowUsers с подстановочным символом:
AllowUsers *@192.168.1.100
Или:
AllowUsers *@ИМЯ_ХОСТА
Возможно ограничить ключ ssh или ключ на основе ca набором адресов в файле .ssh/authorized_keys домашнего каталога данного пользователя:
from="192.168.1.*,192.168.2.*" ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABA...etc...mnMo7n1DD useralias
В этом примере открытый ключ для useralias будет действовать только с заданных адресов.
Заключение
Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.
Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.
Для мониторинга бэкапов в целом, можете воспользоваться моей объемной статьей по теме — Мониторинг бэкапов с помощью zabbix.
Онлайн курс по Linux
Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «Administrator Linux. Professional» в OTUS. Курс не для новичков, для поступления нужны базовые знания по сетям и установке Linux на виртуалку. Обучение длится 5 месяцев, после чего успешные выпускники курса смогут пройти собеседования у партнеров.
Что даст вам этот курс:
- Знание архитектуры Linux.
- Освоение современных методов и инструментов анализа и обработки данных.
- Умение подбирать конфигурацию под необходимые задачи, управлять процессами и обеспечивать безопасность системы.
- Владение основными рабочими инструментами системного администратора.
- Понимание особенностей развертывания, настройки и обслуживания сетей, построенных на базе Linux.
- Способность быстро решать возникающие проблемы и обеспечивать стабильную и бесперебойную работу системы.
Проверьте себя на вступительном тесте и смотрите подробнее программу по .
