MySQL OneKey Backup

A shell script to backup mysql database and send message with pushoo.

Features

Backup all databases or specified databases.
Push message with pushoo.
Delete expired dump files.
Support custom commands before and after the dump.
Support custom mysqldump options.
Support on CentOS 7+、Ubuntu 18.04+、Debian 9+.

Requirements

You need to install the following software before using the script.

mysqldump
pushoo-cli
nodejs 14+

You can install them with the following command.

# install mysqldump
bash /path/to/mysql_backup.sh do_install_mysql_client

# install pushoo-cli
bash /path/to/mysql_backup.sh do_install_pushoo_cli

# install nodejs 16
bash /path/to/mysql_backup.sh do_install_nodejs

Attention: You need config pushoo-cli before use it.

Arguments

The script has 10 variables, you can set them in the script or pass them as arguments. following is the description of each variable.

dump_target_dir - The directory where the dump file will be saved. Default: /path/to/db_backups. Required.
db_host - The host of the database. Default: 127.0.0.1. Required.
db_user - The user of the database. Default: root. Required.
db_password - The password of the database. Default is empty. Required.
db_names - The names of the database. If set empty, all databases will be backed up. Required.
db_port - The port of the database. Default: 3306. Required.
dump_opts - The options of the mysqldump command. Default: --single-transaction --quick --lock-tables=false. Optional.
expire_hours - The dump file at the specified directory will be deleted after the specified number of hours. Default: 4320. Optional.
before_dump_command - The command to be executed before the dump. Default is empty. Optional.
after_dump_command - The command to be executed after the dump. Default is empty. Optional.

for example:

bash mysql_backup.sh "dump_target_dir" "db_host" "db_user" "db_password" "db_names" db_port "dump_opts" expire_hours "before_dump_command" "after_dump_command"

Usage

# backup "db1" and "db2" to "/path/to/db_backups" and the dump file will be deleted after 4320 hours.
bash /path/to/mysql_backup.sh "/path/to/db_backups" "127.0.0.1" "root" "examplepassword" "db1 db2" 3306 "--single-transaction --quick --lock-tables=false" 4320
# or
bash /path/to/mysql_backup.sh "/path/to/db_backups" "127.0.0.1" "root" "examplepassword" "db1 db2"

# backup "db1" and "db2" to "/path/to/db_backups" and the dump file will be deleted after 4320 hours. and log to /var/log/db_backup.log
bash /path/to/mysql_backup.sh "/path/to/db_backups" "127.0.0.1" "root" "examplepassword" "db1 db2" >> /var/log/db_backup.log 2>&1

CronTab

You can use crontab to schedule the script to run periodically. For example, you can run the script every day at 3:00 AM.

0 3 * * * /path/to/mysql_backup.sh "/path/to/db_backups" "127.0.0.1" "root" "examplepassword" "db1 db2" >> /var/log/db_backup.log 2>&1

Docker

You can use docker image, funnyzak/apline-cron to schedule the script to run periodically.

Reference

MySQL Command Line Client Installation Instructions
pushoo-cli is a command line tool with Pushoo.js pushes multiple platform messages.
Pushoo.js

Contribution

If you have any questions or suggestions, please feel free to submit an issue or pull request.