MySql incremental backup of only added lines

14
January 13, 2019, at 08:50 AM

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
READ ALSO
How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

I've to launch a website that I develop on my side with a SQL version 56

29
SUM quantity of products that belong to the same group (group_id)

SUM quantity of products that belong to the same group (group_id)

I have a product catalog, each product have 2 quantities (products_quantity and products_incoming_quantity), some products are grouped together under group_id

46
I can not recover data, even by dumping the Wordpress database

I can not recover data, even by dumping the Wordpress database

I recently made a backup of my Wordpress cms database, but now when I restore using the same sql commands in mysql, of success in the dump, however the data of the backup is not recovered, for example the posts, I have more than 100 and did not recover...

16
Difference between two DateTime values -MySql

Difference between two DateTime values -MySql

I am working on an application where I need to get the difference between two columns having data type as DateTime and if the difference is less than one hour, it should give 1 and if greater than 1 and less than 2, it should return 2 and so onI have been trying...

35