A SQL dump of a DB is just a text file that includes SQL code to re-create your DB. So you could just manually adjust the SQL code (within the dump file) so it will be compliant with MySQL 5.6/MariaDB 10.1. If this is likely to occur again the future, then you could write a script to do the update for you (so you won't need to manually update the file in the future).

I can't help you much with the required SQL changes themselves because I'm really only a fairly novice SQL user. Although if you are unsure, please post and I'm happy to help as best I can.

I am pretty handy with bash though, so once you have the required changes worked out, I'm sure I could assist you to create a script (or at least provide some commands) to do the updates in the SQL dump file for you.

If you go the MySQL route, please let me know if you have any issues and I'll try to help out as best I can.

Yes I concur, editing 1.6GB of SQL by hand sounds like a bad idea at best! (Not to mention painful...)

Re your MySQL install, I'm glad to hear that it's (mostly) working for you. I'm no MySQL/SQL expert either, but I am very handy with Debian and my googlefu is pretty strong, so please feel free to ask about anything and I'll do my best to at least point you in the right direction.

Good luck with it.

When you use the CLI, any commands that you run, run as the user you are logged in as. So if you're logged in as root, then all commands will run as root. 3 options to deal with that spring to mind:

• use 'su'
• install (and then use) 'sudo'
• continue to use root, then adjust permissions afterwards (manually, via a umask, or via a cron job)

I'll elaborate a little...

Use 'su'

The reason why 'su' doesn't work by default with the webserver user (www-data) is because by default www-data does not have a shell. But you can work around that pretty easily, by explicitly granting it a shell with the '-s' switch; like this:

su - www-data -s /bin/sh

Now you are in a session running as the webserver user. To exit back to the root user use the 'exit' command. Alternatively, if you only have a single command to run, you can do that in a one liner using the '-c' switch, like this:

su - www-data -s /bin/sh -c "command to run"

(Obviously replace "command to run" with the actual command you wish to run)

Install 'sudo'

Installing software from the Debian (or TurnKey) repos is super easy. Install sudo like this:

apt update
apt install sudo

Then you can sudo as the www-data user, like this:

sudo -u www-data command to run

(Obviously replace "command to run" with the actual command you wish to run). It's also worth noting that when you use sudo, unlike su, there is no option to run within a session, so you'll need to prefix every command with 'sudo -u www-data'.

Continue to use root, then adjust permissions afterwards

This is probably the most fiddly option but should work ok. If you are happy working from the commandline, then it supports the option to have your site quite locked down. Essentially it's a case of keep doing what you're doing, but whenever you run commands from the commandline you'll need to correct the permissions. The easy way to do that (and ensure that all the Magento functionality from within the WebUI always works) is grant webserver ownership to the whole Magento directory. E.g. like this:

chown -R www-data:www-data /var/www/magento

However, the "best practice" way to do that, is only grant write permissions to places that the webserver actually needs to write to. E.g. '/var/www/magento/var/cache' (and likely some other directories, e.g. for files uploaded via the Magento webUI). If you go that path, then even if a bad guy manages to break into your site, the only files they can change are files that the webserver user has write access to (i.e. the cache, etc). Whether that approach might make sense on your system will depend on who is using it and via which interface. Granting ownership for just specific locations is essentially the same as above, but only on the specific directory(ies?) you want to change ownership of. E.g.:

chown -R www-data:www-data /var/www/magento/var/cache /var/www/magento/another-dir

Bonus points - automated permission setting/fixing

As hinted above, you could also automate this last option. You could do that either via a cron job that runs every minute (i.e. a task that runs regularly and 'fixes' the permissions, probably as per above chown commands). Or alternatively, you could set a default group owner for the Magento files. As these defaults are recursive, setting that in the Magento doc root will ensure that all files/directories have the www-data group ownership.

It's worth noting that if you went this path, you will need to ensure that the directories and files have the required access via the group permissions (rather than the user level permissions). You can even set up ACL (access control lists) if you really want to dig into it, but it's likely overkill for this task...

I won't go into too much detail, but essentially you set an appropriate umask to ensure that pre-defined group permissions are set on files and directories. You'll need to ensure that the files/directories have the appropriate group permissions (by default any work you do as root, will belong to root user, so you need to ensure appropriate www-data group access). I.e. that the directories have '77x' permissions (e.g. '775') and files have '66x' permissions (e.g. '664'). If you're unfamiliar with 'nix octal permissions, read=4, write=2 and execute=1; the digits are user, group & everyone/global. FWIW the execute permission on files is exactly what you would expect, but on directories means something different, often referred to as "search permission". So to enter a directory, the user must have 'execute' permission of the directory.

TBH, I haven't played with this last method much, but if you're interested and need more pointers, please post.

Extra info

Also, you may already be aware of this, but it's probably well worth noting that TurnKey is Debian under the hood (v15.x = Debian 9/Stretch). So any info regarding Debian Stretch will likely apply to TurnKey v15.x too. As Ubuntu is also a Debian derivative, then often Ubuntu instructions will also apply. But please be aware that Ubuntu is NOT binary compatible with Debian (TurnKey is). So it is NOT recommend to add (and install from) Ubuntu specific repositories, such as Ubuntu PPAs. You might get away with it for a while, but eventually it will bite you! :)

Hope that helps.

Great solution, I like it. Thanks for posting back with that. Perhaps it's even worth us considering including something similar by default? I'm not sure how many will use it, but worthy of consideration.

I'm not sure that we'll use them all, nor when we might add them, but thanks so much for sharing. I really appreciate it.