[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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

    Einmal editiert, zuletzt von Lord Wektabyte ()

    • Offizieller Beitrag

    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 7.0.4-2 sandworm | 64 bit | 6.5 proxmox kernel

    plugins :: omvextrasorg 7.0 | kvm 7.0.10 | compose 7.1.2 | k8s 7.0-6 | cputemp 7.0 | mergerfs 7.0.3


    omv-extras.org plugins source code and issue tracker - github


    Please try ctrl-shift-R and read this before posting a question.

    Please put your OMV system details in your signature.
    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 :thumbup:

    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

    • Offizieller Beitrag

    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 7.0.4-2 sandworm | 64 bit | 6.5 proxmox kernel

    plugins :: omvextrasorg 7.0 | kvm 7.0.10 | compose 7.1.2 | k8s 7.0-6 | cputemp 7.0 | mergerfs 7.0.3


    omv-extras.org plugins source code and issue tracker - github


    Please try ctrl-shift-R and read this before posting a question.

    Please put your OMV system details in your signature.
    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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:


    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

    • Offizieller Beitrag

    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 7.0.4-2 sandworm | 64 bit | 6.5 proxmox kernel

    plugins :: omvextrasorg 7.0 | kvm 7.0.10 | compose 7.1.2 | k8s 7.0-6 | cputemp 7.0 | mergerfs 7.0.3


    omv-extras.org plugins source code and issue tracker - github


    Please try ctrl-shift-R and read this before posting a question.

    Please put your OMV system details in your signature.
    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

    • Offizieller Beitrag

    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 7.0.4-2 sandworm | 64 bit | 6.5 proxmox kernel

    plugins :: omvextrasorg 7.0 | kvm 7.0.10 | compose 7.1.2 | k8s 7.0-6 | cputemp 7.0 | mergerfs 7.0.3


    omv-extras.org plugins source code and issue tracker - github


    Please try ctrl-shift-R and read this before posting a question.

    Please put your OMV system details in your signature.
    Please don't PM for support... Too many PMs!

  • Not really. Only because of what I explained time ago:

    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

    • Offizieller Beitrag

    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 7.0.4-2 sandworm | 64 bit | 6.5 proxmox kernel

    plugins :: omvextrasorg 7.0 | kvm 7.0.10 | compose 7.1.2 | k8s 7.0-6 | cputemp 7.0 | mergerfs 7.0.3


    omv-extras.org plugins source code and issue tracker - github


    Please try ctrl-shift-R and read this before posting a question.

    Please put your OMV system details in your signature.
    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

  • That was a quick reply! I am busy right now re-installing mysql.
    Will let you know the outcome in the next 30 minutes. If it fails, I'll be off to bed. :)


    EDIT: Looks like my backup raspberry image was slightly outdated, although only 8 days old. I will update first.

  • I followed the instructions to move the files but when I activate MySQL and save and apply I get this error:



    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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
    _____________________________________________________________________________________________________________________________


    Zitat

    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.

Jetzt mitmachen!

Sie haben noch kein Benutzerkonto auf unserer Seite? Registrieren Sie sich kostenlos und nehmen Sie an unserer Community teil!