Hello, have just joined the forum to seek some advice on MySQL.
When performing a yum rpm installation, we end up with the default layout
For starters we are going to place the database files on a separate folder?
Is that a best practice? What you guys are doing for corporate/production deployments?
Also what about mysqld being under /usr/sbin and scripts under /usr/bin
As of Red Hat Enterprise Linux 7.0, /sbin has been moved to /usr/sbin, so the latter will include all system administration binariesâŠ
That said, what would be the best practice in corporate environments? Is there any âbest practicesâ/âhow-toâ document in Percona that would cover this?
Hello @sulimo,
The yum rpm method already works with best practices in mind with regards to where the binaries are installed. Many helper scripts expect these files in these locations so I suggest not moving them.
For the mysql data directory, this is normally /var/lib/mysql. Many companies will simply re-mount a different disk to this path. Others choose /opt/mysql and mount their RAID or other block device here, and then in /etc/my.cnf, change the parameter datadir=/opt/mysql
Makes perfect sense. See, Iâm coming from the âoracle worldâ where we separate RDBMS binaries from those of the OS, thus I was trying to understand what was the standard among the MySQL Community, for corporate installations of MySQL
As for /var/lib/mysql, Was there not an issue whereby the âlost+foundâ directory was confused as a database? Guess that has been fixed in current versions?
What else should be placed on a separate mount point ?
log-bin?
relay-log?
âŠ
Yes, the âlost+foundâ issue was fixed many years ago.
The only reason you would typically put the other logs onto separate disks would be if you are saturating the disk IO with normal read/write activity. Until then, Iâd keep it simple and just leave as defaults.
Yes, compared to the âoracle worldâ, normal things in MySQL will be 100x easier and simpler.
Other than performance reasons, should log files, traces, etc be placed on a separate mount? Else, do we not risk them filling up OS partitions and potentially impacting OS functionality?
Do they all end up under /var/log?
I know most can be enable/disabled, purged and capped. Yet, should any of those, if not all, have a separate mount point?
Other than space and/or performance issues, no real reason to move any of the log files. Separating them just to separate makes backups more difficult.
Relay-logs are purged automatically, as soon as they are not needed. You will only find these logs on replicas.
Only the actual âerror_logâ lives in /var/log/mysql/ by default. All other âlogsâ (binlog, relaylog, slow log, redo logs), are in /var/lib/mysql. Yes, you can run the risk of running out of root disk space, which is why most either re-mount a larger disk to /var/lib/mysql or move the $datadir (and all non-error_logs) to a different dir/mount-point altogether.
So would the recommendation be to have a separate mount pint for the âerror_logâ?
in other words, can (and should) we redirect /var/log/mysqld.log?
The idea is to prevent mysqld (Or other mysql processes) from running out of root disk space
To achieve that we would then:
a) re-mount /var/log/mysql, that would take care of $datadir and all non-error_logs
b) what about error_logs?
c) anything else worth considering?
a) I think you meant /var/lib/mysql, yes. remove whatever is in there, mount new disk over it. Iâm thinking it might be a better idea to make a new location anyways, like /opt/mysql. Letâs say your mount fails, /var/lib/mysql still exists but empty cause no mount. On an empty dir, mysql will attempt to re-init. This will look like major loss of data until you stop mysql, remove the re-init, and restore the mount. Yea, I think better, safer would be a brand new mount location.
b) You can edit /etc/my.cnf and move the location of the error log anywhere. We are talking a couple hundred megs with normal usage. Or just switch the error log to use systemctl/journald and rotation is managed for you.
c) not until you have production load. get another machine/VM and install PMM to monitor MySQL. This will be very important to have if you need us for support/perf tuning.