Linux系统中的Mysql数据库Mysqldump备份与恢复

简介：一般的数据库MySQL的备份方式，可分为以下冷备和热备。冷备，顾名思义，就是将数据库关掉，利用操作系统命令拷贝数据库相关文件。而热备指的是在线备份，即不关闭数据库服务的情况下，对数据库进行备份。实际生产中基本上都是热备的备份方式，这种备份方式也可分为物理备份和逻辑备份。物理备份是通过复制整个数据文件或表空间，包括数据文件、日志文件、索引文件等实现数据库备份，可以直接将备份文件覆盖到原有数据文件上，从而实现数据恢复，而逻辑备份则需要通过导入备份文件来恢复数据。而逻辑备份则是将数据库中的数据以SQL语句的形式导出备份，数据库恢复的时候，需要通过导入备份文件来恢复数据。物理备份常用的工具是Percona提供的XtraBackup，而逻辑备份常用的工具是MySQL自带的Mysqldump。

本文主要介绍Mysqldump备份工具备份与恢复数据库的过程，详细内容请参考下文。

一、Mysqldump备份原理

1、备份原理

说明：Mysqldump命令的工作原理比较简单，首先是查出需要备份的表的结构；然后在文本文件中生成一个CREATE语句；最后再将表中的所有记录转换成一条INSERT语句。通过这些SQL语句，就能够创建表并插入数据。

(1)Mysqldump逻辑备份方式是通过读取数据库的结构和数据，然后将其转化为SQL语句或其它可移植的文本格式存储。

(2)Mysqldump是一个客户端命令，这像使用mysql命令连接数据库一样，需要指定连接的用户名，数据库IP，以及使用-p选项提示输入密码。

2. 一致性备份原理

说明：Mysqldump加上--single-transaction参数的备份即使一致性备份。执行start transaction命令，以此来获取一致性备份，不过该参数仅对innodb存储引擎有效。

备注：可以通过使用>show engines;检查Mysql数据库是否使用innodb数据引擎。

二、Mysqldump备份数据

1、Mysqldump命令介绍

说明：当Mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

2.Mysqldump命令格式

(1)语法选项

(2)连接选项

(3)筛选选项

3.Mysqldump备份实例

(1)备份单个数据库

执行指令# mysqldump -hlocalhost -uroot -p'passwd' dbname > /usr/local/dbname.sql

备注：mysqldump备份并且压缩数据的格式为

mysqldump -hlocalhost -uroot -p'passwd' dbname | gzip > /usr/local/dbname.sql.gz

(2)备份多个数据库

执行指令# mysqldump -hlocalhost -uroot -p'passwd' --databases dbname1 dbname2 > /usr/local/dbname.sql

备注：mysqldump加上了--databases选项，然后后面跟多个数据库。

(3) 备份所有的数据库

执行指令# mysqldump -uroot --p'passwd' --all-databases > /usr/local/all.sql

(4) 备份数据库user下的表emp和dept

执行指令# mysqldump -u root -p user emp dept > emp_dept.sql

(5) 备份数据库user排除指定某个表dept

执行指令# mysqldump -u root -p user --database test --ignore-table=user.dept > user.sql

(6) 备份数据库user排除多个指定表dept和emp

执行指令# mysqldump -u root -p user --database user --ignore-table=user.emp --ignore-table=user.dept > user.sql

三、Mysqldump恢复数据

说明：当Mysqldump恢复数据时，可以执行以下指令

执行指令# mysql -u username -P [dbname] < filename.sql

备注：如果使用--all-databases参数备份了全部数据库，那么恢复的时候不需要指定数据库。