QFQ

import paramiko

import subprocess

import os

import os.path

ssh_host_1 = 'w22a.math.uzh.ch' #'w16.math.uzh.ch'

hostname_1 = 'w22a' #'w16'

ssh_host_2 = 'wdb22.math.uzh.ch' #'wdb16.math.uzh.ch'

hostname_2 = 'wdb22' #'wdb16'

ssh_private_key_path = os.path.expanduser('~/.ssh/id_rsa')

db_name_1 = input("Name of the data db: ")

db_name_2 = input("Name of the typo3 db: ")

project_path = input("Project path: ")

ssh_username= 'root' 

dumpFilename1 = db_name_1 + ".sql"

dumpFilename2 = db_name_2 + ".sql"

tmpPath = "/var/tmp/"

dumpFilepath1 = tmpPath + dumpFilename1

dumpFilepath2 = tmpPath + dumpFilename2

htmlPath = os.path.dirname(project_path)

cdCmd = 'cd ' + htmlPath

project_name = os.path.basename(project_path)

backup_name = project_name + "_backup.tar"

dumpFilenameTar = project_name + "_dumps.tar.gz"

# Prompt with database names

print(f"Starting tear down process\n------------------------")

print(f"Project - Host: {ssh_host_1} / Path: {project_path}")

print(f"SQL DB - Host: {ssh_host_2} / DB: {db_name_1} / {db_name_2}")

# Prompt for confirmation

confirmation = input(f"Do you really want to proceed with the showed databases? (y/N): ").strip().lower()

# Check the response

if confirmation != 'y':

	print("Operation aborted by the user.")

	sys.exit(1)

# Copy the backup files from wdb16 to the actual host

destinationPath = "/scratch/tmp/365/"

# Establish SSH connection to wdb16 and execute commands

with paramiko.SSHClient() as ssh:

    ssh.load_system_host_keys()

    ssh.connect(ssh_host_2, username=ssh_username, key_filename=ssh_private_key_path)

    print("Creating SQL dumps")

    # Execute mysqldump commands

    mysqldump_commands = [

        f"mysqldump {db_name_1} > {dumpFilepath1}",

        f"mysqldump {db_name_2} > {dumpFilepath2}"

    ]

    for command in mysqldump_commands:

        stdin, stdout, stderr = ssh.exec_command(command)

        print(stdout.read().decode())

        print(stderr.read().decode())

    # Comprize both dumps in one tar

    tar_command = f"cd {tmpPath} && tar -cvzf {tmpPath}{dumpFilenameTar} {dumpFilename1} {dumpFilename2}"

    stdin, stdout, stderr = ssh.exec_command(tar_command)

    print(stdout.read().decode())

    print(stderr.read().decode())

    print(f"SQL tar file successfully created: {tar_command}")

# Copy files using SCP

scp_commands = [

    f"scp {ssh_username}@{hostname_2}:{tmpPath}{dumpFilenameTar} {destinationPath}"

]

for command in scp_commands:

    subprocess.run(command, shell=True)

    print(f"SQL tar file copied successfully: {command}")

# Establish SSH connection to w16 and execute commands

with paramiko.SSHClient() as ssh:

    ssh.load_system_host_keys()

    ssh.connect(ssh_host_1, username=ssh_username, key_filename=ssh_private_key_path)

    print("----------------------------------\nCreating tar file from project folder")

    # Navigate to the parent directory of the project folder

    parent_dir = os.path.dirname(project_path)

    # Execute tar command

    tar_command = f"cd {parent_dir} && tar -cvf {backup_name} {project_path}"

    stdin, stdout, stderr = ssh.exec_command(tar_command)

    print(stdout.read().decode())

    print(stderr.read().decode())

    print(f"tar file successfully created: {tar_command}")

# Copy tar file using SCP

scp_command = f"scp {ssh_username}@{hostname_1}:{htmlPath}/{backup_name} {destinationPath}"

subprocess.run(scp_command, shell=True)

print(f"tar file successfully copied: {scp_command}")

print("------------------------------\nMerging tar sql files to tar backup")

# Change directory to destinationPath

os.chdir(destinationPath)

# First extract the sql dumps from tar.gz

tar_extract_command = f"tar -xzf {dumpFilenameTar}"

subprocess.run(tar_extract_command, shell=True)

print(f"Extracted sql tar successfully: {tar_extract_command}")

tar_append_command = f"tar -rf {backup_name} {dumpFilename1} {dumpFilename2}"

gzip_command = f"gzip {backup_name}"

subprocess.run(tar_append_command, shell=True)

subprocess.run(gzip_command, shell=True)

print(f"Merge successfully: {tar_append_command} && {gzip_command}")

print("-------------------------------\nDeleting SQL tar and dump files on scratch")

remove_dump_commands = [

	f"rm {dumpFilenameTar}",

	f"rm {dumpFilename1}",

	f"rm {dumpFilename2}"

]

for command in remove_dump_commands:

    completed_process = subprocess.run(command, shell=True)

    print(f"Deleted dump on scratch successfully: {command}")

# Establish SSH connection to w16 and execute commands

with paramiko.SSHClient() as ssh:

    ssh.load_system_host_keys()

    ssh.connect(ssh_host_1, username=ssh_username, key_filename=ssh_private_key_path)

    print(f"-------------------------------\nDeleting backup file on {hostname_1}")

    # Execute tar command

    rm_command = f"rm {htmlPath}/{backup_name}"

    stdin, stdout, stderr = ssh.exec_command(rm_command)

    print(stdout.read().decode())

    print(stderr.read().decode())

    print(f"Deleted tar file successfully: {rm_command}")

# Establish SSH connection to wdb16 and execute commands

with paramiko.SSHClient() as ssh:

    ssh.load_system_host_keys()

    ssh.connect(ssh_host_2, username=ssh_username, key_filename=ssh_private_key_path)

    print(f"-------------------------------\nDeleting sql dumps on {hostname_2}")

    # Execute tar command

    rm_dump_commands = [

	    f"rm {dumpFilepath1}",

	    f"rm {dumpFilepath2}",

	    f"rm {tmpPath}{dumpFilenameTar}"

    ]

    for command in rm_dump_commands:

        stdin, stdout, stderr = ssh.exec_command(command)

        print(stdout.read().decode())

        print(stderr.read().decode())

        print(f"Deleted dump file successfully: {command}")

print(f"------------------------------------------------\nBackup file path: {destinationPath}{backup_name}.gz")