How do I move a MySQL data directory to an external hard drive?
I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?
- Stop MySQL
- Copy MySQL data directory to new location
- Edit the MySQL configuration file (update datadir variable).
- Update the new path in apparmor
- Reload apparmor
- Start MySQL (
/etc/init.d/mysql restart)
But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:
2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/ neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 - Permission denied)
What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64
mysql
add a comment |
I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?
- Stop MySQL
- Copy MySQL data directory to new location
- Edit the MySQL configuration file (update datadir variable).
- Update the new path in apparmor
- Reload apparmor
- Start MySQL (
/etc/init.d/mysql restart)
But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:
2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/ neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 - Permission denied)
What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64
mysql
As a side note, does the file name in the warning message have a space as first letter?...MWDatabase/mysql/ neo.lower-test
– Jehad Keriaki
May 18 '15 at 4:26
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24
add a comment |
I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?
- Stop MySQL
- Copy MySQL data directory to new location
- Edit the MySQL configuration file (update datadir variable).
- Update the new path in apparmor
- Reload apparmor
- Start MySQL (
/etc/init.d/mysql restart)
But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:
2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/ neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 - Permission denied)
What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64
mysql
I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?
- Stop MySQL
- Copy MySQL data directory to new location
- Edit the MySQL configuration file (update datadir variable).
- Update the new path in apparmor
- Reload apparmor
- Start MySQL (
/etc/init.d/mysql restart)
But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:
2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/ neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 - Permission denied)
What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64
mysql
mysql
edited May 23 '17 at 12:40
Community♦
1
1
asked May 17 '15 at 14:17
Srikanth VaindamSrikanth Vaindam
1163
1163
As a side note, does the file name in the warning message have a space as first letter?...MWDatabase/mysql/ neo.lower-test
– Jehad Keriaki
May 18 '15 at 4:26
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24
add a comment |
As a side note, does the file name in the warning message have a space as first letter?...MWDatabase/mysql/ neo.lower-test
– Jehad Keriaki
May 18 '15 at 4:26
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24
As a side note, does the file name in the warning message have a space as first letter?
...MWDatabase/mysql/ neo.lower-test– Jehad Keriaki
May 18 '15 at 4:26
As a side note, does the file name in the warning message have a space as first letter?
...MWDatabase/mysql/ neo.lower-test– Jehad Keriaki
May 18 '15 at 4:26
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24
add a comment |
3 Answers
3
active
oldest
votes
The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".
I did copy with ownership and permissions (cp -r -k /var/lib/mysql /media/<new data dir>).
– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
add a comment |
Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.
add a comment |
There are several issues relating to permissions and mysqld/mariadb:
1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)
2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)
3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=
I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f101732%2fhow-do-i-move-a-mysql-data-directory-to-an-external-hard-drive%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".
I did copy with ownership and permissions (cp -r -k /var/lib/mysql /media/<new data dir>).
– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
add a comment |
The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".
I did copy with ownership and permissions (cp -r -k /var/lib/mysql /media/<new data dir>).
– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
add a comment |
The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".
The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".
answered May 17 '15 at 17:12
Rick JamesRick James
42.9k22258
42.9k22258
I did copy with ownership and permissions (cp -r -k /var/lib/mysql /media/<new data dir>).
– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
add a comment |
I did copy with ownership and permissions (cp -r -k /var/lib/mysql /media/<new data dir>).
– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
I did copy with ownership and permissions (
cp -r -k /var/lib/mysql /media/<new data dir>).– Srikanth Vaindam
May 17 '15 at 17:41
I did copy with ownership and permissions (
cp -r -k /var/lib/mysql /media/<new data dir>).– Srikanth Vaindam
May 17 '15 at 17:41
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Hmmm... Then my two guesses are not sufficient.
– Rick James
May 17 '15 at 19:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
Selinux maybe hurting you here.
– eroomydna
May 19 '15 at 12:13
add a comment |
Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.
add a comment |
Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.
add a comment |
Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.
Try umask=0000 first (without fmask, dmask). It will be clearer whether it is selinux casing troubles or not. I could start mysql with mount options fmask=077,dmask=0077 and with uid=999,gid=1002 (mysql:mysql in my passwd; check your numbers). It is like workaround if webserver+cgi run as root. Before mysql could not start even as root. Pretty strange. Maybe a bug in mysql or mount.
answered Sep 21 '18 at 2:45
J. DenverJ. Denver
111
111
add a comment |
add a comment |
There are several issues relating to permissions and mysqld/mariadb:
1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)
2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)
3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=
I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
There are several issues relating to permissions and mysqld/mariadb:
1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)
2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)
3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=
I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
There are several issues relating to permissions and mysqld/mariadb:
1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)
2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)
3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=
I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
There are several issues relating to permissions and mysqld/mariadb:
1. Basic ugo/rwx Eunuchs(tm) permissions
1.2. This should be taken care of by
1.2.1. [cp --permissions ...]
1.2.2. OR [rsync -av ...]
1.2.3. OR [chown -R ...] (as in the OP)
2. ACLs
2.1. This should be handled by Selinux [semanage fcontext -a .../restorecon -R],
2.2. OR by Apparmor (as in the OP; I don't know the syntax)
3. Systemd configuration**
3.1. Systemd imposes default restrictions about what files service processes can access
3.2. This should be handled by [Service] directives
3.1.1. Under e.g. in file /etc/systemd/system/mariadb.service.d/something.conf
3.1.2. Such as ProtectHome=, ProtectSystem=, ReadWritePaths=
I have successfully moved MariaDB files, normally under /var/lib/mysql/, to other locations using the first two steps; I have another system where the first two steps were not enough, I am going to try it again tonight (production system, cannot just do it at any time).
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 7 mins ago
Brian CarcichBrian Carcich
1
1
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Brian Carcich is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f101732%2fhow-do-i-move-a-mysql-data-directory-to-an-external-hard-drive%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
As a side note, does the file name in the warning message have a space as first letter?
...MWDatabase/mysql/ neo.lower-test– Jehad Keriaki
May 18 '15 at 4:26
@JehadKeriaki No, there is no space. that might be copy paste error.
– Srikanth Vaindam
May 18 '15 at 5:24