MySQL Data backup Import export description and corresponding shell script

1. Export the entire database (–hex-blob for BLOB data, prevent garbled and import failures)

Mysqldump-u user name-p database name > exported file name

The code is as follows	Copy Code
Mysqldump-u root-p--default-character-set=gbk--hex-blob i5a6 > I5a6.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name

The code is as follows	Copy Code
Mysqldump-u root-p i5a6 users> i5a6.sql

3. Export a database structure

The code is as follows	Copy Code
Mysqldump-u Root-p-D--add-drop-table i5a6 >d:/i5a6.sql

-D No data –add-drop-table add a drop table before each CREATE statement

4. Import Database

Common source Commands

Enter the MySQL database console,

such as Mysql-u root-p

The code is as follows	Copy Code
Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)

The code is as follows	Copy Code
Mysql>source D:/i5a6.sql

Above all is the comparison scattered code, below we look at the shell code

The first thing I want to bring to you in this article is the full script. The script is described later. I assume you already know shell scripting, mysqldump and crontab.

Database Export Code

The code is as follows

Copy Code

#!/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

The code is as follows

Copy Code

#!/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

code is as follows	copy code
0 5 * * 7   Execute cleanup script