mysql -- 备份与还原

目录

一、备份

热备 (online)

1、XtraBackup

2、SQLyog

3、navicat

冷备

Ⅰ、备份方式（方案）

1、完全备份(全备)

（1）、mysqldump

2、增量备份

好处：

坏处是：

哪些支持增量备份：

3、差异备份

二、还原：

Ⅰ、备份还原具体操作

三、GTID的主从复制

Ⅰ、GTID是什么？

Ⅱ、GTID的工作原理：

Ⅲ、DTID的主从复制的不同之处

Ⅳ、【基于日志点复制的缺点：】

Ⅴ、【基于GTID复制的优点是：】

Ⅵ、【当failover时（故障切换/故障转喻）如何解决？】

---

官方文档：https://dev.mysql.com/doc/refman/5.7/en/backup-types.html

一、备份

热备 (online)

mysql服务器处于开启状态

1、XtraBackup

        Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

2、SQLyog

3、navicat

冷备

mysql服务器处于关闭状态

Ⅰ、备份方式（方案）

1、完全备份(全备)

full backup ---》 所有的内容全部备份

优点:恢复的时候方便快

缺点:消耗空间大，时间长

（1）、mysqldump

• 备份所有数据库

mysqldump -uroot -p'123456' --all-databases > all_dump.sql

• 备份xz数据库

mysqldump -uroot -p'123456' --databases xz> xz_dump.sql

• 备份xz数据库下的sa1,sa3表

mysqldump -uroot -p'123456' xz sa1 sa3> xz_sa*_dump.sql

用法：

[root@localhost backup]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
[root@localhost backup]#

• 还原

mysql: 全备 + 二进制日志

[root@localhost backup]# mysql -uroot -p'123456' < xz_dump.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]#

2、增量备份

上一次备份后新产生的数据，增加的数据

好处：

        是每次备份需要的数据较少，耗时较短，占用空间较小；

坏处是：

        数据恢复比较麻烦

哪些支持增量备份：

• innobackupex执行的命令

• xtrabackup 软件名

• mysqldump + 二进制日志

• Incremental

3、差异备份

：每次备份都和完全备份进行差异比较

二、还原：

1、备份MySQL

2 、操作mysql

3、mysqlbinllog

（1）根据时间点恢复

（2）根据位置点来恢复

Ⅰ、备份还原具体操作

• 全备+二进制位置号恢复

 #1、产生一个全新的二进制文件
root@xz 16:39  mysql>flush logs;   
Query OK, 0 rows affected (0.01 sec)
root@xz 16:39  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
root@xz 16:41  mysql>
#2、给数据库做全备
[root@localhost mysql]# mysqldump -uroot -p'123456' --databases  xz>/backup/xz.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# cd /backup/
[root@localhost backup]# ls
all_dump.sql  xz_dump.sql  xz_sa*_dump.sql  xz.sql
#3、让数据发生变化，进行insert等操作
root@xz 16:51  mysql>insert into sa3(id,name,num) values(6,石孟孟,02),(7,李岚清,06),(8,胡晓晴,05);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
root@xz 16:53  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000001 |      728 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#4、模拟出现故障
root@xz 16:53  mysql>drop database xz;
Query OK, 4 rows affected (0.01 sec)
root@(none) 16:55  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TENNIS             |
| apiserver          |
| flask_SM           |
| flask_TM           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)
root@(none) 16:56  mysql>
#5、开始恢复数据
    先恢复全备
root@(none) 16:58  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TENNIS             |
| apiserver          |
| flask_SM           |
| flask_TM           |
| mysql              |
| performance_schema |
| sys 
| xz                 |
+--------------------+
8 rows in set (0.00 sec)
root@(none) 16:59  mysql>show tables;
+--------------+
| Tables_in_xz |
+--------------+
| as2          |
| changsha     |
| sa1          |
| sa3          |
+--------------+
4 rows in set (0.00 sec)
root@xz 17:00  mysql>select * from sa3;
+----+------+------+
| id | name | num  |
+----+------+------+
|  1 | shi  |  001 |
|  2 |      |  002 |
|  3 | shi  |  001 |
|  4 |      |  003 |
|  5 | shi  |  003 |
+----+------+------+
5 rows in set (0.00 sec)
    再根据二进制日志找到删除数据库之前的position位置号
