latte Studio

mysql备份还原

mysql备份还原实验，
 运维/数据库  6575  14 分钟  

为什么备份

why

为了应对灾难恢复，人工一定会出错，软硬件一定会故障

  • 硬件如磁盘故障
  • 软件崩溃
  • 自然灾害
  • 黑客攻击
  • 管理员误操作

要点

  • 考虑成本，
  • 业务允许丢失多少数据
  • 恢复数据需要多久完成
  • 要做还原测试
  • 定期还原演练

备份分类

按照对业务影响分：

  • 热备：备份过程中，可读可写，业务无感知，正常运行
  • 温备：备份中，可读，不可写，业务写操作阻塞
  • 冷备：数据库停机，将磁盘数据全部copy一份，业务会停机

按照数据完整性分：

  • 完全备份：假设周一库有1w数据，对整库做完全的数据镜像，即完全备份；
  • 差异备份：之后每天增加100数据，每天的零点，备份和周一全备相比增加的部分，即周二备份100，周三备份200，周四备份300...还原简单，全备+最近的差异备份+现在点的binlog即可恢复
  • 增量备份：之后每天增加100数据，每天的零点，备份和前一天相比增加的部分，即周三，周四，都只需备份100数据；还原复杂，全备+每次的增量备份依次恢复+现在点的binglog才可恢复，中间的有一份增量备份损坏即无法恢复！

image-20200924171133428

按照数据层次分：

  • 物理备份；直接导致磁盘上的二进制数据，速度快，和存储引擎有关，占用一倍存储空间
  • 逻辑备份：导出数据相关的sql，如mysqldump导出的就是各种sql语句，恢复时，就是把sql重新执行一遍，和存储引擎无关，甚至是语法兼容的其他数据库，都可以用此种形式做迁移恢复；备份的是文本的sql语句，占用空间较小

备份什么

  • 数据，毫无疑问
  • 日志，binlog，事务日志redo，undo，日志可以重现或回滚数据库的状态，
  • 配置文件，服务器的配置文件，如/etc/my.cnf
  • 程序，procedure，function，trigger，event scheduler

备份工具

  • cp，tar等命令行，用于物理文件的备份；只适合冷备；
  • lvm的快照，先加锁，然后瞬间快照，后解锁，由于速度快相当于热备，本质借助了支持快照的文件系统进行备份
  • mysqldump：逻辑备份，适合所有存储引擎，结合innodb引擎实现热备，再结合binlog可以做主从复制；可以全备，可以对某库某表做部分备份；
  • xtrabackup：percona提供的对innodb做热备的工具，物理备份，速度快，支持全备，增量备份
  • mariadb backup：mariadb 10.1.26后集成，基于xtrabackup
  • mysqlbackup：热备，mysql 企业版组件
  • mysqlhotcopy：perl实现，使用于myisam，使用lock tables，flush tables，最后cp或scp实现备份，几乎算是冷备了

实验

基于lvm的备份

介绍

​ 借助具有快照功能的文件系统如lvm，zfs实现备份，快照速度极快，原理就是数据的多副本，快照就相当于给快照一刻的数据集合做了个规定，后续数据修改都是将对应数据复制一份再做修改，被修改的数据都存在多副本，没被修改的数据只有一份，

​ 快照后，对快照复制一份，就可以删除快照了，此时数据就不会有多副本了

备份：

  1. 添加新磁盘，并安装lvm2工具

  2. 创建pv，vg，lv，并将某lv做mysql的数据目录，（binlog应该单独存放）

  3. 初始化数据库目录，导入一批实验数据

  4. 开始备份：

    1. 连入mysql实例，加全局读锁；
    2. 滚动日志，生成新的日志文件，并记录此刻日志位置；
    3. shell命令行中做lv的快照；
    4. 在mysql命令行，取消全局读锁
    5. 之后数据库正常读写，数据量较大时，加全局读锁的时间可能较长，全局读锁期间数据库只能读不能写，因此数据时完整且一致，无更改的，快照几乎瞬间完成
    6. 挂载lv的快照卷到某目录，
    7. 将目录下所有文件拷贝到备份存储目录，（可远程，可本地）
    8. 取消快照卷挂载，删除快照卷，完成备份；

  5. 添加新磁盘，并安装lvm2工具

    [root@host2 ~]# yum install -y lvm2
