When working with tightly secured production environments, traditional tools like mysqldump might not be available. Here's a common scenario:

• No direct filesystem access to database files
• Missing mysqldump binary on the web server
• Restricted execution permissions
• Database running on separate infrastructure

The most reliable approach creates a secure tunnel through your web server:

ssh -f -L 3306:db.internal.example.com:3306 user@web.example.com -N

This forwards your local port 3306 to the remote database via the web server. Now you can use local MySQL clients.

MySQL often verifies the origin host in authentication. To handle this:

# Option 1: Hosts file entry
127.0.0.1 db.internal.example.com

# Then connect using:
mysqldump -h db.internal.example.com -u dbuser -p dbname

Alternative without hosts file modification:

mysql --protocol=TCP -h 127.0.0.1 -P 3306 \
  --default-auth=mysql_native_password \
  -u dbuser -p dbname \
  -e "SELECT * FROM table" > dump.sql

For environments where even local mysqldump isn't available:

# Generate schema
mysql -h dbhost -u user -p -e "SHOW CREATE DATABASE dbname" > dump.sql
mysql -h dbhost -u user -p dbname -e "SHOW TABLES" | \
  grep -v Tables_in | \
  while read table; do 
    mysql -h dbhost -u user -p dbname -e "SHOW CREATE TABLE $table" >> dump.sql
  done

# Dump data
mysql -h dbhost -u user -p dbname -e "SELECT * FROM table" >> dump.sql

Create a cron job combining these techniques:

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
ssh -f -L 3306:db.internal.example.com:3306 user@web.example.com -N
sleep 2
mysqldump -h db.internal.example.com -u backup_user -p"$DB_PASS" dbname \
  | gzip > /backups/db_${TIMESTAMP}.sql.gz

Remember to store credentials securely and test your backup restoration process.

---

Many developers face situations where they need to back up a remote MySQL database but don't have direct access to mysqldump. This often happens on locked-down shared hosting environments where:

• The mysqldump utility isn't installed
• Users can't execute binaries in their home directories
• The database server is separate from the web server

The most reliable workaround is creating an SSH tunnel to forward the remote MySQL port to your local machine:

ssh -f -L 3306:mysql.example.com:3306 user@ssh.example.com -N

This command:

• Forwards local port 3306 to the remote MySQL server
• Uses -f to run in background
• Uses -N to not execute remote commands

When trying to connect, you might encounter authentication issues:

mysqldump -P 3306 -h localhost -u user -p db
# Error: Access denied for user 'user'@'localhost'

This happens because MySQL sees the connection as coming from 'localhost' rather than the original host.

One solution is to edit your local hosts file:

127.0.0.1 mysql.example.com

Then connect using:

mysqldump -P 3306 -h mysql.example.com -u user -p db

If you can't use mysqldump at all, you can generate a dump using just the MySQL client:

mysql -h mysql.example.com -u user -p db -e "SHOW TABLES" | \
grep -v Tables_in | \
while read table; do 
  echo "Dumping $table"
  mysql -h mysql.example.com -u user -p db -e "SHOW CREATE TABLE $table"
  mysql -h mysql.example.com -u user -p db -e "SELECT * FROM $table"
done > dump.sql

For a completely self-contained solution, pipe the output directly through SSH:

ssh user@ssh.example.com "mysql -h mysql.example.com -u user -pdbpass db -e 'SELECT * FROM table'" > local_dump.sql

For scheduled backups, create a script like:

#!/bin/bash
DATE=$(date +%Y%m%d)
ssh -f -L 3306:mysql.example.com:3306 user@ssh.example.com -N
mysqldump -P 3306 -h mysql.example.com -u user -p db > backup_$DATE.sql
kill $(ps aux | grep "ssh -f -L 3306" | awk '{print $2}')