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

Какие способы бывают?

1 - при помощи интерфейса phpMyAdmin.

2 - при помощи панели управления хостинг-провайдера.

3 - при помощи сторонней программы.

Какой способ лучше?

Мы рекомендуем первый, т.к. его схема проста, и используется большинством вебмастеров. Третий способ мы не рекомендуем использовать! Никогда не доверяйте содержимое своего сайта сторонним программам, к тому же от непонятных разработчиков. Еще можно использовать второй способ, но его алгоритм очень разнится, и зависит от панели управления хостера. Поэтому, мы детально рассмотрим первый вариант, и расскажем как грамотно перенести базы данных MySQL, без потери и повреждения данных.

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

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

1 - Ищем раздел “MySQL”, “Базы данных” или что-то похожее.

2 - В нем нажимаем “Создать”.

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

4 - База данных создана, но она пока что пустая.

Экспортируем БД со старого хостинга

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

2 - Слева в углу выберите свою базу данных (ту, которую вы собираетесь экспортировать на компьютер, чтобы потом перенести на другой хостинг).

4 - Возможно Вас попросят выбрать способ экспорта “Обычный” (много настроек) или “Быстрый” (мало настроек). Не имеет значения какой выбирать, главное изменить только те настройки, которые мы описываем ниже.

5 - Нужно выбрать все таблицы, нажав на кнопку “Выделить все”.

7 - На всякий случай, запоминаем кодировку, но не трогаем ее.

8 - Жмем “Ок” и сохраняем файл с БД себе на компьютер. Обычно сохраняемый файл имеет расширение.sql.

Импорт БД на сервер нового хостера

1 - Таким же образом ищем phpMyAdmin на новом хостинге.

2 - В нем выбираем нужную базу данных (мы ее создавали в начале статьи, и она осталась пустой).

3 - Жмем на вкладку “Импорт”.

4 - Нажимаем “Обзор” и выбираем сохраненную на компьютере базу данных.

5 - Проверьте, чтобы кодировка совпадала с той, в которой Вы сохраняли БД.

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

Какие бывают проблемы?

