目录

Life in Flow

知不知，尚矣；不知知，病矣。
不知不知，殆矣。

X

数据库的备份

   
    0 0 410686931

数据备份的意义

  • 保护数据的安全。
  • 在出现意外的时候（硬盘的损坏，断电，黑客的攻击），以便数据的恢复。
  • 导出生产的数据以便研发人员或者测试人员测试学习。
  • 高权限的人员操作失误导致数据丢失，以便恢复。

数据库的备份类型

  • 完全备份：对整个数据库的数据进行备份。
  • 部分备份：对部分数据进行备份（可以是一张表也可以是多张表）
     增量备份：是以上一次备份为基础来备份变更数据的，节约空间。
     差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间。
    部分备份

数据库备份的方式

 逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高。

  • 逻辑备份：直接生成 SQL 语句保存起来，在恢复数据的时候执行备份的 SQL 语句来实现数据的恢复。常见备份工具：mysqldump
  • 物理备份：直接拷贝相关的物理数据

数据库备份的场景

  • 热备份：备份时，数据库的读写操作不会受到影响。
  • 温备份：备份时，数据库的读操作可以进行，但是写操作不能执行。
  • 冷备份：备份时，不能进行任何操作。

mysqldump 数据备份（跨机器）

# mysqldump使用语法
mysqldump -u 用户 -h host -p 密码 dbname table > 路径

# 远程备份单库
mysqldump -uroot -p123 -h192.168.31.220 db1 | gzip  > /mysql_data_back/db1_bak.sql.gz

# 远程备份单库(保留创建库的sql语句，这样数据恢复的时候不需要指定库)
mysqldump -uroot -p123 -h192.168.31.220 --databases db1 | gzip  > /mysql_data_back/db1_bak.sql.gz

# 远程备份单库单表：db1库中的test表
mysqldump -uroot -p123 -h192.168.31.220 db1 test | gzip  > /mysql_data_back/db1_test.sql.gz

# 远程备份多库
mysqldump -uroot -p123 -h192.168.31.220 db1 db2 | gzip  > /mysql_data_back/dbs_bak.sql.gz

# 远程备份全库
mysqldump -uroot -p123 -h192.168.31.220 --all-databases | gzip  > /mysql_data_back/all.sql.gz

MySQL 数据恢复（跨机器）

# 远程恢复数据（备份的数据文件里有创建库的语句）
mysql -uroot -p123 -h192.168.31.220 < db1_bak.sql

# 远程恢复数据（备份的数据文件里没有创建库的语句,如果库已经丢失，需要手动创建库）
mysql -uroot -p123 -h192.168.31.220 db1 < db1_bak.sql

MySQL 数据物理备份

  • 务必先停止 MySQL 服务，再进行物理备份。
  • 务必停止 MySQL 服务，再进行物理数据还原，再启动 MySQL 服务。
    查找数据库源文件路径

MyISAM 表源文件

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
.frm ：记录着表结构信息的文件
.MYI：记录着索引的文件
.MYD :记录着表的数据

InnoDB 表源文件
 InnoDB 有着共享表空间跟独立表空间的概念。

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
.frm ：记录着表结构信息的文件
.ibd ：独立表空间，里边记录这个表的数据和索引
ibdata1：共享表空间，里边记录表的数据和索引

物理备份

################### MyISAM引擎的数据物理备份与恢复
# 备份test_MyISAM库
tar -zcf test_MyISAM.gz test_MyISAM
rm -rf test_MyISAM
# 恢复test_MyISAM库
tar -xf test_MyISAM.gz

################### InnoDB引擎的数据物理备份与恢复
#  备份test_InnoDB库
tar -zcf test_InnoDB.gz test_InnoDB
cp ibdata1 ib_logfile0 ib_logfile1  /mysql_data_back/
rm -rf test_InnoDB
rm -rf ibdata1 ib_logfile0 ib_logfile1
# 恢复test_InnoDB库
tar -xf test_InnoDB
cp /mysql_data_back/ib* .
chown mysql:mysql ibdata1 ib_logfile0 ib_logfile1
service mysql restart

请求全局读锁
 如果不停止 MySQL，而且还要想要物理备份可以使用请求全局锁。

# 请求全局读锁
flush tables with read lock;
# 解锁
unlock tables;

二进制日志备份数据

 二进制日志就是记录着 MySQL 数据库中的一些写入性操作，比如一些增删改，但是，不包括查询！
二进制日志功能
 一般情况下，二进制日志有着数据复制和数据恢复的功能。开启二进制日志会有 1% 的性能消耗！

二进制日志备份恢复

# 查看二进制日志文件  第二种：注释掉配置文件里边的default-character-set=utf8
[root@localhost etc]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002

# 把二进制日志文件导出成普通文件
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /usr/local/mysql/var/mysql-bin.000002 > mysqlbin.sql

# 使用grep过滤出delete财务部的操作
[root@localhost test]# more mysqlbin.sql | grep -B 5 'DROP'
/*!*/;
# at 296
  #在这里！！！！！！！！！！！！
#190722 18:52:10 server id 1  end_log_pos 440 CRC32 0xf25c0850 	Query	thread_id=2	exec_time=0	error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1563792730/*!*/;
DROP table dept

# 或者直接查看二进制日志文件也可以
[root@localhost test]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002 | cat -n  | grep -iw 'DROP'
    35	drop table dept

[root@localhost test]# mysqlbinlog --no-defaults /usr/local/mysql/var/mysql-bin.000002 | cat -n | sed -n '25,35p'
    25	/*!\C utf8 *//*!*/;
    26	SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
    27	SET @@session.lc_time_names=0/*!*/;
    28	SET @@session.collation_database=DEFAULT/*!*/;
    29	BEGIN
    30	/*!*/;
    31	# at 296
  # 在这里！！！！！
    32	#190722 18:52:10 server id 1  end_log_pos 440 CRC32 0xf25c0850 	Query	thread_id=2	exec_time=0	error_code=0
    33	use `db1`/*!*/;
    34	SET TIMESTAMP=1563792730/*!*/;
    35	drop table dept

# mysql恢复数据的逻辑备份
mysql -uroot -p123 -h192.168.31.220 db1 < db1.sql 

# 数据库查看：数据还原至删除财务部门之前
mysql> select * from dept;
+--------+-----------+--------+
| deptnu | dname     | addr   |
+--------+-----------+--------+
|     10 | 研发部    | 北京   |
|     20 | 工程部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 财务部    | 深圳   |
+--------+-----------+--------+
4 rows in set (0.00 sec)

# 利用上面找到的删除的位置，之后使用二进制日志进行恢复数据
mysqlbinlog --no-defaults --set-charset=utf8  --stop-position="296
" /usr/local/mysql/var/mysql-bin.000002 | mysql -uroot -p


# 数据库查看：数据还原至删除财务部门之前
mysql> select * from dept;
+--------+-----------+--------+
| deptnu | dname     | addr   |
+--------+-----------+--------+
|     10 | 研发部    | 北京   |
|     20 | 工程部    | 上海   |
|     30 | 销售部    | 广州   |
|     40 | 财务部    | 深圳   |
|     50 | 测试部    | 南京   |
+--------+-----------+--------+
4 rows in set (0.00 sec)
作者：Soulboy