Mysql dump ���� linux

Cнятие и установка дампа в MySQL (mysqldump) — tips & tricks

В теории

В теории все просто — MySQL из коробки имеет утилиту mysqldump, которая позволяет снять копию базы в виде набора SQL инструкций.

В реальности все гораздо хуже. Дело в том, что mysqldump криво работает с кодировками, отличными от Latin1. Тоесть экспортировать базу в UTF8 следуя официальной документации (man mysqldump), тоесть с указанием —default-character-set=utf8, и импортировать ее потом обратно — не представляется возможным. Кодировка будет битая в следствии «двойного преобразования», выполняемого утилитой mysqldump — пруфлинк

Собственно, по ссылке выше есть и рецеп. Суть его в том, что нужно экспортировать базу и затем импортировать ее в Latin1 вне зависимости от того, какая реально кодировка используется. При этом конечно нужно вычестить из *.sql файл инструкции SET NAMES…

Еще один важный момент, который следует учесть. Как правило мы снимаем дамп базы с именем A и пытаемся накатить его на базу с именем B. Соответственно, возможны коллизии из за того, что в созданном после экспорта файле в SQL запросах и командах СУБД будет присутствовать упоминание A. Нужно заменить все вхождения A на B, ниже под катом будет пример как это сделать средствами VIM.

И на последок, перед загрузкой дампа старую базу лучше полностью отчистить. Сделать это можно по-разному, я же для себя нашел удобным маленький shell-скрипт (drop_all.sh), который автоматизирует для меня эту работу:

#!/usr/local/bin/bash
MUSER=»$1″
MPASS=»$2″
MDB=»$3″
HOST=»$4″

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

TABLES=$($MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e ‘show tables’ | $AWK ‘< print $1>‘ | $GREP -v ‘^Tables’ )

for t in $TABLES
do
echo «Deleting $t table from $MDB database. »
$MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e «SET FOREIGN_KEY_CHECKS = 0;drop table $t;SET FOREIGN_KEY_CHECKS = 1;»
done

Итак, алгоритм

Последовательность шагов такова:

bash> mysqldump -h -u -p —default-character-set=latin1 -N > latin_dump.sql

2) Открываем получившийся в итоге файл и осуществляем замену. Сохраняем изменения.

bash> vi latin_dump.sql

3) Чистим базу перед загрузкой дампа.

4) Загружаем дамп.

bash> mysql —force -h -u -p —default-character-set=latin1 -D

Источник

MySQL шпаргалки

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

Сегодня я решил опубликовать свои шпаргалки, на самые часто встречающиеся ошибки при работе с MySQL.

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump —no-data — u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE

Заливаем архив бекапа в базу
gunzip
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e , т.е.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql , где:

• -Q оборачивает имена обратными кавычками
• -c делает полную вставку, включая имена колонок
• -e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

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

Общие факты

• Полезно под каждую базу на боевом сервере создавать своего пользователя
• Кодировка базы может быть любой, если она UTF8
• В большинстве случаев лучше использовать движок InnoDB
• В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
• Новую копию MySQL всегда можно настроить и оптимизировать
• Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
  ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа

• На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.
  На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.
• Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
• Для хранения булевых значений, нужно использовать TINYINT(1)
• А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

• В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
• Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
• Самая правильная кодировка для вашей БД UTF8

Не забывайте, что

• DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
• TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
• Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:
  SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

Отладка

• Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
• А потом оптимизировать запросы через EXPLAIN
• И наблюдать за запросами удобно через программу mytop

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

Источник

📑 Дампы баз данных MySql — mysqldump

Общее описание программы mysqldump

Для работы с дампами баз данных MySql существует клиентская программа mysqldump, изначально написанная Игорем Романенко (Igor Romanenko). Mysqldump входит в состав дистрибутивов всех клонов MySql. Полная документация — http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html, а здесь только краткая шпаргалка по использованию основных функций.

Программа mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL сервер (не обязательно MySQL сервер).

Существует три основных способов использования mysqldump: создать набор из одной или более таблиц, набор из одного или более полных баз данных, или весь сервер MySQL, как показано здесь:

Например, сделать дамп базы DATABASE:

• -u или -–user=… — имя пользователя
• -h или —host=… — удаленный хост (для локального хоста можно опустить этот параметр)
• -p или —password — запросить пароль
• database — имя базы данных
• damp.sql — файл для дампа

Залить базу из файла дампа:

Основные опции программы mysqldump:

Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.

Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.

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

Используется полная форма оператора INSERT (с именами столбцов).

Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

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

Использовать команду INSERT DELAYED при вставке строк.

Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. Очень рекомендуется использовать.

Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases.

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

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

Добавляет ключевое слово REPLACE в оператор INSERT.

Параметр направляет дамп в файл file. Имеет смысл только в Windows. В Linux системах лучше перенаправить результат в файл при помощи последовательностей > и >>.

Файл сокета для подсоединения к localhost. Это для любителей использовать нестандартные настройки.