安装lvm管理工具
   
#添加新磁盘，重新扫描总线，不重启实现识别新磁盘
[root@host2 ~]# for i in /sys/class/scsi_host/host*/scan;do echo "- - -" >$i;done
[root@host2 ~]# fdisk -l
...
Disk /dev/sdb: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

  6. 创建pv，vg，lv，并将某lv做mysql的数据目录，（binlog应该单独存放）

       
# 用新磁盘，创建pv，vg，lv
root@host2 ~]# pvcreate /dev/sdb 
  Physical volume "/dev/sdb" successfully created.
[root@host2 ~]# vgcreate mysql_vg /dev/sdb 
  Volume group "mysql_vg" successfully created
   
# 创建了名为mysqldata的lv，将要挂载到mysql的数据目录下；
[root@host2 ~]# lvcreate -n mysqldata -L 3G mysql_vg
  Logical volume "mysqldata" created.
[root@host2 ~]# lvdisplay 
  --- Logical volume ---
  LV Path                /dev/mysql_vg/mysqldata
  LV Name                mysqldata
  VG Name                mysql_vg
  LV UUID                7YQ4T7-JQWz-06Li-L2sC-UPzc-gONk-keJW1J

  7. 初始化数据库目录，导入一批实验数据

    # 格式化lv，找出uuid，写入fstab文件
   
[root@host2 ~]# mkfs.ext4 /dev/vg1/mysqldata 
[root@host2 ~]# blkid /dev/vg1/mysqldata 
/dev/vg1/mysqldata: UUID="c95a64d3-0bff-4a1d-a81b-5eb0f692c831" TYPE="ext4" 
   
   
# 挂载
   
[root@host2 ~]# cat /etc/fstab 
UUID="c95a64d3-0bff-4a1d-a81b-5eb0f692c831" /data/mysql ext4 defaults 0 0
[root@host2 mariadb-10.2.25-linux-x86_64]# mount -a
[root@host2 ~]# df -h
/dev/mapper/vg1-mysqldata  2.9G  9.0M  2.8G   1% /data/mysql
   
# 重新初始化数据目录
./scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
   
# 导入一个库
[root@host2 ~]# mysql < hellodb_innodb.sql 
[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

  8. 开始备份：

    1. 1. 连入mysql实例，加全局读锁；
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec
      
2. 滚动日志，生成新的日志文件，并记录此刻日志位置；
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.00 sec)
      
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      385 |              |                  |
+------------------+----------+--------------+------------------+
[root@host2 ~]# mysql -e "show master status" > binlog-position.txt
[root@host2 ~]# cat binlog-position.txt 
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
mysql-bin.000003	385	
      
3. shell命令行中做lv的快照；此时生成了一个快照卷
[root@host2 ~]# lvcreate --snapshot /dev/vg1/mysqldata -n mysqlback --size 1G --permission r  Logical volume "mysqlback" created.
      
[root@host2 ~]# ll /dev/vg1/mysql*
lrwxrwxrwx 1 root root 7 Sep 27 11:10 /dev/vg1/mysqlback -> ../dm-3
lrwxrwxrwx 1 root root 7 Sep 27 11:10 /dev/vg1/mysqldata -> ../dm-0
      
4. 在mysql命令行，取消全局读锁，
创建一个库，模拟快照后数据变更；
      
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.01 sec)
      
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
      
      
5. 之后数据库正常读写，**数据量较大时，加全局读锁的时间可能较长，全局读锁期间数据库只能读不能写，因此数据时完整且一致，无更改的，快照几乎瞬间完成**
      
