备份蓝鲸鱼数据库05 00 * sh /opt/script/mysqlbak.sh
[root@iZ2vc3am23bjd9z30thvsrZ ~]# cat /opt/script/mysqlbak.sh
#!/bin/bash

mysql参数
mysql_user=”root”
mysql_password=”MyNewPass2021”
mysql_host=”127.0.0.1”
mysql_port=”6446”

备份的数据库
databases=(adcenter auth ApolloConfigDB ApolloPortalDB business_1 config electronicContract finance_1 flow loanOrder_1 oss platform_init qualityInspection_1 sms xxl_job)
#mysql容器名称
#docker_name=”mysql”
#备份文件存放地址
backup_location=/home/odata/mysql

定时清理备份文件
expire_backup_delete=”ON”
backup_time=date +%Y%m%d%H%M
backup_dir=$backup_location

创建文件夹
for var in ${databases[@]};
do
mkdir -p $backup_location/$var
done

备份
for var in ${databases[@]};
do
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $var > $backup_dir/$var/$backup_time$var.sql
done

定期清理日志
if [ “$expire_backup_delete” == “ON” ];then
for var in ${databases[@]};
do
find $backup_location/$var -name "*.sql" -type f -mtime +30 -exec rm -rf {} \; > /dev/null 2>&1
done
fi

备份全部数据库（包含存储过程、自定义函数及事件）
mysqldump -uroot -pxxxxxx —single-transaction -R -E —all-databases > /tmp/all_database.sql

要求记录 binlog 位点信息 可用于搭建从库
mysqldump -uroot -pxxxxxx —single-transaction -R -E —all-databases —master-data=2 > /tmp/all_database.sql

备份指定数据库
mysqldump -uroot -pxxxxxx —single-transaction -R -E —databases db1 > /tmp/db1.sql
mysqldump -uroot -pxxxxxx —single-transaction -R -E —databases db1 db2 > /tmp/db1_db2.sql

备份部分表
mysqldump -uroot -pxxxxxx —single-transaction db1 tb1 > /tmp/tb1.sql
mysqldump -uroot -pxxxxxx —single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sql

导出某个表，数据以单行insert显示
mysqldump -uroot -pxxxxxx —single-transaction —skip-extended-insert db1 tb1 > /tmp/tb1.sql

导出单表的部分数据
mysqldump -uroot -pxxxxxx —single-transaction db1 tb1 —where=” create_time >= ‘2021-06-01 00:00:00’ “ > /tmp/tb1.sql
mysqldump -uroot -pxxxxxx —single-transaction db1 tb1 —where=’id < 10’ > /tmp/tb1.sql

排除某些表导出
mysqldump -uroot -pxxxxxx —single-transaction —databases db1 —ignore-table=db1.tb1 —ignore-table=db1.tb2 > /tmp/db1.sql

只导出结构或只导出数据
mysqldump -uroot -pxxxxxx db1 —no-data > /tmp/db1_jiegou.sql
mysqldump -uroot -pxxxxxx db1 —no-create-info > /tmp/db1_data.sql

只导出某个库的存储过程及自定义函数
mysqldump -uroot -pxxxxxx -d -t -R db1 > /tmp/db1_routine.sql

远程导出 即MySQL服务端不在本地
mysqldump -uroot -pxxxxxx -hxxx.xxx.xx -P3306 —single-transaction —databases db1 > /tmp/db1.sql