MySQL backup to S3: mysqldump piped to the AWS CLI

The short route: mysqldump into aws s3 cp

To back up MySQL to S3, pipe mysqldump into aws s3 cp, using a dash as the source so the AWS CLI reads the stream from standard input:

mysqldump --single-transaction --routines --events --databases shop | aws s3 cp - s3://amzn-s3-demo-bucket/mysql/shop.sql

Every option is documented. The MySQL 8.4 manual says --single-transaction dumps InnoDB tables in a consistent state without blocking applications. --routines and --events add stored procedures, functions and scheduled events, which are not included by default. Triggers are included by default. The AWS CLI reference shows the dash form for uploading a stream.

Prerequisites: privileges, credentials and plan

• MySQL privileges. The manual lists SELECT for dumped tables, SHOW VIEW for views, TRIGGER for triggers, and PROCESS unless you pass --no-tablespaces. --routines needs the global SELECT privilege and --events needs EVENT for the databases.
• Password out of the command line. The manual calls a password on the command line insecure and says to use an option file. Put the user and password in the [client] group of ~/.my.cnf and restrict that file's permissions.
• AWS CLI configured with credentials allowed to write objects to the bucket.
• No paid plan. mysqldump is part of MySQL; you pay AWS for the S3 storage you use.

Step by step, with the options that matter

1. Choose the scope. --databases name dumps the named databases and writes CREATE DATABASE and USE statements. --all-databases dumps everything; as of MySQL 8.4 it still needs --routines and --events stated explicitly.
2. Keep it consistent. Use --single-transaction for InnoDB. While it runs, the manual says no other connection should issue ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE. MyISAM tables are not dumped consistently by this option.
3. Record the binary log position if you want point-in-time recovery. --source-data=2 writes the coordinates into the dump as a comment. It requires the RELOAD privilege and binary logging.
4. Stream to S3. Pipe to aws s3 cp - s3://... with a date in the key. For streams over 50 GB, add --expected-size in bytes, or the upload can fail at the 10,000 part limit.
5. Add encryption or a storage class if needed. --sse AES256 and --storage-class STANDARD_IA are documented s3 cp options.
6. Check the object. aws s3 ls s3://amzn-s3-demo-bucket/mysql/ --human-readable --summarize
7. Expire old objects with an S3 Lifecycle expiration action.

SQL dumps compress well. To compress, put a compressor between the two commands; the AWS CLI guide's own streaming example pipes through bzip2.

Worked example: nightly dump and a test restore

A team runs this nightly for one database, without --databases so the dump can be loaded under a different name:

mysqldump --single-transaction --routines --events shop | aws s3 cp - s3://amzn-s3-demo-bucket/mysql/shop-2026-10-03.sql --sse AES256

The wrapper script starts with set -o pipefail, so a failed dump makes the whole pipeline fail, and it substitutes the current date into the object key on each run, for example with $(date +%F). If you put the command straight into a crontab line instead, write every % as \%: cron treats an unescaped percent sign as a newline.

On a server with GTIDs enabled, the manual says mysqldump by default writes a SET @@GLOBAL.gtid_purged statement into the dump, which changes the GTID state of the server where the file is reloaded. For a dump meant only for test restores, --set-gtid-purged=OFF leaves that statement out.

Each week they download one file and load it into a scratch database, following the manual's steps for a single-database dump:

aws s3 cp s3://amzn-s3-demo-bucket/mysql/shop-2026-10-03.sql shop.sql

mysqladmin create shop_restore_test

mysql shop_restore_test < shop.sql

Then they compare row counts on key tables. A dump made with --databases or --all-databases is loaded with mysql < dump.sql and recreates the original database names, so only do that on an isolated restore target. Acceptance checks lists what to verify.

Mistakes to guard against

• A broken pipe that still uploads. If mysqldump fails midway, the upload can still succeed with a truncated file. Run the script under bash with set -o pipefail at the top, alert on non-zero exits and watch object sizes.
• Missing routines and events. Without the two flags they are silently absent.
• Redirecting output in Windows PowerShell. The manual warns this produces a UTF-16 file that cannot be loaded, and says to use --result-file instead.
• Expecting point-in-time recovery from the dump alone. That also needs the binary logs written after the dump, which you must keep somewhere safe.

The managed alternative, and when native tools are enough

For one or two MySQL databases with someone reading the alerts, the script above is enough. For large InnoDB data sets the MySQL manual itself points to physical backup tools as much faster to restore than a logical dump. If the database is on Amazon RDS, automated backups with point-in-time recovery are built in; a dump to S3 is then your independent copy.

If you would rather not maintain the script, SimpleBackups runs scheduled MySQL backups to storage you choose and reports failures. As read on its pricing page on 3 October 2026, Basic is $0 for one job and Lite is $49 per month for five jobs with a 5 GB maximum database size. The Postgres version of this page is Postgres backup to S3, and managed versus scripted backups compares the effort.