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
这个修订版脚本做了以下改动：
实现效果
[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
