Mysql перенос базы на другой диск
Как я могу переместить таблицы MySQL с одного физического сервера на другой?
Например, такой точный сценарий: у меня есть сервер MySQL, который использует таблицу innodb и имеет размер около 20 ГБ.
Я хочу переместить его на новый сервер, какой самый эффективный способ сделать это?
Я не получаю выгоду от других людей, использующих этот инструмент. Он бесплатный / с открытым исходным кодом, и хотя он был сделан компанией, его достаточно просто использовать, и вам не нужно думать о покупке поддержки у этой компании.
Мой любимый способ - передать команду sqldump команде sql. Вы можете сделать все базы данных или конкретную. Так, например,
Вы можете сделать все базы данных с
Единственная проблема заключается в том, что база данных слишком велика и канал разрушается. В этом случае вы можете сделать таблицу за таблицей или любым из других методов, упомянутых ниже.
Чтобы это работало, мне нужно было создать пустую базу данных с тем же именем на удаленном сервере, а затем добавить имя этой базы данных в конец команды.
Это решение подходит только для полностью контролируемой сети (если и только в защищенной частной сети, читайте: не в Интернете)! Но быстрое и простое решение!
Это молниеносно! Но то, что сказал @Zugwalt, было верно и для меня. Команда не работала, пока я не создал базу данных (пустую). Add добавил имя базы данных в конец команды.
Я недавно переместил базу данных на 30 ГБ со следующей ошибкой:
Шаг 4 — Перезапуск MySQL
Теперь мы готовы запустить MySQL.
Чтобы убедиться, что новая директория data действительно используется, зайдите в MySQL:
И снова чтобы увидеть информацию о директории data введите:
Примечание: если значение не изменилось, то попробуйте перезапустить весь сервер, а не только службу MySQL.
Теперь, когда мы перезапустили MySQL и подтвердили, что используется новое расположение, воспользуйтесь возможностью убедиться, что ваша база данных полностью функционально. После того, как вы удостоверились в целостности всех существующих данных, вы можете удалить резервную копию директории data:
Перезапустите MySQL последний раз, чтобы проверить, что всё работает как и ожидалось:
Подготовка
Проверим где находится текущая БД MySQL.
Видим, что файлы базы находятся в /var/lib/mysql/. Чтобы закрыть командную строку MySQL, введите exit.
Останавливаем сервер MySQL:
Проверяем статус, мы должны убедиться, что сервер MySQL остановлен:
Видим — Status: "Server shutdown complete".
Шаг 1 — Перемещение директории Data MySQL
Подготавливаясь для перемещения директории MySQL data, давайте проверим текущее расположения, для этого запустите интерактивную сессию MySQL с учётными данными администратора.
Когда появится запрос, введите пароль рута MySQL. Затем в приглашении MySQL выполните запрос для показа текущего расположения директории data:
Этот вывод подтверждает, что MySQL настроена на использование директории data по умолчанию, /var/lib/mysql/, т.е. эту директорию нам и нужно перемещать. После подтверждения этого, напечатайте exit; для выхода.
Чтобы быть уверенными в целостности данных, вы отключим MySQL перед тем, как начнём делать наши изменения:
systemctl не показывает результат выполнения команды управления сервисом, поэтому если вы хотите убедиться, что всё прошло успешно, используйте следующую команду:
Теперь, когда сервер отключён, мы скопируем с rsync существующую директорию с базой данных в новое расположение. Использование флага -a сохраняет права доступа и другие свойства каталога, -v обеспечивает вербальный вывод, поэтому вы можете следить за прогрессом.
Внимание: убедитесь, что отсутствуют завершающие слеши после имён директорий, они могут появиться при использовании автозавершения по tab. Когда присутствует завершающий слеш, rsync сбросит содержимое директории в точку монтирования вместо перенесения директории mysql.
Когда rsync закончит, переименуйте текущую папку, добавив к её имени расширение .bak и сохраните её до тех пор, пока не подтвердится, что перемещение было успешным. Переименовывая папку, мы с одной стороны убедимся, что сервер точно работает с новым расположением, при этом мы сохраним резервную копию на случай, если что-то пошло не так:
Теперь мы готовы переключиться на настройку.
Шаг 2 — Указание на новое расположение Data
MySQL имеет несколько способов переписать значения конфигурации. В зависимости от того, используете ли вы MySQL или MariaDB расположение и название конфигурационных файлов может различаться. Например, для MySQL это файл /etc/mysql/mysql.conf.d/mysqld.cnf
Отредактируйте этот файл в соответствии с вашей новой директорией data. Найдите строку, которая начинается с datadir= и измените на путь, который соответствует новому расположению. В моём случае (новой директорией является /home/mial/mysql/) обновлённая строка выглядит так:
Эта строка расположена в секции [mysqld].
Если вы используете MariaDB, то отредактируйте, например, файл /etc/mysql/mariadb.conf.d/50-server.cnf:
В нём также после секции [mysqld] можно изменить значение datadir. Ещё обратите внимание для секции [mariadb] – значения, вписанные здесь, будут распространяться только на MariaDB, и на секцию [mariadb-10.0] – опции, указанные в ней, будут влиять только на MariaDB десятой версии.
Казалось бы, уже пора снова поднимать MySQL, но нам ещё нужно кое-что настроить перед тем, как мы успешно сможем это сделать.
Требования
- ОС Ubuntu 18.04 LTS. Или Ubuntu 16.04.
- Работаем из-под root.
- Сервер MySQL 8
Новый сервер
- установить MySQL (не запускать)
- распаковать сжатый файл ( tar -xzvf mysqldata.tar.gz )
- переместить содержимое mysqldata в datadir
- Убедитесь, что ваш innodb_log_file_size такой же на новом сервере, или, если это не так, не копируйте старые файлы журнала ( mysql сгенерирует их )
- Запустите MySQL
Пропустить копию после сжатия / распаковки. Передайте tar по сети, используя ssh, или (если и только если в защищенной частной сети прочитано: не через Интернет) используйте netcat, чтобы избежать издержек шифрования. Кроме того, если в локальной сети пропустить gzipping, если у вас есть быстрый сетевой канал, вы обнаружите, что передача ограничена на вращающемся ядре, выполняющем сжатие
Это работает как для innodb, так и для myisam? Кроме того, пользователи MySQL находятся в датадир, а?
@ giorgio79, конечно, до тех пор, пока вы перемещаете файлы ibdata. По умолчанию они находятся в датадир. Пользователи MySQL хранятся в папке mysql в табличном пространстве пользователя.
@ TypoCubeᵀᴹ извините, что на ответ ушло более двух лет, но это помогло бы мне, если бы кто-то сказал однозначно: «Да, он работает с Windows на Linux». В моем случае я перешел с Windows Server 2012 R2 на Cent OS (Red Hat 4.8.5-11) . Конкретная версия mysql была Maria DB 10.1 . Как было предписано, я остановил обе службы mysql, произвел Rsynced каталог данных, и после запуска службы mysql на новом сервере все базы данных, таблицы базы данных и пользователи базы данных были полностью исправны.
В соответствии с Руководством по изучению сертификации MySQL 5.0 , глава 32, раздел 32.3.4, стр. 456 457 описаны условия двоичной переносимости, в которых описывается следующее:
- Обе машины должны использовать целочисленную арифметику с двумя дополнениями
- Обе машины должны использовать формат IEEE с плавающей запятой, иначе таблицы не должны содержать столбцы с плавающей запятой (FLOAT или DOUBLE)
Если вы сконфигурируете InnoDB для использования табличных пространств для таблиц, условия переносимости двоичных файлов будут расширены, чтобы включить также файлы .ibd для таблиц InnoDB. (Условия для общих табличных пространств все еще применяются, потому что он содержит словарь данных, в котором хранится информация обо всех таблицах InnoDB.)
Если условия двоичной переносимости не выполняются, вы можете скопировать таблицы MyISAM или InnoDB с одного сервера на другой, выгрузив их в некотором текстовом формате (например, с помощью mysqldump) и перезагрузив их на целевой сервер.
Существует два основных способа перемещения отдельных таблиц на основе механизма хранения.
Для данного примера предположим следующее:
- datadir - это / var / lib / mysql
- база данных называется mydb
- таблица в базе данных mydb называется mytable .
MyISAM таблицы
Если mydb.mytable использует механизм хранения MyISAM, таблица будет физически отображаться как три отдельных файла.
- /var/lib/mysql/mydb/mytable.frm (файл .frm)
- /var/lib/mysql/mydb/mytable.MYD (файл .MYD)
- /var/lib/mysql/mydb/mytable.MYI (файл .MYI)
.Frm содержит структуру таблицы
.MYD содержит данные таблицы
.MYI содержит страницу индекса таблицы
Эти файлы используются взаимозависимо для представления таблицы с логической точки зрения в MySQL. Так как эти файлы не имеют дальнейшей логической привязки к нему, перенос таблицы с одного сервера БД на другой. Вы даже можете сделать это с сервера Windows на сервер Linux или MacOS. Конечно, вы можете отключить MySQL и скопировать 3 файла таблицы. Вы можете запустить следующее:
в одном сеансе SSH держать таблицу только для чтения и удерживать блокировку в течение 24 часов. Через секунду выполните копирование в другой сессии ssh. Затем завершите сеанс mysql с 24-часовой блокировкой. Вам не нужно ждать 24 часа.
Таблицы InnoDB
Исходя из вышеупомянутой цитаты из книги сертификации, существует множество факторов, которые определяют, как сделать резервную копию конкретной таблицы InnoDB. Для простоты, ясности и краткости просто выполните mysqldump желаемой таблицы, используя параметры --single-транзакции, чтобы получить идеальный дамп таблицы на момент времени. Не нужно разбираться с семантикой InnoDB, если вам нужна только одна таблица. Вы можете перезагрузить этот дамп-файл на любой сервер MySQL по вашему выбору.
Поскольку два вопроса были объединены здесь (Jcolebrand): РЕДАКТИРОВАТЬ
Если вы более чем готовы жить с некоторой медленной производительностью БД, вы можете выполнить серию rsyncs со старого сервера (ServerA) на новый сервер (ServerB), даже если mysql все еще работает на ServerA.
Шаг 01) Установите ту же версию MySQL на сервере B, что и у ServerA
Шаг 02) На сервере A, запустите SET GLOBAL innodb_max_dirty_pages_pct = 0; из mysql и около 10 минут (Это удаляет грязные страницы из пула буферов InnoDB. Это также помогает быстрее выполнять завершение работы mysql) Если ваша база данных - это MyISAM, вы можете пропустить этот шаг.
Шаг 03) rsync --archive --verbose --stats --partial --progress --human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql
Шаг 04) Повторяйте шаг 03, пока rsync не займет менее 1 минуты
Шаг 05) service mysql stop на сервере А
Шаг 06) Выполните еще одну rsync
Шаг 07) scp ServerA:/etc/my.cnf ServerB:/etc/
Шаг 08) service mysql start на сервере B
Шаг 08) service mysql start на сервере А (необязательно)
Вы можете создать подчиненное устройство репликации следующим образом. Просто помните, что в главном /etc/my.cnf явно указывается идентификатор сервера, а в ведомом /etc/my.cnf - другой номер для идентификатора сервера.
Я использую MySQL 5.5 на локальной машине для анализа большого количества правительственных данных. Я создал локальную базу данных, которая находится на моем диске по умолчанию (Win7 C: drive). Я хотел бы сохранить данные на моем диске E: большой внешний eSATA-диск.
Какие шаги я должен предпринять?
AppArmor
Настроим AppArmor, чтобы предоставить MySQL право на изменение нового каталога. Редактируем файл /etc/apparmor.d/tunables/alias:
Если этого не сделать, то получим ошибку:
Шаг 3 — Настройка правил контроля доступа AppArmor
Нам нужно сообщить AppArmor, чтобы он разрешил MySQL записывать в новую директорию. Это будет сделано посредством создания псевдонима между директорией по умолчанию и новым расположением. Чтобы сделать это, отредактируйте файл псевдонимов AppArmor:
В конце этого файла добавьте следующее правило псевдонимов:
Чтобы изменения вступили в силу, перезапустите AppArmor:
Вывод для systemctl и journalctl заканчивается:
Перезапуск MySQL
MySQL при запуске проверяет наличие директории /var/lib/mysql/mysql (ЗАЧЕМ?). Чтобы он не ругался, создадим пустую папку:
Заключение
В этой инструкции мы переместили директорию MySQL с базами данных data в новое расположение и обновили правила контроля доступа AppArmor в Ubuntu для соответствия изменениям.
Я использую MySQL 5.5 на локальной машине для анализа большого количества правительственных данных. Я создал локальную базу данных, которая находится на моем диске по умолчанию (диск Win7 C:). Я хотел бы сохранить данные на моем диске E: большом внешнем диске eSATA.
Какие шаги я должен предпринять?
Вы не можете экспортировать дБ в виде файла sql, затем сохранить файл и сделать e: затем в будущем, если вы хотите восстановить дБ, вы можете импортировать этот файл
@tq - Это не сработает. Он хочет изменить каталог данных сервера MySQL. Если он экспортирует и импортирует его, он все равно будет на том же диске!
Предупреждение, когда вы перемещаете файлы базы данных на внешний диск, вам придется держать его подключенным к компьютеру, в противном случае ваша база данных выйдет из строя, так как не сможет найти файлы данных
- Отключение mysql.
- Переместите все файлы в текущем каталоге данных в новое место (проверьте расположение в шаге 3 - datadir параметр).
- Найдите my.ini файл (он находится в установочном каталоге mysql). Измените datadir значение параметра, чтобы оно указывало на новое местоположение.
- Запустите MySQL.
Это дало мне ошибки при попытке запустить MySQL (проблемы с разрешениями). Я остановился на решении , которое я публикую здесь , которое, я надеюсь, поможет сэкономить время.
@adam windows упоминается в основной части вопроса, но он не помечен как вопрос только для окон, и окна не упоминаются в заголовке. Я разместил ссылку в комментариях, потому что другие пользователи, не работающие в Windows, могут также наткнуться на этот вопрос.
Я обнаружил, что должен был использовать, / а не \ как разделитель каталогов в пути к каталогу datadir . Я использую WAMPserver на Windows 10.
Каталог ProgramData может быть скрыт в вашем файловом менеджере. Я думал, что он не существует, пока я не изменил настройку для отображения скрытых файлов.
Несмотря на то, что вы запрашиваете окна, вот несколько хороших указаний для тех, кто заканчивает здесь и ищет направления linux / ubuntu:
- У вас есть данные MySQL на внешнем / USB / альтернативном диске /mountpoint/var/lib/mysql , возможно, с компьютера, который умер или старой установки
- Вы хотите переместить его в новую версию Ubuntu локально /var/lib/mysql
- Остановить MySQL с:
sudo /etc/init.d/mysql stop . - Сделайте резервную копию текущего (чистый) MySQL каталога данных с:
sudo cp -Rp /var/lib/mysql /var/lib/mysql.backup . - Затем скопируйте старый каталог данных со старого диска с помощью:
sudo cp -Rp /mountpoint/var/lib/mysql /var/lib/mysql
где mountpoint находится путь к монтированию. - Теперь перезапустите MySQL с помощью:
sudo /etc/init.d/mysql start
- Прежде всего, сделайте резервную копию ваших данных, используя mysqldump.
- Если вы устанавливаете Mysql Server из официального пакета, вы можете сделать это, запустив программу MySQLINstanceConfig.exe из каталога bin вашего сервера.
- Импорт данных
- Остановите Mysql сервер.
- Скопируйте данные из вашего каталога данных в новый каталог
- Откройте файл my.cnf (в моем случае «C: \ ProgramData \ MySQL \ MySQL Server 5.5 \ my.ini»)
- найдите строку как datadir = "C: \ ProgramData \ MySQL \ MySQL Server 5.5 \ data \" и измените путь к вашему новому местоположению
- Запустите свой сервер
Для Windows, начиная с MySQL Installer 1.4.6 (2015-04-07) , местоположение данных может быть указано в разделе реестра, который указывает местоположение my.ini файла, поэтому, если данные были первоначально установлены в расположении , отличном от заданного по умолчанию my.ini файл не будет C:\ProgramData\MySQL\MySQL Server x.x\ .
Расположение значений реестра (по крайней мере, начиная с MySQL 5.6):
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MySQL AB\MySQL Server x.x\
Location это то, REG_SZ что указывает на папку, в которой находятся файлы приложения MySQL; по умолчанию: C:\Program Files\MySQL\MySQL Server x.x\
DataLocation это то, REG_SZ что указывает на папку, где my.ini находится; по умолчанию также: C:\Program Files\MySQL\MySQL Server x.x\
Кроме того, my.ini путь к файлу включен в команду службы в этом разделе реестра: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MySQLXX в ImagePath REG_EXPAND_SZ значение.
Значение по умолчанию этой строки: "C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server x.x\my.ini" MySQLXX
Итак, суть в том, что для перемещения файлов данных в этом сценарии вы должны выполнить шаги, опубликованные @ITCuties с некоторыми дополнениями.
База данных MySQL 8 при установке на Ubuntu по умолчанию пишется в папку /var/lib/mysql. Со временем база растёт и поднимается вопрос переноса её на другой раздел диска. Перенесём базу данных в /u01/mysql/mysql.
Перемещаем каталог данных MySQL
Создадим папку /u01/mysql, в неё будем переносить папку с данными MySQL.
С помощью rsync переносим MySQL в другую папку:
Флаг –a сохраняет привилегии и другие свойства каталога. Флаг –v предоставляет подробный вывод. Теперь внимание, папка /var/lib/mysql теперь находится по адресу /u01/mysql/mysql (ну так мне было нужно).
Переименуем старую папку /var/lib/mysql, сохраним её на случай сбоя:
По умолчанию путь настроен в файле /etc/mysql/mysql.conf.d/mysqld.cnf, редактируем.
Найдите строку datadir= и укажите в ней путь к новому каталогу данных /u01/mysql/mysql.
4 ответа
- Завершение работы mysql.
- Переместите все файлы в вашем текущем каталоге данных в новое место (проверьте местоположение в шаге 3 - datadir ).
- Найдите my.ini (он находится в каталоге установки mysql). Измените значение параметра datadir , чтобы указать на новое местоположение.
- Запустите mysql.
Хотя вы запрашиваете окна, здесь - это хорошие направления для тех, кто заканчивается здесь ищет направления linux /ubuntu:
- У вас есть данные mysql на внешнем /usb /альтернативном диске /mountpoint/var/lib/mysql , возможно, с компьютера, который умер или старой установки
- Вы хотите перенести его на свою новую установку Ubuntu локально /var/lib/mysql
- Остановить MySQL с помощью:
sudo /etc/init.d/mysql stop . - Сделайте резервную копию текущего (чистого) каталога данных MySQL с помощью:
sudo cp -Rp /var/lib/mysql /var/lib/mysql.backup . - Затем скопируйте старый каталог данных со старого диска с помощью:
sudo cp -Rp /mountpoint/var/lib/mysql /var/lib/mysql
где mountpoint - это путь к монтированию. - Теперь перезапустите MySQL с помощью:
sudo /etc/init.d/mysql start
- Прежде всего, сделайте резервную копию своих данных с помощью mysqldump.
- Если вы устанавливаете Mysql Server из официального пакета, вы можете сделать это, запустив программу MySQLINstanceConfig.exe из каталога bin вашего сервера.
- Импортировать данные
- Остановить сервер Mysql.
- Скопировать данные из каталога данных в новый каталог
- Откройте файл my.cnf (в моем случае «C: \ ProgramData \ MySQL \ MySQL Server 5.5 \ my.ini»)
- найдите строку типа datadir = "C: \ ProgramData \ MySQL \ MySQL Server 5.5 \ data \" и измените путь к вашему новому местоположению
- Запустите свой сервер
Для Windows, начиная с MySQL Installer 1.4.6 (2015-04-07) , расположение данных может быть указано в разделе реестра, в котором указано расположение my.ini файл, поэтому, если данные изначально были установлены в не по умолчанию, файл my.ini не будет находиться в C:\ProgramData\MySQL\MySQL Server x.x\ .
Расположение значений реестра (по крайней мере, с MySQL 5.6):
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MySQL AB\MySQL Server x.x\
Location - это REG_SZ , который указывает на папку, в которой находятся файлы приложений MySQL; по умолчанию: C:\Program Files\MySQL\MySQL Server x.x\
DataLocation - это REG_SZ , который указывает на папку, где находится my.ini ; по умолчанию: C:\Program Files\MySQL\MySQL Server x.x\
Кроме того, путь к файлу my.ini включен в служебную команду в этом разделе реестра: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MySQLXX в значении ImagePath REG_EXPAND_SZ .
Значение по умолчанию для этой строки: "C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server x.x\my.ini" MySQLXX
Итак, основная причина заключается в том, что для перемещения файлов данных в этом сценарии вам необходимо выполнить шаги, отправленные @ITCuties с некоторыми дополнениями.
Со временем базы данных растут, иногда перерастая место на файловой системе. Не важно, хотите ли вы использовать более просторное хранилище под базу данных, или использовать RAID, сетевые блочные устройства или разместить базы данных на другом диске, а не на котором расположена основная система, данная инструкция проведёт вас по процессу смены расположения директории data вашей СУБД MySQL.
Создание символической ссылки на директорию данных MySQL
Вместо правки конфигурационного файла, можно было бы создать символическую ссылку до нового расположения.
Далее создаём символическую ссылку:
Старый сервер
- Остановить MySQL сервер
- Скопируйте содержимое datadir в другое место на диске ( ~/mysqldata/* )
- Запустите сервер mysql снова (время простоя составило 10-15 минут)
- сжать данные ( tar -czvf mysqldata.tar.gz ~/mysqldata )
- скопируйте сжатый файл на новый сервер
Читайте также: