/ mysql: как сделать дамп через консоль (терминал)

mysql: как сделать дамп через консоль (терминал)

Для бэкапа баз можно воспользоватся утилитой mysqldump. Она сделает дамп базы mysql в текстовый файл.

mysqldump -uuser -ppasswd -f database_name > dump.sql

Этой командой мы делаем бэкап базы данных под именем database_name в файл dump.sql

Еще несколько вариантов:

mysqldump -uuser -ppasswd -f --default-character-set=utf8 database_name | gzip -c > filename.sql.gz

Если нужно автоматизировать удаление старых архивов, можно воспользоваться cron и командой find. Запуская периодически

find ~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} ;

Тем самым Вы будете удалять архивы, которые "старше" 7 дней.

Несколько полезных параметров mysqldump:

Databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup mysql создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;

All-databases позволяет сделать backup всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);

Add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;

No-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;

Восстановление из бэкапа

mysql -uroot -p --database DB --default-character-set=UTF-8

Оптимизация и обслуживание базы mysql

Есть и очень ленивая версия процесса, обработает все MyISAM таблицы во всех БД, доступных указанному пользователю и работает без остановки сервера MySQL:

mysqlcheck -u root -p --optimize --all-databases

Или в конкретной БД:

mysqlcheck -u root -p --optimize имя_базы_данных

Без оптимизации, только проверку и ремонт можно сделать и для таблиц типа InnoDB:

mysqlcheck -u root -p --auto-repair --check --all-databases

Нет необходимости убеждать опытного Джумловода о регулярном бекапе сайта (backup - резервное копирование данных). Нередки взломы сайтов, бывают сбои у хостера, наконец, не исключена и "ошибка пилота", когда вы сами, случайно (тренируясь с командами Unix Shell , например), удалите весь сайт.

В CMS Joomla есть два объекта, которые нужно периодически резервировать: база данных MySQL и собственно файлы самой Joomla. Сущестует несколько способов создания и сохранения резервных копий, каждый имеет свои достоинства и недостатки. Для наглядности приведу их в виде табличек:

Резервное копирование средствами хостинг-провайдера. Некоторые хостеры ежедневно делают резервную копию базы данных Joomla (и не только этой CMS) и файлов сайта. В случае необходимости, их можно восстановить одновременно или в отдельности.
Достоинства	Недостатки
Все происходит автоматически, вам не нужно беспокоитсья, что забудете сделать резервную копию вовремя Существуют несколько копий, бэкапы делаются каждые сутки. Вы можете восстановить, например, сайт на состояние 3 дня назад	Резервные копии отнимают дисковое пространство, отведенное вам хостером Хранится ограниченное количество копий (как правило не более семи) за последние дни Соответственно, если вы не вовремя заметили необходимость восстановления - вы рискуете потерять нужную резервную копию Нет возможности настроить - какие директории подлежат бекапу, а какие нет

Резервное копирование средствами с помощью специальных компонентов Joomla (например - "JoomlaPack")
Достоинства	Недостатки
Устанавливается в Joomla как стандартный компонент и всегда "под рукой" Некоторые из них могут работать по расписанию и отсылать бекап базы MySQL на e-mail	Долгое время работы (создания бекапа) Не всегда достаточно отведенного на хостинге времени для завершения работы скрипта (по умолчанию PHP скрипту дается 30 секунд) Ненадежная работа на больших сайтах Для компонентов, использующих Ajax-технологию (почти все) - требуется надёжное Интернет - соединение

Резервное копирование сторонними программами файлов и базы данных по-отдельности
Достоинства	Недостатки
Работают не только с Joomla, но и с другими CMS Позволяет выбрать какие директории резервировать, а какие нет Доступны некоторые дополнительные функции, например: работа с файлами на хостинге, сброс (восстановление) пароля CуперАдминистратора Joomla Позволяет создавать и распаковывать ZIP - архивы на сервере	Приходится устанавливать отдельные скрипты на хостинг, настраивать их, заводить для них отдельные пароли "Полуавтоматическая" работа - вы должны сами указывать какие папки резервировать и следить за процессом Создание бекапа файлов по частям (из-за 30 секундного ограничения работы скрипта) Потенциальная опасность удалить (из-за невнимательности) нужные файлы или папки Не всегда работает на бесплатных хостингах (например сайт, www.110mb.com)

