Difference between revisions of "MariaDB (10.3)"

From Hexwiki
Jump to navigation Jump to search
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:

  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