6. 挂载lv的快照卷到某目录，
[root@host2 ~]# mkdir /tmp1
[root@host2 ~]# mount /dev/vg1/mysqlback /tmp1/
mount: /dev/mapper/vg1-mysqlback is write-protected, mounting read-only
[root@host2 ~]# ll /tmp1/
total 122980
-rw-rw---- 1 mysql mysql    16384 Sep 27 10:41 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Sep 27 10:41 aria_log_control
drwx------ 2 mysql mysql     4096 Sep 27 11:01 hellodb
-rw-rw---- 1 mysql mysql     2543 Sep 27 11:01 host2.b.com.err
-rw-rw---- 1 mysql mysql        5 Sep 27 11:01 host2.b.com.pid
-rw-rw---- 1 mysql mysql      938 Sep 27 10:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 27 11:01 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 27 10:41 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibtmp1
-rw-rw---- 1 mysql mysql        0 Sep 27 11:01 multi-master.info
drwx------ 2 mysql mysql     4096 Sep 27 10:41 mysql
-rw-rw---- 1 mysql mysql    29277 Sep 27 10:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql     9007 Sep 27 11:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql      385 Sep 27 11:08 mysql-bin.000003
-rw-rw---- 1 mysql mysql       57 Sep 27 11:08 mysql-bin.index
drwx------ 2 mysql mysql     4096 Sep 27 10:41 performance_schema
drwx------ 2 mysql mysql     4096 Sep 27 10:41 test
      
7. 将目录下所有文件拷贝到备份存储目录，（可远程，可本地）
cp 的-a选项保证文件的权限等属性信息在复制中保持完全一致；
      
[root@host2 ~]# mkdir /archdir
[root@host2 ~]# cp -ar /tmp1/* /archdir/
[root@host2 ~]# ll /archdir/
total 122968
-rw-rw---- 1 mysql mysql    16384 Sep 27 10:41 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Sep 27 10:41 aria_log_control
drwx------ 2 mysql mysql      272 Sep 27 11:01 hellodb
-rw-rw---- 1 mysql mysql     2543 Sep 27 11:01 host2.b.com.err
-rw-rw---- 1 mysql mysql        5 Sep 27 11:01 host2.b.com.pid
-rw-rw---- 1 mysql mysql      938 Sep 27 10:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 27 11:01 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 27 10:41 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibtmp1
-rw-rw---- 1 mysql mysql        0 Sep 27 11:01 multi-master.info
drwx------ 2 mysql mysql     4096 Sep 27 10:41 mysql
-rw-rw---- 1 mysql mysql    29277 Sep 27 10:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql     9007 Sep 27 11:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql      385 Sep 27 11:08 mysql-bin.000003
-rw-rw---- 1 mysql mysql       57 Sep 27 11:08 mysql-bin.index
drwx------ 2 mysql mysql       20 Sep 27 10:41 performance_schema
drwx------ 2 mysql mysql       20 Sep 27 10:41 test
      
      
8. 取消快照卷挂载，删除快照卷，完成备份；
[root@host2 ~]# umount /tmp1/
[root@host2 ~]# lvremove /dev/vg1/mysqlback 
Do you really want to remove active logical volume vg1/mysqlback? [y/n]: y
  Logical volume "mysqlback" successfully removed

恢复：

  1. 删除所有数据目录下文件，停止mysql进程，模拟数据损坏；

  2. 将所有备份数据文件拷贝回数据目录，注意权限一致；

  3. 启动mysql进程，检查数据，此时已经恢复到快照时状态

  4. 重放备份时记录日志位置到最新一刻的日志位置的binlog，（binlog应单独存储）使得数据恢复到崩溃前一刻；

  5. 删除所有数据目录下文件，停止mysql进程，模拟数据损坏；

    # 将binlog复制一份用作备份点后的数据重放恢复；
# 实际中，binlog应单独存储，
   
[root@host2 ~]# cp -a /data/mysql/mysql-bin.* /tmp1/
[root@host2 ~]# ll /tmp1/
total 52
-rw-rw---- 1 mysql mysql 29277 Sep 27 10:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql  9007 Sep 27 11:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql   515 Sep 27 11:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql    57 Sep 27 11:08 mysql-bin.index
   
# 因连同pid文件一起删除，所以mysql无法正常停止，生产中pid应和数据文件分开存放；
# 使用kill命令强行停止mysql进程；
   
