SHELL automatically backs up the Mysql database and uploads it to the ftp space or sends attachments to the mailbox

In operation projects, you will inevitably encounter database backup operations, because the database stores the core and most valuable data of the website. if data is damaged due to unpredictable reasons

In operation projects, you will inevitably encounter database backup operations, because the database stores the core and most valuable data of the website. if data is damaged or lost due to unpredictable reasons, the attack on a website is devastating. the lessons learned once and again remind us that we must back up data. However, manual backup is indeed troublesome and requires manual operations every day, if there are too many sites, it would be a kind of torment, but fortunately we can use a good script to allow the system to automatically back up data, saving effort. Although this script is relatively simple, it will help us a lot, it can be said once and for all!

Simply put, the backup script:

DB_USER = "user" is the user name.

DB_PASS = "password" is the password

Mkdir/home/mysqlbak/$ DATE specifies and creates the directory to be backed up

BIN_DIR = "/usr/local/mysql/bin" specifies the location of your MYSQL

BCK_DIR = "/home/mysqlbak/$ DATE" back up your database to a folder in the DATE directory.

$ BIN_DIR/mysqldump-opt-u $ DB_USER-p $ DB_PASS discuz> $ BCK_DIR/discuz. SQL

This means you can add the database you want to back up the database, and add a database. "discuz" is the name of the database to be backed up, "discuz. SQL is the data file name backed up.

The script is as follows:

View sourceprint?
01 #! /Bin/sh

02 # File:/home/mysql/backup. sh

03 # Database info bakupmysql

04 DB_USER = "user"

05 DB_PASS = "password"

06 # Others vars

07 DATE = 'date + % Y-% m-% d'

08 mkdir/home/mysqlbak/$ DATE

09 BIN_DIR = "/usr/local/mysql/bin"

10 BCK_DIR = "/home/mysqlbak/$ DATE"

11 # TODO

12 $ BIN_DIR/mysqldump -- opt-u $ DB_USER-p $ DB_PASS discuz> $ BCK_DIR/discuz. SQL

13 $ BIN_DIR/mysqldump -- opt-u $ DB_USER-p $ DB_PASS zjblog> $ BCK_DIR/zjblog. SQL

14 $ BIN_DIR/mysqldump -- opt-u $ DB_USER-p $ DB_PASS openads> $ BCK_DIR/openads. SQL
Several scripts for automatic mysql database backup are collected below:

1. automatically export the mysql backup and upload it to the specified ftp

View sourceprint?
01 #! /Bin/bash

02 # ftp settings

03 Host = FTP_IP

04 Username = FTP_user

05 Passwd = FTP_passwd

06 # Back Up mysql and export it to a file

07 mysqldump-u user-p password database name >$ (date + % Y % m % d)-database name. SQL

08 # compressing backup files

09 tar-zcvf $ (date + % Y % m % d) mysql database name .tar.gz $ (date + % Y % m % d )*

10 # upload to ftp

11 echo "open $ Host

12 user $ Username $ Passwd

13 bin

14 cd/hzg/

15 prompt off

16 mput $ (date + % Y % m % d) mysql database name .tar.gz

17 printf "/n"

18 close

19 bye "| ftp-I-n

20 # delete the old backup file

21 rm-rf $ (date + % Y % m % d)-database name. SQL $ (date + % Y % m % d) mysql database name .tar.gz

22 # rm-rf $ (date + % Y % m % d )*

23 rm-rf $ (date + % Y % m % d )*
2. automatically back up the mysql database and send it to the specified email

View sourceprint?
1 # back up and export the mysql database to a file

2 mysqldump-uroot-ppassword -- databases db1 db2 db3>/home/website/backups/databackup. SQL

3 # compressing backup files

4 tar zcf/home/website/backups/databackup. SQL .tar.gz/home/website/backups/

5 # send to the specified mailbox

6 echo "theme: database backup" | mutt-a/home/website/backups/mysqlbackup. SQL .tar.gz-s "content: database backup" www@gmail.com

7 # delete the old backup file

8 rm-r/home/website/backups /*
Replace the password in-ppassword with your own root password and db1 db2 db3 with the name of the database to be backed up. Replace the path and email address.

You can also use the following command to directly export the mysql backup as a compressed file:

View sourceprint?
1 mysqldump-u user-p password database name | gzip-c >$ (date + % Y % m % d)-database name. SQL
To implement automatic backup, you only need to save the above script content as mysqlbackup. sh and add it to crontab. For example, in ssh, enter:

Crontab-e

Input:

00 00 ***/home/website/mysqlbackup. sh

The backup script is automatically run at every day.

For details about crontab, refer to running scheduled tasks with crontab in CentOS Linux.

Note: If it is added to crontab and runs automatically, it is best to use the absolute path (/usr/loca/mysql/bin/mysqldump, which depends on the installation directory, you can use which mysqldump to check). Otherwise, you may not be able to find the backup and upload.

I have attached my vps automatic backup code for your reference only!

View sourceprint?
01 #! /Bin/bash

02 # ftp settings

03 Host = 'ftp Host IP'

04 Username = 'ftp username'

05 Passwd = 'ftp password'

06 Database = 'database name'

07 Date = $ (date + % Y % m % d)

08

09 # Back Up mysql and export it to a file

10/usr/local/mysql/bin/mysqldump-u username-p password $ {Database }>$ {Date }_$ {Database }. SQL & amp;/bin/tar-zcvf contains multiple date0000_1_database0000.tar.gz $ {Date }_$ {Database }. SQL -- remove-files

11

12 # upload to ftp

13 echo "open $ Host

14 user $ Username $ Passwd

15 bin

16 cd web

17 prompt off

18 delete multiple objects date0000_00000000database0000.tar.gz

19 mput synchronized into date0000_00000000database0000.tar.gz

20 printf "\ n"

21 close

22 bye "| ftp-I-n & echo 'upload finished .'

23

24 # Transfer backup data. here, the decision path is used to add-f and forcibly overwrite existing files.

25/bin/mv-f then fetch date0000_00000000database0000.tar.gz/data/bak/