Как сделать копию таблицы mysql
Резервное копирование данных в MySQL
1. Копирование файлов базы
Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.
В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.
Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).
Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.
Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.
2. Копирование через текстовые файлы
Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается — об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.
В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump. Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.
Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель zapimir есть на хабре).
Недостатки универсальных утилит бэкапа в текстовые файлы — это относительно невысокая скорость работы и отсутствие возможности делать инкрементные бэкапы.
3. Инкрементные бэкапы
Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности — это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.
Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».
Здесь практически вне конкуренции система Percona XtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.
Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.
4. Репликация
Избежать откатов призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («слейвы»), которые получают инкрементные бэкапы с мастера в режиме реального времени. Таким образом, время отката уменьшается почти до сетевого лага. В случае краха Мастера можно оперативно назначить «новым Мастером» один из слейвов и перенаправить клиентов на него. Кроме того, слейвы могут обрабатывать запросы на чтение данных (SELECT-ы); это можно использовать для выполнения каких-то расчетов или снижения нагрузки на мастера. MySQL поддерживает репликацию «из коробки», процесс настройки репликации в MySQL хорошо описан юзером whisk. Существует возможность запуска конфигураций Master-Master, а с помощью внешних аппаратно-программных систем — и балансировки нагрузки между мастерами. Только не нужно забывать про ограничения, накладываемые CAP-теоремой.
Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE users SET password=«Haha!», изменения будут тут же скопированы на слейв, и откатить их назад станет невозможно.
Репликацию можно совместить с бэкапом на уровне файлов базы, останавливая слейв, а не мастера.
Как сделать дамп базы MySQL / MariaDB
В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:
Пример базовой команды для резервирования базы:
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
* полный перечень параметров смотрите в официальном руководстве.
Примеры создания дампа MySQL
1. С последующим архивированием
2. Для одновременно нескольких баз
3. Для всех баз одной командой
4. Резервирование только структуры базы
5. Создание копии определенной таблицы
Для этого после базы через пробел перечисляем названия таблиц
6. Резервирование прав доступа на СУБД
Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;
7. Проигнорировать определенную таблицу
Выполняется при помощи ключа ignore-table:
Скрипт для резервного копирования
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Резервное копирование в phpMyAdmin
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт:
В разделе «Способ экспорта» ставим переключатель в положение Обычный:
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Выбираем компрессию, например, zip:
И в нижней части окна нажимаем OK.
Начнется загрузка файла с резервной копией на компьютер.
Для восстановления базы читайте инструкцию Как восстановить базу MySQL
Скопировать таблицу из одной БД в другую mysql + php
Здравствуйте. Помогите составить запрос.
Нужно скопировать таблицу из одной БД в другую.
Пробовал так:
Добавлено через 14 часов 26 минут
Здравствуйте. Помогите составить запрос.
Нужно скопировать таблицу из одной БД в другую.
Пробовал так:
Сам спросил, сам отвечу)
Скопировать запись из одной ячеки в другую талицу
Помогите пожалуйста составить запрос. Суть такая нужно одним запросом скопировать id ячейки.
Копирование данных из одной таблицы в другую (MySQL)
Здравствуйте, как из одной заполненной таблицы скопировать значения (не все) и заполнить другую.
В одной таблице несколько ссылок на другую таблицу
В схеме БД существует связь между таблицами ВидыПолисов и Риски, когда ВидыПолисов имеет несколько.
А говорили «не работает» 
А теперь во-второй раз работать не будет 
Ну так синтаксис же не правильный был, ну или mysql_select_db нужно было добавить 
Работает и в 10й
CREATE TABLE db2.table при существующей таблице работает? Странно 
Почему бы тогда копированию при несуществующей тоже не работать?
Добавлено через 1 минуту
CREATE TABLE db2.table при существующей таблице работает? Странно Почему бы тогда копированию при несуществующей тоже не работать?
Добавлено через 1 минуту
Я вас не правильно понял)
Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.
Скопировать id с одной таблицы на другую таблицу другую форму
Добрый день! Подскажите, как и где написать,чтобы id с одной таблицы скопировался в другую таблицу.
Скопировать таблицу из одной БД в другую БД
Доброго времени суток. Я расширил свою программу и случилось так что все нужные данные находятся в.
Как программно скопировать таблицу из одной базы в другую
как программно скопировать таблицу из одной базы в другую? есть 2 базы: 1.mdb и 2.mdb в 1ой базе.
Скопировать все строки из определенных полей одной таблицы в другую таблицу
Имеются 2 таблицы: ADOTable1 ADOTable6 Нужно скопировать все строки из полей AAA, BBB, CCC.
Как скопировать строку и вставить в ту же таблицу с полем autoincrement в MySQL?
Использование MySql. Как я могу сделать это в одном запросе?Пожалуйста, помогите
11 ответов
вам придется позаботиться о возможном повторе id из 2 во втором случае конечно.
IMO, лучше всего использовать операторы sql только для копирования этой строки, в то же время ссылаясь только на столбцы, которые вы должны и хотите изменить.
Вы можете использовать этот запрос:
для быстрого, чистого решения, которое не требует, чтобы вы называли столбцы, вы можете использовать подготовленный оператор, как описано здесь: https://stackoverflow.com/a/23964285/292677
Если вам нужно комплексное решение, чтобы вы могли делать это часто, вы можете использовать эту процедуру:
вы можете запустить его с:
отказ от ответственности: это решение только для тех, кто будет многократно дублировать строки во многих столы, часто. Это может быть опасно в руках злоумышленник.
это помогло, и он поддерживает столбцы BLOB / TEXT.
вы также можете пройти в ‘0’ в качестве значения для столбца автоприращения, правильное значение будет использоваться при создании записи. Это намного проще, чем временные таблицы.
источник: копирование строк в MySQL (см. второй комментарий TRiG к первому решению Lore)
Я искал ту же функцию, но я не использую MySQL. Я хотел скопировать все поля, кроме, конечно, первичного ключа (id). Это был одноразовый запрос, не используемый ни в одном скрипте или коде.
Я нашел свой путь с PL / SQL, но я уверен, что любой другой SQL IDE будет делать. Я сделал базовый
затем экспортируйте его в файл SQL, где я мог бы найти
Я просто отредактировал его и использовал:
Я склонен использовать вариант того, что mu слишком короткий:
пока таблицы имеют одинаковые поля (за исключением автоматического приращения в таблице журнала), это работает хорошо.
поскольку я использую хранимые процедуры, когда это возможно (чтобы облегчить жизнь другим программистам, которые не слишком знакомы с базами данных), это решает проблему необходимости возвращаться и обновлять процедуры каждый раз, когда вы добавляете новое поле в таблицу.
Он также гарантирует, что при добавлении новых полей в таблицу они начнут появляться в таблице журнала немедленно без необходимости обновлять запросы к базе данных (если, конечно, у вас нет некоторых, которые явно задают поле)
предупреждение: вам нужно будет добавить все новые поля в обе таблицы одновременно, чтобы порядок полей оставался неизменным. в противном случае вы начнете получать странные ошибки. Если вы единственный, кто пишет интерфейсы, базы данных, и вы очень осторожно, тогда это работает хорошо. В противном случае, придерживайтесь именования всех ваших полей.
Примечание: во-вторых, если вы не работаете над сольным проектом, который вы уверены, что другие не будут работать над ним, придерживайтесь явного перечисления всех имен полей и обновляйте свои операторы журнала по мере изменения схемы. Этот ярлык, вероятно, не стоит долгосрочной головной боли, которую он может вызвать. особенно на производственной системе.
дамп строки, которую вы хотите sql, а затем использовать сгенерированный SQL, за вычетом столбца ID, чтобы импортировать его обратно.
Бэкап базы данных MySQL на сервере Ubuntu
Что такое MySQL?
MySQL — это популярная система управления базами данных (СУБД), использующая для управления данными язык запросов SQL. MySQL идеально подходит для хранения данных сайта или веб-приложения.
Резервное копирование (или бэкап) — очень важная для сохранности любых данных операция. Особенно это касается баз данных. Бэкап базы данных MySQL можно выполнить несколькими способами, о чём и пойдёт речь в этой статье.
Примечание: Для выполнения руководства использовался сервер Ubuntu 12.04 и MySQL 5.5, но более современные версии программного обеспечения будут работать подобным образом.
Бэкап базы данных MySQL при помощи mysqldump
Утилита mysqldump — один из самых простых и удобных способов создания резервной копии MySQL.
Для начала нужно экспортировать БД. Об экспортировании БД при помощи mysqldump можно прочесть здесь. Базовый синтаксис команды выглядит так:
Восстановление БД
Чтобы восстановить дамп БД, созданный при помощи mysqldump, нужно просто перенаправить вывод в файл MySQL.
Для этого создайте пустую БД для хранения импортированных данных. Войдите в MySQL:
Создайте новую БД, чтобы переместить в неё данные из дампа, а затем закройте командную строку MySQL:
CREATE DATABASE database_name;
exit
Перенаправьте дамп-файл в файл БД:
Скопированные данные будут восстановлены в новой БД.
Копирование таблицы MySQL в текстовый файл
Также MySQL позволяет сохранять данные из таблицы прямо в текстовые файлы с помощью оператора select.
Общий синтаксис команды:
SELECT * INTO OUTFILE ‘table_backup_file’ FROM name_of_table;
Данный оператор сохранит данные из таблицы в файл на сервере MySQL. Имейте в виду: если файл с таким именем уже существует, операция не будет выполнена.
Примечание: Эта опция сохраняет только данные таблицы. Если таблица имеет сложную структуру, которую нужно сохранить, этот метод использовать не рекомендуется.
Утилита automysqlbackup
Утилита automysqlbackup доступна в стандартных репозиториях Ubuntu. Она позволяет выполнять бэкап БД автоматически на регулярной основе.
Чтобы установить эту программу, введите в терминал:
sudo apt-get install automysqlbackup
Главный конфигурационный файл утилиты находится в /etc/default/automysqlbackup; откройте его с правами администратора:
sudo nano /etc/default/automysqlbackup
Как видите, данный файл по умолчанию присваивает множество переменных из файла /etc/mysql/debian.cnf, который содержит данные для авторизации. Из этого файла automysqlbackup считывает пользователя, пароль и БД, резервные копии которых нужно создать.
Стандартное место хранения резервных копий — /var/lib/automysqlbackup. Найдите этот каталог и ознакомьтесь со структурой бэкапов:
ls /var/lib/automysqlbackup
daily monthly weekly
Каталог daily содержит подкаталог для каждой БД, в котором хранится сжатый sql дамп, полученный в результате последнего запуска команды:
Для настройки автоматического запуска резервного копирования система Ubuntu устанавливает вместе с этой программой демона cron.
Репликация баз данных
Для резервного копирования данных можно также использовать репликацию MySQL в связке с любым из перечисленных выше методов.
Репликация — это процесс зеркалирования данных с ведущего сервера на другие (тип master-slave) или с любого сервера связки на остальные серверы (тип master-master).
Но поскольку репликация постоянно фиксирует изменения динамической системы, она может пострадать от резервного копирования, потому что при этом фиксируется определенный момент времени.
Чтобы устранить эту проблему, можно:
Временное отключение репликации
Чтобы временно отключить репликацию на slave-сервере, введите:
Есть ещё один метод, который не отключает репликацию полностью, а просто ставит её на паузу:
Остановив репликацию, можно выполнить бэкап при помощи любого из вышеперечисленных методов. При этом не нужно останавливать master-сервер.
После этого просто возобновите репликацию:
Настройка доступа к серверу резервного копирования
Также можно получить последовательный набор данных, временно сделав их доступными только для чтения.
Это можно сделать как на сервере master, так и на slave.
Для начала откройте MySQL с правами root:
Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
Выполните бэкап при помощи mysqldump.
После того как резервное копирование будет выполнено, верните систему в её обычное состояние:
SET GLOBAL read_only = OFF;
UNLOCK TABLES;
Нерекомендуемые методы резервного копирования
Скрипт mysqlhotcopy
MySQL предоставляет perl-скрипт для быстрого резервного копирования по имени mysqlhotcopy. Этот инструмент позволяет очень быстро скопировать БД на локальной машине, но он имеет некоторые ограничения, из-за которых его лучше не использовать.
Во-первых, этот скрипт копирует только данные, хранящиеся при помощи механизмов MyISAM и Archive. Большинство пользователей не меняют механизмы хранения для своих БД, а MySQL, начиная с версии 5.5, по умолчанию использует механизм InnoDB. Следовательно, скрипт mysqlhotcopy не может скопировать такой тип данных.
Во-вторых, данные, скопированные при помощи этого скрипта, можно запустить только на той же машине, на которой хранится БД. То есть mysqlhotcopy не сможет скопировать данные с удалённого сервера.
Копирование файлов таблиц
Следующий метод, который не рекомендуется применять, — это простое копирование файлов таблиц MySQL.
Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.
Конечно, использовать эту технику с механизмами хранения, которые хранят свои данные в файлах, довольно разумно; однако InnoDB, механизм хранения MySQL по умолчанию, нельзя скопировать таким образом.
Заключение
Как видите, MySQL предоставляет самые различные методы копирования данных. Все они имеют свои преимущества и недостатки, некоторые из них проще, некоторые — более широкого применения.
В целом, метод резервного копирования зависит от индивидуальных потребностей и ресурсов сервера, а также от производственной среды. Какой бы метод вы выбрали, обязательно проверьте резервные копии и попробуйте восстановить данные.