[root@host2 ~]# rm -rf /data/mysql/*
[root@host2 ~]# ss -nlt
State      Recv-Q Send-Q Local Address:Port                Peer Address:Port              
LISTEN     0      128                *:22                             *:*                  
LISTEN     0      100        127.0.0.1:25                             *:*                  
LISTEN     0      80                :::3306                          :::*                  
LISTEN     0      128               :::22                            :::*                  
LISTEN     0      100              ::1:25                            :::*                  
[root@host2 ~]# service mysqld status
 ERROR! MariaDB is not running, but lock file (/var/lock/subsys/mysql) exists
   
# kill掉mysql进程；
[root@host2 ~]# ps -ef|grep msyql
root       2575   1335  0 11:21 pts/0    00:00:00 grep --color=auto msyql
[root@host2 ~]# ps -ef|grep mysql
root       2237      1  0 11:01 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/host2.b.com.pid
mysql      2364   2237  0 11:01 ?        00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/host2.b.com.err --pid-file=/data/mysql/host2.b.com.pid --socket=/tmp/mysql.sock --port=3306
root       2577   1335  0 11:21 pts/0    00:00:00 grep --color=auto mysql
[root@host2 ~]# kill -9 2237
[root@host2 ~]# kill -9 2364

  6. 将所有备份数据文件拷贝回数据目录，注意权限一致；

    # 注意原先快照时，将pid文件，一起快照备份了，应该删除原来的pid文件，锁文件，再重启msyql进程
# 生产中，锁文件，pid文件都应该单独存储；
[root@host2 ~]# cp -ar /archdir/* /data/mysql/
[root@host2 ~]# ll /data/mysql/
total 122980
-rw-rw---- 1 mysql mysql    16384 Sep 27 10:41 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Sep 27 10:41 aria_log_control
drwx------ 2 mysql mysql     4096 Sep 27 11:01 hellodb
-rw-rw---- 1 mysql mysql     2543 Sep 27 11:01 host2.b.com.err
-rw-rw---- 1 mysql mysql        5 Sep 27 11:01 host2.b.com.pid
-rw-rw---- 1 mysql mysql      938 Sep 27 10:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Sep 27 11:01 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 27 10:41 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Sep 27 11:01 ibtmp1
-rw-rw---- 1 mysql mysql        0 Sep 27 11:01 multi-master.info
drwx------ 2 mysql mysql     4096 Sep 27 10:41 mysql
-rw-rw---- 1 mysql mysql    29277 Sep 27 10:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql     9007 Sep 27 11:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql      385 Sep 27 11:08 mysql-bin.000003
-rw-rw---- 1 mysql mysql       57 Sep 27 11:08 mysql-bin.index
drwx------ 2 mysql mysql     4096 Sep 27 10:41 performance_schema
drwx------ 2 mysql mysql     4096 Sep 27 10:41 test
[root@host2 ~]# service mysqld status
 ERROR! MariaDB is not running, but PID file exists
[root@host2 ~]# rm -rf /data/mysql/host2.b.com.pid 
[root@host2 ~]# service mysqld status
 ERROR! MariaDB is not running, but lock file (/var/lock/subsys/mysql) exists
[root@host2 ~]# rm -rf /var/lock/subsys/mysql

  7. 启动mysql进程，检查数据，此时已经恢复到快照时状态

    [root@host2 ~]# service mysqld status
 ERROR! MariaDB is not running
