On Wed, 13 Aug 2025 at 12:50, sacawulu via discuss <[email protected]> wrote: > > Hi, > > I am planning to setup a (three-node) galera cluster, holding three > different databases for three different mysql client servers. > > I am isolating the three databases with users like db1-user@client1 / > db2-user@client2 / db3-user@client3 and created grants accordingly. > > Additionally we want to prevent any DB from filling up the datadir, so I > created three lvm volumes db1/db2/db3 and mounted them under the > datadir. (correct permissions, selinux, etc, etc) > > Now, here is what AI (perplexity.ai) tells me abut doing this: > > > Manually creating a directory (e.g. with mkdir) directly under the MariaDB > > data directory is unsupported and can lead to inconsistencies or MariaDB > > not recognizing the database properly. > > > > MariaDB includes tooling like mariadb-install-db to initialize the data > > directory and its system tables correctly, which is part of setting up the > > database environment safely. > > However, as I've also tried all of this: it seems to work fine so far. > As far as I can tell, the only difference is the absence of db.opt in > the lvm-mounted db directories. > > This is how I've now created the mounts to be: > > > /dev/mapper/mysql-tmp 458M 30M 428M 7% /var/lib/mysqltmp > > /dev/mapper/mysql-mysql 458M 161M 297M 36% /var/lib/mysql > > /dev/mapper/mysql-db1 458M 30M 428M 7% /var/lib/mysql/db1 > > /dev/mapper/mysql-db2 458M 30M 428M 7% /var/lib/mysql/db2 > > /dev/mapper/mysql-db3 458M 30M 428M 7% /var/lib/mysql/db3 > > Some questions I have: > > Is mounting separate LVM volumes at individual database directories > under the MariaDB data directory a good or safe practice, or should I > avoid this to maintain compatibility with MariaDB tools and consistency?
Short answer: this is a terrible idea. Long answer: Things that will break: 1) DROP DATABASE will fail in all kinds of weird ways when it cannot remove the directory because the directory is a mountpoint 2) If you are only backing up the database directory and your redo log and master tablespace which are in the root of the datadir are not included at the exact same point in time (which it won't be if you are using snapshsots for backup purposes, then what you restore won't work because your ibdata1 and ib_logfile* won't be present, and the users and data dictionary that are in the mysql schema won't be included or at least won't be guaranteed to be consistent. On newer MariaDB versions mysql schema is mostly in Aria table format, which means you'll also need the Aria log and control files - which also won't be consistent. > What other or additional methods can help minimize the impact of a > problematic database or user from affecting the others? Separate VMs for separate users. mysqld_multi might help with some taskset and priority tweaks for each maraidbd process running, and depending on the file system you are using, you may get somewhere with ionice but I don't see how separate LVMs per schema will help you with the noisy neighbour isolation. > Are there other best practices I should consider in this Galera cluster > setup? If the problem you are trying to solve is noisy neighbour users, Galera will make it worse. > Background: I have three client nodes, each primarily using one Galera > node, with failover to the others via HAProxy. This setup has worked > well so far. What exact problem are you trying to solve? -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net Follow us: LinkedIn: https://www.linkedin.com/company/shatteredsilicon X: https://x.com/ssiliconbg _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
