How to automatically back up a MySQL database

2 years ago
Olivia Parker
2 minutes

There are two common ways to automatically backup a MySQL database, which are using command line tools or writing scripts.

  1. Using command line tools:
    You can use the mysqldump command to backup a MySQL database. By setting up a scheduled task to run this command regularly, you can achieve automatic backups.

For example, you can create a shell script called backup.sh with the following contents:

#!/bin/bash
mysqldump -u <用户名> -p<密码> <数据库名> > /path/to/backup.sql

Then use cron to create a scheduled task to execute the script.

crontab -e

Add the following content to the open editor to set up a backup to run every day at 3 AM:

0 3 * * * /bin/sh /path/to/backup.sh

Save the file and exit the editor, cron will automatically run the backup script at the specified time.

  1. Using a script:
    A script can be written to automatically back up MySQL databases, and cron can be used to schedule the execution of the script.

For example, you can create a Python script named backup.py with the following contents:

import subprocess

username = '<用户名>'
password = '<密码>'
database = '<数据库名>'
backup_file = '/path/to/backup.sql'

subprocess.call(['mysqldump', '-u', username, '-p' + password, database, '>', backup_file])

Then use cron to create a scheduled task to execute the script.

crontab -e

To schedule a backup to run every day at 3 AM, add the following content to the open editor.

0 3 * * * /usr/bin/python /path/to/backup.py

Save the file and exit the editor, and the cron job will automatically run the backup script at the specified time.

Backup files can be saved in a specified directory using any method, allowing for database recovery in the future.

bannerAds