[root@host2 ~]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@host2 ~]# ss -nlt
State      Recv-Q Send-Q Local Address:Port                Peer Address:Port              
LISTEN     0      128                *:22                             *:*                  
LISTEN     0      100        127.0.0.1:25                             *:*                  
LISTEN     0      80                :::3306                          :::*                  
LISTEN     0      128               :::22                            :::*                  
LISTEN     0      100              ::1:25                            :::*                  
[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.25-MariaDB-log MariaDB Server
   
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
# 此时已经恢复到了快照时刻的数据；

  8. 重放备份时记录日志位置到最新一刻的日志位置的binlog，（binlog应单独存储）使得数据恢复到崩溃前一刻；

    [root@host2 ~]# cat binlog-position.txt 
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
mysql-bin.000003	385		
   
[root@host2 ~]# ll /tmp1/
total 52
-rw-rw---- 1 mysql mysql 29277 Sep 27 10:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql  9007 Sep 27 11:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql   515 Sep 27 11:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql    57 Sep 27 11:08 mysql-bin.index
   
# 根据快照之前日志位置的记录，从那一时刻开始向后重放mysql的binlog，
# 可以看到快照后的数据变化，创建了一个库，也已经恢复
[root@host2 ~]# mysqlbinlog /tmp1/mysql-bin.000003 --start-position=385 |mysql
[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.25-MariaDB-log MariaDB Server
   
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

mysqldump

介绍

​ 属于逻辑备份，备份的是一个文本文件，内容为数据库所有的库，表，表中数据的sql语句，不受存储引擎的限制，结合innodb引擎的事务和mvcc机制可以实现热备；

​ 适合数据量较小的时候，速度较慢，也由于备份的是sql，对应sql兼容的其他数据库，可以做不同数据库软件的迁移；

[root@host2 ~]# mysqldump --help
mysqldump  Ver 10.16 Distrib 10.2.25-MariaDB, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

-A 备份所有数据库，等价于--all-databases，备份中的语句包含create database 和 use database
--databases 后跟的是要备份的某几个库
database 后跟的是某库里的某几个表
-F 【锁表后，备份前会滚动一次日志文件】，使得备份后的数据修改从新一个日志文件的某位置开启，重放时，从改位置重放即可；【mysqldump会加全局读锁，结合innodb的mvcc的话就不用咯】【结合master-data使用，只滚动一次，不然每备份一个库就滚动一次】

-E,--events 备份所有的事件调度器
-R,--routines 备份所有存储过程和函数

--triggers备份表的触发器，默认选项
--default-charachter-set=utf8 指定字符集

--master-data=[1|2]，会在备份出的文件中加入change master语句，记录备份开始时的binlog日志位置，不用人为再执行语句进行记录，2选项会加入注释符，该语句不生效，1没有注释符；【前提是开启binlog】

  --single-transaction 使得整个备份过程在一个事务里，这样，在默认隔离等级，可重复读的情况下，备份中数据看到的是备份开始那一刻的快照，实现数据一致性；前提需是innodb引擎；
  
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does);
                      
 -f 忽略sql错误，继续执行
 -q 不查缓存，直接输出，加快备份
 --hexo-blob 十六进制格式存储二进制列，有bin，blob，bit数据类型时使用，避免乱码；

myisam

​ 存储引擎是myisam时，只能温备，因为备份前要加全局读锁，期间只读不能写，属于温备 --lock-all-tables

生产中建议备份选项：

mysqldump -uroot -A -F -E -R -x --master-data=1 --flush-privileges --triggers --defautl-character-set=utf8 --hex-blob > $BACK_DIR/fullbak_$BACKUP_TIME.sql

就是将innodb引擎时的--single-transaction换成了-x全局锁表，因为innodb有mvcc，支持可重复读事务等级，不需要锁表哦

innodb

​ 存储引擎是innodb时，--single-tracation，使得备份在一个事务内部，事务隔离等级可重复读，再加上mvcc的机制，实现了热备，备份期间可以继续读写，修改的是数据的多个副本，后续根据事务日志，binlog可以逐步重放数据，

生产中备份选项：

mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hexo-blob > $BACK_DIR/fullbak_$BACK_TIME.sql

# 全库备份，刷新日志，备份event scheduler，procedure，function，开始备份事务，记录binlog位置，刷新权限，备份触发器，字符集utf8，十六进制转存二进制避免乱码，
# 热备，不影响数据库读写，不影响业务，需要开启binlog
# 备份出文件，一般是变量表示的带时间，备份位置的.sql文件

备份：

1、连入mysql，可本地，可选程

[root@host2 ~]# mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > /root/`date +%F`.sql

# 设置需要的选项，用msyqldump命令直接备份出sql文件，sql文件可以防止网络文件系统上，异机存储；
# 导出的是备份开始前一刻的数据镜像，

2、查看导出的sql文件

-- MySQL dump 10.16  Distrib 10.2.25-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       10.2.25-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=385;
# change master，记录了备份开始前，滚动日志后，新日志文件的文件名和位置；

恢复：

1、模拟损坏

# 直接删除数据目录下所有文件，模拟数据损坏；
[root@host2 ~]# rm -rf /data/mysql/*

# pid文件因为单独存放，仍可以通过pid文件停止实例，
[root@host2 ~]# service mysql stop
Redirecting to /bin/systemctl stop mysql.service

# 但启动报错
[root@host2 ~]# service mysql start
Redirecting to /bin/systemctl start mysql.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

2、在一个新安装的数据库实例中恢复，（此处是同一个实例，重新初始化数据目录）

[root@host2 ~]# /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
# 启动后，可以看出，此为一个完全初始化数据库，没有任务业务数据
[root@host2 ~]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

3、用全备sql文件恢复，并重放binlog

# 实际是把sql文件中sql全部执行了一遍，生成和原库一样的数据；
# 看到，已经恢复到了快照一刻的数据；

[root@host2 ~]# mysql < 2020-09-27.sql 
[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |

# 根据change master记录的日志位置，开始重放binlog，可以看到快照后的数据也恢复
# 快照后，create database db2，创建了一个库；
[root@host2 ~]# mysqlbinlog --start-position=385 mysql-bin.000006 | mysql
[root@host2 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

xtrabackup（有问题，暂略）

暂定是xtrabackup和mariadb版本兼容问题，用mariadbackup试试；

介绍

percona公司开源的对innodb和xtardb存储引擎做物理层数据备份的工具，percona是mysql数据库的发行版公司，xtradb是基于innodb

https://www.percona.com/software/mysql-database/percona-xtrabackup

特点：

  • 热备
  • 速度快，可靠性高
  • 可压缩，节省存储空间和流量
  • 具有备份检验功能
  • 开源，free
  • 不和mysql server进程交互，直接备份物理数据层

安装

程序组成

2.2版本之前

innobackupex  		perl脚本
xtrabackup:c/c++ 	二进制程序
xbcrypt				加解密
xbstream			支持并发写的流文件

xtrabackup 用来备份innodb表，和xtradb表，
innnobackupex 脚本备份非innodb表，同时调用xtrabackup备份innodb表，该脚本是对xtrabackup的一层封装；

2.4之后

将脚本innobackupex功能集成到xtrabackup二进制程序中，
向下兼容，原脚本做成了二进制程序的软链接

安装方式

[root@host2 ~]# yum install -y percona-xtrabackup

[root@host2 ~]# rpm -ql !$
rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.3.6
/usr/share/doc/percona-xtrabackup-2.3.6/COPYING
/usr/share/doc/percona-xtrabackup-2.3.6/README
/usr/share/doc/percona-xtrabackup-2.3.6/VERSION
/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

[root@host2 ~]# file /usr/bin/innobackupex 
/usr/bin/innobackupex: symbolic link to `xtrabackup'
[root@host2 ~]# file /usr/bin/xtrabackup 
/usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=2afaaf839107c7e1e47a80f3c314481b888b5778, stripped
# 四个程序文件，以及对应man手册

# epel源
[root@host2 ~]# yum list percona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
Available Packages
percona-xtrabackup.x86_64                          2.3.6-1.el7                          epel

yum install -y percona-xtarbackup epel源

# 官方下载
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

xtrabackup备份过程解析

image-20200927192059049

全备与恢复

备份

  1. 在原数据库做全备；

恢复

  1. 在要恢复的主机（一般是其他主机上的新的实例）
    1. 预准备阶段：回滚未完成的事务，已完成事务未应用到数据上的，将数据做对应修改
    2. 复制到数据库目录：数据库目录要空，mysql进程不启动
    3. 复制后的数据文件，修改属性
    4. 启动mysql进程

全备、增量备份与恢复

备份

  1. 首次全备
  2. 模拟第一次修改数据，
  3. 第一次增量备份
  4. 模拟第二次修改数据
  5. 第二次增量备份
  6. 将全备、增量1、增量2的3次备份的三个文件都拷贝要目标主机，

还原

  1. 预准备阶段，
  2. 合并第一次增量备份到全备
  3. 合并第二次增量备份到全备
  4. 将全部合并完成的数据文件，拷贝到数据目录，改权限，
  5. 修改属主为myslq
  6. 启动新实例mysql
updatedupdated2021-03-092021-03-09

相关文章：

加载评论