MySQL数据备份命令

---

一、备份命令

1、备份命令

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

 mysqldump -h 192.168.1.100 -p3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql

2、备份压缩

• 导出的数据有可能比较大，不好备份到远程，这时候就需要进行压缩
• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz

mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --database cmdb | gzip > /data/backup/cmdb.sql.gz

3、备份同个库多个表

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 表1 表2 … > 文件名.sql

mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql

4、同时备份多个库

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql

mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql

5、备份实例上所有的数据库

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --all-databases > 文件名.sql

mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases > /data/backup/all_db.sql

6、备份数据出带删除数据库或者表的sql备份

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --add-drop-table --add-drop-database 数据库名 > 文件名.sql

mysqldump -uroot -ppassword --add-drop-table --add-drop-database cmdb > /data/backup/all_db.sql

7、备份数据库结构，不备份数据

• 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --no-data 数据库名1 数据库名2 数据库名3 > 文件名.sql

mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql

二、案例

  #备份一个库
  mysqldump -h127.0.0.1 -p3306 -uroot  -p123456  --databases yyrt_lf | gzip > /home//home/db_backups/test.sql.gz

  gunzip < test1-20221129_144248.sql.gz | mysql -h127.0.0.1 -uroot -p123456   #test

  #备份多个库
  mysqldump -h127.0.0.1 -p3306 -uroot -p123456  --databases test test2 | gzip > /home/backups/all.sql.gz

  gunzip < all.sql.gz | mysql -h10.1.7.247 -uroot -p123456   #test

  #备份全部库
  mysqldump -h127.0.0.1 -p3306 -uroot -p123456    --all-databases | gzip > /home/backups/all.sql.gz

  gunzip < all.sql.gz | mysql -h127.0.0.1 -uroot -p123456   #test

2、备份shell命令

#!/bin/bash
#数据库地址
db_host=127.0.0.1
#数据库用户名
db_user=root
#数据库密码
db_password=123456
#数据库名称多个用空格隔开
db_name_arr=(test_yjdsns test2 test3)
#备份存放路径
backup_dir=/home/db_backups
#备份命名所使用的日期格式
db_data=$(date +%Y%m%d_%H%M%S)

###判断存放目录是否存在####
if [ ! -d $backup_dir ]
then
mkdir -p $backup_dir
fi

#清理7天前的备份
find $backup_dir -name $db_name"*.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1

for dbName in ${db_name_arr[*]}
do
  dumpFile=$dbName-$db_data.sql.gz
  mysqldump -h$db_host -p3306 -u$db_user -p$db_password --databases $dbName | gzip > $backup_dir/$dumpFile
done

三、其他

crontab测试器

1、其他参数

-d 结构(–no-data:不导出任何数据，只导出数据库表结构)
-t 数据(–no-create-info:只导出数据，而不添加CREATE TABLE 语句)
-n (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
-R (–routines:导出存储过程以及自定义函数)
-E (–events:导出事件)
–triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
-B (–databases:导出数据库列表，单个库时可省略）
–tables 表列表（单个表时可省略）

①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d