Difference between revisions of "MariaDB (10.3)"
| Line 1: | Line 1: | ||
__TOC__ | __TOC__ | ||
| − | It may have taken awhile but here we are. | + | It may have taken awhile but here we are. In my [[MySQL 5.5 Guide|MySQL_(5.5)]] 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. | Buster at least includes mysqltuner, which is a useful guide program to watch for issues. | ||
| Line 58: | Line 58: | ||
| + | |||
| + | == 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: | ||
| + | |||
| + | # In MySQL: | ||
| + | # GRANT RELOAD ON *.* TO qcmaint@localhost IDENTIFIED BY 'crazylongpass'; | ||
| + | # In a script file, run | ||
| + | # /usr/bin/mysql -u qcmaint -p"crazylongpass" -e 'FLUSH QUERY CACHE;' | ||
| + | # via cron, two to three times an hour. My /etc/cron.d/mysql: | ||
| + | # 4,24,44 * * * * root /root/flushqc.sh | ||
| + | # 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 | ||
{{Bottom Buster}} | {{Bottom Buster}} | ||
Revision as of 09:26, 13 July 2020
It may have taken awhile but here we are. In my MySQL_(5.5) 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
/etc/mysql/mariadb.conf.d/99-server.cnf
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:
- In MySQL:
- GRANT RELOAD ON *.* TO qcmaint@localhost IDENTIFIED BY 'crazylongpass';
- In a script file, run
- /usr/bin/mysql -u qcmaint -p"crazylongpass" -e 'FLUSH QUERY CACHE;'
- via cron, two to three times an hour. My /etc/cron.d/mysql:
- 4,24,44 * * * * root /root/flushqc.sh
- 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