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-
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
adminaccount.
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# dump all databases for inclusion in system backup - daily 37 4 * * * mysqldump --all-databases --single-transaction --quick > /var/dumps/all.sql
- The
mysqldumpshould be a cron job, synced with system backups.
- 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.
- Pros:
-
- easy: single command to re-load database
- Cons:
-
- inflexible: overwrites all databases