MariaDB (10.3)

It may have taken awhile but here we are. In my MySQL 5.5 Guide I go over whether the Query Cache is relevant for you, and how to make use of compression in the barracuda file format. These bits are still useful.

Buster at least includes mysqltuner, which is a useful guide program to watch for issues.

After installing mariadb be sure to

systemctl stop mariadb
chown mysql:mysql /innodb/
chmod 0750 /innodb
usermod -a -G hugepager mysql

And either

mv /var/lib/mysql/ibdata1 /innodb/

or remove it entirely if you are on a virgin installation.

Replace /innodb/ or ignore as necessary.

If you are changing the size of the ib_logfiles, you will want to first remove them:

rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1

cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/99-server.cnf

To make your override file.

max_allowed_packet

Add

max_allowed_packet        = 32M 

Or whatever you need to all relevant files. If you end up running extremely large queries you may need to increase this. I mostly set it as a note to increase it later, as necessary. 32MB is already the default in some instances.

To [client] in /etc/mysql/mariadb.conf.d/50-client.cnf

To [mysql] and [mysqldump] in /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

The legacy mysql configuration files look like so:

/etc/mysql/conf.d/mysqldump.cnf

[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

/etc/mysql/conf.d/mysqldump.cnf

[mysql]
max_allowed_packet        = 32M

Final steps

If you are using the Query Cache, then you will need to setup a cron to flush it regularly, else you are going to see the problems people complain about a lot sooner. First, make an appropriate user:

1. In MySQL:
2. GRANT RELOAD ON *.* TO qcmaint@localhost IDENTIFIED BY 'crazylongpass';
3. In a script file, run
4. /usr/bin/mysql -u qcmaint -p"crazylongpass" -e 'FLUSH QUERY CACHE;'
5. via cron, two to three times an hour. My /etc/cron.d/mysql:
6. 4,24,44 * * * * root /root/flushqc.sh
7. My experience is that there is a spot between running it every half hour and every 15 minutes where flushing no longer helps - you should probably stop using the query cache entirely if this breaks down for you.

/root/.my.cnf

No point in hiding this, naturally. Since root can always set the MySQL password, it can be handy just to stick a giant uncrackable mess in /root/.my.cnf rather than have something crackable:

[client]
user=root
password=yourrandompasshere
socket=/run/mysqld/mysqld.sock

Debian Buster

Initial: Designing a Server - IPMI Installation - Second Steps

---

Core: Network - OpenSSH - Packages - sysctl.conf - Iptables - Security - User Management

---

Stack: Unbound - MariaDB - Nginx - PHP (7)

---

Mail

Mail Setup - Mail Tables - OpenDKIM - Mail and DNS - Spamassassin - ClamAV - Postfix - Dovecot - Reputation Management

---

Logs and Security: RKHunter - Logging - Logcheck - Backups - AppArmor