1 - При импорте БД в ней не должно быть запросов типа «CREATE DATABASE, /*!40101 SET @OLD ». Чтобы проверить их наличие - откройте файл БД на своем компьютере любым текстовым редактором (лучше всего подходит Notepad++) и через Ctrl+А поищите эти запросы. Если найдете, то просто удалите их, и попробуйте снова импортировать БД.

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

Иногда возникает необходимость перенести SQL базу данных с одного SQL-сервера на другой. Обычно процесс переноса данных заключается в создании резервной копии базы данных и восстановлении ее на другом SQL-сервере. Однако, казалось бы, в такой простой операции могут возникать разного рода трудности. В данной статье мы попробуем разобраться с некоторыми из них.

1. Если база данных с таким именем уже существует

Если при переносе базы данных обнаружится, что база данных с таким именем уже существует, или при восстановлении возникает ошибка, что файл базы данных с таким именем уже существует, требуется вручную указать новое имя базы данных и/или папку, в которой будут размещаться физические файлы БД. Это можно сделать в SQL Server Managment Studio, указав при восстановлении новое имя базы данных на закладке Общие

и папку, в которой должны будут размещаться файлы БД (закладка Файлы)

2. Перенос базы данных Альта-ГТД вместе с дополнительными базами ЭД

Если требуется перенести базу данных Альта-ГТД вместе с дополнительными базами ЭД, то необходимо:

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

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

3. Если при восстановлении была переименована рабочая база данных, тогда необходимо для всех дополнительных баз выполнить скрипт:

UPDATE [Имя_Доп_Базы].. SET = "_gtd_ed@Имя_Основной_Базы"

4. Если при восстановлении была переименована одна или несколько дополнительных баз, то для каждой из них необходимо выполнить скрипт

UPDATE [Имя_Основной_Базы].. SET = "Новое_Имя_Доп_Базы" WHERE = "Старое_Имя_Доп_Базы"

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

К настоящему времени используются версии InterBase от 4.x до 6.x, причем в шестой версии база данных может быть создана в диалекте 1 или в диалекте 3. В общем случае переход от младшей версии InterBase к старшей не требует каких-то специальных действий, и базы данных работают нормально, но при этом пользователь не может воспользоваться дополнительными услугами, которые предоставляются старшей версией. В случае же выполнения процедуры переноса базы данных можно будет воспользоваться дополнительными услугами. Что же касается диалектов версии 6.x, то в них некоторые типы данных интерпретируются по-разному. Например, в ранних версиях InterBase и в диалекте 1 версии 6.x определен один тип даты Date, значение которого в начале содержит дату, а затем время. В диалекте 3 версии 6.x определены три типа - Timestamp, который полностью соответствует типу Date, определенному в ранних версиях; тип Date, который содержит значения только дат, и тип Time, который содержит значения времени.

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

Каждая операционная система собственным способом интерпретирует каждый тип данных. При инсталляции сервера InterBase любой версии выполняется его настройка на соответствующую операционную систему и аппаратную среду.

Таким образом, каждая база данных имеет «привязку» к версии сервера InterBase, к операционной системе и аппаратной среде.

Этим и объясняется необходимость выполнения процедуры переноса базы данных.

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

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

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

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

Для смены диалекта (например, с первого на третий) надо либо пересоздать базу данных, либо воспользоваться утилитой у/іх.

Алгоритм процедуры переноса базы данных

а. Создать файл резервной копии базы данных. Файл создается одним из способов, рассмотренных выше. Желательно проверить, корректно ли создался файл резервной копии. Для этого на том же персональном компьютере развернуть в другом каталоге базу данных и проверить ее работоспособность.

б. Создать файл копии зарегистрированных пользователей на сервере InterBase. Следует помнить, что сведения о пользователях хранятся в файле isc4.gdb на сервере InterBase и в самой базе данных. Для копирования файла iscA.gdb можно воспользоваться той же самой утилитой gbak.

Пример 12.7. Копирование файла зарегистрированных пользователей базы данных.

gbak -b -user SYSDBA -password masterkey C:IBServeisc4.gdb C:isc4.gdk

в. Переустановить сервер InterBase или перейти на другой персональный компьютер. После переустановки сервера на персональном компьютере (или переходе на другой персональный компьютер) необходимо файл iscA.gdb восстановить с помощью той же утилиты gbak.

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

Для таких клиентов целесообразно переустановить клиентскую часть InterBase на персональных компьютерах клиентов.

Пример 12.8. Перенос файла зарегистрированных пользователей базы данных.

gbak -с -user SYSDBA -password masterkey C:isc4.gdk C:isc4.gdb

В примерах 12.7 и 12.8 имелось в виду, что выполняется замена версии InterBase на одном компьютере.

г. Восстановить (перенести) базу данных одним из способов, описанных выше.

Предложенный выше алгоритм надежно работает при повышении версии InterBase. Если же надо понизить номер версии InterBase , то для выполнения этой операции необходимо иметь два персональных компьютера: первый - с работающей базой данных на старшей версии InterBase, второй - с установленным сервером InterBase низшей версии. Запускаем процедуру создания резервной копии базы данных (п. «а» алгоритма) со второго компьютера. При этом будет создан файл резервной копии в низшей версии. Но возможны следующие варианты:

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

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

Клиенты всех версий InterBase , в отличие от клиентов, работающих с диалектом 3 версии 6.x, не имеют доступа:

К ключевым словам:

CURRENTDATE CURRENTTIME CURRENT_ TIMESTAMP COLUMN

TIMESTAMP

К идентификаторам, заключенным в кавычки.

У вас есть база данных MS SQL Server, которую нужно перенести на другой физический комп. Вы уже сделали бэкап и радостно приступаете к восстановлению. Но тут обнаруживается, что на том компе, куда нужно перенести базу, установлена более старая версия MS SQL Server. Stack Overflow уверяет вас, что всё плохо. Но так ли это на самом деле?

Конечно, перенос базы из более новой версии в старую - это не классический и не самый правильный сценарий работы. Но зачастую базы данных создаются такими, что они поддерживают все более новые версии SQL, начиная с какой-то, например с 2008 R2, т.к. прямая совместимость у MS SQL более чем отличная. И, например, ваш клиент уже поставил себе MS SQL 2016, а у вас на тестовом сервере для разработки стоит MS SQL 2014. А вы хотите развернуть себе базу клиента, чтобы разобраться, где у него путаница с данными.

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

Да, можно сгенерировать SQL-скрипты всей базы, включая данные. Но представьте, у вас в базе куча блоб-полей с большими данными, и вообще размер всей базы 500+ ГБ. Представляете, сколько будет занимать такой скрипт, сколько времени он будет генерироваться и исполняться.

Ограничение номер один заключается в том, что вам нужен доступ через MS SQL Management Studio к обоим серверам - старому и новому. Если это не возможно, то должна быть возможность на той машине, откуда нужно перенести базу, установить ту версию SQL, в которую нужно перенести базу, чтобы перенести базу сначала в эту версию локально, а потом уже перетащить её через бэкап или непосредственно через *df файлы базы данных (через Detach/Attach) на новую машину (версия SQL Server"а в этом случае уже будет совпадать).

Еще одним ограничением является то, что вам будет необходим скрипт схемы базы данных (всех объектов, включая таблицы, индексы, констрейнты, хранимые процедуры, триггеры и т.п.) без данных, причем инструкции создания Foreign Key Constraints должны в этом скрипте идти в самом конце, отдельно от скрипта создания самих таблиц.

Опишу кратко сам алгоритм переноса данных. Все действия выполняются в сеансе Management Studio, подключенной к серверу, на который нужно перенести базу.

1) На новом сервере создаем пустую базу данных с такими же файлами и файловыми группами, как переносимая база.

2) Скриптом схемы базы данных создаем все объекты базы (таблицы, индексы, представления, триггеры, хранимые процедуры и функции), но без создания Foreign Key Constraints. Создавать FK на этом этапе нельзя, т.к. они будут мешать вставке данных.

3) Подключаем базу данных, из которой будем переносить данные, в качестве Linked Server"а, чтобы можно было использовать в запросах к новой базе данных обращения к старой базе данных.

EXEC sp_addlinkedserver @server=N"LinkedServerAlias", @srvproduct=N"", @provider=N"SQLNCLI", @datasrc=N"LinkedServerHost\LinkedServerName"; EXEC sp_addlinkedsrvlogin "LinkedServerUser", "false", null, "RealUser", "RealUserPassword";
4) Т.к. структуры баз совпадают, воспользуемся встроенной хранимой процедурой sp_msforeachtable, которая позволяет выполнить запрос над каждой таблицей БД, чтобы сгенерировать скрипт переноса данных из старой базы в новую через запрос вида

INSERT INTO ? SELECT * FROM ?
Вместо знака вопроса sp_msforeachtable подставляет имя каждой таблицы и выполняет запрос несколько раз (по одному разу на каждую таблицу).

Здесь я натолкнулся на самое большое количество граблей.

А) Проблема номер один заключается в том, что для таблиц с IDENTITY-полями необходимо вызывать:

SET IDENTITY_INSERT ON; --INSERT INTO ... (сама вставка); SET IDENTITY_INSERT OFF;
б) Проблема номер два заключается в том, что на таблицах, в которых нет IDENTITY-полей, делать данный вызов нельзя, поэтому требуется динамически определять, есть в таблице IDENITY-колонка или нет.

Это можно сделать таким запросом:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME="SomeTable") AND (COLUMNPROPERTY(object_id("dbo.SomeTable"), COLUMN_NAME, "IsIdentity") = 1)
в) Проблема номер три заключается в том, что, как оказалось, в режиме IDENITY_INSERT ON нельзя делать

INSERT INTO ... SELECT * FROM ...
, а нужно перечислять конкретные поля.

Перечислить поля таблицы в строку можно таким запросом:

SELECT SUBSTRING((SELECT ", " + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "SomeTable" ORDER BY ORDINAL_POSITION FOR XML path("")), 3, 200000);
4) Генерируем скрипт вставки по все таблицы:

Процедура генерации скрипта

EXEC sp_msforeachtable N" DECLARE @command varchar(MAX); DECLARE @name varchar(200); SET @name=""?""; SET @name = SUBSTRING(@name, 8, LEN(@name)-8); SET @command = """"; SELECT @command= SUBSTRING((SELECT "", "" + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = """" + @name + """" ORDER BY ORDINAL_POSITION FOR XML path("""")), 3, 200000); SET @command = ""INSERT INTO ""+ @name +"" (""+ @command + "") SELECT "" + @command + "" FROM "" + ""LinkedServerAlias.SourceDatabase."" + ""?""; SET @command= ""IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME="""""" + @Name + """""") AND (COLUMNPROPERTY(object_id(""""dbo.""+@Name+""""""), COLUMN_NAME, """"IsIdentity"""") = 1)) SET IDENTITY_INSERT "" + @name + "" ON; "" +@command; SET @command=@command+"";"" + ""IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME="""""" + @Name + """""") AND (COLUMNPROPERTY(object_id(""""dbo.""+@Name+""""""), COLUMN_NAME, """"IsIdentity"""") = 1)) SET IDENTITY_INSERT "" + @name + "" OFF;""; PRINT (@command); --EXEC(@command); // Если раскомментировать, скрипт будет сразу исполняться, а не только выводиться на экран "

6) Исполняем скрипт на создание всех Foreign Key Constraints (теперь уже можно).

7) Готово! Вы перенесли базу из нового сервера SQL в старый, хоть это и считалось невозможным. Причем перенос осуществляется всего лишь раза в полтора медленнее, чем скорость передачи данных по сети, т.е. довольно быстро.

8) Прибираемся за собой (отключаем Linked Server):

EXEC sp_droplinkedsrvlogin "LinkedServerUser", null; sp_dropserver "LinkedServerAlias";
Ограничения метода.

1) Подобным методом не получится перенести таблицы, в которых есть колонки с типом XML.
Наверняка есть множество других ограничений, т.к. в той базе, которую я переносил подобным способом, не использовалось множество возможностей SQL-сервера. Вы можете написать об ограничениях в комментариях, и я дополню ими статью.

Спасибо за внимание! Надеюсь, кому-то поможет.

Многие задумывались, как перенести скрипты с базой данных MySQL на другой хостинг. Так вот, я написал в этой статье, как перенести Вашу базу данных, с помощью SSH/телнет и PHPMyAdmin"а.

Если Вы имеете доступ по телнет или SSH на оба сервера, то последовательность Ваших действий будет следующей:
Заходите по телнет/SSH на исходный сервер. Экспортируете содержание Вашей базы данных следующей командой:

mysqldump -uВашлогин -pВашпароль _mysql ВашабазаДанных > baza.sql

После выполнения этой команды, все содержание Вашей базы данных сохранится в файле baza.sql.

Затем Вам надо закачать полученный файл с Вашей базой данных на сервер получатель. Это можно сделать в том же сеансе телнет/SSH командой ftp, или используя любой предпочтительный для Вас клиент (сначала скачиваете файл baza.sql на свой компьютер, а потом закачиваете его на сервер получатель). После того, как файл Вашей базы данных оказался на сервере получателе, заходите на этот сервер по телнет/SSH. Загрузить Вашу базу данных на сервер получатель Вы можете, выполнив следующую команду:

mysql -uВашлогин -pВашпароль _mysql ВашабазаДанных < baza.sql

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

Описанная выше методика может использоваться в тех случаях, когда Вы имеете доступ к обоим серверам по телнет или SSH и в тех случаях, когда Ваша база данных достаточно большая (несколько десятков тысяч записей). В тех случаях, когда Вы не имеете доступа к серверам (или к одному из них) по телнет или SSH или Вы не умеете работать в командной среде юникс и пользоваться телнет или SSH клиентом, то можно использовать следующую методику:
На сервере источнике заходите в скрипт работы с MySQL базами данных (Как правило, это PHPMyAdmin). Выбираете базу данных, предназначенную для переноса и в ее свойствах указываете "Просмотреть дамп (схему) БД" (Следует заметить, что конкретные названия пункты меню, могут отличаться от упомянутых здесь в связи с тем на разных серверах могут использоваться разные версии программы, и потому, очень желательно, ознакомиться с соответствующей документацией). Отмечаете галочками Нужные Вам пункты: "Только структуру", "Структура и данные", "Только данные" и отмечаете пункт "Послать". Когда Вы нажмете кнопку "Пошел" через какое-то время Вам будет предложено загрузить файл - это и будет содержание Вашей базы данных. Когда файл будет загружен на Ваш компьютер.