В этой статье я опишу последний вариант: резервное копирование файлов и базы данных CMS Joomla сторонними программами.

Как сделать дамп базы данных MySQL. Создание резервной копии базы данных CMS Joomla

Программа создает резервную копию базы данных MySQL , независимо от того, какой CMS вы пользуетесь. Это отдельный PHP - скрипт.

База данных (БД) - это программное средство, предназначенное для хранения и обработки структурированных данных значительного объема. На нашем хостинге вы можете использовать БД MySQL и SQ Lite.

MySQL - одна из самых распространенных бесплатных систем управления базами данных. Подавляющее большинство имеющихся на рынке систем управления сайтами (CMS), форумов, блогов, галерей и прочих программ используют MySQL для своей работы - в частности, все программные продукты, доступные для автоматической установки из Панели управления.

Создание новой БД

Для создания новой базы данных в меню Панели управления хостингом перейдите в раздел «Базы данных» и на открывшейся странице нажмите кнопку «Создать базу данных». В форме нужно указать:

1. Имя базы данных - имеет вид логин_имя , где логин - пользователь Панели управления, а имя - произвольный набор латинских букв и цифр. Получившееся в результате полное имя БД не может быть длиннее 32 символов.
2. Имя пользователя - имеет вид, аналогичный имени БД, и не может быть длиннее 16 символов. Это пользователь, который имеет доступ к одной или нескольким базам данных, и у каждого пользователя есть свой пароль.
3. Пароль пользователя - может быть задан вручную или произвольно. При выборе существующего пользователя для него можно задать новый пароль - в таком случае прежнее значение пароля перезапишется.

Заполнив поля, нажмите кнопку «Создать базу данных». Реквизиты для подключения понадобятся в конфигурационных файлах CMS или других скриптов, а также для удаленного подключения к БД посредством консоли или приложения phpMyAdmin.

Создание дампа БД

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

Создавать дампы баз данных можно несколькими способами:

• через раздел «Базы данных» Панели управления хостингом, кликнув по имени БД и нажав кнопку «Скачать резервную копию».
• в веб-интерфейсе phpMyAdmin , вкладка «Экспорт».
• утилитой mysqldump в консоли.

Получившийся дамп можно использовать для переноса и импорта в новую БД или просто для хранения (как резервную копию данных).

Создание дампа через phpMyAdmin

phpMyAdmin - веб-интерфейс для работы с базами данных MySQL. Он доступен как на нашем хостинге, так и на подавляющем большинстве других площадок; имеется он и в распространенном программном комплексе Denwer , а также его можно установить прямо в каталог сайта.

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

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

После выбора нужной вам БД в левом столбце появится список содержащихся в БД таблиц. Для быстрого создания дампа перейдите на вкладку «Экспорт» в правой колонке phpMyAdmin и нажмите «Вперед». После подготовки дампа браузер предложит сохранить этот файл на компьютер или автоматически скачает его.

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

Создание дампа в консоли MySQL

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

Для создания дампа БД в консоли необходимо использовать утилиту mysqldump . Соединитесь с сервером по SSH .

Ssh [email protected]

где login - логин в Панели управления, domain.ru - любой из доменов, размещаемых вами на нашем хостинге.

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

$

где server - имя сервера, где размещен ваш аккаунт.

Создание дампа выполняется командой:

Mysqldump -u user -p dbname > dump.sql

где user - имя пользователя вашей БД, dbname - имя БД, dump.sql - имя файла, в который будет сохранен дамп БД. Обратите внимание, пароль пользователя необходимо будет ввести сразу после запуска команды.

Если после входа на сервер вы не меняли рабочую директорию, получившийся дамп вы найдете в корневой директории аккаунта. Перед скачиванием (по FTP , SFTP или HTTP) дамп можно заархивировать утилитой gzip :

Gzip -c dump.sql > dump.sql.gz

где dump.sql.gz - имя архива, в который будет сжат дамп вашей БД.

Импорт данных в БД

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

• через Панель управления хостингом: «Базы данных» → имя БД → кнопка «Загрузить резервную копию». Дамп должен быть запакован в архив .gz или.zip ;
• в веб-интерфейсе phpMyAdmin , вкладка «Импорт»;
• утилитой mysql в командной строке.

