5

My OS is windows 7.

  • How can I encrypt data while performing a mysqldump?
  • From the command prompt, can I take a mysqldump with encrypted varchar and texts values?

Please help me.

Thanks

2
  • This is a shout out to everyone answering this question to supply a Windows 7 based answer. If you have Windows-based encryption tools, please readjust your answers where possible Sep 16 '11 at 16:22
  • 1
    We use MySQLBackupFTP mysqlbackupftp.com it performs backups via mysqldump and has the encryption option.
    – user89558
    Jul 10 '18 at 14:34
3

To encrypt your mysqldump data in windows try the following command at the command prompt:

 mysqldump *databaseName* > *dbNameBackup.sql* && cipher /e /a *dbNameBackup.sql*
4
  • Excuse me for executing this command any other application I need to install? Also I need to encrypt only varchar and text fields. By using this command can I do like that? Thanks.
    – learner
    Sep 19 '11 at 12:40
  • @learner You do not need to install any extra application for this. Cipher comes with Windows. This will encrypt the whole file. To encrypt only certain columns, you will have to do it before backing up. Sep 19 '11 at 13:28
  • how to decrypt using cipher?
    – learner
    Sep 20 '11 at 4:54
  • @learner to decrypt do: cipher /d /a dbNameBackup.sql Sep 20 '11 at 6:23
6

You can use ccrypt. Example:

  • Creating the key file:

    echo 'mySecretKey123' > ~/.backup.key
    chmod 600 ~/.backup.key
    
  • Encrypting the backup:

    mysqldump databasename | ccrypt -k ~/.backup.key | bzip -c > ~/backup-mysql.sql.bz2.cpt
    
  • To decrypt the backup:

    cat ~/backup-mysql.sql.bz2.cpt | ccat -k ~/.backup.key | bunzip2 -c | less
    
1
  • Sorry, I didn't see that you are using Windows. The example above is for Linux...
    – IT2
    Sep 16 '11 at 16:22
1

Using a password inside the script is a really bad idea as this can be seen in ps aux and read out by every system user.

I would suggest you to look into mysqldump-secure. This is a shell script that does openssl encryption based on public-private key encryption and is a lot more performant than gpg. Additionally it will compress your databases and writes a log file of success/failure. It also ships with a nagios plugin that will let you know any problems that happened during the dump.

0

MySQL file encryption & decryption command line:

  • encryption:

    openssl enc -aes-256-cbc -in /path/example.sql -out /path/example.file
    
  • decryption:

    openssl enc -aes-256-cbc -d -in /path/example.file > /path/example.sql
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.