运维开发（一）MySQL备份

发表于

本文主要介绍MySQL备份。

关键词：MySQL

逻辑备份

数据库对象级备份，备份内容是表、索引、存储过程等数据库对象，常见工具为MySQL mysqldump、Oracle exp/imp等。

逻辑备份备份速度较慢，恢复速度较慢，体积较大，需要数据库正常运行才可以执行逻辑备份。

优点是备份级别可控，即可以任意选择备份的数据库或者备份的表。

逻辑备份适用于任何存储引擎，一般使用mysqldump进行数据库的逻辑备份。

常见参数

参数名 缩写 含义 默认值
--user -u 用户名
--password -p 密码
--host -h 数据库地址
--port -P 数据库端口号
--databases -B 指定要备份的数据库
--all-databases -A 备份mysql服务器上所有的数据库
--add-drop-database 每个数据库创建之前添加drop数据库语句 未开启
--add-drop-table 创建表之前添加drop语句 开启
--event -E 导出事件 未开启
--routines -R 导出存储过程以及自定义函数 未开启
--triggers 导出触发器 开启
--extended-insert -e 使用具有多个VALUES列的INSERT语法 开启
--ignore-table 指定不导出的表。需要忽略多个表时，需要重复多次
--no-data -d 不导出任何数据，只导出数据库表
--no-create-info -t 只导出数据，不添加CREATE TABLE语句
--force -f 导出过程中忽略SQL错误
--tz-utc 导出顶部设置时区TIME_ZONE='+00:00' 开启
--where -w 只转储指定WHERE条件选择的记录
--set-gtid-purged 是否添加SET @@GLOBAL.GTID_PURGED输出
--single-transaction 通过在一个事务中导出所有表从而创建一个一致性的快照，适用于innodb引擎
--master-data 该选项将当前服务器的binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER命令；如果为2，输出的CHANGE MASTER命令前添加注释信息

参考命令

导出、导入所有的库

1
mysqldump -uroot -proot -hlocalhost -A > all_mysql_data.sql
1
mysql -uroot -proot -hlocalhost < all_muysql_data.sql

导出、导入指定库

1
mysqldump -uroot -proot -hlocalhost newcoder > newcoder.sql
1
mysql -uroot -proot -hlocalhost < newcoder.sql

导出、导入某几个库

1
mysqldump -uroot -proot -hlocalhost -B aaa bbb > aaa_bbb.sql
1
mysql -uroot -proot -hlocalhost < aaa_bbb.sql

导出、导入某个库的某些表数据

比如操作aaa数据库的bbb表和ccc表

1
2
# 可以在导出时加--add-locks参数，表示导出时锁定数据库表
mysqldump -uroot -proot -hlocalhost aaa bbb ccc > bbb_ccc.sql
1
2
# 要确保aaa库存在
mysql -uroot -proot -hlocalhost aaa  < bbb_ccc.sql

只导出数据库结构，不导出数据

1
mysqldump -uroot -proot -hlocalhost aaa -d > bbb_ccc.sql
1
2
# 要确保aaa库存在
mysql -uroot -proot -hlocalhost aaa < bbb_ccc.sql

物理备份

数据库文件级备份，备份内容是操作系统上数据库文件，常见工具为MySQL XtraBackup、Oracle RMAN等。

由存储数据库的内容和文件的副本组成，适用于大型数据库。

优点是备份速度快，恢复速度快，体积小。

如果数据库正在运行，需要先执行锁定数据库，防止在备份期间更改数据库的内容。

一般使用XtraBackup进行数据备份。

Xtrabackup优点

  • 备份速度快，物理备份可靠

  • 备份过程不会打断正在执行的事务（无需锁表）

  • 能够基于压缩等功能节约磁盘空间和流量

  • 自动备份校验

  • 还原速度快

  • 可以流传将备份传输到另外一台机器上

  • 在不增加服务器负载的情况备份数据

Xtrabackup常见参数

参数 含义 备注
--backup 备份到指定目录
--target-dir 指定目录
--stream 流式传输指定备份，参数xbstream
--compress 是否启用压缩备份
--compress-threads 指定压缩线程
--databases 指定要备份的数据库
--databases-exclude 排除要备份的数据库
--user 用户名
--password 密码
--host 数据库IP
--rsync 使用rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。

参考命令

流式备份并压缩、解压

1
xtrabackup --backup --user=root --password=root --host=192.168.31.75 --stream=xbstream | gzip  > `date +%Y%m%d`.gz
1
gzip -f -d < `date +%Y%m%d`.gz  | xbstream -x -C mysql