CODEWITHSUNDEEP
pythonmysql
Christian Moen
Christian Moen

Reputation: 1301

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

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?

Upvotes: 4

Views: 19310

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476567

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.

Upvotes: 20

Related Questions