Прерывает действие параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

Расширенный режим вывода. Вывод детальной информации о работе программы.

Вывести информацию о версии программы.

Выполнить дамп только для выбранных записей. Кавычки обязательны.

Вывести справочную информацию и выйти из программы.

Примеры использования программы mysqldump

Создание только структуры базы без данных

Создание дампа только нескольких таблиц

Создание дампа и архивирование его

Создание архивного дампа с указанием его даты

Источник

ИТ База знаний

Курс по Asterisk

Полезно

— Узнать IP — адрес компьютера в интернете

— Онлайн генератор устойчивых паролей

— Онлайн калькулятор подсетей

— Калькулятор инсталляции IP — АТС Asterisk

— Руководство администратора FreePBX на русском языке

— Руководство администратора Cisco UCM/CME на русском языке

— Руководство администратора по Linux/Unix

Навигация

Серверные решения

Телефония

FreePBX и Asterisk

Настройка программных телефонов

Корпоративные сети

Протоколы и стандарты

Как сделать резервную копию и восстановить базу данных MySQL с помощью Mysqldump

Делай бекапы — не будет факапов

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

Онлайн курс по Linux

Мы собрали концентрат самых востребованных знаний, которые позволят тебе начать карьеру администратора Linux, расширить текущие знания и сделать уверенный шаг к DevOps

Файлы резервных копий, созданные утилитой mysqldump, представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может генерировать файлы в формате CSV и XML. Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.

Синтаксис команды Mysqldump

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

Выражения утилиты mysqldump имеют следующую форму:

• options — параметры mysqldump
• file.sql — дамп (резервная копия) файла

Для использования команды mysqldump сервер MySQL должен быть доступен и запущен.

Резервное копирование одной базы данных MySQL

Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы данных с именем database_name , используя пользователя root , и сохранить ее в файл с именем database_name.sql , вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого вы используете для выполнения экспорта, и этот пользователь не требует пароля, вы можете пропустить опции -u и -p :

Резервное копирование нескольких баз данных MySQL

ля резервного копирования нескольких баз данных MySQL одной командой вам нужно использовать параметр —database , за которым следует список баз данных, которые вы хотите сделать резервную копию. Каждое имя базы данных должно быть разделено пробелом.

Команда выше создаст файл дампа, содержащий обе базы данных.

Резервное копирование всех баз данных MySQL

Используйте опцию —all-database для резервного копирования всех баз данных MySQL:

Как и в предыдущем примере, команда выше создаст один файл дампа, содержащий все базы данных.

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможность резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла bash FOR:

Команда выше создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.

Создание сжатой резервной копии базы данных MySQL

Если размер базы данных очень большой, рекомендуется сжать вывод. Для этого просто перенаправьте вывод в утилиту gzip и перенаправьте его в файл, как показано ниже:

Создать резервную копию с отметкой времени

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

Команда выше создаст файл в следующем формате database_name-20200223.sql

Восстановление дампа MySQL

Вы можете восстановить дамп MySQL с помощью инструмента mysql. Общий синтаксис команды выглядит следующим образом:

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

В следующем примере первая команда создаст базу данных с именем database_name , а затем импортирует в нее дамп database_name.sql :

Восстановление одной базы данных MySQL из полного дампа MySQL

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-database и хотите восстановить одну базу данных из файла резервной копии, который содержит несколько баз данных, используйте параметр —one-database , как показано ниже:

Экспорт и импорт базы данных MySQL одной командой

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

Команда выше передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед выполнением команды убедитесь, что база данных уже существует на удаленном сервере.

Автоматизация резервного копирования с помощью Cron

Автоматизация процесса резервного копирования баз данных так же проста, как создание задания cron, которое будет запускать команду mysqldump в указанное время.

Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:

1. Создайте файл с именем .my.cnf в вашем домашнем каталоге пользователя: Скопируйте и вставьте следующий текст в файл .my.cnf . Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.
2. Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ, используя команду cmod (подробнее про которую можно прочесть тут):
3. Создайте каталог для хранения резервных копий при помощи комадны mkdir (про нее тоже есть статья):
4. Откройте ваш пользовательский файл crontab: Добавьте следующее задание cron, которое будет создавать резервную копию имени базы данных mydb каждый день в 3 часа ночи: Не забудьте заменить username вашим реальным именем пользователя. Вы также можете создать еще один cron job, чтобы удалить любые резервные копии старше 30 дней: Конечно, вам нужно настроить команду в соответствии с вашим местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим Руководством по поиску файлов в Linux с помощью командной строки.

Заключение

Это руководство охватывает только основы, но оно должно быть хорошим началом для тех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump. Если вы хотите найти больше материалов про базы данных, то просто наберите sql в нашем поиске!

Мини — курс по виртуализации

Знакомство с VMware vSphere 7 и технологией виртуализации в авторском мини — курсе от Михаила Якобсена

Источник