How do I move a MySQL data directory to an external hard drive?












3















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?




  1. Stop MySQL

  2. Copy MySQL data directory to new location

  3. Edit the MySQL configuration file (update datadir variable).

  4. Update the new path in apparmor

  5. Reload apparmor

  6. 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










share|improve this question

























  • 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
















3















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?




  1. Stop MySQL

  2. Copy MySQL data directory to new location

  3. Edit the MySQL configuration file (update datadir variable).

  4. Update the new path in apparmor

  5. Reload apparmor

  6. 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










share|improve this question

























  • 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














3












3








3








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?




  1. Stop MySQL

  2. Copy MySQL data directory to new location

  3. Edit the MySQL configuration file (update datadir variable).

  4. Update the new path in apparmor

  5. Reload apparmor

  6. 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










share|improve this question
















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?




  1. Stop MySQL

  2. Copy MySQL data directory to new location

  3. Edit the MySQL configuration file (update datadir variable).

  4. Update the new path in apparmor

  5. Reload apparmor

  6. 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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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



















  • 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










3 Answers
3






active

oldest

votes


















1














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".






share|improve this answer
























  • 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



















1














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.






share|improve this answer































    0














    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).





    share








    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.




















      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      1














      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".






      share|improve this answer
























      • 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
















      1














      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".






      share|improve this answer
























      • 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














      1












      1








      1







      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".






      share|improve this answer













      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".







      share|improve this answer












      share|improve this answer



      share|improve this answer










      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



















      • 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













      1














      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.






      share|improve this answer




























        1














        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.






        share|improve this answer


























          1












          1








          1







          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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Sep 21 '18 at 2:45









          J. DenverJ. Denver

          111




          111























              0














              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).





              share








              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.

























                0














                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).





                share








                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.























                  0












                  0








                  0







                  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).





                  share








                  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).






                  share








                  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.








                  share


                  share






                  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.






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      ف. موراي أبراهام

                      صرب

                      كأس إنترتوتو