本文主要是介绍shell中编写备份数据库脚本(使用mysqldump工具),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
mysqldump备份
目录
mysqldump备份
分库备份
分表备份
利用自带工具mysqldump 实现数据库分库分表备份。
要想知道需要备份哪些数据库,就得先列出来
mysql -uroot -p'Openlab123!' -N -e 'show databases' | egrep -on_schema|mysql|performance_schema|sys"
mysql: [Warning] Using a password on the command line interfa
MySchool_db
MyScl_db
WORK
mysl
-N
: 或者写作--no-column-names
,是一个选项,告诉mysql客户端在输出查询结果时不包含列名行。这对于脚本或自动化任务特别有用,因为它使得输出更易于解析。
-e 'show databases'
:-e
后面跟的是执行的SQL命令,这里是要执行的命令是show databases
,该命令用于列出MySQL服务器上所有的数据库。整个命令的作用是:以root用户身份,使用密码登录MySQL服务器,并且在登录后执行
show databases
命令来显示服务器上的所有数据库列表,同时在输出时不包含列标题。命令会列出所有数据库中名称匹配除去
information_schema
,mysql
,performance_schema
, 或sys
的数据库的行
mysqldump进行数据库备份
mysqldump -uroot -pOpenlab123! -B MySchool_db > MySchool_db.sql
备份肯定是不能只备份一条 --> 使用for循环进行备份
分库备份
思路:将除去系统自带的数据库以外的数据库名赋值给DBS,再使用for循环遍历DBS变量循环内部就对每一个数据库进行备份
[root@localhost script] DBS=$(mysql -uroot -p'Openlab123!' -N -e "show databases" | egrep -v "information_schema|mysql|performance_schema|sys")
mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost script] echo $DBS
MySchool_db MyScl_db WORK mysl[root@localhost script] for db in $DBS
> do
> echo 备份$db
> done
备份MySchool_db
备份MyScl_db
备份WORK
备份mysl
编写shell备份服务器脚本
#!/bin/bashDBS=$(mysql -uroot -p'Openlab123!' -N -e "show databases" | egrep -v "information_schema|mysql|performance_schema|sys")for db in $DBS
domysqldump -uroot -pOpenlab123! -B $db > ${db}_$(date +%F).sql
done
启动脚本
[root@localhost script] ls
db_back.sh
#以下警告是因为使用mysqldump命令的时候直接在命令行输入了密码,要解决这个问题可以重定向到/dev/null 下
[root@localhost script] bash db_back.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost script] ll
总用量 52
-rw-r--r--. 1 root root 221 5月 27 21:10 db_back.sh
-rw-r--r--. 1 root root 22792 5月 27 21:10 MySchool_db_2024-05-27.sql
-rw-r--r--. 1 root root 10939 5月 27 21:10 MyScl_db_2024-05-27.sql
-rw-r--r--. 1 root root 2413 5月 27 21:10 mysl_2024-05-27.sql
-rw-r--r--. 1 root root 5644 5月 27 21:10 WORK_2024-05-27.sql
此时一个初步的脚本已经完成了,接下来就是优化
- 将路径修改为变量,将四个基本的数据库修改为变量的形式,将用户密码修改为变量的形式---都是常用修改的设为变量以便往后修改
- 将主程序加入死循环,并将备份脚本放到特定的目录下。
- 如果存在这个目录就直接创建,如果不存在就创建并跳过本次循环再生成脚本
#!/bin/bashBAK_DIR=/backup/db
DB_BASE="information_schema|mysql|performance_schema|sys"
DBS=$(mysql -uroot -p'Openlab123!' -N -e "show databases" | egrep -v ${DB_BASE})
DB_USER_PW="-uroot -pOpenlab123!"# main program
while true;doif [ -d ${BAK_DIR} ];thenfor db in $DBSdomysqldump ${DB_USER_PW} -B $db > ${BAK_DIR}/${db}_$(date +%F).sqldonebreakelif [ ! -d ${BAK_DIR} ];thenmkdir -p ${BAK_DIR}continuefi
done
分表备份
一样的思路,将每一个库里面每一个表做循环备份
#!/bin/bashBAK_DIR=/backup/db
DB_USER_PW="-uroot -pOpenlab123!"
DB_BASE="information_schema|mysql|performance_schema|sys"
DBS=$(mysql ${DB_USER_PW} -N -e "show databases" | egrep -v ${DB_BASE})# main program
while true;doif [ -d ${BAK_DIR} ];thenfor db in $DBSdoTABS=$(mysql ${DB_USER_PW} -N -e "show tables from $db")for tab in $TABSdomysqldump ${DB_USER_PW} $db $tab > ${BAK_DIR}/${db}_${tab}_$(date +%F).sqldonedonebreakelif [ ! -d ${BAK_DIR} ];thenmkdir -p ${BAK_DIR}continuefi
done
实现效果
[root@localhost script] bash db_back_tables.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost script] ls /backup/db/
MySchool_db_grade_2024-05-28.sql MySchool_db_subject_2024-05-28.sql MyScl_db_index_4_2024-05-28.sql MyScl_db_tab22_2024-05-28.sql WORK_test_2024-05-28.sql
MySchool_db_result_2024-05-28.sql MySchool_db_t1_2024-05-28.sql MyScl_db_index5_2024-05-28.sql mysl_t1_2024-05-28.sql
MySchool_db_student_2024-05-28.sql MySchool_db_user_2024-05-28.sql MyScl_db_student_2024-05-28.sql WORK_college_2024-05-28.sql
MySchool_db_Student_V_1_2024-05-28.sql MyScl_db_index1_2024-05-28.sql MyScl_db_student_count_2024-05-28.sql WORK_dept_2024-05-28.sql
MySchool_db_Student_V_grade_2024-05-28.sql MyScl_db_index2_2024-05-28.sql MyScl_db_tab11_2024-05-28.sql WORK_emp_2024-05-28.sql
其实代码还可以继续优化,因为没有达成将每一个表备份到固定的目录之下
#!/bin/bashDB_USER_PW="-uroot -pOpenlab123!"
DB_BASE="information_schema|mysql|performance_schema|sys"
BAK_ROOT=/backup# 创建根备份目录
mkdir -p ${BAK_ROOT}# 获取所有数据库列表,排除特定系统库
DBS=$(mysql ${DB_USER_PW} -N -e "show databases" | egrep -v ${DB_BASE})# 遍历数据库
for db in $DBS
doBAK_DIR=${BAK_ROOT}/${db}# 检查备份目录是否存在,使用if-elif结构if [ ! -d "${BAK_DIR}" ]; then# 如果目录不存在,则创建mkdir -p "${BAK_DIR}"fi# 获取数据库内的所有表TABS=$(mysql ${DB_USER_PW} -N -e "show tables from ${db}")# 遍历表并执行备份for tab in $TABS; domysqldump ${DB_USER_PW} ${db} ${tab} > ${BAK_DIR}/${db}_${tab}_$(date +%F).sqldone
done
这个修订版脚本做了以下改动:
- 移除了不必要的
while true
循环,因为它可能导致无限循环或不期望的退出。- 仅创建一次备份根目录,并为每个数据库动态创建子目录。
- 为每个数据库单独备份其所有表,而不是在每个目录内重新遍历所有数据库。
- 修正了备份文件路径,确保它们被正确地保存在每个数据库对应的备份目录下。
实现效果
[root@localhost script] bash db_back_tables.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost script] tree /backup/
/backup/
├── MySchool_db
│ ├── MySchool_db_grade_2024-05-28.sql
│ ├── MySchool_db_result_2024-05-28.sql
│ ├── MySchool_db_student_2024-05-28.sql
│ ├── MySchool_db_Student_V_1_2024-05-28.sql
│ ├── MySchool_db_Student_V_grade_2024-05-28.sql
│ ├── MySchool_db_subject_2024-05-28.sql
│ ├── MySchool_db_t1_2024-05-28.sql
│ └── MySchool_db_user_2024-05-28.sql
├── MyScl_db
│ ├── MyScl_db_index1_2024-05-28.sql
│ ├── MyScl_db_index2_2024-05-28.sql
│ ├── MyScl_db_index_4_2024-05-28.sql
│ ├── MyScl_db_index5_2024-05-28.sql
│ ├── MyScl_db_student_2024-05-28.sql
│ ├── MyScl_db_student_count_2024-05-28.sql
│ ├── MyScl_db_tab11_2024-05-28.sql
│ └── MyScl_db_tab22_2024-05-28.sql
├── mysl
│ └── mysl_t1_2024-05-28.sql
└── WORK├── WORK_college_2024-05-28.sql├── WORK_dept_2024-05-28.sql├── WORK_emp_2024-05-28.sql└── WORK_test_2024-05-28.sql4 directories, 21 files
这篇关于shell中编写备份数据库脚本(使用mysqldump工具)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!