Install and configure mariadb as SQL server (successor to mysql). Some advice on small-system backups.
2021-02-05 Ubuntu 20.10, Raspbian buster
Installationgraham:~ 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
Do not set a password for root on database server.
Root does have access from localhost, authenticated via domain socket,
but we will define an SQL user
adminas administrator separate from root.
- Assign and record password to database's
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.
Backupgraham:~ sudo mkdir -p /var/dumps graham:~ sudo chmod 700 /var/dumps graham:~ sudo crontab -e
mysqldumpshould be a cron job, synced with system backups.
- 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
- 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
Suppose the backed-up database dump has been retrieved from system backup
media and placed in
- easy: single command to re-load database
- inflexible: overwrites all databases