MySQL's mysqldump is primarily designed for full database backups, but with some clever techniques, we can achieve incremental-like behavior. The key is combining mysqldump with MySQL's binary logging system and timestamp-based filtering.

First, enable binary logging in your my.cnf/my.ini:

[mysqld]
log-bin=mysql-bin
server-id=1

For a differential backup, note the current binary log position after your last full backup:

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 107      |              |                  |
+------------------+----------+--------------+------------------+

Then create a script to backup new changes:

#!/bin/bash
mysql -e "FLUSH LOGS"
mysqlbinlog --start-position=107 /var/lib/mysql/mysql-bin.000003 > incremental.sql

For tables with timestamp columns:

mysqldump -u root -p --where="last_modified > '2023-10-01 00:00:00'" db_name table_name > incremental.sql

For serious production environments, consider:

xtrabackup --backup --target-dir=/backups/base --incremental-basedir=/backups/base

Here's a complete bash script for scheduled backups:

#!/bin/bash
DATE=$(date +%Y-%m-%d)
FULL_DIR="/backups/full"
INC_DIR="/backups/incremental"

# Full backup on Sundays
if [ $(date +%u) -eq 7 ]; then
    mysqldump --all-databases --single-transaction > $FULL_DIR/full_$DATE.sql
    mysql -e "PURGE BINARY LOGS BEFORE NOW()"
else
    LAST_LOG=$(ls -t /var/lib/mysql/mysql-bin.0* | head -1)
    mysqlbinlog $LAST_LOG > $INC_DIR/inc_$(date +%Y%m%d_%H%M%S).sql
fi

To restore from incremental backups:

mysql < full_backup.sql
mysqlbinlog incremental_backup.sql | mysql

Many developers assume mysqldump can only perform full backups, but with the right techniques, we can achieve incremental-like functionality. While specialized tools like Percona XtraBackup exist, mysqldump remains widely accessible for basic incremental backup needs.

This method leverages the --where clause with timestamp columns:

mysqldump -u user -p database table --where="last_modified > '2023-11-15 00:00:00'" > diff_backup.sql

For true incremental backups, combine mysqldump with MySQL's binary logging:

# First create a full backup
mysqldump --master-data=2 --single-transaction --flush-logs -u root -p dbname > full_backup.sql

# Later capture incremental changes
mysqlbinlog --start-datetime="2023-11-15 14:00:00" mysql-bin.000123 > incr_backup.sql

Here's a Bash script that implements timestamp tracking:

#!/bin/bash
LAST_BACKUP=$(cat /backups/last_timestamp.txt || echo "1970-01-01 00:00:00")
NOW=$(date +"%Y-%m-%d %H:%M:%S")

mysqldump -u backup_user -p'password' mydb --where="updated_at > '$LAST_BACKUP'" \
  | gzip > /backups/diff_$(date +%Y%m%d_%H%M%S).sql.gz

echo $NOW > /backups/last_timestamp.txt

• Always test backup restoration procedures
• For large databases, consider table-by-table differentials
• Monitor binary log disk usage when enabled
• Implement proper backup rotation policies

For minimal downtime during full backups:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Record position for later incremental backups
-- Run mysqldump in another session
UNLOCK TABLES;