MySql incremental backup of added rows only

Question

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed?

Background here is that I have an instance of home-assistant running on a raspberry pi. Historical data is being logged to a MySql database on the same pi. Keeping the database on the pi is the best solution for speed and responsiveness. As time goes the database grows larger which makes home-assistant run slower and eventually my pi will run out of space. The common solution for this seems to be to purge the database and remove all data older than x days, but this really does not appeal to me, I want to keep my data and a database should be the ideal place for that, right? I also have a NAS running MySql where space is not an issue, and I want to use that as a backup location, either in a mysql database or in some sort of dump file scheme.

How can I create a backup on the NAS where new data is added from the pi periodically, without deleting the old data on the nas that is no longer present in the pi database? I've been looking into combinations of mysqldump, diff and grep without success..

Answer 1

you need to mount your NAS drive into your RaspberryPI and give that location as incremental backup location to your backup script.

for mounting:

sudo apt-get install cifs-utils
mkdir /mnt/nas
mount -t cifs //192.168.x.x/data /mnt/nas -o username=username,password=password

Example backup script:

mysqlbackup --defaults-file=/home/dbadmin/my.cnf \
  --incremental --incremental-base=history:last_backup \
  --backup-dir=/mnt/nas/backup_dir \
  --backup-image=incremental_image1.bi 
   backup-to-image