Controlling the Database Locations
New to MariaDB/MySQl, but I've much Oracle experience. I've downloaded and installed the full MariaDB Windows MSI onto my Windows 7 64bit laptop. This put the System Database etc. under C:\Program Files\MariaDB 10.2\data\ . I gather all (user) databases now created will be subdirectories of that ..\data\ folder too.
Since it is good IT practice to always keep Data separate from Programs/Executables, I want to a) Move the MariaDB system datafiles (all of them) to a new (Data) folder location; and b) Ensure that new (user) databases are created as subfolders below a specified data folder. I.e. keep data and programs separate. And also have a better backup layout.
Questions: 1. How can I safely relocate the system database etc. to a new (Data) location and ensure MariaDB fully knows that? 2. How can I specify where new user databases should be created?
Observations 1. The Knowledgebase articles seem a bit light on the basic DBMS installation and configuration side - e.g. how to install so as to layout the system in particular ways. (Yes, I've noted the --datadir option. But default install prevents that approach.) They seem to assume a standard default installation with all data below ...\MariaDB xx.x\data\...
2. T'would be better if the standard installation process allowed the installer to choose where to put the system and user databases and made the necessary installation/configuration adjustments. Leave ...\Program Files\MariaDB 10.2\data\ as the default if you must - but it is bad practice to use \Progam Files\... for data.
Answer Answered by Ian Jones in this comment.
OK - I've found Solutions to my questions raised above... After digging into the Oracle MySQL documentation and "inferring" back to the MariaDB Windows Installation I've worked out and successfully tried the following:
Qn 1. "How can I safely relocate the system database etc. to a new (Data) location...?" You cannot. MariaDB does not seem to support MOVE DATABASE capabilities directly. (Maybe possible indirectly.) BUT a solution (for me) was to de-install MariaDB and re-install it via the Windows MSI installer but specifying the DATA location to where I wanted it. The WinMSI installer allows this but it's not intuitively obvious how to do it. The Win Installation guide (at https://mariadb.com/kb/en/library/installing-mariadb-msi-packages-on-windows/ ) says "To change the data directory location, select "Database instance" in the feature tree, and use the "Browse" button to point to another place." Well, that works once you've decoded its meaning.
2. "T'would be better if the standard installation process allowed the installer to choose where to put the system and user databases and made the necessary installation/configuration adjustments." Well - as above, it does do so. But in my view it does not make the method very obvious. Could be clearer.
3. And I'm still strongly of the view MariaDB should not default to putting the Databases under the /Program Files/ folder. That really is to be discouraged strongly. Windows has a /Progam Data/ folder which would be a better default. Or just encourage (even force) the installer to specify their own choice of location.
4. I've also picked up that there's a way to "point" MariaDB to where your individual databases are. By default they sit as sub-folders under the Data path. But I've not yet figured out exactly what the "point" method is and how to use it. Again, a case where the MariaDB documentation lacks clarity.