MySQL database copy

I need to create a copy of the database in my MySQL Server using a Python program.

After a little research, I found mysqldump as the better approach:
import subprocess

command_to_copy_db = f"mysqldump -h {SQL_DB_HOST} -P 3306 -u {SQL_DB_USER} -p{SQL_DB_PASSWORD} {src_database_name} | mysql -u root -p{SQL_DB_PASSWORD} {dest_database_name}"

backup_db_command_result = subprocess.run(command_to_copy_db, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
shell=True, text=True)


My query:

Are there any issues with this approach or are there any better approaches to do this?

Yes, what you are doing is a fine solution for a smaller database. If your databases get larger (say, 1GB or more) you may find that using mysqldump is too slow. For larger databases, use Percona XtraBackup or filesystem snapshots.

Executing mysqldump as a subprocess is the right way to create a dump output. Some people try to duplicate mysqldump's functionality by writing SQL queries directly, but they underestimate how complex mysqldump is. It handles tables, views, stored procedures, triggers, events. It handles identifiers containing special characters. It supports NULLs properly and character sets. It can do compressed data. It can handle any size database (although slowly).

What I would recommend as an improvement to your command is to avoid putting username and password in arguments in your subprocess commands. Use MySQL option files instead, and put the username and password in that file.

Also use the --single-transaction option to mysqldump if you use InnoDB tables. It helps to avoid long-lasting locks on the tables.

StackExchange Database Administrators Q#323202, answer score: 7