In order to restore a MySQL database to another database, use routines and triggers.
mysqldump -p user -p --routines --triggers db1 > db1.sql |
To restore to another database, just use the normal command.
mysql -u user -p db2 < db1.sql |
mysqldump
MySQL Backup To S3 Bucket
Here’s my MySQL backup script to the S3 Bucket.
Just a couple of things about the script. It’s using …
- AWS CLI
- Mysqldump
They must be setup and configured to work properly.
#!/bin/bash cd /root/database TIMESTAMP=$(date +%Y-%m-%d) S3FILE="s3://bucketname/sqlbackup/backup-$TIMESTAMP.sql" /usr/bin/mysqldump dbname > dbname.sql /usr/local/bin/aws s3 cp dbname.sql $S3FILE sleep 3s rm dbname.sql |
Finally, set the S3 bucket with a 7 day retention. Backups older than 7 days are automatically deleted.