[SOLVED] Change MySQL database location OpenMediaVault 2.1

    • OMV 2.x
    • Resolved

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • [SOLVED] Change MySQL database location OpenMediaVault 2.1

      Well, I recently installed MySQL plugin to store the databases for Owncloud and Wordpress plugins but I found that the databases are stored in the boot disk. Call me paranoid, but if the boot disk fails, I could restore it using Clonezilla. But the image that Clonezilla took, in case the boot disk breaks, has an old database. To solve that, I managed to do a scheduled backup of the MySQL plugin automatically every day and save it to a shared folder in a data drive.
      My question is: could it be possible to directly change the default location of the MySQL plugin (/var/lib/mysql) to store the database directly to a data disk? That would be useful so in case something in the system disk fails, when I restore it, the database would not be affected.

      Hope someone can help me
      Thanks in advance.

      EDIT: it seems like an older version of the MySQL plugin allowed us to select a shared folder to store the data. Why it has been removed in newer versions?

      Guillem
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.

      The post was edited 1 time, last by Lord Wektabyte ().

    • Yep, very easy. Decide where you want it - ie /media/long_uuid/mysql

      Stop the service - service mysql stop
      Make the directory - mkdir -p /media/long_uuid/mysql
      Change the owner - chown mysql:mysql /media/long_uuid/mysql
      Then copy all files to that location - cp -rfvp /var/lib/mysql/* /media/long_uuid/mysql/

      Add datadir = /media/long_uuid/mysql to the extra options box in the plugin. Save and apply.

      That's it.
      omv 4.1.15 arrakis | 64 bit | 4.15 proxmox kernel | omvextrasorg 4.1.13
      omv-extras.org plugins source code and issue tracker - github

      Please read this before posting a question and this and this for docker questions.
      Please don't PM for support... Too many PMs!
    • I'm going to take a snapshot and try this method. Thanks for the fast answer Ryecoaaron.

      In 10 minutes I tell you if the thread can be resolved or not :thumbsup:
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • THANKS! It worked for me. Very easy. I mark the thread as SOLVED if any other people need help with this.

      Sorry to insist, Aaron, but I saw in older versions of the MySQL plugin that the data directory had to be chosen in the plugin. Why it has disappeared in this newer versions?
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • gsola96 wrote:

      Sorry to insist, Aaron, but I saw in older versions of the MySQL plugin that the data directory had to be chosen in the plugin. Why it has disappeared in this newer versions?

      Because it was a pain in the ass. Many people had problems with it. Quite a few people asked how to put it on the boot drive (usually because they use an ssd). MySQL is so easy to backup and restore, that it didn't seem like an issue to put it on the boot drive. Most users can move it (with some help) :)
      omv 4.1.15 arrakis | 64 bit | 4.15 proxmox kernel | omvextrasorg 4.1.13
      omv-extras.org plugins source code and issue tracker - github

      Please read this before posting a question and this and this for docker questions.
      Please don't PM for support... Too many PMs!
    • Thanks again for the explanation. It is not any difficult to change the location...
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • Sorry to come here again. After convincing one friend to use OMV in an old box, I'm setting his machine up.

      I'm installing MySQL plugin and I want to change the directory to a data drive (same as I did in my own box when I post this thread). The problem is that I follow the same instructions that @ryecoaaron wrote here and I have an error and mysqld can't start.

      If it helps, this is the error I get when enabling the plugin:
      Display Spoiler
      Error #4000:
      exception 'OMVException' with message 'Failed to execute command 'export LANG=C; invoke-rc.d 'mysql' start 2>&1': Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
      invoke-rc.d: initscript mysql, action "start" failed.' in /usr/share/php/openmediavault/initscript.inc:176
      Stack trace:
      #0 /usr/share/php/openmediavault/initscript.inc(141): OMVSysVInitScript->invoke('start')
      #1 /usr/share/php/openmediavault/initscript.inc(61): OMVSysVInitScript->start()
      #2 /usr/share/openmediavault/engined/module/mysql.inc(130): OMVSysVInitScript->exec()
      #3 /usr/share/openmediavault/engined/rpc/config.inc(206): OMVModuleMySql->startService()
      #4 [internal function]: OMVRpcServiceConfig->applyChanges(Array, Array)
      #5 /usr/share/php/openmediavault/rpcservice.inc(125): call_user_func_array(Array, Array)
      #6 /usr/share/php/openmediavault/rpcservice.inc(158): OMVRpcServiceAbstract->callMethod('applyChanges', Array, Array)
      #7 /usr/share/openmediavault/engined/rpc/config.inc(224): OMVRpcServiceAbstract->callMethodBg('applyChanges', Array, Array)
      #8 [internal function]: OMVRpcServiceConfig->applyChangesBg(Array, Array)
      #9 /usr/share/php/openmediavault/rpcservice.inc(125): call_user_func_array(Array, Array)
      #10 /usr/share/php/openmediavault/rpc.inc(79): OMVRpcServiceAbstract->callMethod('applyChangesBg', Array, Array)
      #11 /usr/sbin/omv-engined(500): OMVRpc::exec('Config', 'applyChangesBg', Array, Array, 1)
      #12 {main}



      Thanks
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • It is most likely a permissions issue at the location you are trying to change it to. The location must be owned by mysql:mysql.
      omv 4.1.15 arrakis | 64 bit | 4.15 proxmox kernel | omvextrasorg 4.1.13
      omv-extras.org plugins source code and issue tracker - github

      Please read this before posting a question and this and this for docker questions.
      Please don't PM for support... Too many PMs!
    • It seems like this. But I don't understand why, when you replied me some time ago it worked, and now, doing the same, does not work.
      I tried with thrailing slashes, without them...
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • Hard to say. Do you really need it on a different drive? You can schedule a backup to a data drive with the plugin now.
      omv 4.1.15 arrakis | 64 bit | 4.15 proxmox kernel | omvextrasorg 4.1.13
      omv-extras.org plugins source code and issue tracker - github

      Please read this before posting a question and this and this for docker questions.
      Please don't PM for support... Too many PMs!
    • Not really. Only because of what I explained time ago:

      gsola96 wrote:

      Call me paranoid, but if the boot disk fails, I could restore it using Clonezilla. But the image that Clonezilla took, in case the boot disk breaks, has an old database.


      Just because it's more practical to restore only a Clonezilla image than restoring a CLonezilla + SQL backup.

      Thanks for your pattience. I'll investigate why I can't do that now...
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • Somethings have changed. With the scheduled backup, you wouldn't lose your database and the plugin now has restore which takes a very little amount of time.
      omv 4.1.15 arrakis | 64 bit | 4.15 proxmox kernel | omvextrasorg 4.1.13
      omv-extras.org plugins source code and issue tracker - github

      Please read this before posting a question and this and this for docker questions.
      Please don't PM for support... Too many PMs!
    • Apologies for restarting an old post.
      I've also installed mysql from plugins, and the web page (MyWebSQL) for it did pop up as expected.
      Then I wanted to move the database to the harddrive, as my setup is a RaspberryPI B+. I followed the instructions to move the database. Then I did a reboot.

      After that I get the "standard" "It worked web page..." trying to access the web admin of OMV. Trying to access "MyWebSQL", also failed with "404 Not found".

      It seems things only went wrong after copying the files or perhaps it was already messed prior to the reboot.

      Is there anything that I missed to not destroy the web gui?
    • I don't know why you messed up the web, but when I had this problem (not because of changing MySQL database) I log in via SSH or directly with the command line and launch omv-firstaid. Then restoring web server (or similar) in the options and Then doing service nginx restart.

      That worked for me every time I got any "It works"

      Enviat des del meu D2303 usant Tapatalk
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • I followed the instructions to move the files but when I activate MySQL and save and apply I get this error:

      ​[color=rgb(0, 0, 0); font-family: tahoma, arial, verdana, sans-serif; font-size: 12px; line-height: normal; white-space: pre]Error #4000:
      exception 'OMVException' with message 'Failed to execute command 'export LANG=C; invoke-rc.d 'mysql' start 2>&1': Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
      invoke-rc.d: initscript mysql, action "start" failed.' in /usr/share/php/openmediavault/initscript.inc:176
      Stack trace:
      #0 /usr/share/php/openmediavault/initscript.inc(141): OMVSysVInitScript->invoke('start')
      #1 /usr/share/php/openmediavault/initscript.inc(61): OMVSysVInitScript->start()
      #2 /usr/share/openmediavault/engined/module/mysql.inc(130): OMVSysVInitScript->exec()
      #3 /usr/share/openmediavault/engined/rpc/config.inc(206): OMVModuleMySql->startService()
      #4 [internal function]: OMVRpcServiceConfig->applyChanges(Array, Array)
      #5 /usr/share/php/openmediavault/rpcservice.inc(125): call_user_func_array(Array, Array)
      #6 /usr/share/php/openmediavault/rpcservice.inc(158): OMVRpcServiceAbstract->callMethod('applyChanges', Array, Array)
      #7 /usr/share/openmediavault/engined/rpc/config.inc(224): OMVRpcServiceAbstract->callMethodBg('applyChanges', Array, Array)
      #8 [internal function]: OMVRpcServiceConfig->applyChangesBg(Array, Array)
      #9 /usr/share/php/openmediavault/rpcservice.inc(125): call_user_func_array(Array, Array)
      #10 /usr/share/php/openmediavault/rpc.inc(79): OMVRpcServiceAbstract->callMethod('applyChangesBg', Array, Array)
      #11 /usr/sbin/omv-engined(500): OMVRpc::exec('Config', 'applyChangesBg', Array, Array, 1)
      #12 {main}[/color]


      After reboot, the gui is still there, even for MyWebSQL even though its not activated yet.
      The only change I made, is to leave the bind address in the gui as default. Previously I used my Raspberry's fixed IP.[color=rgb(0, 0, 0); font-family: tahoma, arial, verdana, sans-serif; font-size: 12px; line-height: normal; background-color: rgb(232, 232, 232)]Failed to execute command 'export LANG=C; invoke-rc.d 'mysql' start 2>&1': Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! invoke-rc.d: initscript mysql, action "start" failed[/color]
    • Same error as me in previous posts in this thread.
      I managed to change the location doing a symlink from /media/dataDisk/mysql to /var/lib/mysql

      Hope it helps!

      Enviat des del meu D2303 usant Tapatalk
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • Symlinks are like shortcuts in Windows. So if you keep default mysql location but you want the database data to be stored actually on another place, you can make a symlink between the two locations so /var/lib/mysql will "point" to /media/your-disk/your-sql-directory

      Google a bit about that, they're easy to set up and mysql works fine for me with this method.

      Greetings
      Guillem.

      Enviat des del meu D2303 usant Tapatalk
      DISCLAIMER: :!: I'm not a native English speaker, I'm sorry if I don't explain as good as you would want. :!:

      My NAS:
      Always the latest OMV Erasmus running on an AMD Sempron 3850 @1.3GHz with 4.9.0 Backports Kernel
      with 120GB Samsung SSD 850 EVO for OpenMediaVault & 2x500GB Primary Data HDD + 1TB Secondary HDD for Backup & 2TB USB 3.0 External HDD for offline backup

      Plugin list:
      Flash Memory, Locate, OMV-Extras.org, RSnapshot, Sensors, Syncthing, SMB/CIFS, SSH, USB Backup
      _____________________________________________________________________________________________________________________________

      The Schrödinger's code is that one which is going to work and it's full of bugs at the same time; until you test it, you won't be able to determine it.
    • Thanks for all the help.
      MySQL has now stopped working altogether. I've completely removed all packages (I hope) and want to restart the installation. It's still too much touch and go for me to trust it yet. I don't know if its the Raspberrypi setup I am using.

      I will investigate the symlinks. I understand the principle but I seldom manage to get it working. It tends to bite me in the long run. LOL! It's actually the lack of Knowledge is what's biting me.