Введение
В качестве примера я рассмотрю серверы с установленными там продуктами bitrix, работающими в bitrixenv. Особенностью будет то, что bitrix до сих пор использует не самую свежую версию mysql от percona — Percona Server for MySQL 5.7. Тем не менее, проблем с этим нет никаких. Версия будет поддерживаться минимум до октября 2023 года.
Для полных и инкрементных бэкапов я рассмотрю утилиту Percona XtraBackup, которая позволяет делать архивы баз данных на лету без блокировок таблиц. В моей статье будет использоваться версия 2.4, так как именно она поддерживает mysql 5.7. Это максимально доступная версия в репозиториях, которые устанавливает окружение bitrixenv.
Примеры в этой статье будут актуальны практически для всех версий Mysql и XtraBackup, так как в подходах и командах отличий почти нет
Важно знать, что последняя версия XtraBackup на момент написания статьи была 8.0 и она поддерживает популярный форк mysql — MariaDB только до версии 10.2 включительно, да и то с оговорками. Для более поздних версий mariadb рекомендуется использовать mariabackup
Это форк XtraBackup, который в использовании практически ничем не отличается от оригинала.
Сегодня я рассмотрю инкрементные бэкапы mysql только с помощью XtraBackup, а так же полные бэкапы в том числе с помощью mysqldump. MariaDB и Mariabackup рассматривать не буду. Принципиальных отличий между ними нет. Там все то же самое.
Возможные ошибки
В процессе восстановления мы можем столкнуться с разными ошибками. Рассмотрим их примеры.
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.
Бэкап отдельной таблицы или базы
Не всегда нужны архивные копии всего 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 строк, что и были до этого.
Бэкап и восстановление mysql с помощью mysqldump
Теперь просто для справки приведу примеры бэкапа и восстановления баз данных mysql с помощью mysqldump. Для небольших баз этого инструмента хватает за глаза и использовать что-то другое не имеет смысла. Преимущество xtrabackup в скорости работы и в возможности без проблем сделать инкрементный бэкап. Если он вам не нужен и база не большая, достаточно будет старого доброго mysqldump.
Бэкап всех баз mysql сервера с его помощью:
# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases > /root/backupdb/all.sql
Можно сразу же сжимать его.
# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases | /usr/bin/gzip -c > /root/backupdb/`date "+%Y-%m-%d"`sql.gz
Бэкап конкретной базы данных.
/usr/bin/mysqldump sitemanager -uroot -p'password' | /usr/bin/gzip -c > /root/backupdb/sitemanager_`date "+%Y-%m-%d"`sql.gz
Мне чаще всего мешают в дампе команды на создание базы данных — CREATE DATABASE, поэтому я их убираю ключом no-create-db.
/usr/bin/mysqldump --no-create-db sitemanager -uroot -p'password' | /usr/bin/gzip -c > /root/backupdb/sitemanager_`date "+%Y-%m-%d"`sql.gz
Для того, чтобы восстановить базу данных из дампа, можно воспользоваться следующими командами. Выполняются из консоли mysql.
# mysql -uroot -p mysql> use sitemanager; mysql> source /root/backupdb/sitemanager.sql;
Если дамп без команды на создание базы данных и ее нет у вас на сервере, то не забудьте ее перед этим создать. Так же восстановить базу данных из дампа можно следующим образом.
mysql> use sitemanager; mysql> \. /root/backupdb/sitemanager.sql
Вот простенький скрипт, который позволяет забэкапить все базы данных сервера, при этом бэкап каждой базы будет в отдельном файле. Это удобнее, чем все базы единым файлом. Оттуда потом трудно доставать конкретную базу.
#!/bin/bash
for i in `mysql -uroot -p'password' -e'show databases;' | grep -v information_schema | grep -v Database`;
do
/usr/bin/mysqldump -uroot -p'password' $i | /usr/bin/gzip -c > /root/backupdb/`date +%Y-%m-%d`-$i.sql.gz;
done
Так же могу порекомендовать вот этот скрипт для бэкапа — https://github.com/adegtyarev/mysqlbackup. Описывать его не буду, по комментариям в скрипте понятен его функционал.
Если вам нужно из полного бэкапа mysql восстановить отдельную таблицу, то ее можно выделить из полного дампа через обычный awk примерно вот так.
# cat sitemanager.sql | /usr/bin/awk '/CREATE TABLE `b_catalog_discount`/,/UNLOCK TABLES/' > /tmp/b_catalog_discount.sql
Дальше через source можно восстановить данные из этого дампа отдельной таблицы.
Иногда бывает полезно сделать не просто полный backup базы данных, а разбить его сразу на таблицы. Тут поможет следующий скрипт.
#!/bin/bash
USER='root'
PASS='R(zDXcVUmI[zwx%aNBTN'
MYSQL="mysql --user=$USER --password=$PASS --skip-column-names";
DIR="/root/backupdb"
for s in mysql `$MYSQL -e "SHOW DATABASES"`;
do
mkdir $DIR/$s;
for t in `$MYSQL -e "SHOW TABLES FROM $s"`;
do
/usr/bin/mysqldump --user="$USER" --password="$PASS" --opt $s $t | /usr/bin/gzip -c > $DIR/$s/$t.sql.gz;
done
done
В таком варианте он сделает потабличный бэкап всех баз данных. Если вам не нужны все базы, то в цикле можете вместо предложенного перебора всех существующих баз указать одну конкретную.
for s in mysql sitemanager;
Восстановить потом всю базу из такого потабличного бэкапа можно таким образом.
#!/bin/bash
#
DB=sitemanager;
USER='root'
PASS='R(zDXcVUmI[zwx%aNBTN'
DIR="/root/backupdb/sitemanager"
for s in `ls -1 $DIR`;
do
echo "--> $s restoring... ";
zcat $DIR/$s | /usr/bin/mysql --user=$USER --password=$PASS $DB;
done
При использовании пароля в открытом виде в mysql или mysqldump, в консоль постоянно сыпятся предупреждения.
mysql: Using a password on the command line interface can be insecure.
Чтобы их не было, перенесите, как я показывал выше, пароль в отдельный файл ~/.my.cnf, а из скрипта уберите авторизацию вообще.
На этом, пожалуй, насчет бэкапа баз mysql сервера все. Поделился основными своими наработками.
Добавление базы в MariaDB
Для работы с базой необходимо после создания добавить пользователя к этой базе и назначить права.
Добавление базы с параметрами
MariаDB > CREATE DATABASE `base` CHARACTER SET utf8 COLLATE utf8_general_ci; = вывод команды = Query OK, 1 row affected (0.00 sec)
Просмотр пользователей с выводом их прав
MariaDB > SELECT User,Host FROM mysql.user; = вывод команды = +--------+-----------+ | User | Host | +--------+-----------+ | root | localhost | | mysql | localhost | +--------+-----------+ 2 rows in set (0.005 sec)
Права пользователя баз данных MariaDB
В случае необходимости подключатся к базе с других компьютеров необходимо создать пользователя с нужным параметром и дать права на доступ в настройках сервера MariaDB!
Права на доступ к серверу баз данных делается в двух местах:
- Параметр bind-address=0.0.0.0 в конфигурационном файле самого сервера баз MariaDB разрешающий подключатся с любого адреса (или укажите конкретный IP) в разделе ;
- Права пользователя на возможность удаленного подключения к базе данных.
Для работы с базами мне удобно использовать и эта программа позволяет настроить доступ к базам работающих на разных серверах.
Менять права пользователя root не желательно, но можно создать пользователя и дать ему полные права. Держать ещё одного пользователя с полными правами не разумно, но иногда необходимо. Добавляются пользователю полные права командой:
MariaDB > GRANT ALL PRIVILEGES ON *.* to 'имя пользователя'@'%';
После добавления пользователя с полными правами и имеющегося PhpMyAdmin настроенного для подключения к другим серверам баз данных, можно зайти и создать все необходимые базы и пользователей.
Например, ниже представлена полная версия команд после выполнения которых будет создан пользователь sevo44 с полными правами:
mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.4.8-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB > CREATE USER `sevo44`@`%` IDENTIFIED BY 'ПАРОЛЬ'; Query OK, 0 rows affected (0.008 sec) MariaDB > GRANT ALL PRIVILEGES ON *.* to 'sevo44'@'%'; Query OK, 0 rows affected (0.017 sec) MariaDB > FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) MariaDB > exit Bye
Для безопасности я никогда не создаю пользователя с полными правами который может подключатся с удаленных мест. Для каждой базы свой пользователь!
В случае если надо поменять права имеющемуся пользователю это делается следующей командой, после подключения к серверу баз данных:
# Команда смены прав доступа пользователя на подключение с любого адреса (параметр %) MariaDB > UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='имя_пользователя'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # Команда смены прав доступа на базы MariaDB > UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='имя_пользователя'; Query OK, 0 rows affected (0.03 sec) Rows matched: 0 Changed: 0 Warnings: 0
Добавление пользователя
# Права на доступ только с localhost MariаDB > CREATE USER `base_user`@localhost IDENTIFIED BY 'ПАРОЛЬ'; # Права на доступ с любого адреса (при использовании знаков в названиях код заключается в кавычки! MariаDB > CREATE USER `base_user`@`%` IDENTIFIED BY 'ПАРОЛЬ'; # Права на доступ с адреса 10.10.0.2 (при использовании знаков в названиях код заключается в кавычки! MariаDB > CREATE USER `base_user`@`10.10.0.2` IDENTIFIED BY 'ПАРОЛЬ'; = правильный вывод команды для любой команды = Query OK, 0 rows affected (0.10 sec)
Назначение пользователя базе
# При назначении прав выставляем пользователя с нужными правами! MariaDB > GRANT ALL PRIVILEGES ON base.* to base_user@localhost; = вывод команды = Query OK, 0 rows affected (0.04 sec)
После всех манипуляция с базами необходимо обновить права доступа
MariаDB > FLUSH PRIVILEGES; = вывод команды = Query OK, 0 rows affected (0.02 sec)
Решение проблемы backup через plink
В общем, ранее меня эти проблемы не напрягали, т.к. бэкап нужно было делать не часто, а когда я стал дорабатывать блог, то оперативные архивы стали очень необходимы и я стал искать решения. Я знал, что подключаясь из Linux — это делается элементарно, через . Поэтому я стал искать аналогию на Windows. В конечном счете, необходимо было выполнить команду на удаленном сервере, которая создаст архив и сложит его в локальную папку на клиенте. Долго искать не пришлось — мне помог plink. Как говориться, все гениальное — просто: качаем . Выполняем следующие действия, открыв cmd в каталоге с plink.exe:
c:\PuTTY_folder>plink -pw P@sswOrd user@ssh.host.example tar czf - /var/www/host.example > c:\backup_by_plink\backup_of_my_site.tar.gz
Здесь:
- plink — собственно, сам exe’шник
- -pw P@sswOrd — пароль к учетной записи user
- user@ssh.host.example — имя пользователи и хост, на котором буде выполнены программа
- tar czf — /var/www/host.example — собственно, исполняемая программа (читаем основные команды linux). Указывая «-» мы говорим tar’y выводить архив на консоль (вернее stdout).
- > — символ командной строки Windows, осуществляющий перенаправление вывода команды в файл (аналог в Linux)
- c:\backup_by_plink\backup_of_my_site.tar.gz — место сохранения архива.
Т.о. мы решаем все 4 проблемы. При этом, если отключим компрессию (ключ z), то практически снизим нагрузку на CPU на хостинге.
Ссылки
Plink и Putty — http://www.chiark.greenend.org.uk/~sgtatham/putty/
документация — http://www.chiark.greenend.org.uk/~sgtatham/putty/docs.html
документация на русском — http://putty.org.ru/docs.html
P.S.
Надеюсь, что кроме меня данная заметка будет кому-то полезна. Так же, хочу отметить, что кроме tar, тут никто не мешает использовать mysqldump, например. До новых встреч!
Создание JSON-полей в MySQL
Создание JSON-полей в MySQL
MySQL — самая популярная система управления реляционными базами данных с открытым исходным кодом.
В этом руководстве объясняется, как создавать базы данных MySQL или MariaDB с помощью командной строки.
Прежде чем вы начнете
Мы предполагаем, что в вашей системе уже установлен сервер MySQL или MariaDB.
Как установить MySQL на CentOS 7, Ubuntu 18.04, Debian 9. Как установить MariaDB на CentOS 7, Ubuntu 18.04, Debian 9
Все команды выполняются от имени администратора (минимальная привилегия, необходимая для создания новой базы данных — ) или с учетной записью root.
Чтобы получить доступ к оболочке MySQL, введите следующую команду и при появлении запроса введите пароль пользователя root MySQL:
Если вы не установили пароль для своего корневого пользователя MySQL, вы можете опустить ключ .
Если вам нужно изменить корневой пароль MySQL, следуйте этому руководству по сбросу корневого пароля MySQL через командную строку.
Создать базу данных MySQL
Создать новую базу данных MySQL так же просто, как запустить одну команду.
Чтобы создать новую базу данных MySQL или MariaDB, введите следующую команду, где — это имя базы данных, которую вы хотите создать:
Чтобы избежать ошибок, если база данных с тем же именем, которое вы пытаетесь создать, существует, вы можете использовать следующую команду:
В выводе выше вы можете увидеть что означает, что запрос был успешным, и которое говорит нам, что база данных уже существует и новая база данных не была создана.
В Linux базы данных MySQL и имена таблиц чувствительны к регистру.
Просмотреть все базы данных MySQL
Чтобы просмотреть базу данных, которую вы создали, из оболочки MySQL выполните следующую команду:
Команда выше напечатает список всех баз данных на сервере. Вывод должен быть похож на это:
Выберите базу данных MySQL
При создании базы данных новая база данных не выбирается для использования. Чтобы выбрать базу данных перед началом сеанса MySQL, используйте следующую команду:
После выбора базы данных все последующие операции, такие как создание таблиц, будут выполняться в выбранной базе данных.
Создайте базу данных MySQL с помощью mysqladmin
Вы также можете создать новую базу данных MySQL из терминала Linux с помощью утилиты mysqladmin.
Например, чтобы создать базу данных с именем , введите следующую команду и введите свой пароль пользователя root MySQL при появлении запроса:
Вывод
Вы узнали, как создавать базы данных MySQL.
Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.
Мысль Мариадб
Вы можете отключить или активировать Telnet через команду Запрос или панель управления, выполнив следующие действия в Windows 10/8/7.
Вот как можно быстро очистить старые элементы рабочего стола через командную строку, используя этот командный файл.
В этом руководстве описывается, как удалить (или удалить) базу данных MySQL или MariaDB через командную строку.
Подключение к серверу через SSH
Мы уже выяснили, что представляет собой SSH и команды для него. Теперь установим соединение с сервером.
Естественно, перед началом надо арендовать виртуальный хостинг или VDS у одного из доступных провайдеров. У Timeweb, к примеру.
Если у вас macOS или Linux
- Запускаем программу Terminal.
- Вводим в консоль команду со следующим синтаксисом ssh имя пользователя@адрес сервера. В моем случае это ssh root@89.223.127.80.
- Указываем пароль суперпользователя (его отправляет хостинг-провайдер сразу после регистрации).
- Жмем Enter.
Все. Соединение установлено, можно переходить к работе непосредственно с сервером.
Если у вас Windows
- Скачиваем и устанавливаем программу PuTTY.
- В строку IP-адрес вводим адрес своего VDS или виртуального хостинга.
- Жмем на кнопку Open.
- Вводим пароль администратора, чтобы получить доступ к управлению.
Управление протоколом SSH
У команды для подключения к удаленному PC по SSH есть две важных опции:
- ssh -p номер порта имя пользователя@адрес сервера — заменяет стандартный 22-й порт на иной, что положительно сказывается на безопасности и устойчивости к автоматическим хакерским атакам от ботов.
- ssh-copy-id -i путь до файла с ключом имя пользователя@адрес сервера— копирует ключ на сервер, чтобы вход осуществлялся без логина и пароля, а именно через ключ.
Вывод
Есть много различных методов выполнения резервного копирования в MySQL. Все они имеют свои преимущества и недостатки, но некоторые из них гораздо проще реализовать и в более широком смысле полезнее, чем другие.
Схема резервного копирования, которое вы выбираете для развертывания, будет в значительной степени зависеть от ваших индивидуальных потребностей и ресурсов, а также вашей производственной среды. Какой бы метод вы не выбрали, не забудьте проверить свои резервные копии и практику восстановления данных, так что вы можете быть уверены в том, что процесс функционирует нормально.
