Graham Eddy

mariadb server

Install and configure mariadb as SQL server (successor to mysql). Some advice on small-system backups.

2021-02-05 Ubuntu 20.10, Raspbian buster


Installation

graham:~ sudo apt update graham:~ sudo apt install mariadb-server graham:~ sudo mysql_secure_installation Current password: Set password? N Remove anonymous user? Y Disallow remote root? Y Remove test database? Y Reload privileges now? Y graham:~ sudo mariadb > GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' \ IDENTIFIED BY 'password' WITH GRANT OPTION; > FLUSH PRIVILEGES; > EXIT

Backup for small databases

This is a simple scheme that assumes file system backups are reliably and regularly performed, and that the database dump will be picked up as part of that.

Backup

graham:~ sudo mkdir -p /var/dumps graham:~ sudo chmod 700 /var/dumps graham:~ sudo crontab -e
temp filenew file or append to end
# dump all databases for inclusion in system backup - daily
37 4 * * * mysqldump --all-databases --single-transaction --quick > /var/dumps/all.sql
Pros:
  • flexible: SQL text so human-readable and editable
  • everything together in one file for easy storage
  • portable: can be imported onto any host running mariadb or mysql
Cons:
  • locks all databases until finished
  • non-incremental and text-based so grows very large very quickly (might exhaust storage capacity!)
  • harder to separate concerns of different databases
  • can only be loaded back into original server

Restore

Suppose the backed-up database dump has been retrieved from system backup media and placed in /var/dump/old_all.sql.

graham:~ sudo mariadb < /var/dump/old_all.sql
Pros:
  • easy: single command to re-load database
Cons:
  • inflexible: overwrites all databases