[root@localhost support-files]# cd /data/mysql/
[root@localhost mysql]# ls
apiserver        flask_SM        ibtmp1                     mysql               server-cert.pem
auto.cnf         flask_TM        localhost-bin.000001       mysql.sock          server-key.pem
ca-key.pem       ib_buffer_pool  localhost-bin.000002       mysql.sock.lock     sys
ca.pem           ibdata1         localhost-bin.index        performance_schema  TENNIS
client-cert.pem  ib_logfile0     localhost.localdomain.err  private_key.pem
client-key.pem   ib_logfile1     localhost.localdomain.pid  public_key.pem
[root@localhost mysql]# mysqlbinlog localhost-bin.000002
[root@localhost mysql]# mysqlbinlog localhost-bin.000002|egrep -C 5 drop database
##找到删除数据库前的位置号
[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=1170 localhost-bin.000002|egrep  mysql -uroot -p'123456'
mysql:[Warning] a password on the command line interface can be insecure.
#6、查看数据是否恢复

根据时间节点进行恢复

#数据库备份
mysqlbinlog --start-datetime="2022-09-14 17:02:32" --stop-datetime="2022-09-14 17:12:45" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
#找到对应时间点进行数据库二进制恢复
mysqlbinlog --start-datetime="2022-09-14 17:12:45" --stop-datetime="2022-09-14 17:20:12" /data/mysql/localhost-bin.000002|mysql-uroot -p'123456'

三、GTID的主从复制

DTIG的全称为：Global Transaction Identifier

是在争锋复制环境中对一个事务的唯一标识，全局唯一，一个事务对应一个GTID

Ⅰ、GTID是什么？

1、全局唯一，一个事务对应一个GTID
2、替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制

3、MySQL5.6开始支持
4、在传统的主从复制中，slave端不用开启binlog;但是在GTID主从复制中，必须开启binlog5、slave端在接受master的binlog时，会校验GTID值
6、为了保证主从数据的一致性，多线程同时执行一个GTID

Ⅱ、GTID的工作原理：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。

2、slave端的i/o线程将变更的binlog，写入到本地的relay 1og中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。

4、如果有记录,说明该GTID的事务已经执行，slave会编辑
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlogo

6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描

Ⅲ、DTID的主从复制的不同之处

- 配置文件在主从复制的基础上还得加入：

gtid-mode=ON
enforce-gtid-consistency=ON
log-bin=/usr/local/mysql/data/mysql-bin  #指定二进制日志存放路径
log_slave-start=1

- 在slave上启动master的信息

CHANGE MASTER TO MASTER_HOST='192.168.8.133',
MASTER_USER='sc_slave',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSTITION=1;

• 比binlog+pos的传统方式，在恢复的时候，以前做过的不再执行，可以节约故障恢复的时间

Ⅳ、【基于日志点复制的缺点：】

1、故障转移时重新获取master的日志点信息比较困难，基于日志点复制时从master的binlog的偏移量进行增量同步，如果指定错误会造成遗漏或者重复，造成主从不一致。

Ⅴ、【基于GTID复制的优点是：】

1.可以很方便的进行故障转移，记录master最后事务的GTID值。比如master : A ,slave : B,C。

        当A挂了后，B执行了所有A传过来的事务。当C连接到B后，在自己的binlog找到最后一次A传过来的GTID。然后C将这个GTID发送给B，B获取到这个

        GTID，就开始从这个GTID的下一个GTID开始发送事务给C。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。

2.slave不会丢失master的任何修改(开启了log_slave_updates)

        log_slave_updates=on: 使slave从master二进制日志里read后执行的操作可以记录到自己的二进制日志里，方便failover后多台slave实现保持数据一致时节约时间

Ⅵ、【当failover时（故障切换/故障转喻）如何解决？】

基于binlog+时间戳/position主从复制

基于binlog+GTID的主从复制 优势恢复更加快捷和方便

中间件：

mycat -->开源，活跃的性能好的数据库中间件

MySQL-touter