setting-up MySQL database backup using holland and xtrabackup<\/a>. As a routine I have seen database backups being run under users other than “mysql” or “root” for such physical backups (looking at security aspect). For that, we need to add this backup-user to mysql group and grant the group read and exec permissions on MySQL datadir.<\/p>\n\n\n\nAdding backup user to mysql group:\nusermod -a -G mysql backup\n\nGranting group-read,execute permissions to backup user (on MySQL datadir):\nchmod g+rx \/mysql\/data -R\n<\/pre>\n\n\n\n\n\n\n\nThe backup running from “backup” user will be able to read the datadir contents and thus backup will succeed.<\/p>\n\n\n\n
Now the fact is by default, MySQL creates database and RAID directories with an access permission value of 0700. So, whenever a new database is created it will not have group-read,execute permissions on them. Thus our xtrabackup will fail reading those files\/folders, until we grant the group-read,execute permissions.
This fails our backup. The simple fix to this is manually granting read,execute permission on new database directory to the backup user.<\/p>\n\n\n\n
This is the background and now we’re going to answer it \ud83d\ude42<\/p>\n\n\n\n
What if MySQL create databases with the permission we need: user=all, group=read,execute, others=none (750)!<\/p>\n\n\n\n
We can do this using UMASK and UMASK_DIR system variables. UMASK defines the mode for files creation while UMASK_DIR is the base mode for directory creation. Yes, UMASK \/ UMASK_DIR actually are modes and not masks despite the name.<\/p>\n\n\n\n
We need 740 permissions for files and 750 for directories, so those will be our respective modes. Let’s see how we get that…<\/p>\n\n\n\n
Add following lines to MySQL startup script:<\/p>\n\n\n\n
[backup@mysql \/mysql\/data]# vi \/etc\/init.d\/mysqld\n\n\tUMASK=0640\n\texport UMASK\n\tUMASK_DIR=0750\n\texport UMASK_DIR\n<\/pre>\n\n\n\n(Here \/mysql\/data is MySQL datadir)<\/p>\n\n\n\n
Restart MySQL and create database & table.<\/p>\n\n\n\n
[backup@mysql \/mysql\/data]# \/etc\/init.d\/mysqld restart\nShutting down MySQL ..... SUCCESS!\nStarting MySQL .. SUCCESS!\n[backup@mysql \/mysql\/data]# mysql -e \"create database abc; use abc;create table a(id int);\"; ls -l | grep abc;ls -l abc\/;\ndrwxr-x---. 2 mysql mysql 4096 Jun 23 08:23 abc\ntotal 16\n-rw-r-----. 1 mysql mysql 8556 Jun 23 08:23 a.frm\n-rw-r-----. 1 mysql mysql 65 Jun 23 08:23 db.opt\n<\/pre>\n\n\n\nNote that under \/mysql\/data (MySQL datadir), we’ve proper group permissions provided as well as the datafiles are having group read permissions.<\/p>\n\n\n\n
Well this ends our fix here, we fixed directory & file permissions for MySQL datadir go grant group user rx (read,execute) access. Though how does this work is a little more than simply setting umask & umask_dir environment variables.<\/p>\n\n\n\n
Let’s get into slight detail:<\/p>\n\n\n\n
UMASK is the user-file creation mode when creating files.\nUMASK_DIR is the user-directory creation mode when creating directories.\n<\/em><\/pre>\n\n\n\nThe UMASK and UMASK_DIR variables, despite their names, are used as modes, not masks. Read this again, MODES not MASKS.<\/span><\/p>\n\n\n\nAnd “Modes” are the permissions given to users, groups and\/or the ‘other’ class to access files. That’s why we set the UMASK & UMASK directory values according to the desired permissions.<\/p>\n\n\n\n
MySQL Documentation on UMASK and file permissions:<\/p>\n\n\n\n
If UMASK is set, mysqld uses ($UMASK | 0600) as the mode for file creation, so that newly created files have a mode in the range from 0600 to 0666 (all values octal).\n<\/em><\/pre>\n\n\n\nCalculations:<\/p>\n\n\n\n
($UMASK | 0600)\n=> 740 | 600 \n=> 740\n<\/pre>\n\n\n\nSo that’s how we get our 740 permissions for MySQL datafiles.<\/p>\n\n\n\n
MySQL Documentation on UMASK_DIR and directory permissions:<\/p>\n\n\n\n
If UMASK_DIR is set, mysqld uses ($UMASK_DIR | 0700) as the base mode for directory creation, which then is AND-ed with ~(~$UMASK & 0666), so that newly created directories have a mode in the range from 0700 to 0777 (all values octal). The AND operation may remove read and write permissions from the directory mode, but not execute permissions.\n<\/em><\/pre>\n\n\n\nFrom above documentation quote, our equation resolves as follows:<\/p>\n\n\n\n
($UMASK_DIR | 0700) AND ~(~$UMASK & 0666)\nConsider, UMASK_DIR=750 and UMASK=740\n=> ( ~( ~0740 & 0666) & 0750 ) \n=> ( ~( 0037 & 0666) & 0750 ) \n=> ( ~( 0026 ) & 0750 ) \n=> ( 0751 & 0750 ) \n=> ( 0750 )\n<\/pre>\n\n\n\nThat is how the directory permissions are set.<\/p>\n\n\n\n
In the MySQL bug-report (see references) we have a feature request for introducing new variables, $MYSQL_FILE_MODE & $MYSQL_DIRECTORY_MODE and deprecating & discarding the confusing ones, $UMASK & $UMASK_DIR.<\/p>\n\n\n\n
Hope this helps.<\/p>\n\n\n\n
References:<\/p>\n\n\n\n
\n- https:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/environment-variables.html<\/li>\n\n\n\n
- https:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/file-permissions.html<\/li>\n\n\n\n
- https:\/\/bugs.mysql.com\/bug.php?id=35090<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"UMASK and UMASK_DIR are amongst few MySQL Environment variables which defines directory and file creation modes (file permissions). In this post we will understand using UMASK & UMASK_DIR for changing the database directory and file permissions from it’s default to what we need.\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[582,427,404,405],"class_list":{"0":"post-2240","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-datadir-permissions","9":"tag-mysql","10":"tag-umask","11":"tag-umask_dir"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2240","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2240"}],"version-history":[{"count":11,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2240\/revisions"}],"predecessor-version":[{"id":2878,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2240\/revisions\/2878"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2240"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2240"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2240"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}