За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Dump MySQL, создание резервной копии БД

Декабрь12
Вашему вниманию качественная раскрутка сайта петербург.

Предисловие

Рано или поздно у всех стоит вопрос о создании копии БД. В сети есть очень много разных программ скриптов и прочего, но они хороши тогда, когда БД маленькая, ну как сейчас у нас, но если БД весит 1-2 ГГб, что делать???? Тут путь один, использовать родную командную утилиту mysqldump — тут столько букв, что я решил описать, по моему мнению, значимые параметры.

Синтаксис

В чем её преимущество — она быстрая, с кучей параметров, скорость её работы всегда будет выше, чем у phpmyadmin и им подобным.

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

где
[options] — это опции которые нужны для разных случаев, об этом ниже
db_name — база(ы) данных

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

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev > maxnag_dev.sql

где -uroot — это параметр u означает юзер root, название этого юзера.

-ppassword — это параметр p означает пароль пользователя root

maxnag_dev — название БД, которая дампируетса.

> maxnag_dev.sql — знак больше говорит что весь поток дампа будет записан в файл maxnag_dev.sql по пути ~/maxnag, можно прописать любой путь.

Это самый простой способ получения дампа, для большинства случаев он подходит.

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

maxnag@svn:~/maxnag> mysql -uroot -ppassword maxnag_dev < maxnag_dev.sql
или
maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev < maxnag_dev.sql

параметры теже, команда восстанавливает БД из файла maxnag_dev.sql в БД maxnag_dev. Обратите внимание, что первая команда не выводит на экран содержимое дампа, а вторая выводит (я думаю это тоже регулируется в настройках)

Параметры и примеры

Теперь самое интересное. Это параметры и примеры.

Дамп только «скелета» таблиц

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

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword --no-data maxnag_dev > maxnag_dev.sql

Создание дампа нескольких БД

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

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword -B maxnag_dev mybloog > twoDB.sql

Создание дампа с исключением некоторых таблиц

—ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.

Создание дампа с выборочными таблицами

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev --tables user user_data > page.sql

Обратите внимание, что сначала идет название таблицы, а потом перечисляться нужные таблицы.

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

—triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user --triggers > user.sql

