User Tools

Site Tools


servers:mariadb

Information

Prerequisites

Settings

  • Settings being done before installation is intentional
sudo -e '/etc/mysql/mariadb.conf.d/custom.cnf'
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
expire_logs_days = 7
max_binlog_size = 100M
skip-networking = On
table_definition_cache = 1400

Verify

sudo mysqladmin variables | egrep 'utf8|networking|socket'

Dependencies

sudo apt install 'mariadb-server'

Initial Setup

sudo systemctl enable 'mariadb' --now && sudo mysql_secure_installation

Database Repair and Optimization

Create MySQL User

sudo mysql
GRANT SELECT, INSERT ON *.* to 'maintenance'@'localhost' IDENTIFIED BY 'x';
FLUSH PRIVILEGES;
EXIT

Authentication File

sudo mkdir -p '/var/lib/mysql/auth' && sudo chown -R 'mysql':'mysql' '/var/lib/mysql/auth' && sudo -u 'mysql' -e '/var/lib/mysql/auth/maintenance' && sudo chmod '600' '/var/lib/mysql/auth/maintenance'
[mysqlcheck]
user=maintenance
password=x

Service

sudo -e '/etc/systemd/system/db-m.service'
[Service]
User=mysql
Group=mysql
Type=oneshot
ExecStart='/usr/bin/mysqlcheck' --defaults-extra-file='/var/lib/mysql/auth/maintenance' --auto-repair --optimize --all-databases --force
ExecStartPost='/bin/sync'

Timer

sudo -e '/etc/systemd/system/db-m.timer' && sudo systemctl daemon-reload && sudo systemctl enable 'db-m.timer' --now && sudo systemctl start 'db-m' && sudo systemctl status 'db-m' -l
[Unit]
Description=MariaDB Database Check, Repair, and Optimization
After=mariadb.service

[Timer]
OnCalendar=weekly
Persistent=true

[Install]
WantedBy=timers.target

Notable Commands

Show Users on Server

select User,Host from mysql.user;

Socket File

/var/run/mysqld/mysqld.sock
servers/mariadb.txt · Last modified: 2020/07/28 21:20 by Sean Rhone