Optimizing Backup and Restore when there are a large number of tables in the database.

3 min readOct 10, 2023

We have a database with large number of tables. In some environments this is over 150k tables in the database. Currently we are moving our environment from one cloud location to another. When we do that moving data in the database became the problem.

Our data has certain characteristics. Tables are created dynamically. A predefined structure is used to create tables but new tables are created as new data uploads. Due to this, we cant use services like AWS DMS effectively as when DMS doesn’t copy over the Keys / Indexes.

Problem Statement

So finally came to the point that we will have to move data inside the downtime. So now the task is to minimize the downtime. DMS is slow so its not optimal for data transmission within the downtime. So we moved towards using backup and restore option. But when there are 150k tables backup itself took more than 15 hours. One reason was that the Database instance was smaller. But still it was not utilized in the backup process.

Note that in our case database was MySQL.

Solution

Solution I came up is to create a python code that will parallelize the backup and restore process. It did cut down the backup time in half for our resources. Then we decided to increase the old environment database instance resources eventually. But even without that this parallel processing method gave good results. In my code I used python multiprocessing. And Python 3 is required to run this code.

Backup

While backup we create defined number of processes and divide tables among the processes to backup. And from the list of tables, chunks of tables are being backed up in a given instance and backup is written to a different files and saved in a given folder.

All parameters in my code are provided through environment variables.

from multiprocessing import Pool, TimeoutError, freeze_support
import time
import os
import subprocess

source_host = os.environ['SOURCE_HOST']
source_user = os.environ['SOURCE_USER']
source_pass = os.environ['SOURCE_PASS']
folder = os.environ['FOLDER']
segments_per_thread = int(os.environ['SEG_PER_THREAD'])
no_of_subprocesses = int(os.environ['NO_OF_PROCESSES'])

def split_list(alist, wanted_parts=1):
    length = len(alist)
    return [ alist[ i * length // wanted_parts: (i + 1) * length // wanted_parts ] 
             for i in range(wanted_parts) ]

def process_table_list(index, local_tables) : 
    print(index)
    print(len(local_tables))
    split_local_tables = split_list(local_tables, segments_per_thread)
    for x in split_local_tables :
        dump_table_list = ""
        for table_x in x:
            table_str = table_x.decode("utf-8") 
            table_str = table_str.strip()
            dump_table_list = dump_table_list + table_str + " "
        dump_command = "mkdir -p {folder};export MYSQL_PWD={source_pass};mysqldump -h {source_host} -u {source_user} --no-tablespaces --skip-triggers --skip-lock-tables mydatabase {dump_table_list}>> {folder}/mydatabase_{index}.sql".format(source_host = source_host, source_user = source_user, source_pass = source_pass, folder = folder, index = index, dump_table_list = dump_table_list)
        os.system(dump_command)

start = time.time()
table_command = "export MYSQL_PWD={source_pass}; mysql -h {source_host} -u {source_user} -s -e \"select table_name from information_schema.tables where table_schema = 'mydatabase'\"".format(source_host = source_host, source_user = source_user, source_pass = source_pass)

sp = subprocess.Popen(['/bin/bash', '-c', table_command], stdout=subprocess.PIPE)
tables = sp.stdout.readlines()
print(len(tables))
split_tables = split_list(tables, wanted_parts=no_of_subprocesses)
poolmap = []
for i in range(no_of_subprocesses):
    poolmap.append((i, split_tables[i]))

pool = Pool(processes=no_of_subprocesses)
pool.starmap(process_table_list, poolmap)
pool.close()
pool.join()
end = time.time()
print ("Time to backup table details")
print(end - start)

Restore

For restore we need same number of processes as backup as each process creates a different file. But idea is the same.

from multiprocessing import Pool, TimeoutError, freeze_support
import time
import os
import subprocess

target_host = os.environ['TARGET_HOST']
target_user = os.environ['TARGET_USER']
target_pass = os.environ['TARGET_PASS']
folder = os.environ['FOLDER']
no_of_subprocesses = int(os.environ['NO_OF_PROCESSES'])

def process_table_list(index) : 
    restore_command = "export MYSQL_PWD={target_pass};mysql -h {target_host} -u {target_user} mydatabase < {folder}/mydatabase_{index}.sql".format(target_host = target_host, target_user = target_user, target_pass = target_pass, folder = folder, index = index)
    os.system(restore_command)

start = time.time()
pool = Pool(processes=no_of_subprocesses)
poolmap = []
for i in range(no_of_subprocesses):
    poolmap.append(i)
pool.map(process_table_list, poolmap)
pool.close()
pool.join()
end = time.time()
print ("Time to restore table details")
print(end - start)

Conclusion

This is to help make backup and restore methods faster and efficiently use the resources you have within a downtime. This can be adapted to be used according to the use case. Hope I solved someone’s problem here.

Good Luck !!!

References

https://github.com/danushkaf/multiprocessing_mysql_dump_restore_python