【mysql数据库备份还原(2)-更新中】

一、备份策略

1.直接拷贝数据库文件

当你使用直接备份方法时，必须保证表不再被使用。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器
1)停止服务器：systemctl stop mysqld
2)直接复制整个数据库目录。
cd /var/lib/mysql
tar -cJf /opt/db1.sql.xz ./db1
3)目标服务器上还原：
scp 对端地址:/opt/db1.sql.xz 目标目录
systemctl stop mariadb
Scp
4)目标服务器数据库目录授权，重启服务器测试。

2.图形化工具进行备份：Navicat

点这里查看

3.mysqldump工具进行备份：完全备份+增量备份，速度相对比较慢，适合中小型数据库，MyISAM是温备份，InnoDB是热备份

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件。
mysqldump备份常用参数：-A 所有数据库
-B 某个数据库
-F 备份时刷新日志，生成一个新的日志文件
-x 锁表（所有表）
-d 只备份结构(no data)
使用1：备份某个数据库

mysqldump -uroot -p'密码' -B db1 | gzip > db1.sql.gz
zcat db1.sql.gz  #不解压进行查看内容

使用2：备份数据库中表结构

Mysqldump -uroot -p'密码' -d -B db1 | gzip > db1_2.sql.gz

使用3：备份表

Mysqldump -uroot -p'密码'   db1 Books Authors | gzip > tab.sql.gz 

还原数据库：
方法一：mysql -uroot -p'密码' 数据库名 < db1.sql.gz

还原db1数据库： zcat  db1.sql.gz | mysql -uroot -p'密码'
还原表：mysql -uroot -p'秘密' 数据库名< 文件

方法二：mysql命令行使用source命令:
进入数据库：source 备份文件路径

Mysql -uroot -p'密码' -e 'use db1;source 文件;'

增量备份：使用mysqlbinlog

前提：
1）my.cnf，是要开启MySQL log-bin日志功能，重启MySQL log_bin =/var/lib/mysql/localhsot-x

vim /etc/my.cnf
# log_bin
log_bin
server_id=123
然后重启服务：[root@localhost ~]# systemctl restart mysqld

2）存在一个完全备份，生产环境一般凌晨某个时刻进行全备

步骤一：进行完全备份

Create database db2;
Use db2;
mysql> CREATE TABLE emp (
    -> empno int(4) NOT NULL,
    -> ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    -> job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    -> mgr int(4) NULL DEFAULT NULL,
    -> hiredate date NOT NULL,
    -> sai int(255) NOT NULL,
    -> comm int(255) NULL DEFAULT NULL,
    -> deptno int(2) NOT NULL,
    -> PRIMARY KEY (empno) USING BTREE
    -> );
mysql> INSERT INTO emp VALUES (1001, '甘宁','文员', 1013, '2000-12-17', 8000, NULL, 200);
mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       493 |
| localhost-bin.000002 |       177 |
| localhost-bin.000003 |      1110 |
+----------------------+-----------+
3 rows in set (0.00 sec)

然后使用mysqldump进行完全备份：

[root@localhost ~]# mysqldump -uroot -p'Pineyang000!' -B db2 > /bak/db2.sql

步骤二 ：进行插入数据，并且删除数据库，之后刷新日志

