mysql数据备份

备份的基本常识

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

• 数据库的备份类型：
  （1）完全备份：对整个数据库的数据进行备份
  （2）部分备份：对部分数据进行备份（可以是一张表也可以是多张表）

  copy
  1
  2
  增量备份：是以上一次备份为基础来备份变更数据的，节约空间
  差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间。
  

• 数据库备份的方式：
  （1）逻辑备份：直接生成sql语句保存起来，在恢复数据的时候执行备份的sql语句来实现数据的恢复。
  （2）物理备份：直接拷贝相关的物理数据
  区别：逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高。

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

备份操作

• 利用mysql自带命令mysqldump来备份单库或者多库

copy
1
2
3
4
5
6
7
8
9
10
11
12
//mysqldump使用语法：
mysqldump -u 用户 -h host -p 密码 dbname table > 路径
//远程备份单库例子：
mysqldump -uroot -p123456 -h192.168.56.101 student | gzip  > /mysql_data_back/student_users.sql.gz
//远程备份单库例子并保留创建库语句：
mysqldump -uroot -p123456 -h192.168.56.101 --databases student | gzip  > /mysql_data_back/student.sql.gz
//远程备份单库单表的例子：
mysqldump -uroot -p123456 -h192.168.56.101 student  users | gzip  > /mysql_data_back/student_users.sql.gz
//远程备份多库的例子：
mysqldump -uroot -p123456 -h192.168.56.101 --databases student CD | gzip  > /mysql_data_back/student_CD.sql.gz
//远程备份全库的例子：
mysqldump -uroot -p123456 -h192.168.56.101 --all-databases  | gzip  > /mysql_data_back/all.sql.gz

• mysql数据的恢复

copy
1
2
3
4
5
//远程恢复数据（备份的数据文件里有创建库的语句）：
mysql -uroot -p123456 -h192.168.56.101  < student_bak.sql
​
//远程恢复数据（备份的数据文件里没有创建库的语句）：
mysql -uroot -p123456 -h192.168.56.101  student < student_bak.sql

• mysql之物理备份

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
//看找数据库源文件路径（一）：

mysql> show variables like 'datadir%';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| datadir       | /data/mydata/ |
+---------------+---------------+
​
//看找数据库源文件路径（二）：

 cat /etc/my.cnf
​
//MyISAM表源文件：

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
​
.frm ：记录着表结构信息的文件
​
.MYI：记录着索引的文件
​
.MYD :记录着表的数据
​
//InnoDB表源文件：InnoDB有着共享表空间跟独立表空间的概念。

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则

.frm ：记录着表结构信息的文件

.ibd ：独立表空间，里边记录这个表的数据和索引

ibdata1：共享表空间，里边记录表的数据和索引

//请求全局读锁：
flush tables with read lock;

//解锁：
unlock tables;

• mysql之利用二进制日志mysqlbinlog备份数据

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
//什么是二进制日志：
//二进制日志就是记录着mysql数据库中的一些写入性操作，比如一些增删改，但是，不包括查询！
//二进制日志有哪些功能：
//一般情况下，二进制日志有着数据复制和数据恢复的功能
//开启二进制日志会有1%的性能消耗！
//查看二进制日志是否开启：

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |

//开启二进制日志 : vi /etc/my.cnf
[mysqld]
log-bin=/data/mydata/log_bin/mysql-bin
server-id=1
//查看所有的binlog日志列表：
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     23638 |
+------------------+-----------+
//刷新二进制日志：
flush logs;
//重置(清空）二进制日志文件：
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1091 |
+------------------+-----------+
//使用mysqldump备份数据时，加上-F选项可以重新生成一个新的二进制日志文件
mysqldump -uroot -p Student user -F  > user_bak.sql

• mysql之利用二进制日志mysqlbinlog恢复数据

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
//查看二进制日志文件的内容报错：

[root@master log_bin]# mysqlbinlog mysql-bin.000002
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
​
/**
解决：

第一种：在mysqlbinlog 后边加上 --no-defaults 
​
第二种：注释掉配置文件里边的default-character-set=utf8
​
把二进制日志文件导出成普通文件：

 mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql
*/
​
//找出要恢复的位置：

（1）找出关键字的行数：mysqlbinlog --no-defaults mysql-bin.000002 | cat -n  | grep -iw 'drop'
​
[root@master log_bin]# mysqlbinlog --no-defaults mysql-bin.000002 | cat -n  | grep -iw 'drop'
  455  DROP TABLE `user` /* generated by server */
​
​
（2）打印出相关内容：mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | sed -n '445,455p'
​
[root@master log_bin]# mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | sed -n '445,455p'

​
恢复数据：

//第一步：把备份的数据表user恢复到数据库中：mysql -uroot -p Student < /mysql_data_back/user_bak.sql

//第二步：利用上面找到的删除的位置进行恢复数据

mysqlbinlog --no-defaults --set-charset=utf8  --stop-position="455" /data/mydata/log_bin/mysql-bin.000002 | mysql -uroot -p 

本文作者：菜鸟王999

本文链接：https://www.cnblogs.com/bigdata123/p/16320237.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。