mysqldump 命令

2018/04/25 Linux

前言

在日常运维工作中,数据库的备份可以说是一项必会而且尤为重要的技能。当数据库发生故障或人为误操作的同时能够通过备份及时还原数据库。是体现运维价值的其中之一。

1、备份时要注意的点

(1) 能容忍最多丢失多少数据;

(2) 恢复数据需要在多长时间内完成;

需要恢复哪些数据:

(a) 做还原测试,用于测试备份的可用性;
(b) 还原演练;

2、备份类型

  • 完全备份,部分备份:
完全备份:整个数据集;
部分备份:只备份数据子集;
  • 完全备份、增量备份、差异备份
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来的变化的数据;
差异备份:仅备份最近一次完全备份以来变化的数据;

增量备份,差异备份如下图:

增量与差异备份

  • 热备份、温备份、冷备份
热备:读写操作均可执行;
温备:读操作可执行,但写操作不可执行;
冷备:读写操作均不可执行;

MyISAM:温备不能热备;
InnoDB:可以热备;
  • 物理备份、逻辑备份
物理备份:直接复制数据文件进行备份;
逻辑备份:从数据库中"导出"数据另存而进行备份;

3、备份什么?

(1)数据

(2)二进制日志、InnoDB的事务日志;

(3)代码(存储过程、存储函数、触发器、事件调度器)

(4)服务器的配置文件;

4、备份工具

mysqldump:逻辑备份工具,适用于所有存储引擎,温备;完全备份、部分备份;对InnoDB存储引擎支持热备;
cp, tar等复制归档工具;物理备份工具,适用所有存储引擎,冷备;完全备份,部分备份;
lvm的快照:几乎冷备;借助于文件系统管理工具进行备份;
mysqlhotcopy:几乎冷备;仅适用于MyISAM存储引擎;

5、备份工具的选择

(1) mysqldump + 复制binlog
	mysqldump:完全备份;
	复制binlog中指定时间范围内的event;增量备份;
(2) lvm2快照 + 复制binlog
	lvm2快照:适用cp或tar等做物理备份;完全备份;
	复制binlog中指定时间范围内的event;增量备份;
(3) xtrabackup
	由Percona提供的支持对InnoDB做热备(物理备份)的工具;支持完全备份、增量备份;

6、如何创建mysql的备份用户

MySQl备份用户需要一下权限:

SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT, EVENT

权限列表

1.SELECT 读取
2.SHOW DATABASES 允许访问完整的数据库列表
4.LOCK TABLES 允许锁定表
5.RELOAD 允许载入和刷新服务器缓存
6.REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址
7.EVENT 允许执行事务

示例:

MariaDB [mysql]> CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'bakpass';
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, EVENT, REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> grant execute on *.* to 'bakuser'@'localhost';

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

7、mysqldump 备份工具

mysql自带的逻辑备份工具,客户端命令,通过mysql协议连接至mysqld服务器;

命格式:

mysqldump [options] [db_name [tbl_name ...]]
mysqldump [options] db_name [tbl_name ...] # 此种方法不会生成create database语句;
mysqldump [options] --databases db_name ... # 备份单个,或多个数据库;
mysqldump [options] --all-databases	# 备份所有数据库
  • 备份的数据集
-A, --all-databases:备份所有数据库;
-B db_name,...或者 --databases db_name, ...:备份单个数据库或导出几个数据库。参数后面所有名字参量都被看作数据库名。
  • 备份MyISAM存储引擎

支持温备,锁定备份库,而后启动备份操作;

锁定方法:

--lock-all-tables:锁定所有库的所有表;
--lock-tables:对每个单独的数据库,在启动备份之前锁定其所有表;
  • 备份InnoDB存储引擎

支持热备

--single-transaction
  • 其它常用选项
--host, -h:需要导出的主机信息;

--password, -p:连接数据库密码;

--user, -u:指定连接的用户名;

--port, -P:连接数据库端口号;

-E, --events:备份指定数据库相关的所有event scheduler;

-R, --routines:备份指定数据库相关的所有存储过程和存储函数;

--triggers:备份表相关的触发器;

--master-data=[value]
	1:记录为CHANGE MASTER TO语句,此语句不被注释;
	2:记录为注释的CHANGE MASTER TO语句;建议备份时加上此参数;

--flush-logs:锁定表完成后,执行flush logs命令;

--no-data, -d:不导出任何数据,只导出数据库表结构。

8、备份示例

备份单个数据库

不加任何选项直接备份

$ mysqldump -ubakuser -p -h10.100.4.213 -P3306 qmms_biz > /home/wangenzhi/qmms_biz.sql

备份函数和触发器

在上面不加任何选项执行备份操作时,如果其数据库内部有函数或者触发器,将不会备份。所以我们在工作中就要与开发确认一下,备份的数据库中是否有函数和触发器。

$ mysqldump -ubakuser -p -h10.100.4.213 -P3306 -R --triggers qmms_biz > /home/wangenzhi/qmms_biz_backup-`date +%Y-%m-%d-%H-%M`.sql

查看备份出的数据内部是否有创建函数的语句

备份数据库的表结构而不备份数据

$ mysqldump -ubakuser -p -h10.100.4.213 -P3306 -R --triggers -d qmms_biz > /home/wangenzhi/qmms_biz.sql

备份某一张表

# mysqldump -u 用户名 -p -R --triggers 数据库名 表名> 导出的文件名
$ mysqldump -ubakuser -p -h10.100.4.213 -P3306 -R --triggers qmms_biz qmms_sys_info > /home/wangenzhi/qmms_sys_info.sql

Search

    Table of Contents