Импорт с помощью phpMyAdmin

Зайдя в интерфейс phpMyAdmin и выбрав в левом столбце БД, в которую нужно загрузить дамп, в правой части окна выберите вкладку «Импорт».

В стандартном диалоговом окне, которое появится после нажатия кнопки «Обзор», выберите дамп, хранящийся на вашем компьютере. Укажите требуемую кодировку (по умолчанию наши серверы MySQL используют UTF-8 ) и нажмите «Ок» - начнется процесс загрузки дампа на сервер и импорта его содержимого в созданную вами БД.

В случае успешного импорта слева вы увидите список таблиц загруженной БД, а справа - сообщение об успешном завершении процесса импорта и о количестве совершенных запросов к БД.

Теперь вы можете начинать использование этой базы вашим сайтом или иным программным обеспечением.

Импорт в консоли MySQL

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

Mysql -u user -p dbname < dump.sql

где user - имя пользователя для работы с вашей БД, dbname - имя вашей БД, dump.sql - имя файла дампа, который нужно импортировать в БД.

Если утилита mysql завершила работу без ошибок, дамп корректно загружен в БД.

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

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

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

mysqldump -u root -p -f name_database >

Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt

Файл можно не создавать, MySQL создаст его сам.

mysql -u root -p -f name_database < C:\mydb_backup_name_database.txt

этой командой мы импортируем данные бэкапа из файла C:\mydb_backup_name_database.txt

Примечание: -f, --force - опция, которая указывает продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка.

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

mysqldump -u root -pPwd -f name_database > C:\mydb_backup_name_database.txt

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

Рассмотрим более тонкие настройки mysqldump:

--databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;

--all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);

Ключ --help . Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;

--add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;

--no-data . С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;

--result-file=... - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick . Это решит проблему.

Приведем еще пару полезных примеров:

mysqldump -u root -pPwd -f --default-character-set=cp1251 DBNAME | gzip -c > filename.txt .gz

распаковывать такой архив можно командой:

gunzip filename.txt .gz

Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:

mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz

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

set DBCHARACTER = utf8

set DBNAME = breach

mysqldump -u root -pPwd -f --default-character-set=$DBCHARACTER $DBNAME | bzip2 -c > sql.$DBNAME .`date "+%Y-%m-%d"`.bz2

Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически

find ~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} \;

Тем самым Вы будете удалять архивы, которые "старше" семи дней.

MySQL: коррекция таблиц, дамп базы, загрузка данных

Итак. Чего полезного умеет MySQL.

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

Другими словами, не надо думать, что для каждого сайта надо "поднимать" и настраивать отдельную копию MySQL.

Для каждого пользователя создается своя область хранения информации, которая называется database. И если доступ к MySQL вам предоставил провайдер, то наверняка он предоставил вам все права на вашу личную database, в которой вы и будете хозяйничать.

Для работы с MySQL можно использовать любой SQL-клиент, который вам наиболее привычен и удобен. Лично я использую стандартного клиента MySQL, работающего в UNIX-shell.

Обычное общение с MySQL сильно напоминает работу в shell или в DOS (для тех, кто никогда не видел в глаза UNIX).

Для начала, вы подключаетесь к MySQL, что может выглядеть как:
bash> /usr/local/bin/MySQL -h MySQL.int -u 21ru -p 21ru
где есть вызов самого клиента MySQL с указанием адреса сервера MySQL (MySQL.int — это адрес отдельного сервера с MySQL), указанием имени пользователя и названия базы данных (database).

После нажатий enter-а, MySQL запросит у меня пароль, проверит его и пустит в свой shell:

Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. CoMySQLands end with ; or \g. Your MySQL connection id is 3060933 to server version: 3.23.49 Type "help;" or "\h" for help. Type "\c" to clear the buffer MySQL>
Вот так. И далее начинаем общаться с MySQL.

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

Что же можно сделать с MySQL, кроме обычных запросов к таблицам базы данных.

Можно модифицировать сами эти таблицы. Для этого используется запрос с ключевым словом ALTER TABLE .

Например, нам надо добавить в существующую таблицу tbl_text поле text_length в формате int с изначальным значением 100. Пишем:
alter table tbl_text add column text_length int not null default 100; и все записи в таблице tbl_text получат новое поле text_length со значением 100.

