When dealing with failing storage hardware, servers often remount filesystems as read-only as a protective measure. This creates a critical challenge for database backups since traditional methods like:

mysqldump -u user -p dbname > backup.sql
scp backup.sql user@remote:/backups/

become impossible when you can't write temporary files locally.

The solution lies in Unix pipe redirection, which allows direct streaming between processes. Here's the basic syntax:

mysqldump -u user -p dbname | ssh user@remote "cat > /backups/backup.sql"

For production environments, consider these enhanced versions:

With compression:

mysqldump -u user -p dbname | gzip | ssh user@remote "cat > /backups/backup.sql.gz"

With progress monitoring:

mysqldump -u user -p dbname | pv | ssh user@remote "cat > /backups/backup.sql"

For unreliable networks, use these techniques:

# Resume failed transfers
mysqldump -u user -p dbname | ssh user@remote "dd of=/backups/backup.sql conv=notrunc"

Always prefer SSH keys over passwords:

# Generate key if needed
ssh-keygen -t rsa -b 4096
ssh-copy-id user@remote

When even local MySQL connections fail due to read-only filesystems:

ssh user@remote "mysqldump -h original_server -u user -p dbname" > local_backup.sql

For cron jobs, use this wrapper script:

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u user -p dbname | gzip | ssh user@remote "cat > /backups/db_${TIMESTAMP}.sql.gz"

---

When dealing with failing hardware where the filesystem becomes read-only, traditional database backup methods fail because they require local temporary storage. This creates a critical operational challenge for sysadmins who need to preserve data integrity during server failures.

The solution lies in Unix pipes and SSH's ability to handle stdin/stdout streams. Here's the fundamental command structure:

mysqldump -u [user] -p[password] [database] | gzip | ssh user@remotehost "cat > /path/to/backup.sql.gz"

Basic MySQL to Remote Server:

mysqldump --single-transaction -u dbuser -p dbname | \
ssh backupuser@backup.server.com "cat > /mnt/backups/dbname_$(date +%Y%m%d).sql"

Compressed Stream with Progress Monitoring:

mysqldump -u admin -p'password' --all-databases | \
pv -pterb | \
gzip -c | \
ssh user@backupserver "dd of=/backup/mysql/full_$(date +%F).sql.gz"

For production environments, consider these security enhancements:

• Use SSH keys instead of password authentication
• Implement network-level encryption with VPN tunnels
• Restrict SSH commands using authorized_keys forced commands

Broken Pipe Errors: These often indicate SSH connection timeouts. Solve by adding ServerAliveInterval to your SSH config:

Host *
    ServerAliveInterval 60
    TCPKeepAlive yes

Memory Constraints: For large databases, use these mysqldump flags:

--quick --single-transaction --skip-lock-tables

When direct piping isn't feasible, establish a tunnel first:

ssh -f -L 3307:localhost:3306 backupuser@backup.server.com -N
mysqldump -u remoteuser -p -h 127.0.0.1 -P 3307 dbname > localfile.sql