shell脚本练习（6）：备份MySQL数据库表

一、脚本编写

编写脚本如下：

#!/bin/bash

# 系统数据库
SYS_DB="information_schema|mysql|performance_schema|sys"

# 需要备份的数据库
DB=`mysql -N -e "show databases" | egrep -v $SYS_DB`

for i in $DB;do
        # 备份的路径
        BAK_PATH="/server/db/${i}"
        [ -d ${BAK_PATH} ] || mkdir -p ${BAK_PATH}

        # 备份数据库
        mysqldump -B $i > "${BAK_PATH}/${i}_`date +%F`.sql"

        # 数据库的表
        TB=`mysql -N -e "show tables from ${i}"`

        # 备份数据库的表
        for j in $TB;do
                mysqldump $i $j > "${BAK_PATH}/${i}_${j}_`date +%F`.sql"
        done

done

二、输出结果

执行脚本后查看其目录结构：

[root@localhost ~]# tree /server/db/
/server/db/
├── company
│   ├── company_2025-05-15.sql
│   ├── company_admin_2025-05-15.sql
│   ├── company_dept_2025-05-15.sql
│   ├── company_emp_2025-05-15.sql
│   ├── company_form_2025-05-15.sql
│   └── company_product_2025-05-15.sql
├── goods
│   ├── goods_2025-05-15.sql
│   ├── goods_admin_2025-05-15.sql
│   ├── goods_form_2025-05-15.sql
│   └── goods_product_2025-05-15.sql
└── jiaowu
    ├── jiaowu_2025-05-15.sql
    ├── jiaowu_courses_2025-05-15.sql
    ├── jiaowu_scores_2025-05-15.sql
    ├── jiaowu_students_2025-05-15.sql
    └── jiaowu_tutors_2025-05-15.sql

3 directories, 15 files

可以搭配计划任务实现定时备份数据库。