Shell script to back up mysql database _ MySQL

Back up mysql database bitsCN.com using shell scripts

Back up mysql databases using shell scripts

Requirement

Sort out some script programs near the end of the year and share with you a script for mysql database backup. The environment is ubuntu10.04server.

Ideas

Obtain the names of all databases on the mysql server and filter out databases that do not need to be backed up.

Export SQL files of all databases through mysqldump for loop

Compress all SQL files with zip encryption

Regular data cleaning

Shell code

Database export code

[Html]

#! /Bin/bash

#1. database information definition

Mysql_host = "192.168.1.1"

Mysql_user = "root"

Mysql_passwd = "root"

# SQL backup directory

Root_dir = "/backup"

Back_dir = "/backup/databases"

Data_dir = "databases"

Store_dir = "database"

If [! -D $ back_dir]; then

Mkdir-p $ back_dir

Fi

# Backup database array

Db_arr = $ (echo "show databases;" | mysql-u $ mysql_user-p $ mysql_passwd-h $ mysql_host)

# Single-instance databases that do not require backup

Nodeldb = "test1"

# Current date

Date = $ (date-d '+ 0 days' + % Y % m % d)

# Zip package password

Zippasswd = "passwd"

Zipname = "lczh _" $ date ". zip"

#2. go to the backup directory

Cd $ back_dir

#3. cyclic backup

For dbname in $ {db_arr}

Do

If [$ dbname! = $ Nodeldb]; then

Sqlfile = $ dbname-$ date ". SQL"

Mysqldump-u $ mysql_user-p $ mysql_passwd-h $ mysql_host $ dbname> $ sqlfile

Fi

Done

#4.tar package all SQL files

Tar-zcPpf $ root_dir/$ store_dir/$ zipname -- directory/$ root_dir/$ data_dir

# Deleting an SQL file after successful packaging

If [$? = 0]; then

Rm-r $ data_dir

Fi

Regular data cleanup scripts

Function

Regularly clear backup files 14 days ago

Shell code

[Html]

#! /Bin/bash-

#1. parameter configuration

# Mysql file backup directory

Backup_dir1 = "/backup/test1 /"

Backup_dir2 = "/backup/test2 /"

Backdir_arr = ($ backup_dir1 $ backup_dir2)

# Expiration time

Keep_time = 14

# For the current week, crontab is executed on Week 7 with an odd number

Week = $ (date + % W)

Flag = 'expr $ week % 2'

#2. clear expired files and only execute the command in seven days of the odd week

If [$ flag-eq 1]; then

For dir in $ {backdir_arr [*]}

Do

If [-d $ dir]; then

# Search for file data beyond 14 days

Clean_arr = 'find $ dir-type f-mtime + $ keep_time-exec ls {}/;'

For cleanfile in $ {clean_arr}

Do

Rm $ cleanfile

Done

Fi

Done

Fi

Crontab configuration

[Html]

0 5 ** 7 run the cleanup script

Postscript

If you have better database backup methods or insufficient shell scripts, you can leave a message with me. I promise to reply and cheer up!

BitsCN.com