mysql> INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 30000, 30);
mysql> select * from emp;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 | NULL |     20 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 | 3000 |     30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
mysql> drop table emp;
Query OK, 0 rows affected (0.14 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
#刷新日志后会出现新的二进制日志文件
mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       493 |
| localhost-bin.000002 |       177 |
| localhost-bin.000003 |      1644 |
| localhost-bin.000004 |       154 |
+----------------------+-----------+
4 rows in set (0.00 sec)

步骤三：先进行恢复完全备份，然后使用mysqlbinlog生成增量备份，最后恢复增量备份

[root@localhost mysql]# mysql -uroot -p'Pineyang000!' < /bak/db2.sql
mysql> select * from emp;
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename  | job    | mgr  | hiredate   | sai  | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
|  1001 | 甘宁   | 文员   | 1013 | 2000-12-17 | 8000 | NULL |     20 |
+-------+--------+--------+------+------------+------+------+--------+
1 row in set (0.00 sec)
[root@localhost mysql]# mysqlbinlog   localhost-bin.000003  --base64-output=DECODE-ROWS -v
[root@localhost mysql]# mysqlbinlog localhost-bin.000003 --start-position=1110 --stop-position=1414 -r /bak/pos1.sql
[root@localhost mysql]# mysql -uroot -p'Pineyang000!' < /bak/pos1.sql
mysql> select * from emp;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
|  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 |  8000 | NULL |     20 |
|  1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000 | 3000 |     30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
2 rows in set (0.00 sec)

4.mydumper工具进行备份

5.xtrabackup工具备份数据库（与InnoDB Hotbackup相似）

xtrabackup 备份数据库，实现完全热备份与增量热备份（MyISAM是温备份，InnoDB是热备份）
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份
工具InnoDB Hotbackup的一个很好的替代品。
Xtrabackup有两个主要的工具：xtrabackup、innobackupex
xtrabackup 只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。
innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表。
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文
件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相
关的文件。这些文件会被保存至一个以时间命令的目录中
重点：理解innobackupex备份请戳我

安装地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
帮助文档：https://www.percona.com/doc/percona-xtrabackup/8.0/backup_scenarios/full_backup.html
1.安装innobackupex的软件包： yum localinstall percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm

[root@manager sh]# yum localinstall percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@manager sh]# rpm -ql  percona-xtrabackup-24-2.4.8-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.8
/usr/share/doc/percona-xtrabackup-24-2.4.8/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
#innobackupex 帮助
[root@manager sh]# innobackupex --help

1.【完全备份】

innobacpupex备份命令（此次针对mysql数据库备份，使用的是root用户）： innobackupex -uroot -pPineyang000! /backup/mysql

[root@manager sh]# innobackupex -u root -p Pineyang000! /backup/mysql

可以指定目录的时候去掉时间戳：–no-timestamp
备份成功后，查看/backup/mysql/时间戳下的文件

[root@localhost ~]# ll /backup/mysql/2020-07-12_14-22-17/
总用量 12340
-rw-r----- 1 root root      426 7月  12 14:22 backup-my.cnf
drwxr-x--- 2 root root      274 7月  12 14:22 db1
drwxr-x--- 2 root root       50 7月  12 14:22 db2
-rw-r----- 1 root root      454 7月  12 14:22 ib_buffer_pool
-rw-r----- 1 root root 12582912 7月  12 14:22 ibdata1
drwxr-x--- 2 root root     4096 7月  12 14:22 mysql
drwxr-x--- 2 root root     8192 7月  12 14:22 performance_schema
drwxr-x--- 2 root root     8192 7月  12 14:22 sys
-rw-r----- 1 root root       25 7月  12 14:22 xtrabackup_binlog_info
-rw-r----- 1 root root      113 7月  12 14:22 xtrabackup_checkpoints
-rw-r----- 1 root root      468 7月  12 14:22 xtrabackup_info
-rw-r----- 1 root root     2560 7月  12 14:22 xtrabackup_logfile

还原命令：
1.使用--apply-log:备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。 利用 --apply-log参数，此参数的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

[root@localhost mysql]# innobackupex -u root -p 'Pineyang000!' --apply-log /backup/mysql/2020-07-12_14-22-17/

2.停止服务器，清空数据目录/var/lib/mysql/*
systemctl stop mysqld
rm -rf /var/lib/mysql/*
3.使用--copy-back还原数据

[root@localhost mysql]# innobackupex -u root -p Pineyang000! --copy-back /backup/mysql/2020-07-12_14-22-17/

4.修改数据目录权限，启动服务

[root@localhost lib]# chown -R mysql:mysql /var/lib/mysql/
[root@localhost lib]# ll /var/lib/mysql
总用量 122920
drwxr-x--- 2 mysql mysql      274 7月  12 14:43 db1
drwxr-x--- 2 mysql mysql       50 7月  12 14:43 db2
-rw-r----- 1 mysql mysql      454 7月  12 14:43 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 7月  12 14:43 ibdata1
-rw-r----- 1 mysql mysql 50331648 7月  12 14:43 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 7月  12 14:43 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 7月  12 14:43 ibtmp1
drwxr-x--- 2 mysql mysql     4096 7月  12 14:43 mysql
drwxr-x--- 2 mysql mysql     8192 7月  12 14:43 performance_schema
drwxr-x--- 2 mysql mysql     8192 7月  12 14:43 sys
-rw-r----- 1 mysql mysql       26 7月  12 14:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      468 7月  12 14:43 xtrabackup_info
[root@localhost lib]#systemctl restart mysqld

#还原之后mysql可以正常使用，数据什么都在

2.【增量备份】

--incremental 备份类型:增量备份
--incremental-basedir=指定上一次备份路径
--incremental-dir= 还原时指定增量备份路径
增量备份的前提：先准备好上一次的完全备份

[root@localhost mysql]# innobackupex -u root -p Pineyang000! --no-timestamp /backup/mysql/2020-07-24

第一次插入数据：

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into haha values(3);
Query OK, 1 row affected (0.04 sec)

mysql> select * from haha;
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

第一次增量备份：

[root@localhost mysql]# innobackupex -u root -p Pineyang000! --incremental --incremental-basedir=/backup/mysql/2020-07-24 --no-timestamp /backup/mysql/incr1

第二次插入数据：

mysql> insert into haha values(4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from haha;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

第二次增量备份：

[root@localhost mysql]# innobackupex -u root -p Pineyang000! --incremental --incremental-basedir=/backup/mysql/incr1 --no-timestamp /backup/mysql/incr2

还原命令：
1、使用--apply-log命令回滚完全备份文件:通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

[root@localhost mysql]# innobackupex --apply-log --redo-only /backup/mysql/2020-07-24

2、把第一次增量备份数据合并到全量备份数据上

[root@localhost mysql]# innobackupex --apply-log --redo-only /backup/mysql/2020-07-24 --incremental-dir=/backup/mysql/incr1
#版本不同有的是 --apply-log-only （=--apply-log --redo-only）

此时第一次增量备份上的数据已经合并到全量备份上了，查看xtrabackup_checkpoints：发现完全备份的lsn和第一次增量备份的lsn相同

[root@localhost mysql]# more /backup/mysql/2020-07-24/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 22758355
last_lsn = 22758364
compact = 0
recover_binlog_info = 0
[root@localhost mysql]# more /backup/mysql/incr1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 22757971
to_lsn = 22758355
last_lsn = 22758364
compact = 0
recover_binlog_info = 0

3、把第一次增量备份数据合并到全量备份数据上

[root@localhost mysql]# innobackupex --apply-log --redo-only /backup/mysql/2020-07-24 --incremental-dir=/backup/mysql/incr2

此时第二次增量备份上的数据已经合并到全量备份上了，查看xtrabackup_checkpoints：发现完全备份的lsn和第二次增量备份的lsn相同

[root@localhost mysql]# more /backup/mysql/2020-07-24/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 22758703
last_lsn = 22758712
compact = 0
recover_binlog_info = 0
[root@localhost mysql]# more /backup/mysql/incr2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 22758355
to_lsn = 22758703
last_lsn = 22758712
compact = 0
recover_binlog_info = 0

4、停止服务器，清空数据目录

[root@localhost mysql]# systemctl stop mysqld
[root@localhost mysql]# rm -rf /var/lib/mysql/*

5、使用--copy-back还原完全备份数据

[root@localhost mysql]# innobackupex -u root -p Pineyang000! --copy-back /backup/mysql/2020-07-24
[root@localhost mysql]# ll /var/lib/mysql
总用量 188460
drwxr-x--- 2 root root     4096 7月  24 21:51 db1
drwxr-x--- 2 root root      274 7月  24 21:51 db2
drwxr-x--- 2 root root      130 7月  24 21:51 empinfo
-rw-r----- 1 root root      454 7月  24 21:51 ib_buffer_pool
-rw-r----- 1 root root 79691776 7月  24 21:51 ibdata1
-rw-r----- 1 root root 50331648 7月  24 21:50 ib_logfile0
-rw-r----- 1 root root 50331648 7月  24 21:51 ib_logfile1
-rw-r----- 1 root root 12582912 7月  24 21:51 ibtmp1
drwxr-x--- 2 root root     4096 7月  24 21:51 mysql
drwxr-x--- 2 root root     8192 7月  24 21:51 performance_schema
drwxr-x--- 2 root root     8192 7月  24 21:51 sys
-rw-r----- 1 root root       25 7月  24 21:51 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      555 7月  24 21:51 xtrabackup_info

#但是因为是root用户执行，所以/var/lib/mysql/下的数据文件的属主和属组都变成了root，所以在开启mysql服务时需要将该目录下的文件改为mysql
6、修改数据目录权限,然后重新启动服务验证

[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql/
[root@localhost mysql]# ll /var/lib/mysql
总用量 188460
drwxr-x--- 2 mysql mysql     4096 7月  24 21:51 db1
drwxr-x--- 2 mysql mysql      274 7月  24 21:51 db2
drwxr-x--- 2 mysql mysql      130 7月  24 21:51 empinfo
-rw-r----- 1 mysql mysql      454 7月  24 21:51 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 7月  24 21:51 ibdata1
-rw-r----- 1 mysql mysql 50331648 7月  24 21:50 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 7月  24 21:51 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 7月  24 21:51 ibtmp1
drwxr-x--- 2 mysql mysql     4096 7月  24 21:51 mysql
drwxr-x--- 2 mysql mysql     8192 7月  24 21:51 performance_schema
drwxr-x--- 2 mysql mysql     8192 7月  24 21:51 sys
-rw-r----- 1 mysql mysql       25 7月  24 21:51 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      555 7月  24 21:51 xtrabackup_info
[root@localhost mysql]# ll /var/lib/mysql -d
drwxr-x--x. 8 mysql mysql 241 7月  24 21:51 /var/lib/mysql

启动服务，验证之前插入的数据是否存在

[root@localhost mysql]# systemctl restart mysqld
mysql> select * from haha;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

6.LVM快照备份数据库

lvm快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适
合比较烦忙的数据库
前提：
数据文件要在逻辑卷上；
此逻辑卷所在卷组必须有足够空间使用快照卷；
数据文件和事务日志要在同一个逻辑卷上；
前提：MySQL数据lv和将要创建的快照要在同一vg，vg要有足够的空间存储
操作流程：
1）锁表 flush table with read lock
2）查看position号并记录，便于后期恢复 show master status
3）创建snapshot快照 create snapshop
4）解表 unlock tables
5）挂载snapshot
6）拷贝snapshot数据，进行备份。备份整个数据库之前，要关闭mysql服务（保护ibdata1文件）
7）卸载
8）移除快照

7.MYSQL表的导入导出

MYSQL表的导出：
Mysql>Select *from db2.tb7 into outfile ‘tb7.txt’;
#文件会放在数据库数据文件下面：/usr/local/mysql/data/tb7.txt
注： rpm包安装的数据库执行表的导出时可能会出现错误：–secure-file-priv
解决：
#system vim /etc/my.cnf
secure-file-priv=/var/lib/mysql
System Systemctl restart mysqld
或临时设置Show variables like ‘secure_file_priv’;
Set global secure_file_priv=/var/lib
那么文件就放在/var/lib/mysql/db2/tb7.txt
MYSQL表的导入：

1. mysql>load data infile ‘db.student.txt’ into table db.student.txt;
   或
   2.命令行：mysqlimport -uroot -p密码 数据库名 ‘文件名’