How do you encrypt data while performing a mysqldump?

Question

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

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 — RolandoMySQLDBA, Sep 16 '11 at 16:22
We use MySQLBackupFTP http://mysqlbackupftp.com it performs backups via mysqldump and has the encryption option. — , Jul 10 '18 at 14:34

score 6 · Answer 1 · answered Sep 16 '11 at 16:16

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

answered Sep 16 '11 at 16:16

IT2

93
5

Sorry, I didn't see that you are using Windows. The example above is for Linux... – IT2 Sep 16 '11 at 16:22

score 3 · Accepted Answer · answered Sep 16 '11 at 15:03

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*

answered Sep 16 '11 at 15:03

StanleyJohns

5,928
2
19
44

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. – StanleyJohns Sep 19 '11 at 13:28
how to decrypt using cipher? – learner Sep 20 '11 at 04:54
@learner to decrypt do: cipher /d /a dbNameBackup.sql – StanleyJohns Sep 20 '11 at 06:23

score 1 · Answer 3 · answered Dec 28 '15 at 19:24

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.

score 0 · Answer 4 · edited Feb 07 '17 at 19:34

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

edited Feb 07 '17 at 19:34

Andriy M

20,973
6
52
93

answered Feb 07 '17 at 18:38

prabhu

1

How do you encrypt data while performing a mysqldump?

4 Answers4