0

I am not very experienced with databases so I hope my question makes sense.

I am setting up a MySQL/MariaDB instance for Nextcloud on my Ubuntu server and the data will be stored on a ZFS pool.

Several guides/blogs mentions that its best practice to separate data and log files to separate datasets with different properties. So in I add the following to my my.cnf configuration file so that logs and data are stored in different directories and not directly under /var/lib/mysql which is the default.

[mysqld]
 
datadir = /var/lib/mysql/data 
innodb_log_group_home_dir = /var/lib/mysql/log 
innodb_data_home_dir = /var/lib/mysql/data 
slow_query_log_file = /var/lib/mysql/log/slow.log 
log_error = /var/lib/mysql/log/error.log 
aria-log-dir-path = /var/lib/mysql/log

In the /var/lib/mysql directory I see that the data/ and and log/ directory has been created and contains some files.

drwxr-xr-x 7 mysql mysql  9 Apr 18 08:03 ./ 
drwxr-xr-x 8 root  root   8 Apr  6 00:10 ../ 
drwxr-xr-x 2 mysql mysql  5 Apr 18 08:03 data/ 
drwxr-xr-x 2 mysql mysql  6 Apr 18 08:03 log/ 
-rw-rw---- 1 mysql mysql  0 Apr 18 08:03 multi-master.info 
drwx------ 2 mysql mysql 90 Apr 18 08:03 mysql/ 
-rw-r--r-- 1 mysql mysql 15 Apr 18 08:03 mysql_upgrade_info 
drwx------ 2 mysql mysql  3 Apr 18 08:03 nextcloud/ 
drwx------ 2 mysql mysql  3 Apr 18 08:03 performance_schema/

However, there are still some additional files and folders (I guess one for each database, they only contain a db.opt file) in the /var/lib/mysql directory.

Are these files/folders not considered neither data nor logs and and if I were to backup/recreate the dababase(s) on a different system would I need to copy anything other than the data/ directory?

norsemanGrey
  • 345
  • 4
  • 10
  • All data of databases that can be accessed by MySQL (or Mariadb), should be in the directory which you can query using this: `show variables like '%datadir%';` – Luuk Apr 26 '22 at 12:21
  • So the additional directories and files can safely be ignored? – norsemanGrey Apr 26 '22 at 12:25
  • [How to change MySQL data directory?](https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory) shows how to change the datadir , but you should make a backup first (and check if the backup is OK) – Luuk Apr 26 '22 at 12:33
  • In your post you say: "directory has been created and contains some files" This is a pretty vague statement, the output of `sudo find /var/lib/mysql -type d` seems more informative – Luuk Apr 26 '22 at 12:34
  • I am not changing the data directory on an existing installation. I am initially setting a different data directory when setting up the MySQL instance, however, as mentioned, some directories and files are still created in the /var/lib/mysql directory (in addition to log/ and data/). I'm not sure what you mean by "vague". You can basically see the directory structure in my post. – norsemanGrey Apr 26 '22 at 12:37
  • I meant to say that there should be a directory `/var/lib/mysql/data/mysql` which holds the info that is stored in your mysql database, and a direcory `/var/lib/mysql/data/nextcloud` which holds the info that is stored in your nextcloud database – Luuk Apr 26 '22 at 12:47
  • Ok. Yes, those directories are created. So not sure why they are created in under the `/var/lib/mysql` directory as well. – norsemanGrey Apr 26 '22 at 12:53
  • look at the dates they where created, April18 is probably the data you install MySQL – Luuk Apr 26 '22 at 13:05
  • Yes everything is created when MySQL is set up. – norsemanGrey Apr 26 '22 at 13:48
  • Then it should be safe to ignore... (Which does NOT mean that I take responsibility when you loose data.... , so backup, and check) – Luuk Apr 26 '22 at 14:01

0 Answers0