Mysql перенос базы данных на другой диск windows

I could successfully alter the data directory of MySQL or MariaDB, and resolve all related issues on Fedora 30. I think the following steps will work on other distributions.

Note: Users of Debian-based distributions like Ubuntu should search how to disable and edit AppArmor and then follow the following steps.

Disabling SELinux

First of all, let me mention that RedHat-based Linux Distributions (RHELs) like Fedora, CentOS, etc. use SELinux that enforces mandatory access control policies. So it’s better to disable it during the following steps and later enable it with some tweaks.

Open the SELinux configuration file

nano /etc/selinux/config

Locate the line that contains SELINUX=enforcing and change its value to SELINUX=disabled, save the file and reboot your system.

Changing the datadir of MySQL

Stop the MySQL services

systemctl stop mysqld.service

Make a new directory for MySQl’s data directory. Due to some reasons which are out of the scope of this solution, it’s highly recommended to not create a data directory under the /home directory, but maybe some of you like me prefer it (it costs more steps).

mkdir /home/eloy/applications/mysql-datadir/

Set ownership and permissions of the new directory to the default MySQL’s data directory(/var/lib/mysql):

chown --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/
chmod --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/

Copy all files from the default directory to the new one

cp -rp /var/lib/mysql/*   /home/eloy/applications/mysql-datadir/

Edit the /etc/my.cnf file, add add the following line under [mysqld] section:

[mysqld]
datadir=/home/eloy/applications/mysql-datadir/

Now you can start your MySQL service via the following command

systemctl start mysqld.service

But if the data directory is created under /home, MySQL won’t start and you would see the following errors and warnings after journalctl -xe:

Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32190) 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Can't create test file /home/eloy/applications/mysql-datadir/eloy-fedora-laptop.lower-> 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: [113B blob data] 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [ERROR] Aborting

Resolving /home issues

Make sure that all the parent directories of the new datadir upwards have x (execute) permissions for all (user, group, and other). I prefer to not use a recursive script so:

chmod +x /home/eloy/applications/mysql-datadir
chmod +x /home/eloy/applications
chmod +x /home/eloy/
chmod +x /home

As it is mentioned creating datadir under /home directory is tricky because by default MySQL does not allow it. Create a file under /etc/systemd/system/mariadb.service.d and put the following lines in:

#open an editor to create a file
nano /etc/systemd/system/mariadb.service.d/centreon.conf 

copy the following lines to the new centreon.conf file and save it

[Service]
ProtectHome=false 
#ProtectSystem=off

Apply the changes by running the following command

systemctl daemon-reload

Now you can run the MySQL service:

systemctl start mysqld.service

Enabling SELinux

Again edit the /etc/selinux/config file, and change the line of SELINUX=disabled to SELINUX=enforcing. Save the file and reboot your system.

To query the current status of SELinux use the following commands, it should print enforcing as an output.

getenforce

the SELinux context uses mysqld_db_t and if it is not set correctly mysqld process will be aborted, so you need to update it:

semanage fcontext -a -t mysqld_db_t "/home/eloy/applications/mysql-datadir(/.*)?"

restorecon -Rv /home/eloy/applications/mysql-datadir

Now you can run MySQL.
Cheers

For Windows, as of the MySQL Installer 1.4.6 (2015-04-07), the data location may be specified in a registry key that specifies the location of the my.ini file, so if the data was initially installed in a non-default location the my.ini file won’t be in C:ProgramDataMySQLMySQL Server x.x.

The location of the registry values are (at least as of MySQL 5.6):

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMySQL ABMySQL Server x.x

Location is a REG_SZ that points to the folder where the MySQL application files are located; by default: C:Program FilesMySQLMySQL Server x.x

DataLocation is a REG_SZ that points to the folder where my.ini is located; by default also: C:Program FilesMySQLMySQL Server x.x

Also, the my.ini file path is included in the service command under this registry key:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesMySQLXX in the ImagePath REG_EXPAND_SZ value.

The default value of this string is: "C:/Program Files/MySQL/MySQL Server 5.6/binmysqld" --defaults-file="C:Program FilesMySQLMySQL Server x.xmy.ini" MySQLXX

So the bottom line is that in order to move data files in this scenario you have to follow the steps posted by @ITCuties with some additions.

Here are the full steps:

1. Shutdown mysql.
2. Look up the above DataLocation registry value to find the current my.ini file location, and if you are wanting to also move the my.ini file update the DataLocation path and move the my.ini file to this new path.
3. If you are changing the location of the my.ini file, you need to update the path in the service command registry key listed above.
4. Open the my.ini file, locate the datadir parameter. Move the files from this datadir path to the path in which you want the data to reside, and then also update the datadir parameter with this new path.
5. Start mysql.

For Windows, as of the MySQL Installer 1.4.6 (2015-04-07), the data location may be specified in a registry key that specifies the location of the my.ini file, so if the data was initially installed in a non-default location the my.ini file won’t be in C:ProgramDataMySQLMySQL Server x.x.

The location of the registry values are (at least as of MySQL 5.6):

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMySQL ABMySQL Server x.x

Location is a REG_SZ that points to the folder where the MySQL application files are located; by default: C:Program FilesMySQLMySQL Server x.x

DataLocation is a REG_SZ that points to the folder where my.ini is located; by default also: C:Program FilesMySQLMySQL Server x.x

Also, the my.ini file path is included in the service command under this registry key:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesMySQLXX in the ImagePath REG_EXPAND_SZ value.

The default value of this string is: "C:/Program Files/MySQL/MySQL Server 5.6/binmysqld" --defaults-file="C:Program FilesMySQLMySQL Server x.xmy.ini" MySQLXX

So the bottom line is that in order to move data files in this scenario you have to follow the steps posted by @ITCuties with some additions.

Here are the full steps:

1. Shutdown mysql.
2. Look up the above DataLocation registry value to find the current my.ini file location, and if you are wanting to also move the my.ini file update the DataLocation path and move the my.ini file to this new path.
3. If you are changing the location of the my.ini file, you need to update the path in the service command registry key listed above.
4. Open the my.ini file, locate the datadir parameter. Move the files from this datadir path to the path in which you want the data to reside, and then also update the datadir parameter with this new path.
5. Start mysql.

Возникает ситуация когда нужно перенести базы mysql в другое место. Например, на отдельный жесткий диск или просто в другое место. Это может быть необходимо, если не хватает места на диске или для увеличения быстродействия, например ssd винт или raid массив.

Способ 1

Если вы хотите выделить под БД полностью весь жесткий диск, то нужно просто примонтировать устройство в mysql директорию по умолчанию. Обычно это /var/lib/mysql Останавливаем сервер

/etc/init.d/mysqld stop

Копируем базы в какую-то временную директорию

mkdir /var/lib/mysql_temp
cp -r /var/lib/mysql/* /var/lib/mysql_temp/

удаляем базы со старой директории

rm /var/lib/mysql/* -Rf

Предполагается что у вас уже есть подготовленный жесткий диск. Если же нет, то его нужно предварительно разбить и создать файловую систему. Это можно сделать например при помощи cfdisk. И так примонтируем устройство:

mount /dev/sdb1 /var/lib/mysql

нужно не забыть прописать в файле /etc/fstab примонтированный раздел, для того что бы при следующей загрузке системы он автоматически монтировался. Более подробнее смотрите мануалы по fstab и mount. В fstab должна быть запись вида:

/dev/sdb1 /var/lib/mysql ext4 defaults 0 0

далее переносим назад наши базы

cp -r /var/lib/mysql_temp/* /var/lib/mysql/

и удаляем резервную копию (с этим лучше пока не спешить, удалить можно когда все успешно перенеслось)

rm /var/lib/mysql_temp/ -Rf

далее нужно правильно выставить права доступа к /var/lib/mysql. Владелец папки должен быть mysql c правом на запись

chown mysql:mysql /var/lib/mysql/ -R
chmod 755 /var/lib/mysql

а так же еще один важный момент, выставляем SELinux права доступа

chcon -R system_u:object_r:mysqld_db_t:s0 /var/lib/mysql/

проверить SELinux права можно командой ls -Z, должно быть что-то вроде этого:

ls -Z /var/lib/mysql/
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

теперь запускаем mysql демон

/etc/init.d/mysqld start

Способ 2

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

останавливаем сервер

/etc/init.d/mysqld stop

копируем базы в новое место. Например, если мы хотим, что бы базы лежали в /media/sda3/mysql/

mkdir /media/sdb1/mysql/
cp -r /var/lib/mysql/* /media/sdb1/mysql/

ставим права доступа к /media/sdb1/mysql/

chown mysql:mysql /media/sdb1/mysql/ -R
chmod 755 /media/sdb1/mysql

а так же SELinux права доступа

chcon -R system_u:object_r:mysqld_db_t:s0 /media/sdb1/mysql/

правим конфиг my.cnf. Обычно он находится в /etc/my.cnf либо /etc/mysql/my.cnf

[mysqld]
datadir=/media/sdb1/mysql
socket=/media/sdb1/mysql/mysql.sock
log-bin=/media/sdb1/mysql/mysql-bin
[mysqld_safe]
datadir=/media/sdb1/mysql

теперь запускаем mysql демон

/etc/init.d/mysqld start

На этом всё. Если что-то не получается, смотрим лог в /var/log/mysql.log

Иногда возникает ситуация когда нужно перенести базы mysql в другое место.
Например, на отдельный жесткий диск или просто в другое место (какие-то
причины безопасности). Это может быть необходимо, если не хватает места на
диске или для увеличения быстродействия, например ssd винт или raid массив.

Способ 1

Если вы хотите выделить под БД полностью весь жеский диск, то нужно просто
примонтировать устройство в mysql директорию по умолчанию. Обычно это
/var/lib/mysql Останавливаем сервер

/etc/init.d/mysqld stop

Копируем базы в какую-то временную директорию

mkdir /var/lib/mysql_temp
cp -r /var/lib/mysql/* /var/lib/mysql_temp/

удаляем базы со старой директории

rm /var/lib/mysql/* -Rf

Предполагается что у вас уже есть подготовленный жесткий диск. Если же нет, то
его нужно предварительно разбить и создать файловую систему. Это можно сделать
например при помощи cfdisk. И так примонтируем устройство:

mount /dev/sda3 /var/lib/mysql

нужно не забыть прописать в файле /etc/fstab примонтированный раздел, для того
что бы при следующей загрузке системы он автоматически монтировался. Более
подробнее смотрите мануалы по fstab и
mount. В fstab должна быть запись вида:

/dev/sda3 /var/lib/mysql ext4 defaults 0 0

далее переносим назад наши базы

cp -r /var/lib/mysql_temp/* /var/lib/mysql/

и удаляем резервную копию (с этим лучше пока не спешить, удалить можно когда
все успешно перенеслось)

rm /var/lib/mysql_temp/ -Rf

далее нужно правильно выставить права доступа к /var/lib/mysql. Владелец папки
должен быть mysql c правом на запись

chown mysql:mysql /var/lib/mysql/ -R
chmod 755 /var/lib/mysql

а так же еще один важный момент, выставляем
SELinux права доступа

chcon -R system_u:object_r:mysqld_db_t:s0 /var/lib/mysql/

проверить SELinux права можно командой ls -Z, должно быть что-то вроде этого:

ls -Z /var/lib/mysql/
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

теперь запускаем mysql демон

/etc/init.d/mysqld start

Способ 2

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

останавливаем сервер

/etc/init.d/mysqld stop

копируем базы в новое место. Например, если мы хотим, что бы базы лежали в
/media/sda3/mysql/

mkdir /media/sda3/mysql/
cp -r /var/lib/mysql/* /media/sda3/mysql/

ставим права доступа к /media/sda3/mysql/

chown mysql:mysql /var/lib/mysql/ -R
chmod 755 /var/lib/mysql

а так же SELinux права доступа

chcon -R system_u:object_r:mysqld_db_t:s0 /media/sda3/mysql/

правим конфиг my.cnf. Обычно он находится в /etc/my.cnf либо /etc/mysql/my.cnf

[mysqld]
datadir=/media/sda3/mysql
socket=/media/sda3/mysql/mysql.sock
log-bin=/media/sda3/mysql/mysql-bin
[mysqld_safe]
datadir=/media/sda3/mysql

теперь запускаем mysql демон

/etc/init.d/mysqld start

На этом всё. Если что-то не получается, смотрим лог в /var/log/mysql.log

Последнее обновление 9 мая 2021 г.

Перейти к содержанию

# service mysql stop

# cp -pR /var/lib/mysql /databases

datadir = /var/lib/mysql

datadir = /databases/mysql

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

• Имеет маленький размер
• Сильно нагружен ОС и системными запросами
• Довольно медленный
• Помирает

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:

Перенос пользовательской базы данных¶

1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.

2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:

• DB Import — импорт новостных лент
• DDB — распределенная база данных
• Sch_to_DB — репликация расписаний
  иначе, есть вероятность потерять часть информации.

3. Запускаем Microsoft SQL Server Management Studio.

4. Самым первым делом всегда делаем бэкап базы!

5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием «RADIO-DB»). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):

6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт TasksDetach (ЗадачиОтсоединить):

7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:

8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA.

9. Копируем эти файлы в новый каталог на новый диск и снова открываем Microsoft SQL Server Management Studio.

10. Нажимаем ПКМ на разделе Databases (Базы данных), выбираем пункт Attach (Присоединить) и в открывшемся окне нажимаем кнопку Add (Добавить) и выбираем нужный нам файл RADIO-DB.mdf уже из нового каталога:

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

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

Перенос системных баз данных¶

Но, остались еще системные базы данных (спрятаны в разделе System Databases). Это msdb, model и tempdb, которые в общем-то тоже будет неплохо перенести на быстрый и отказоустойчивый диск. Тем более, что среди них есть одна, очень для нас важная база — tempdb. Именно через нее проходят все запросы, прежде чем попасть в пользовательскую МБД. Перенести системные базы ничуть не сложнее, чем пользовательские. И для этого надо:

1. Используя Microsoft SQL Server Management Studio, выполнить следующий скрипт:

Его также можно скачать из этого описания и запустить непосредственно на SQl-сервере.

2. Останавливаем службу SQL.

3. Копируем из старого каталога (помним наш пример: C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.

4. Обязательно добавляем учетную запись группы безопасности. Подробно о том, как это сделать, читайте в конце данной статьи, в разделе «Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы».

5. Запускаем службу SQL.

6. Убедиться, что мы все сделали правильно, можно, посмотрев в свойствах каждой системной БД раздел Files (Файлы). Там должны быть новые пути к обоим файлам (самой БД и логу).

Перенос самой системной базы данных master¶

Да, еще у нас осталась самая системная из всех системных баз — master
— путь, прописанный для этой базы, будет путем по умолчанию для всех вновь создающихся баз на данном сервере. Впрочем, для пользователей Digispot это не очень актуально. Тем более, что мы уже умеем менять пути любым базам.

Итак, master:

1. Для изменения пути к БД master, нам понадобится оснастка SQL Server Configuration Manager (Диспетчер конфигурации SQL Server). Запускаем ее и открываем свойства SQL Server:

2. В свойствах SQL Server`а открываем вкладку Startup Parameters (Параметры запуска):

и по очереди меняем все указанные пути на новые.
— каждая строка начинается со своего символа -d, -e или -l. Ни в коем случае не меняйте их и не удаляйте!

3. Каждое изменение пути подтверждаем нажатием кнопки Update.

4. Теперь останавливаем сервис, копируем файлы master.mdf и mastlog.ldf из старого каталога в новый. После чего запускам сервис. ERRORLOG можно не копировать. Он создастся заново.

Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы¶

1. С помощью проводника Windows перейдите в папку файловой системы, в которой находятся файлы базы данных. Правой кнопкой мыши щелкните эту папку и выберите пункт Свойства.

2. На вкладке Безопасность щелкните Изменитьи затем ― Добавить.

3. В диалоговом окне Выбор пользователей, компьютеров, учетных записей служб или групп щелкните Расположения, в начале списка расположений выберите имя своего компьютера и нажмите кнопку ОК.

4. В поле Введите имена объектов для выбора введите имя идентификатора безопасности службы. В качестве идентификатора безопасности службы компонента Компонент Database Engine используйте NT SERVICEMSSQLSERVER для экземпляра по умолчанию или NT SERVICEMSSQL$InstanceName — для именованного экземпляра.

5. Щелкните Проверить имена , чтобы проверить введенные данные. Проверка зачастую выявляет ошибки, по ее окончании может появиться сообщение о том, что имя не найдено. При нажатии кнопки ОК открывается диалоговое окно Обнаружено несколько имен .Теперь выберите идентификатор безопасности службы MSSQLSERVER или NT SERVICEMSSQL$InstanceName и нажмите кнопку ОК. Снова нажмите кнопку ОК , чтобы вернуться в диалоговое окно Разрешения.

6. В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для <имя> установите флажок Разрешить для параметра Полный доступ.

7. Нажмите кнопку Применить, а затем дважды кнопку ОК , чтобы выполнить выход.

Вот теперь, точно всё. Спасибо за внимание!

P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.