Create a python script that create a sql dump with structure and data file from MySQL database

Question

I want to make a backup script in Python that make a .sql file with structure and data, and saves it on the disk.

The issue is that I want to make a routine for a remote backup, so the file will be stored in a different server - to make sure that if my server dies, the data does not die with it.

Is it possible?

willeM_ Van Onsem · Accepted Answer · 2024-04-12 07:34:36Z

20

I don't see why you want to do that in Python since there is already a command for that: mysqldump ^[MySQL-doc]. If you specify it with:

mysqldump -u username -p database

It will query for your password and then write the SQL statements to the stdout.

You can use I/O redirection to write it to a file, like:

mysqldump -u username -p database > file.sql

Since SQL is a quite verbose language, you can also construct a zipped file, by passing the output through the gzip compressor:

mysqldump -u username -p database | gzip --best > file.sql.gz

If you want to "write" a program in Python that does the calls itself, you can do it with subprocess:

import subprocess

username = 'the_username'
password = 'the_password'
database = 'my_fancy_database'

with open('file.sql','w') as output:
    c = subprocess.Popen(['mysqldump', '-u',username,'-p%s'%password,database],
                         stdout=output, shell=True)

But this is only some logic around the mysqldump command.

edited Apr 12, 2024 at 7:34

answered May 20, 2017 at 11:29

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jethro Over a year ago

A full example can be found here: tecadmin.net/python-script-for-mysql-database-backup

Joel G Mathew Over a year ago

Keep in mind that you are using password at the command line, and it can be captured and saved in bash_history, and viewed by other users sharing the same privilege. A better option using mysqldump may be to make it read the details from a temporary auth file.

Collectives™ on Stack Overflow

Create a python script that create a sql dump with structure and data file from MySQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related