《MySQL 简易速速上手小册》第5章：高可用性和灾难恢复（2024 最新版）

import mysql.connector
from mysql.connector import errorcode
# 假设以下是主服务器和从服务器的连接信息
config_master = {
  'user': 'master_user',
  'password': 'master_password',
  'host': 'master_host',
  'database': 'ecommerce_db'
}
config_slave = {
  'user': 'slave_user',
  'password': 'slave_password',
  'host': 'slave_host',
  'database': 'ecommerce_db'
}
try:
    # 连接到主服务器，配置复制
    cnx_master = mysql.connector.connect(**config_master)
    cursor_master = cnx_master.cursor()
    cursor_master.execute("SHOW MASTER STATUS;")
    # 你需要从这里获取文件名和位置，并在从服务器上配置相应的复制起点
    # 连接到从服务器，配置复制
    cnx_slave = mysql.connector.connect(**config_slave)
    cursor_slave = cnx_slave.cursor()
    # 使用获取到的文件名和位置配置从服务器
    # 注意：以下SQL命令需要根据实际情况调整
    cursor_slave.execute("CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='file_name', MASTER_LOG_POS=position;")
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if cnx_master.is_connected():
        cursor_master.close()
        cnx_master.close()
    if cnx_slave.is_connected():
        cursor_slave.close()
        cnx_slave.close()

import subprocess
from datetime import datetime
# 设置备份路径和数据库连接信息
backup_path = "/path/to/your/backup/directory"
db_name = "your_database_name"
db_user = "your_username"
db_password = "your_password"
# 生成备份文件名
timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
backup_filename = f"{backup_path}/{db_name}_{timestamp}.sql"
# 执行全备份
try:
    subprocess.run(f"mysqldump -u {db_user} -p{db_password} {db_name} > {backup_filename}", check=True, shell=True)
    print("Backup successful")
except subprocess.CalledProcessError as e:
    print(f"Error during backup: {e}")

import subprocess
import boto3
from datetime import datetime
# 生成备份文件名
timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
backup_filename = f"{db_name}_{timestamp}.sql"
# 假设已经执行了备份，并且备份文件位于 backup_filename
# 使用 AWS S3 SDK (Boto3) 上传备份文件
s3_client = boto3.client('s3')
bucket_name = 'your_backup_bucket'
try:
    s3_client.upload_file(backup_filename, bucket_name, backup_filename)
    print("Backup successfully uploaded to S3")
except Exception as e:
    print(f"Error uploading backup to S3: {e}")

import os
import subprocess
import boto3
from datetime import datetime
# 数据库信息和备份配置
db_config = {
    'user': 'db_user',
    'password': 'db_password',
    'host': 'localhost',
    'database': 'mydatabase'
}
backup_path = '/path/to/your/backup'
bucket_name = 'your-bucket-name'
# 生成备份文件名
timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
backup_filename = f"{db_config['database']}_{timestamp}.sql"
backup_file_path = os.path.join(backup_path, backup_filename)
# 执行 mysqldump 备份
subprocess.run(f"mysqldump -u {db_config['user']} -p{db_config['password']} {db_config['database']} > {backup_file_path}", shell=True)
# 上传到云存储
s3 = boto3.client('s3')
s3.upload_file(backup_file_path, bucket_name, backup_filename)

import subprocess
# 恢复数据库配置
db_config = {
    'user': 'db_user',
    'password': 'db_password',
    'host': 'localhost',
    'database': 'mydatabase'
}
backup_file_path = '/path/to/your/backup/backupfile.sql'
# 使用 mysql 客户端恢复
subprocess.run(f"mysql -u {db_config['user']} -p{db_config['password']} {db_config['database']} < {backup_file_path}", shell=True)