Кроме таблицы user в дампе содержится еще и триггеры, кусок дампа ниже

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass2` BEFORE INSERT ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass
END */;;

/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass);
END */;;

DELIMITER ;

Создание дампа процедур и ф-ций

—routines, -R — с этим ключем идет их создание.

Создание дампа планировщика заданий

—events, -E с этим ключем идет их создание.

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

—xml, -X Представляет дамп базы данных в виде XML

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user -X> user.xml

вот часть содержимого

<table_data name="user">
    <row>
	<field name="id">1</field>
	<field name="fam">Нагайченко</field>
	<field name="name">М</field>
	<field name="otch">В</field>
	<field name="pass">5f4dcc3b5aa765d61d8327deb882cf99</field>
	<field name="login">maxnag</field>
    </row>
</table_data>

Создание дампа с разбиением его на части

Ну это нужно, если у вас дамп несколько ГГб и естественно вытащить такой, даже в архиве проблематично, то его можно разбить на части

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | split -b 300K - maxnag.sql

Обратите внимаение, что -b, означает что мы указываем на какой объем следует делить, после этого параметра идет размер, он может быть

SIZE may have a multiplier suffix:<br>
b 512, kB 1000, K 1024, MB 1000*1000, M 1024*1024,<br>
GB 1000*1000*1000, G 1024*1024*1024, and so on for T, P, E, Z, Y.<br>

потом идет ТИРЕ, а не знак больше. И в файловой системе получается файл разбитый по указанному объему, для объединения используется команда линукса cat

Ну и напоследок, создание дампа с архивацией

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | gzip > maxnag.sql.gz

B получаем дамп уже в архиве.

————————

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

Статья просмотренна 52616 раз, зашло посетителей 17941

рубрика: MySQL

12 комментариев в “Dump MySQL, создание резервной копии БД”

  1. Avatar
    Эпсон пишет:

    Спасибо за инфу!


  2. Avatar
    maxnag пишет:

    Пользуйтесь!


  3. Avatar
    @workors пишет:

    Я плохо разбираюсь в этом, я хочу научиться объединять базы данных mysql с одинаковых сайтов. На обоих phpmyadmin и одинаковый движок. Я пробовал экспортировать один и импортировать его в другой, но появляется ошибка или повисает страница. Базы данных очень маленькие, тестовые.


  4. Avatar
    maxnag пишет:

    Ну сайт это не только БД, это еще и логика скрипта, возможно на разных сайтах разные логики и может БД не подходит, трудно сказать, что не так.
    А использовать командную строку надо, особенно на огромных БД по 100 МБ через майадмин ты её просто не вытянешь.

    ЗЫ а что хоть за ошибки, могу ли я чем-то помочь?


  5. Avatar
    @workors пишет:

    Я экспортирую базу с 1 сайта и импортирую эту базу на 2 сайт, я так понимаю нужно импортировать в существующую базу данных, так как если создать новую базу данных (с другим названием) и импортировать туда, то на сайте темы не появляются.
    Когда я импортирую в существующую базу ещё одну базу появляется неизвестная ошибка, из за превышения времени ожидания.
    А консоль я не знаю даже где находится.


  6. Avatar
    @workors пишет:

    А если наоборот, со 2 сайта перенести базу на 1, то появляется ошибка дамп данных:
    Ответ MySQL:
    #1062 — Duplicate entry '1' for key 'PRIMARY'
    может можно как нибудь добавить базу данных по другому, например создать новую, и объединить их чтобы темы появлялись из обоих баз на сайте?


  7. Avatar
    maxnag пишет:

    Как я понял у вас простой хостинг (шаринг-хостинг), в таком случае вы не имеете доступа к консоли сервера и эта статья вам вообще не поможет.

    Как я понял из описания, вы просто переписываете данные из одного сайта в другой, но вы не делаете очистку старой БД, поэтому у вас возникаю ошибки (Duplicate entry ‘1’ for key ‘PRIMARY’, говорит о том, что запись с номером 1 есть).

    Что вам надо
    1 Сохранить дамп БД для обоих сайтов
    2 При экспорте данных вы должны ставить галочки в phpmyadmin — «Добавить DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT», в таком случае при импорте этой БД в другую таблицы старой будут удалены, а новые переписаны.

    Если вы хотите объединять данные обеих БД, то для этого пишутся скрипты, или процедуры БД, которые смотрят на логику и делают слияние, процесс достаточно трудоёмкий, особенно если учитывать размеры БД и постоянное изменение.
    Есть и другая возможность, только что пришла в голову, можно делать триггер на БД и при записи данных в БД А, данные будут попадать в БД Б, тем самым у вас будет синхронизация в одну сторону, все что добавлялось в базу А, будет в базе Б, об этом я писал статью Синхронизация БД с помощью триггеров (trigger MySQL).


  8. Avatar
    Александр пишет:

    Абалдеть какая полезная, практическая информация. Спасибо!


  9. Avatar
    maxnag пишет:

    В помощь!


  10. Avatar
    @Sectator пишет:

    Отличный текст, ничего лишнего, и всё по делу! Подскажите, пожалуйста, можно ли с помощью mysqldump использовать паттерны в полях, к примеру, сохранить только те поля, в которых встречается определённый месяц, например, только данные вида "2011-03"?


  11. Avatar
    maxnag пишет:

    Спасибо за комментарий! К сожалению такое делать данная утилита не умеет, можно только указать таблицу(ы) которые хочется задампить.
    Можно воспользоваться разными сторонними программами они это умеют это делать.


  12. Avatar
    Alexander пишет:

    Максим Спасибо!
    Очень помогла статья сэкономить время !


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Декабрь 2009
Пн Вт Ср Чт Пт Сб Вс
« Ноя   Янв »
 123456
78910111213
14151617181920
21222324252627
28293031  

Сейчас на сайте