Кстати, в shell MySQL можно писать запросы в несколько строк. MySQL не начнет интерпретировать ваш запрос, пока вы не закончите его точкой с запятой ";" и enter-ом, следующим за этим знаком.

И прошу заметить, что правильно построенный запрос на основе ALTER TABLE никогда не порушит ваших данных, занесенных ранее в исправляемую таблицу, если вы не удалите поле в таблице, или не измените его тип

Перенос данных

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

Зачастую получается так, что нельзя подключиться сразу к двум базам и перелить данные из одной БД в другую. Особенно, когда старая база данных в старом неведомом формате или вообще представляет собой что-то самодельное.

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

В общем, в этом подходе нет ничего предосудительного, особенно, когда вам необходимо залить десяток-другой тысяч строк. Но если ваша база похожа на настоящую, и количество записей измеряется миллионами, то лучше воспользоваться специальной возможностью MySQL, которая зовется LOAD DATA INFILE .

Это очень гибкая функция MySQL, позволяющая прочесть из файла данные в базу. При этом, гибкость заключается в том, что вы можете описать формат хранения данных в файле, указать диапазон данных, подлежащих заливке и так далее.

И, что особенно важно, скорость заливки данных при помощи LOAD DATA намного превышает построчную скорость заливки insert-ом.

Надеюсь, функция LOAD DATA окажется вам полезной.

Создание дампа в MySQL

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

В подобном случае нам очень полезно будет дампироование базы.

Что такое дамп базы . Это вывод структуры и данных из базы в формате SQL-запросов.

Поясню. Если у нас в базе есть таблицы, а в таблицах данные, то в дамп мы получим SQL-запросы на построение таблиц и insert-ы для заполнения этих таблиц данными.

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

В пакете MySQL есть mysqldump который этим и занимается. Запуск mysqldump очень похож на запуск стандартного клиента MySQL, о котором я писал вначале этого выпуска. То есть, указывается база, указывается пользователь и название database, дампировать которую мы собрались.

Например:
bash> /usr/local/bin/mysqldump -h MySQL.int -u 21ru -p 21ru Разумеется, mysqldump имеет массу всевозможных параметров. Например, указав в строке вызова -d можно вывести в дамп только структуру таблиц, без данных. Это особенно удобно, когда вам надо создать копию структуры базы на другом сервере, не перенося при этом данных. Так же можно указать конкретные таблицы, дамп которых вам необходим и так далее.

Полный список ключей mysqldump можно получить:

Bash> mysqldump —help
Получив на руки дамп базы, вы можете залить его практически в любую другую БД SQL, а уж на любой другой MySQL-север — это точно.

Залить данные из дампа просто:
$ /usr/local/bin/mysql -h MySQL.int -u 21ru -p 21ru где первая часть строки до знака "" -- подключение к базе данных, а вторая — файл, содержащий дамп базы.

MySQL подключится к базе (не забыв спросить у вас пароль) и построчно выполнит все инструкции из файла base.dump.

Как видите, все просто. Попробуйте сами.

Итак. Если вы хотите перенести свой проект, написанный на php+MySQL, то это можно сделать в несколько несложных шагов:

1. Делаем дапм базы (с данными, если нужна копия проекта и без данных, если нужен новый чистый проект).

2. Копируем файлы самого сайта

3. Правим пути в php-файлах, если они изменились.

Кроме того, вы можете пользоваться mysqldump для бэкапа вашей базы. Не забывайте, что MySQL отличается от своих старших братьев отсутствием транзакций. Другими словами, поданную MySQL команду уже никак нельзя отменить. И если вы написали delete from table tbl_name и нажали enter прежде чем ограничить удаление какими-то критериями, таблица tbl_name будет безвозвратно очищена от данных. И в этом случае вас очень выручит mysqldump сделанный незадолго до этого.

Это все, что касается работы MySQL на удаленном хостинге. Если же вы сами для себя установили MySQL и администрируете его, то вам необходимо знать много больше.

Вам необходимо уметь создавать базы, пользователей, правильно определять уровни доступа пользователей к базе, защищать MySQL от внешних атак, настраивать буферизацию, эффективное управление памятью сервера, языками сортировки, и так далее. kurepin.ru