{"id":697,"date":"2016-09-06T09:26:41","date_gmt":"2016-09-06T03:56:41","guid":{"rendered":"https:\/\/pheonixsolutions.com\/blog\/?p=697"},"modified":"2026-09-09T16:42:42","modified_gmt":"2026-09-09T11:12:42","slug":"script-to-take-tablewise-mysqldump-and-store-in-s3","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/script-to-take-tablewise-mysqldump-and-store-in-s3\/","title":{"rendered":"Script to take tablewise mysqldump and store in s3"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This guide covers a script to take table-wise mysqldump backups, encrypt them, and store them in S3. The original version of this script has several real bugs \u2014 including a serious one: the encryption key is derived from the current date, making it guessable rather than secure. This guide walks through a corrected version.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Implementation<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">I. Prerequisites<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>s3cmd<\/code> installed and configured on the backup machine<\/li>\n\n\n\n<li>MySQL root password<\/li>\n\n\n\n<li>An S3 bucket with versioning enabled<\/li>\n\n\n\n<li>AWS access key and secret key with write privileges to that bucket<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">II. How the Script Works<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lists every MySQL database, then every table in each<\/li>\n\n\n\n<li>Dumps each table individually, compresses it, encrypts it<\/li>\n\n\n\n<li>Uploads the encrypted file to S3, deletes the local copy<\/li>\n\n\n\n<li>Removes local backups older than 5 days and emails a summary<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-scaled.png\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"632\" src=\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-1024x632.png\" alt=\"\" class=\"wp-image-11579\" srcset=\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-1024x632.png 1024w, https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-300x185.png 300w, https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-768x474.png 768w, https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-1536x949.png 1536w, https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/mysqldump_s3_backup_architecture-2048x1265.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">III. Real Issues in the Original Script<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Passwords passed incorrectly.<\/strong> <code>mysqldump -u $DB_USER $DB_PASS<\/code> treats the password as an extra database name instead of authenticating with it \u2014 needs to be <code>-p$DB_PASS<\/code>.<\/li>\n\n\n\n<li><strong>Encryption key derived from the current date.<\/strong> <code>DATA=$(date|shasum|base64|head -c 16)<\/code> is guessable, since an attacker who knows roughly when a backup ran can narrow down the key.<\/li>\n\n\n\n<li><strong>Undefined <code>$EMAIL<\/code> variable<\/strong> used in <code>mail -r $EMAIL<\/code>, when only <code>FROM_EMAIL<\/code>\/<code>TO_EMAIL<\/code> were actually defined.<\/li>\n\n\n\n<li><strong>Malformed <code>mail<\/code> commands<\/strong> \u2014 the recipient was embedded inside an unterminated subject string instead of passed as a separate argument.<\/li>\n\n\n\n<li><strong>A corrupted, merged line<\/strong> (<code>>> $BASE_BAK_FLDR\/..\/logdate_OIFS=$IFS; IFS=$'\\n'<\/code>) that looks like two commands accidentally joined into one.<\/li>\n\n\n\n<li><strong>Missing spaces<\/strong> in both a <code>find<\/code> command and an <code>if [ -d ... ]<\/code> test \u2014 both are shell syntax errors as written.<\/li>\n\n\n\n<li><strong>No check that the S3 upload succeeded<\/strong> before deleting the local encrypted file.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">IV. The Corrected Script<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">#!\/bin\/bash\n# support@pheonixsolutions.com\n# Script to take per-table mysqldump backups, encrypt them, and upload to a versioned S3 bucket.\n\nset -uo pipefail\n\nS3BACKUP=\"s3-bucket-name\"\nDB_USER=\"root\"\nDB_PASS=\"DBPassword\"\nBASE_BAK_FLDR=\"\/backup\/sqldailybackups\"\nBACKUP_LOG=\"$BASE_BAK_FLDR\/sqlbackuplog\"\nKEY_LOG=\"$BASE_BAK_FLDR\/encryption-keys.log\"\nFROM_EMAIL=\"from-email@domain.tld\"\nTO_EMAIL=\"to-email@domain.tld\"\n\nif ! command -v s3cmd >\/dev\/null 2>&amp;1; then\n    echo \"s3cmd not found; please install s3cmd\"\n    exit 1\nfi\n\nmkdir -p \"$BASE_BAK_FLDR\"\n: > \"$BACKUP_LOG\"\n\n# Random encryption key instead of one derived from the date\nDATA=$(openssl rand -base64 32)\n\nif [ -z \"$DATA\" ]; then\n    echo \"Encryption key generation failed\" | mail -r \"$FROM_EMAIL\" -s \"SQL backup failed: $(date)\" \"$TO_EMAIL\"\n    exit 1\nfi\n\necho \"$(date) : $DATA\" >> \"$KEY_LOG\"\nchmod 600 \"$KEY_LOG\"\n\nDBS_LIST=$(mysql -u \"$DB_USER\" -p\"$DB_PASS\" -N -e \"show databases;\" 2>>\"$BACKUP_LOG\")\n\nOIFS=$IFS\nIFS=$'\\n'\n\nfor DB in $DBS_LIST; do\n    DB_BKP_FLDR=\"$BASE_BAK_FLDR\/$(date +%d-%m-%Y)\/$DB\"\n    [ ! -d \"$DB_BKP_FLDR\" ] &amp;&amp; mkdir -p \"$DB_BKP_FLDR\"\n\n    TABLES=$(mysql -u \"$DB_USER\" -p\"$DB_PASS\" -N -e \"show tables;\" \"$DB\" 2>>\"$BACKUP_LOG\")\n\n    for table in $TABLES; do\n        DUMP_FILE=\"$DB_BKP_FLDR\/$table.sql.gz\"\n\n        if [ \"$table\" = \"event\" ]; then\n            mysqldump -u \"$DB_USER\" -p\"$DB_PASS\" --events \"$DB\" \"$table\" 2>>\"$BACKUP_LOG\" | gzip > \"$DUMP_FILE\"\n        elif [ \"$table\" = \"general_log\" ] || [ \"$table\" = \"slow_log\" ]; then\n            mysqldump -u \"$DB_USER\" -p\"$DB_PASS\" --skip-lock-tables \"$DB\" \"$table\" 2>>\"$BACKUP_LOG\" | gzip > \"$DUMP_FILE\"\n        else\n            mysqldump -u \"$DB_USER\" -p\"$DB_PASS\" \"$DB\" \"$table\" 2>>\"$BACKUP_LOG\" | gzip > \"$DUMP_FILE\"\n        fi\n\n        openssl enc -aes-256-cbc -pbkdf2 -salt -a -in \"$DUMP_FILE\" -out \"$DUMP_FILE.enc\" -k \"$DATA\"\n\n        if s3cmd put \"$DUMP_FILE.enc\" \"s3:\/\/$S3BACKUP\/$DB\/$table.sql.gz.enc\"; then\n            rm -f \"$DUMP_FILE\" \"$DUMP_FILE.enc\"\n        else\n            echo \"Upload FAILED for $DB\/$table - local files retained\" >> \"$BACKUP_LOG\"\n        fi\n    done\n\n    echo \"$DB\" >> \"$BACKUP_LOG\"\ndone\n\nIFS=$OIFS\n\nfind \"$BASE_BAK_FLDR\" -maxdepth 1 -mtime +5 -type d -exec rm -rf {} \\;\n\necho -e \"List of databases &amp; mysqldump errors (if any):\\n-----\\n$(grep -v 'Using a password on the command line interface can be insecure' \"$BACKUP_LOG\")\\n-----\\nBackup location: s3:\/\/$S3BACKUP\/\\nEncryption key logged in: $KEY_LOG\" \\\n  | mail -r \"$FROM_EMAIL\" -s \"SQL Backup Completed @ $(date)\" \"$TO_EMAIL\"\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">V. Key Fixes at a Glance<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Fix<\/th><th>Why it matters<\/th><\/tr><\/thead><tbody><tr><td><code>-p$DB_PASS<\/code> instead of a bare password argument<\/td><td>Correctly authenticates instead of being misread as a database name<\/td><\/tr><tr><td><code>openssl rand -base64 32<\/code> instead of a date-derived key<\/td><td>Produces a genuinely random, non-guessable encryption key<\/td><\/tr><tr><td>Key logged separately with <code>chmod 600<\/code><\/td><td>Keeps the key out of the general log and restricted to the owner<\/td><\/tr><tr><td><code>-pbkdf2<\/code> added to <code>openssl enc<\/code><\/td><td>Stronger key-derivation function<\/td><\/tr><tr><td>Upload success checked before deleting local files<\/td><td>Prevents data loss if an S3 upload fails<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">VI. Schedule with Cron<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">crontab -e\n<\/pre>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">0 2 * * * \/path\/to\/backup-script.sh\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">VII. Restoring a Table<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">aws s3 cp s3:\/\/your-bucket-name\/database_name\/table_name.sql.gz.enc .\nopenssl enc -aes-256-cbc -d -pbkdf2 -a -in table_name.sql.gz.enc -out table_name.sql.gz -k \"THE_KEY_FROM_THE_LOG\"\ngunzip table_name.sql.gz\nmysql -u root -p database_name &lt; table_name.sql\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Retrieve the key from <code>KEY_LOG<\/code> (<code>\/backup\/sqldailybackups\/encryption-keys.log<\/code>), matching the backup&#8217;s date.<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">VIII. Conclusion<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The core idea \u2014 per-table backups, encrypted and versioned in S3 \u2014 is sound, but the original script had real bugs that would break it or quietly weaken its security. The corrected version fixes the password flag, email syntax, and corrupted line, and replaces a guessable encryption key with a properly random one.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently Asked Questions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Why is a date-derived encryption key such a problem?<\/strong> Because an attacker who can see roughly when a backup was created only needs to test a small number of possible values to reconstruct the key.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Will I lose data if an upload fails?<\/strong> Not with the corrected script \u2014 it now checks the upload succeeded before deleting the local copy, and logs a failure if it didn&#8217;t.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Does this apply to MariaDB too?<\/strong> Yes \u2014 <code>mysqldump<\/code> and the same command syntax work identically against MariaDB.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Related Articles<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/pheonixsolutions.com\/blog\/check-ip-address-centos-7-minimal-server-ifconfig-not-working\/\">Fix ifconfig Not Working on CentOS 7<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/pheonixsolutions.com\/blog\/change-ssh-default-port-disable-ssh-root-login\/\">Change SSH Default Port and Disable SSH Root Login<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Talk to Our Technology Experts<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Need a reliable, secure backup strategy for your databases? Our team can help with backup automation and cloud storage security.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/pheonixsolutions.com\/contact\">Connect with our technology experts.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction This guide covers a script to take table-wise mysqldump backups, encrypt them, and store them in S3. The original [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1022],"tags":[],"class_list":["post-697","post","type-post","status-publish","format-standard","hentry","category-web-architecture","psol-cat-web-architecture"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/phn2x7-bf","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/697","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=697"}],"version-history":[{"count":2,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/697\/revisions"}],"predecessor-version":[{"id":11582,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/697\/revisions\/11582"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=697"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=697"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=697"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}