Question :
When I install MySQL via Yum, it automatically creates the mysql operating system user and group. Then mysqld executes as mysql (instead of as root), and all the files in the data directory are owned by mysql:mysql.
This is obviously correct security practice, I understand why this happens and don’t want to subvert it.
When I’m administering a MySQL system, I’ve just gotten in the habit of using sudo when I need to interact with a protected file (e.g., reading a binary log with mysqlbinlog, creating a backup tarball, etc).
Would it also work to add my OS user to the mysql group? Are there major downsides of this I’m not considering?
Answer :
When exporting information from MySQL for jobs using mysqlbinlog, using sudo is convenient for me too because it is usually to fix or troubleshoot something broken and time is a concern.
If you intend to re-import data extracted from MySQL then you need to work around the problem of permissions issues at some point. When creating dump files using the root account, the files created assume the permissions of the root account which MySQL can have difficulty accessing those files. (I also avoid sticky permissions as a workaround as it just gets messy when you don’t deal with it on a regular basis)
What I might suggest:
- add your OS account to the mysql group as it makes it convenient to view the files in the MySQL data folder if your duties include administrating/troubleshooting MySQL on that server.
- limit the OS accounts that have access to the MySQL data/log files from the OS to reduce the level of exposure to your data.
- developers do not need access to the OS (assuming DB servers are kept separate on the intranet and do not house the web server)
- do not create files/scripts that requires your account to persist if you change duties. (i.e. If I am managing one of your DB servers, I should not find automated scripts under your account.)
For regular administrative tasks, instead of using my local account, I tend to create separate OS ‘service’ accounts to interface with MySQL that are in the mysql group.
If you look at the running processes (ps -ef | grep mysql) you should see that MySQL is initialized by the root account (mysqld_safe) which (as you mentioned) runs the MySQL daemon (mysqld) under the mysql account.
Using this same approach, I tend to create scripts I can run with root privileges which calls and runs another file in a linux shell as a specific user with less permissions. This also makes it easier to add the same script to the root crontab.
I start by creating a service account called ‘mysqlbackup’ and in the home directory add a file .my.cnf
/—— file content
[client]
user = osbackup
password = 12345
——/
Create the administrative script to be run by ‘root’ which executes another file from a shell
- file: /var/lib/scripts/run-db-backup.sh (chmod 700 & chown root:root)
- file contains: su -l mysqlbackup -c /var/lib/scripts/db-backup.sh
Create your ‘real’ script to be run as the OS user ‘mysqlbackup‘
- file: /var/lib/scripts/db-backup.sql (chmod 700 & chown mysqlbackup:mysql)