Dump MySQL, создание резервной копии БД
Предисловие
Рано или поздно у всех стоит вопрос о создании копии БД. В сети есть очень много разных программ скриптов и прочего, но они хороши тогда, когда БД маленькая, ну как сейчас у нас, но если БД весит 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 получаем дамп уже в архиве.
————————
Все параметры можно чередовать. Это наиболее часто используемые мной параметры.
Статья просмотренна 80771 раз, зашло посетителей 25044
Спасибо за инфу!
Пользуйтесь!
Я плохо разбираюсь в этом, я хочу научиться объединять базы данных mysql с одинаковых сайтов. На обоих phpmyadmin и одинаковый движок. Я пробовал экспортировать один и импортировать его в другой, но появляется ошибка или повисает страница. Базы данных очень маленькие, тестовые.
Ну сайт это не только БД, это еще и логика скрипта, возможно на разных сайтах разные логики и может БД не подходит, трудно сказать, что не так.
А использовать командную строку надо, особенно на огромных БД по 100 МБ через майадмин ты её просто не вытянешь.
ЗЫ а что хоть за ошибки, могу ли я чем-то помочь?
Я экспортирую базу с 1 сайта и импортирую эту базу на 2 сайт, я так понимаю нужно импортировать в существующую базу данных, так как если создать новую базу данных (с другим названием) и импортировать туда, то на сайте темы не появляются.
Когда я импортирую в существующую базу ещё одну базу появляется неизвестная ошибка, из за превышения времени ожидания.
А консоль я не знаю даже где находится.
А если наоборот, со 2 сайта перенести базу на 1, то появляется ошибка дамп данных:
Ответ MySQL:
#1062 — Duplicate entry '1' for key 'PRIMARY'
может можно как нибудь добавить базу данных по другому, например создать новую, и объединить их чтобы темы появлялись из обоих баз на сайте?
Как я понял у вас простой хостинг (шаринг-хостинг), в таком случае вы не имеете доступа к консоли сервера и эта статья вам вообще не поможет.
Как я понял из описания, вы просто переписываете данные из одного сайта в другой, но вы не делаете очистку старой БД, поэтому у вас возникаю ошибки (Duplicate entry ‘1’ for key ‘PRIMARY’, говорит о том, что запись с номером 1 есть).
Что вам надо
1 Сохранить дамп БД для обоих сайтов
2 При экспорте данных вы должны ставить галочки в phpmyadmin — «Добавить DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT», в таком случае при импорте этой БД в другую таблицы старой будут удалены, а новые переписаны.
Если вы хотите объединять данные обеих БД, то для этого пишутся скрипты, или процедуры БД, которые смотрят на логику и делают слияние, процесс достаточно трудоёмкий, особенно если учитывать размеры БД и постоянное изменение.
Есть и другая возможность, только что пришла в голову, можно делать триггер на БД и при записи данных в БД А, данные будут попадать в БД Б, тем самым у вас будет синхронизация в одну сторону, все что добавлялось в базу А, будет в базе Б, об этом я писал статью Синхронизация БД с помощью триггеров (trigger MySQL).
Абалдеть какая полезная, практическая информация. Спасибо!
В помощь!
Отличный текст, ничего лишнего, и всё по делу! Подскажите, пожалуйста, можно ли с помощью mysqldump использовать паттерны в полях, к примеру, сохранить только те поля, в которых встречается определённый месяц, например, только данные вида "2011-03"?
Спасибо за комментарий! К сожалению такое делать данная утилита не умеет, можно только указать таблицу(ы) которые хочется задампить.
Можно воспользоваться разными сторонними программами они это умеют это делать.
Максим Спасибо!
Очень помогла статья сэкономить время !