jeffreysprompts.com

Here's a Python script to automate the process of backing up a MySQL database to an S3 bucket:

```python
import pymysql
import boto3
import logging
from datetime import datetime

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Database configuration
db_host = 'your-db-host'
db_user = 'your-db-user'
db_password = 'your-db-password'
db_name = 'your-db-name'

# S3 configuration
aws_access_key = 'your-aws-access-key'
aws_secret_key = 'your-aws-secret-key'
s3_bucket = 'your-s3-bucket'

# Connect to the database
def connect_to_db():
    try:
        connection = pymysql.connect(host=db_host,
                                    user=db_user,
                                    password=db_password,
                                    database=db_name)
        logger.info('Successfully connected to the database.')
        return connection
    except Exception as e:
        logger.error(f'Error connecting to the database: {e}')
        raise

# Backup the database
def backup_database(connection):
    try:
        # Create a backup file name with the current date and time
        backup_file = f'backup-{datetime.now().strftime("%Y-%m-%d-%H-%M-%S")}.sql'
        
        # Dump the database to a file
        with open(backup_file, 'w') as f:
            cursor = connection.cursor()
            cursor.execute(f'SHOW TABLES')
            tables = cursor.fetchall()
            
            for table in tables:
                cursor.execute(f'SELECT * FROM {table[0]}')
                rows = cursor.fetchall()
                
                # Write the table data to the file
                f.write(f'-- Table: {table[0]}\n')
                for row in rows:
                    f.write(f'{row}\n')
                f.write('\n')
        
        logger.info(f'Successfully backed up the database to {backup_file}.')
        return backup_file
    except Exception as e:
        logger.error(f'Error backing up the database: {e}')
        raise

# Upload the backup to S3
def upload_to_s3(backup_file):
    try:
        s3 = boto3.client('s3',
                        aws_access_key_id=aws_access_key,
                        aws_secret_access_key=aws_secret_key)
        
        s3.upload_file(backup_file, s3_bucket, backup_file)
        logger.info(f'Successfully uploaded {backup_file} to S3.')
    except Exception as e:
        logger.error(f'Error uploading {backup_file} to S3: {e}')
        raise

# Main function
def main():
    try:
        connection = connect_to_db()
        backup_file = backup_database(connection)
        upload_to_s3(backup_file)
    except Exception as e:
        logger.error(f'Error: {e}')
    finally:
        if 'connection' in locals() and connection.open:
            connection.close()

if __name__ == '__main__':
    main()
```

This script connects to a MySQL database, backs up all tables to a local file, and then uploads the backup file to an S3 bucket. The script includes error handling and logging to help troubleshoot any issues that may arise. To use this script, simply replace the placeholder values with your own database and S3 configuration details, and then run the script using Python.