Linux shell script export backup MySQL database

Recently, the company has a need to query the MySQL database from the status of 60 data and export.

The instance code is as follows :

#!/bin/bash

Dir= "/cache1" # #要存到导出数据的目录名
open= "Open" # #导出数据的文件名称
Open_dir= "${open}.$ (date +%y%m%d)" # #导出数据的文件名称, distinguished by date
Db= "/OPT/TRUSTEDM/MYSQL/CURRENT/BIN/MYSQL-UROOT-PMV_XQ_MV" # #数据库连接语句
$db-dmed_001-e "Select Receiveremail from edmletterarchive WHERE status = or Status =" >> $dir/open.txt# #查 Solicit data and export
Cat $dir/open.txt| awk ' {print $} ' | Sort |uniq >> $dir/$open _dir # #对导出的文件进行排序和去重
RM $dir/OPEN.TXT-RF

Instance code to upgrade

1. Create a script

The shell script can automatically back up the database. As long as you copy and paste this script into a text editor, enter your database username, password, and database name. Here we back up the database using the mysqldump command. Each line of script commands is described later.
(1) Create two directories "Backup" and "Oldbackup" in the directory where you want to place the backup files, using the root directory

#mkdir/backup
#mkdir/oldbackup
(2) Create and edit the file "backup.sh"

#!bin/bash
Cd/backup
echo "Your are in Backup Directory"
MV backup*/oldbackup
echo "Old Databases are moved to Oldbackup folder"
now=$ (date + "%d-%m-%y--%h:%m:%s")
file=backup-$Now. sql
Mysqldump–u user-name–p ' password ' database-name > $File
echo "Your Database Backup successfully Completed"
(3) Set the executable license of the backup.sh script file

# chmod +x/backup/backup.sh
(4) Script execution

#./backup.sh
The following output is available when the script is run:

root@server1:/download#./backup.sh
Your areindownload Directory
Old Backup databaseismoved to Oldbackup folder
Database backup successful completed
root@server1:/download#
Note: The first execution of the script will have a "No such file" hint, because the old backup file does not exist yet. As long as you execute the script again, there is no problem.

2. Script description

In line 8th, enter your own database user name, password, and database name after the mysqldump command.
Execute the script, first enter the/backup directory (to be consistent with the directory you created), and then move the old database backup to the/oldbackup folder, then generate a filename based on the date and time of the system, and at the end, the mysqldump command generates a " . sql format for database backup files.

3. Use cron to develop a backup plan

Using cron, you can execute the script regularly and the backup will be done automatically. Use the crontab command to edit a cron-executed scheduled task.

#crontab –e
Enter the code in the editor and save the exit:

013* * * */backup/backup.sh

example, the following is a more professional

Ideas

Get all database names for MySQL server and filter out databases that don't need to be backed up
Export all database SQL files through mysqldump for loop
Compress all SQL files with zip encryption
Conduct regular data clean-up work

Shell Code

Database Export Code
[HTML] View plain copy print?
#!/bin/bash

#1. Database Information definition
Mysql_host= "192.168.1.1"
Mysql_user= "Root"
mysql_passwd= "Root"

#sql备份目录
Root_dir= "/backup"
Back_dir= "/backup/databases"
data_dir= "Databases"
Store_dir= "Database"
if [!-D $back _dir]; Then
Mkdir-p $back _dir
Fi

#备份的数据库数组
Db_arr=$ (echo "Show Databases" | mysql-u$mysql_user-p$mysql_passwd-h$mysql_host)
#不需要备份的单例数据库
nodeldb= "Test1"

#当前日期
date=$ (date-d ' +0 days ' +%y%m%d)

#zip打包密码
Zippasswd= "passwd"
Zipname= "Lczh_" $date ". zip"


#2. Access to backup directory
CD $back _dir


#3. Circular Backup
For dbname in ${db_arr}
Todo
If [$dbname!= $nodeldb]; Then
sqlfile= $dbname-$date ". sql"
Mysqldump-u$mysql_user-p$mysql_passwd-h$mysql_host $dbname > $sqlfile
Fi
Done


#4. Tar pack all the SQL files
TAR-ZCPPF $root _dir/$store _dir/$zipname--directory/$root _dir/$data _dir
#打包成功后删除sql文件
If [$? = 0]; Then
Rm-r $data _dir
Fi

Data periodic cleanup script

Role
Regular cleanup of backup files 14 days ago

Shell Code
[HTML] View plain copy print?
#!/bin/bash-

#1. Parameter configuration

#mysql文件备份目录
backup_dir1= "/backup/test1/"
Backup_dir2= "/backup/test2/"
Backdir_arr= ($backup _dir1 $backup _dir2)

#过期文件的时间
Keep_time=14

#当前所在星期, crontab in odd-numbered weeks 7 execution
week=$ (Date +%w)
flag= ' expr $week% 2 '



#2. Clean up expired files, only on odd-numbered weeks 7
If [$flag-eq 1]; Then
For dir in ${backdir_arr[*]}
Todo
If [-D $dir]; Then
#查找14天之外的文件数据
Clean_arr= ' Find $dir-type f-mtime + $keep _time-exec ls {} \; '
For Cleanfile in ${clean_arr}
Todo
RM $cleanfile
Done
Fi
Done
Fi

Crontab Configuration
[HTML] View plain copy print?
0 5 * * 7 Execute cleanup script