MySQL installation layout

[sulimo]

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?

Cheers

[matthewb]

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

[sulimo]

Thanks Matt!

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?
…

Cheers,
J

[matthewb]

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.

[sulimo]

Thanks,

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?

Cheers

[matthewb]

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.
• log-bin will be purged older than binlog_expire_logs_seconds (defaults to 30 days)

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.

[sulimo]

Excellent!!!

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?

Thanks!

[matthewb]

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.

[sulimo]

@matthewb

I think you meant /var/lib/mysql

Yes, my apologies…

And thanks a ton for all the answers…

Cheers