数据库备份

第一节：基本概念

1.1 备份类型

　　热备份：数据库进行备份时，数据库的读写操作均不是受影响

　　温备份：数据库进行备份时，数据库的读操作可以执行，但是不能执行写操作

　　冷备份：数据库进行备份时，数据库不能进行读写操作，即数据库要下线

　　mysql 中进行不同方式的备份还要考虑存储引擎是否支持：

　　　　MyISAM：

　　　　　　热备 ×

　　　　　　温备 √

　　　　　　冷备 √

　　　　InnoDB：

　　　　　　热备 √

　　　　　　温备 √

　　　　　　冷备 √

1.2 备份方式

　　物理备份一般就是通过 tar、cp 等命令直接打包复制数据库的数据文件达到备份的效果

　　逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份（逻辑备份会丢失数据精度）

1.3 备份什么？

　　数据

　　二进制日志

　　InnoDB 事务日志

　　代码（存储过程、存储函数、触发器、事件调度器）

　　服务器配置文件

1.4 备份工具

　　mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备

　　cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎，冷备、完全备份、部分备份

　　lvm2 snapshot：几乎热备，借助文件系统管理工具进行备份

　　mysqlhotcopy：名不副实的的一个工具，几乎冷备，仅支持 MyISAM 存储引擎

　　xtrabackup：一款非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份

1.5 备份策略

1.6 mysql 的数据备份

（1）逻辑备份：备份的是建表、建库、插入等操作所执行 SQL 语句，适用于中小型数据库，效率相对较低。

　　mysqldump

　　mydumper

　　逻辑备份的优点：

　　　　在备份速度上两种备份要取决于不同的存储引擎

　　　　物理备份的还原速度非常快，但是物理备份的最小粒度只能做到表

　　　　逻辑备份保存的结构通常都是纯 ASCII 的，所以我们可以使用文本处理工具来处理

　　　　逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高

　　　　逻辑备份也对保持数据的安全性有保证

　　逻辑备份的缺点：

　　　　逻辑备份要对 RDBMS 产生额外的压力，而裸备份无压力

　　　　逻辑备份的结果可能要比源文件更大，所以很多人都对备份的内容进行压缩

　　　　逻辑备份可能会丢失浮点数的精度信息

（2）物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到异构系统中如 Windows。

　　ORACLE 公司也提供了针对企业的备份软件 MySQL Enterprise Backup 简称

　　建议：

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1　　                 #5.7 必须配置 server-id 启用 binlog 功能
binlog_format=statement　　#5.7 默认 binlog 只记录 create、drop 和 alter，要记录 insert 语句必须设置格式
datadir = /usr/local/mysql/data　  #添加此行，数据存放目录
innodb_file_per_table = 1　　#启用InnoDB独立表空间，默认所有数据库使用一个表空间

 第二节：mysqladmin 管理工具

2.1 mysqladmin 功能

　　执行管理操作的工具

2.2 使用场景

　　检查服务器配置、当前运行状态，创建、删除数据库，设置新密码 flush-privileges、reload 重新加载权限数据表 refresh 刷新数据表、重启日志，start-slave 从服务器上启动复制，stop-slave 从服务器上停止复制。

2.3 应用演示

　　mysqladmin 是一个执行管理操作的客户端程序。它可以用来检查服务器的配置和当前状态、创建和删除数据库等。

　　mysqladmin 工具的使用格式：mysqladmin [option] command [command option] command ......option

option选项：
-c number    自动运行次数统计，必须和-i一起使用
-i number    间隔多长时间重复执行
　　mysqladmin -uroot -p -i 2 -c 5 status    每个两秒查看一次服务器的状态，总共重复5次

-h，--host=name Connect to host. 连接的主机名或iP
-p，--password[=name]登录密码，如果不写于参数后，则会提示输入
-P，--port=# Port number to use for connection. 指定数据库端口
-s， --silent Silently exit if one can't connect to server.
-S，--socket=name Socket file to use for connection. 指定socket file
-i, --sleep=# Execute commands again and again with a sleep between. 间隔一段时间执行一次
-u，--user=name User for login if not current user. 登录数据库用户名
-v，--verbose Write more information. 写更多的信息
-V, --version Output version information and exit. 显示版本

（1）查看服务器状态

[root@centos6 ~]# mysqladmin -uroot -p status
Enter password: 
Uptime: 11070  Threads: 1  Questions: 115  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 12  Queries per second avg: 0.10

（2）修改 root 密码

[root@centos6 ~]# mysqladmin -uroot -p123456 password '111111'

（3）查看 mysqlserve 是否可用

[root@centos6 ~]# mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

（4）查看 mysql 版本

[root@centos6 ~]# mysqladmin -uroot -p version

（5）显示服务器的所有运行进程

[root@centos6 ~]# mysqladmin -uroot -p -i 1 processlist

（6）创建数据库

[root@centos6 ~]# mysqladmin -uroot -p111111 create data_test
[root@centos6 ~]# mysql -uroot -p111111 -e "show databases;"

（7）显示系统的所有数据库

[root@centos6 ~]# mysqlshow -uroot -p

（8）显示数据库中的所有表

[root@centos6 ~]# mysqlshow -uroot -p mysql

（9）显示数据库表列的统计

[root@centos6 ~]# mysqlshow -uroot -p mysql -v

（10）显示数据库表列和行的统计

[root@centos6 ~]# mysqlshow -uroot -p mysql -v -v

（11）删除数据库

[root@centos6 ~]# mysqladmin -uroot -p drop data_test

（12）重载权限信息

[root@centos6 ~]# mysqladmin -uroot -p reload

3、mysqldump 备份工具

3.1 功能

　　数据库逻辑备份程序

3.2 应用场景

　　非大数据备份解决方案 SQL语句重现 备份数据表需要SELECT权限

3.3 语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
　　关于数据库名：
　　-A, --all-databases 所有库
　　-B, --databases bbs test mysql 多个数据库

　　关于其它参数说明：
　　--single-transaction　　 #基于此项可以实现对InnoDB表做热备份
　　-x, --lock-all-tables　　#执行备份时为所有表请求加锁 MyISAM
　　-l, --lock-tables
　　-E, --events　　         #备份事件调度器代码
　　--opt　　                #同时启动各种高级选项
　　-R, --routines　　       #备份存储过程和存储函数
　　-F, --flush-logs　     　#备份之前刷新日志
　　--triggers　　           #备份触发器
　　--master-data=2　　#备库，该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE MASTER命令，主从下有用

　　注意：

　　-B 作用：创建数据库和切换到数据库，恢复时不用创建数据库和删表。备份多个库，-B 数据库1 数据库2...

　　-d 只备份库结构，不包含数据内容

　　备份：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名

　　备份多个表：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名

3.4 数据库备份

[root@centos6 ~]# mysqldump -uroot -p111111 --single-transaction --master-data=2 data_test > /tmp/data_test.sql
[root@centos6 ~]# mysqldump -uroot -p111111 --single-transaction --master-data=2 mysql | gzip > /tmp/mysql.gz
[root@centos6 ~]# mkdir /backup
[root@centos6 ~]# mysqldump -uroot -p111111 --routines --events --triggers --master-data=2 --flush-logs --all-databases > /backup/all.sql

3.5 数据库恢复

[root@centos6 ~]# mysql -uroot -p111111 -e "create database data_test1"
[root@centos6 ~]# mysql -uroot -p111111 data_test1 < /tmp/data_test.sql