How to change a default MySQL directory /var/lib/mysql to another on Plesk for Linux server

Follow

Comments

7 comments

  • Avatar
    oraHow

    Great article. After changing data directory, is it necessary to check used MySQL ports before restart services? 

  • Avatar
    Pavel Mikhaylov

    Hi oraHow,

    Ports configuration does not depend on the data directory, moreover, if not specified otherwise, MySQL always uses the default one, 3306, so there is no need to check that.

  • Avatar
    Marty Peet

    Hey oraHOw, I have tried the configuration changes and got a Zend error in Plesk that it couldn't repair automatically.

    I'm running Onyx on an AWS Lightsail instance. I changed the datadir entry back to the default and got it back running, pretty sure the file path is correct... In step 7, the appamor configuration change, my usr.sbin.mysqld file has quite a few lines of entries HOWEVER none of the lines you have defined below are present?

    # rm /etc/apparmor.d/disable/usr.sbin.mysqld
    # apparmor_parser -a /etc/apparmor.d/usr.sbin.mysqld
    # aa-disable /etc/apparmor.d/usr.sbin.mysqld

    I am running Ubuntu 16.04 64 bit, Plesk Onyx 17.8.11 update #28

    Thanks for any help you can give me

  • Avatar
    Alexandr Redikultsev

    Hi @Marty Peet.

    Those lines should not be in /etc/apparmor.d/usr.sbin.mysqld, only the following lines should exist in the file:

    /usr/sbin/mysqld {
    }

    Other three lines are just commands you have to execute in order to adjust AppArmor.

    If this will not work for you, check MySQL logs at  /var/log/mysql/ or similar for some error messages, and also check /var/log/syslog.

  • Avatar
    Marty Peet

    Hey Aleksandr, thanks for getting back to me.

    This is a CLEAN installation... I haven't changed/adjusted much of anything. The usr.sbin.mysqld file has a number of sections within it including Allow system resource access, Allow network access, Allow config access, Allow execution of server binary, Allow plugin access etc. So, just to clarify, I should DELETE/COMMENT out all of the above and have just the /usr/sbin/mysqld{} remaining in that file, and restart the mysql service

    Thanks for your time and patience

  • Avatar
    Marty Peet

    Hey Aleksandr, did as you outlined and deleted all entries to apparmor in /usr/sbin/mysqld. I restarted mysql and it failed. Had a look in the /var/log/mysql/error.log and there are a number of DENIED messages related to apparmor e.g.

    apparmor="DENIED" operation="open" pointing to profile="/usr/sbin/mysqld",

    It IS pointing to my new datadir listing but it appears that apparmor was/is still being triggered.

    I had created a backup of the usr.sbin.mysqld file (usr.sbin.mysqld.backup) and found a posting online that said if it was in the same directory it would still be processed. Moved that file to a different directory, restarted mysql, it failed

    I restarted the apparmor service service apparmor restart and then attempted to restart mysql. It did NOT restart though I now have different errors and only two of them repeated with different PIDs

    apparmor="DENIED" operation="open" profile="usr/sbin/mysqld" name = /etc/ld.so.cache AND name = /lib/x86_64-linux-gnu/libaio.so.1.0.1

    Did some more research and found the following to disable apparmor for mysql - (could have used this instead of step 7?)

    sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
    sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

    Restarted mysql, SUCCESS.

    Created a new database, checked it's location and....still in the OLD database directory - checked that I had followed your other directions (datadir in psaconf), correct.Checked mysql log file, NO new apparmor denied errors, a listing that has apparmor status, profile_remove /usr/sbin/mysqld

    Do you have any other thoughts that may help me trouble-shoot this process? I need to get my databases to a different location after experiencing unrecoverable errors with a Lightsail instance crashing previously when all databases/webfiles were stored on the instances drive

    Thanks for your time and patience

  • Avatar
    Marty Peet

    Hey Aleksandr, sorted.. the final thing was ownership of the new directory - had to be mysql:mysql, all appears to be working now. Thanks for your assistance and the posting

Please sign in to leave a comment.

Have more questions? Submit a request