Script to implement MySQL sub-database sub-table backup

1) Prepare test data: Build a table and insert test data in bulk by writing scripts.

[email protected] scripts]# cat ceshi.sh

#/bin/bash

Path= "/usr/local/mysql/bin: $PATH" #定义mysql命令所在路径

Myuser=root #定义数据用户名

Dbpath=/server/backup #定义数据备份目录

mypass=xiwei1995 #定义数据用户密码

Socket=/tmp/mysql.sock #定义数据库sock文件, specifying the corresponding path for multiple instances

Mycmd= "Mysql-u$myuser-p$mypass" #定义登录数据库的命令

For dbname in test2 test3 test4 test5

Do

$MYCMD-E "CREATE Database $dbname" #批量创建库

$MYCMD-E "Use $dbname; CREATE TABLE test (ID int,name varchar); INSERT into test values (1, ' testdata ');" #在这些库里面创建表test, and insert test data

Done

2) Use the script to view test data results:

[email protected] scripts]# cat catceshi.sh

#/bin/bash

Path= "/usr/local/mysql/bin: $PATH" #定义mysql命令所在路径

Myuser=root #定义数据用户名

Dbpath=/server/backup #定义数据备份目录

mypass=xiwei1995 #定义数据用户密码

Socket=/tmp/mysql.sock #定义数据库sock文件, specifying the corresponding path for multiple instances

Mycmd= "Mysql-u$myuser-p$mypass" #定义登录数据库的命令

For dbname in test2 test3 test4 test5

Do

Echo =============${dbname}.test=============================

$MYCMD-E "Use $dbname, select * from ${dbname}.test;" #批量查看数据

Done

View the results as follows:

[Email protected] scripts]# sh catceshi7.sh

=============test2.test=============================

+------+----------+

| ID | name |

+------+----------+

| 1 | TestData |

+------+----------+

=============test3.test=============================

+------+----------+

| ID | name |

+------+----------+

| 1 | TestData |

+------+----------+

=============test4.test=============================

+------+----------+

| ID | name |

+------+----------+

| 1 | TestData |

+------+----------+

=============test5.test=============================

+------+----------+

| ID | name |

+------+----------+

| 1 | TestData |

+------+----------+

3) script that really implements MySQL sub-Library sub-table

[email protected] scripts]# cat backmysql.sh

#/bin/bash

Path= "/usr/local/mysql/bin: $PATH" #定义mysql命令所在路径

Myuser=root #定义数据用户名

Dbpath=/server/backup #定义数据备份目录

mypass=xiwei1995 #定义数据用户密码

Socket=/tmp/mysql.sock #定义数据库sock文件, specifying the corresponding path for multiple instances

Mycmd= "Mysql-u$myuser-p$mypass" #定义登录数据库的命令

mydump= "Mysqldump-u$myuser-p$mypass" #备份数据库的命令

[!-D "$DBPATH"] && mkdir $DBPATH

For dbname in ' $MYCMD-e "show databases;" | Sed ' 1,2d ' |egrep-v "Mysql|schema" ' #登录数据库获取数据库里的所有数据库名

Do

mkdir $DBPATH/${dbname}_$ (date +%f)-P #创建对应目录.

For table in ' $MYCMD-e ' show tables from $dbname; "| Sed ' 1d ' #内层循环, get all the tables in each library, and then go into the loop.

Do

$MYDUMP $dbname $table |gzip > $DBPATH/${dbname}_$ (Date +%f)/${dbname}_${table}.sql.gz #备份指定的库内的表到指定目录下, The name of the backup is named after the library table name.

Done

Done

The execution script results are as follows:

[Email protected] scripts]# rm-f/server/backup/*

[Email protected] scripts]# sh backmysql.sh

[Email protected] scripts]# tree/server/backup/

/server/backup/

|--test2_2018-05-12

| '--test2_test.sql.gz

|--test3_2018-05-12

| '--test3_test.sql.gz

|--test4_2018-05-12

| '--test4_test.sql.gz

|--test5_2018-05-12

| '--test5_test.sql.gz

Here, it means that the script executed successfully.

Script to implement MySQL sub-database sub-table backup