当前位置: 萬仟网 > IT编程>数据库>Mysql > MySQL数据库备份和恢复

MySQL数据库备份和恢复

2019年12月01日 15:13  | 萬仟网IT编程  | 我要评论

mysql数据库备份和恢复

在这里插入图片描述

备份恢复概述

为什么要备份

灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

备份注意要点

能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据

还原要点

做还原测试,用于测试备份的可用性
还原演练

备份类型:

* 完全备份,部分备份
    完全备份:整个数据集
    部分备份:只备份数据子集,如部分库或表
* 完全备份、增量备份、差异备份
    增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
    差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
* 注意:二进制日志文件不应该与数据文件放在同一磁盘
* 冷、温、热备份
    冷备:读、写操作均不可进行
    温备:读操作可执行;但写操作不可执行
    热备:读、写操作均可执行
        myisam:温备,不支持热备
        innodb:都支持
* 物理和逻辑备份
    物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    逻辑备份:从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

备份时需要考虑的因素

温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长

备份什么

数据
二进制日志、innodb的事务日志
程序代码(存储过程、函数、触发器、事件调度器)
服务器的配置文件

备份工具

cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
lvm的快照:先加锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
mysqldump:逻辑备份工具,适用所有存储引擎,温备;支持完全或部分备份;对innodb存储引擎支持热备,结合binlog的增量备份
xtrabackup:由percona提供支持对innodb做热备(物理备份)的工具,支持完全备份、增量备份
mariadb backup: 从mariadb 10.1.26开始集成,基于percona xtrabackup 2.3.8实现
mysqlbackup:热备份, mysql enterprise edition组件
mysqlhotcopy:perl 语言实现,几乎冷备,仅适用于myisam存储引擎,使用lock tables、
flush tables和cp或scp来快速备份数据库

冷备份 cp tar

  1. 确定数据库是否关闭,没关闭,关闭掉。(适合于可以停止访问的公司类型)
[root@centos7 ~]#ss -ntl    # 确定3306端口关闭
state      recv-q send-q    local address:port                   peer address:port              
listen     0      128                   *:111                               *:*                  
listen     0      128                   *:6000                              *:*                  
listen     0      5         192.168.122.1:53                                *:*                  
listen     0      128                   *:22                                *:*                  
listen     0      128           127.0.0.1:631                               *:*                  
listen     0      100           127.0.0.1:25                                *:*                  
listen     0      128           127.0.0.1:6010                              *:*                  
listen     0      128           127.0.0.1:6011                              *:*                  
listen     0      128           127.0.0.1:6012                              *:*                  
listen     0      128                  :::111                              :::*                  
listen     0      128                  :::6000                             :::*                  
listen     0      128                  :::22                               :::*                  
listen     0      128                 ::1:631                              :::*                  
listen     0      100                 ::1:25                               :::*                  
listen     0      128                 ::1:6010                             :::*                  
listen     0      128                 ::1:6011                             :::*                  
listen     0      128                 ::1:6012                             :::*                  

# 没关闭使用这条命令关闭
[root@centos7 ~]#systemctl stop mariadb

mariadb [(none)]> show databases;  # 备份前建立一个数据库或表用以测试是否可以还原成功
+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
  1. 打包并压缩mysql目录
[root@centos7 data]#tar -zcvf mysql-$(date +%y%m%d-%h%m).tar.gz /data/mysql/*
[root@centos7 data]#ll   # 打包成带时间格式
total 1324
drwxr-xr-x 4 mysql root     336 nov 29 20:04 mysql
-rw-r--r-- 1 root  root 1355106 nov 29 20:10 mysql-20191130-1144.tar.gz
  1. 发送到另一台服务器(最好服务器上的数据库版本和以前的相同)
[root@centos7 data]#scp mysql-20191130-1144.tar.gz 192.168.39.57:/data/
the authenticity of host '192.168.39.57 (192.168.39.57)' can't be established.
ecdsa key fingerprint is sha256:vyjfahhade2ci7v5wrkzj6idukqfzozpmny56d9qkfi.
ecdsa key fingerprint is md5:22:72:17:9a:a8:93:1a:02:d8:09:17:f4:85:fe:b3:f5.
are you sure you want to continue connecting (yes/no)? yes     
warning: permanently added '192.168.39.57' (ecdsa) to the list of known hosts.
root@192.168.39.57's password: 
mysql-20191130-1144.tar.gz                       100% 1323kb 100.2mb/s   00:00    

[root@centos7 data]#ls    # 查看一下
mysql-20191130-1144.tar.gz

[root@centos7 ~]# mkdir /backup
[root@centos7 ~]# tar zxvf /data/mysql-20191130-1227.tar.gz -c /backup

[root@centos7 backup]# tree   # 查看一下文件是否拷贝过来了
.
└── data
    └── mysql
        ├── aria_log.00000001
        ├── aria_log_control
        ├── db1
        │   └── db.opt
        ├── ib_buffer_pool
        ├── ibdata1
        ├── ib_logfile0
        ├── ib_logfile1
        ├── multi-master.info
        ├── mysql
        │   ├── columns_priv.frm
        │   ├── columns_priv.myd
        │   ├── columns_priv.myi
        │   ├── column_stats.frm
        │   ├── column_stats.myd
        │   ├── column_stats.myi
        │   ├── db.frm
        │   ├── db.myd
        │   ├── db.myi
        │   ├── db.opt
        │   ├── event.frm
        │   ├── event.myd




[root@centos7 mysql]# ll   # 查看一下mysql的目录
total 110668
-rw-rw---- 1 987 981    16384 nov 30 11:44 aria_log.00000001
-rw-rw---- 1 987 981       52 nov 30 11:44 aria_log_control
drwx------ 2 987 981       20 nov 30 11:43 db1
-rw-rw---- 1 987 981      942 nov 30 11:44 ib_buffer_pool
-rw-rw---- 1 987 981 12582912 nov 30 11:44 ibdata1
-rw-rw---- 1 987 981 50331648 nov 30 11:44 ib_logfile0
-rw-rw---- 1 987 981 50331648 nov 19 16:41 ib_logfile1
-rw-rw---- 1 987 981        0 nov 19 16:57 multi-master.info
drwx------ 2 987 981     4096 nov 19 16:41 mysql
-rw-rw---- 1 987 981    29310 nov 19 16:41 mysql-bin.000001
-rw-rw---- 1 987 981     1685 nov 30 11:35 mysql-bin.000002
-rw-rw---- 1 987 981      492 nov 30 11:44 mysql-bin.000003
-rw-rw---- 1 987 981       57 nov 30 11:41 mysql-bin.index
-rw-rw---- 1 987 981        7 nov 30 11:44 mysql-bin.state
drwx------ 2 987 981       20 nov 19 16:41 performance_schema


[root@centos7 mysql]# mv * /var/lib/mysql/  # 移动到原有目录下

[root@centos7 mysql]# systemctl start mariadb.service  # 启动服务

[root@centos7 mysql]# mysql  # 登录数据库

mariadb [(none)]> show databases;  # 之前创建的数据库还在代表还原成功
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysqldump备份工具

  1. 逻辑备份工具:
    mysqldump, mydumper, phpmyadmin
    schema和数据存储在一起、巨大的sql语句、单个巨大的备份文件
    mysqldump:是mysql的客户端命令,通过mysql协议连接至mysql服务器进行备份
  1. 命令格式:
mysqldump [options] database [tables]
mysqldump [options] –b db1 [db2 db3...]
mysqldump [options] –a [options]
  1. mysqldump参考:
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

  2. mysqldump 常见通用选项:
-a, --all-databases #

-b, --databases db_name… #指定备份的数据库,包括create database语句
-e, --events:#备份相关的所有event scheduler
-r, --routines:#备份所有存储过程和自定义函数
--triggers:#备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8  #指定字符集
--master-data[=#]:  #此选项须启用二进制日志
#1:所备份的数据之前加一条记录为change master to语句,非注释,不指定#,默认为1
#2:记录为注释的change master to语句
#此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启-- single-transaction)
-f, --flush-logs #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文 件,配合-a 或 -b 
选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和-- single-transaction或-x,--master-data   一起使用实现,此时只刷新一次二进制日志
--compact      #去掉注释,适合调试,生产不使用
-d, --no-data      #只备份表结构
-t, --no-create-info   #只备份数据,不备份create table
-n,--no-create-db  #不备份create database,可被-a或-b覆盖
--flush-privileges     #备份mysql或相关时需要使用
-f, --force            #忽略sql错误,继续执行
--hex-blob #使用十六进制符号转储二进制列,当有包括binary, varbinary,blob,bit的数 据类型的列时使用,避免乱码
-q, --quick            #不缓存查询,直接输出,加快备份速度
  1. mysqldump命令用法
[root@centos7 ~]# mysqldump --help  # 有三种格式
mysqldump  ver 10.14 distrib 5.5.60-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]
  • 第一种用法(对某一个数据库或者某个数据库的特定表做打印,之后在导入到文件里,只是打印到屏幕里所以要导出)
[root@centos7 ~]# mysqldump -uroo hellodb students  # 备份hellodb数据库的students表   (要指定用户和密码,没设置密码不用指定)不加表名就备份数据库。
-- mysql dump 10.14  distrib 5.5.60-mariadb, for linux (x86_64)
--
-- host: localhost    database: hellodb
-- ------------------------------------------------------
-- server version   5.5.60-mariadb

/*!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 */;
mysqldump: got error: 1044: "access denied for user ''@'localhost' to database 'hellodb'" when selecting the database
  • 删除库做实验
[root@centos7 ~]# mysqldump hellodb > /data/hellodb.sql  # 先导出数据库

mariadb [(none)]> drop database hellodb; # 删除库
query ok, 7 rows affected (0.00 sec)

mariadb [(none)]> show databases;  # 查看已经删除掉了
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mariadb [(none)]> create database hello; # 创建一个数据库名字不一样都可以
query ok, 1 row affected (0.00 sec)

mariadb [(none)]> use hello   # 进入创建的数据库
database changed
mariadb [hello]> source /data/hellodb.sql  # 把sql脚本读入到这个库里
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)


mariadb [hello]> show tables;  # 查看表,表都在。
+-----------------+
| tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)
这种方法可以恢复,但是数据库的格式和字符集都是默认的,这种方法不建议用。
  • 第二种方法(推荐使用方法)
    -b 挑选指定的数据库做备份
[root@centos7 ~]# mysqldump -b hellodb mysql > /data/hellodb_mysql.sql # 导出生成sql脚本,最好加上时间格式。
[root@centos7 ~]# vim /data/hellodb_mysql.sql   # 查看一下这个文件
# 这个sql脚本里面有这一行是用来创建库和定义库的格式的,加上-b才会有这一行。
create database /*!32312 if not exists*/ `hellodb` /*!40100 default character set utf8 */;

use `hellodb`;

mariadb [(none)]> show create database hellodb; # 查看hellodb数据库的字符集和格式
+----------+------------------------------------------------------------------+
| database | create database                                                  |
+----------+------------------------------------------------------------------+
| hellodb  | create database `hellodb` /*!40100 default character set utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 发送到远程主机做测试
[root@centos7 ~]# scp /data/hellodb_mysql.sql 192.168.39.27:/root
root@192.168.39.27's password: 
hellodb_mysql.sql                                100%  509kb  85.6mb/s   00:00  
  • 导入sql脚本
mariadb [(none)]> show databases;  # 远程主机的数据库
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mariadb [(none)]> source /root/hellodb_mysql.sql  # 导入sql脚本

mariadb [mysql]> show databases; # 查看数据库生成
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mariadb [mysql]> show create database hellodb;  # 查看hellodb字符集,是和原本一摸一样的。
+----------+------------------------------------------------------------------+
| database | create database                                                  |
+----------+------------------------------------------------------------------+
| hellodb  | create database `hellodb` /*!40100 default character set utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 第三种方法 (所有的数据库做备份) (完全备份)
[root@centos7 ~]# mysqldump -a |gzip > /data/all.sql.gz # 备份连带压缩一起执行
[root@centos7 ~]# ll /data/ 
total 140
-rw-r--r-- 1 root root 140945 nov 30 16:00 all.sql.gz
# 还原下面会做现在就解释一下。

模拟数据库崩溃,最大限度还原数据

[root@centos7 ~]# mysqldump -a --master-data=2 |gzip > /data/all_'date +%f'.sql.gz
mysqldump: error: binlogging on server not active
# 上面不成功是因为二进制日志没有启用
  • 启用二进制日志
# 事先创建好二进制日志存放的路径
[root@centos7 ~]# chown -r mysql:mysql /data/mysql/  # 创建完路径记得更改权限
[root@centos7 ~]# vim /etc/my.cnf   # 修改配置文件
[mysqld]
log-bin=/data/mysql/bin_log   # 指定二进制日志存放路径(最好和数据库是分开的) 最后的是指定日志的前缀。
[root@centos7 ~]# systemctl restart mariadb.service  # 重启服务
  • 完全备份数据库
[root@centos7 ~]# mysqldump -a --master-data=2 |gzip > /data/all_`ate +%f`.sql.gz
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root  root  141043 nov 30 16:29 all_2019-11-30.sql.gz
  • 模拟添加一条记录,创建一个账号。
mariadb [(none)]> use hellodb
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
mariadb [hellodb]> insert students (name,age,gender)value('a',20,'m');
query ok, 1 row affected (0.00 sec)

mariadb [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| stuid | name          | age | gender | classid | teacherid |
+-------+---------------+-----+--------+---------+-----------+
|     1 | shi zhongyu   |  22 | m      |       2 |         3 |
|     2 | shi potian    |  22 | m      |       1 |         7 |
|     3 | xie yanke     |  53 | m      |       2 |        16 |
|     4 | ding dian     |  32 | m      |       4 |         4 |
|     5 | yu yutong     |  26 | m      |       3 |         1 |
|     6 | shi qing      |  46 | m      |       5 |      null |
|     7 | xi ren        |  19 | f      |       3 |      null |
|     8 | lin daiyu     |  17 | f      |       7 |      null |
|     9 | ren yingying  |  20 | f      |       6 |      null |
|    10 | yue lingshan  |  19 | f      |       3 |      null |
|    11 | yuan chengzhi |  23 | m      |       6 |      null |
|    12 | wen qingqing  |  19 | f      |       1 |      null |
|    13 | tian boguang  |  33 | m      |       2 |      null |
|    14 | lu wushuang   |  17 | f      |       3 |      null |
|    15 | duan yu       |  19 | m      |       4 |      null |
|    16 | xu zhu        |  21 | m      |       1 |      null |
|    17 | lin chong     |  25 | m      |       4 |      null |
|    18 | hua rong      |  23 | m      |       7 |      null |
|    19 | xue baochai   |  18 | f      |       6 |      null |
|    20 | diao chan     |  19 | f      |       7 |      null |
|    21 | huang yueying |  22 | f      |       6 |      null |
|    22 | xiao qiao     |  20 | f      |       1 |      null |
|    23 | ma chao       |  23 | m      |       4 |      null |
|    24 | xu xian       |  27 | m      |    null |      null |
|    25 | sun dasheng   | 100 | m      |    null |      null |
|    26 | a             |  20 | m      |    null |      null |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

mariadb [hellodb]> grant all on hellodb.* to test@'192.168.39.%' identified by 'centos'; # 创建一个账号
query ok, 0 rows affected (0.00 sec)
  • 假设数据库崩溃了(删之前确定二进制日志独立出来了)
[root@centos7 ~]# rm -rf /var/lib/mysql/* # 删除数据库
[root@centos7 ~]# mysql   # 登录不上了
error 2002 (hy000): can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock' (2)
  • 查看数据库和二进制文件
[root@centos7 ~]# ll /var/lib/mysql/  # 数据库没有了
total 0
[root@centos7 ~]# ll /data/mysql/  # 二进制文件独立出来了
total 12
-rw-rw---- 1 mysql mysql 264 nov 30 16:26 bin_log.000001
-rw-rw---- 1 mysql mysql 637 nov 30 16:35 bin_log.000002
-rw-rw---- 1 mysql mysql  54 nov 30 16:26 bin_log.index
  • 重新生成数据库
[root@centos7 ~]# systemctl restart mariadb.service  # 重启服务就可以

# 新版本在重新启用服务的时候不会重新启用数据库
[root@centos7 ~]# mysql_install_db --user=mysql # 使用这条命令在新版上面生成数据库

[root@centos7 ~]# mysql
welcome to the mariadb monitor.  commands end with ; or \g.
your mariadb connection id is 2
server version: 5.5.60-mariadb 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)]> 

[root@centos7 ~]# ll /var/lib/mysql/  # 查看库是否生成
total 37852
-rw-rw---- 1 mysql mysql    16384 nov 30 16:42 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 nov 30 16:42 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 nov 30 16:42 ibdata1
-rw-rw---- 1 mysql mysql  5242880 nov 30 16:42 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 nov 30 16:42 ib_logfile1
drwx------ 2 mysql mysql     4096 nov 30 16:42 mysql
srwxrwxrwx 1 mysql mysql        0 nov 30 16:42 mysql.sock
drwx------ 2 mysql mysql     4096 nov 30 16:42 performance_schema
drwx------ 2 mysql mysql        6 nov 30 16:42 test
  • 新的数据库里之前的哪些库和表是没有的
mariadb [(none)]> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
  • 解压缩完全备份包
[root@centos7 data]# ls
all_2019-11-30.sql.gz  mysql
[root@centos7 data]# gzip -d all_2019-11-30.sql.gz 
[root@centos7 data]# ls
all_2019-11-30.sql  mysql
  • 临时停用二进制日志(不停用的话会有生成一份一模一样的二进制所以最好关闭)
mariadb [(none)]> set sql_log_bin=0;
query ok, 0 rows affected (0.00 sec)
  • 还原数据库的完全备份,当前会话使用。(但是在完全备份到数据库崩溃这段时间的数据这样是还原不了的)
mariadb [(none)]> source /data/all_2019-11-30.sql
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)
.......(省略)

mariadb [test]> show databases;  # 完全备份之前建立的库是在的
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| hello              |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mariadb [test]> use hellodb;
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
mariadb [hellodb]> select * from students;  # 但是在这个表上添加的记录没有了
+-------+---------------+-----+--------+---------+-----------+
| stuid | name          | age | gender | classid | teacherid |
+-------+---------------+-----+--------+---------+-----------+
|     1 | shi zhongyu   |  22 | m      |       2 |         3 |
|     2 | shi potian    |  22 | m      |       1 |         7 |
|     3 | xie yanke     |  53 | m      |       2 |        16 |
|     4 | ding dian     |  32 | m      |       4 |         4 |
|     5 | yu yutong     |  26 | m      |       3 |         1 |
|     6 | shi qing      |  46 | m      |       5 |      null |
|     7 | xi ren        |  19 | f      |       3 |      null |
|     8 | lin daiyu     |  17 | f      |       7 |      null |
|     9 | ren yingying  |  20 | f      |       6 |      null |
|    10 | yue lingshan  |  19 | f      |       3 |      null |
|    11 | yuan chengzhi |  23 | m      |       6 |      null |
|    12 | wen qingqing  |  19 | f      |       1 |      null |
|    13 | tian boguang  |  33 | m      |       2 |      null |
|    14 | lu wushuang   |  17 | f      |       3 |      null |
|    15 | duan yu       |  19 | m      |       4 |      null |
|    16 | xu zhu        |  21 | m      |       1 |      null |
|    17 | lin chong     |  25 | m      |       4 |      null |
|    18 | hua rong      |  23 | m      |       7 |      null |
|    19 | xue baochai   |  18 | f      |       6 |      null |
|    20 | diao chan     |  19 | f      |       7 |      null |
|    21 | huang yueying |  22 | f      |       6 |      null |
|    22 | xiao qiao     |  20 | f      |       1 |      null |
|    23 | ma chao       |  23 | m      |       4 |      null |
|    24 | xu xian       |  27 | m      |    null |      null |
|    25 | sun dasheng   | 100 | m      |    null |      null |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
  • 查看sql脚本来确定丢失的二进制数据位置
[root@centos7 data]# grep '^-- change master to' /data/all_2019-11-30.sql  # 查找以这个开头的行
-- change master to master_log_file='bin_log.000002', master_log_pos=245;  # 最后的数字确定丢失起点位置
  • 以二进制日志恢复丢失数据
[root@centos7 mysql]# ll
total 1064
-rw-rw---- 1 mysql mysql     264 nov 30 16:26 bin_log.000001
-rw-rw---- 1 mysql mysql     656 nov 30 16:42 bin_log.000002 # 要这个文件245之后的
-rw-rw---- 1 mysql mysql   30373 nov 30 16:42 bin_log.000003 # 和这个文件全部内容
-rw-rw---- 1 mysql mysql 1038814 nov 30 16:42 bin_log.000004
-rw-rw---- 1 mysql mysql     245 nov 30 16:42 bin_log.000005
-rw-rw---- 1 mysql mysql     135 nov 30 16:42 bin_log.index

[root@centos7 mysql]# mysqlbinlog bin_log.000002 --start-position=245 > inc.sql
[root@centos7 mysql]# mysqlbinlog bin_log.000003 >> inc.sql
# 顺序不要错了

[root@centos7 mysql]# mysql
welcome to the mariadb monitor.  commands end with ; or \g.
your mariadb connection id is 4
server version: 5.5.60-mariadb 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)]> use hellodb;
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
mariadb [hellodb]> source /data/mysql/inc.sql  # 导入二进制脚本

mariadb [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| stuid | name          | age | gender | classid | teacherid |
+-------+---------------+-----+--------+---------+-----------+
|     1 | shi zhongyu   |  22 | m      |       2 |         3 |
|     2 | shi potian    |  22 | m      |       1 |         7 |
|     3 | xie yanke     |  53 | m      |       2 |        16 |
|     4 | ding dian     |  32 | m      |       4 |         4 |
|     5 | yu yutong     |  26 | m      |       3 |         1 |
|     6 | shi qing      |  46 | m      |       5 |      null |
|     7 | xi ren        |  19 | f      |       3 |      null |
|     8 | lin daiyu     |  17 | f      |       7 |      null |
|     9 | ren yingying  |  20 | f      |       6 |      null |
|    10 | yue lingshan  |  19 | f      |       3 |      null |
|    11 | yuan chengzhi |  23 | m      |       6 |      null |
|    12 | wen qingqing  |  19 | f      |       1 |      null |
|    13 | tian boguang  |  33 | m      |       2 |      null |
|    14 | lu wushuang   |  17 | f      |       3 |      null |
|    15 | duan yu       |  19 | m      |       4 |      null |
|    16 | xu zhu        |  21 | m      |       1 |      null |
|    17 | lin chong     |  25 | m      |       4 |      null |
|    18 | hua rong      |  23 | m      |       7 |      null |
|    19 | xue baochai   |  18 | f      |       6 |      null |
|    20 | diao chan     |  19 | f      |       7 |      null |
|    21 | huang yueying |  22 | f      |       6 |      null |
|    22 | xiao qiao     |  20 | f      |       1 |      null |
|    23 | ma chao       |  23 | m      |       4 |      null |
|    24 | xu xian       |  27 | m      |    null |      null |
|    25 | sun dasheng   | 100 | m      |    null |      null |
|    26 | a             |  20 | m      |    null |      null |  # 添加的a记录回来了
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

mariadb [hellodb]> select user,host,password from mysql.user;
+------+---------------------+-------------------------------------------+
| user | host                | password                                  |
+------+---------------------+-------------------------------------------+
| root | localhost           |                                           |
| root | centos7.localdomain |                                           |
| root | 127.0.0.1           |                                           |
| root | ::1                 |                                           |
|      | localhost           |                                           |
|      | centos7.localdomain |                                           |
| test | 192.168.39.%        | *128977e278358ff80a246b5046f51043a2b1fced | # 添加的账户也还原回来了
+------+---------------------+-------------------------------------------+
7 rows in set (0.00 sec)
  • 最后在启用二进制日志
mariadb [hellodb]> set sql_log_bin=1;
query ok, 0 rows affected (0.00 sec)
还原的时候,数据库必须是不能访问的状态,数据库的二进制日志就是备份。

mysqldump的分库备份

  1. 先查看库名
mariadb [(none)]> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| hello              |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)
  1. 挑出数据库备份(用grep)
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -ev '^(database|information_schema|performance_schema)$'
db1
hello
hellodb
mysql
test
  1. 使用while循环来备份
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -ev '^(database|information_schema|performance_schema)$'|while read db;do mysqldump -b $db|gzip > /data/$db.sql.gz;done
[root@centos7 ~]# ll /data/
total 156
-rw-r--r-- 1 root  root     516 nov 30 17:31 db1.sql.gz
-rw-r--r-- 1 root  root    1898 nov 30 17:31 hellodb.sql.gz
-rw-r--r-- 1 root  root    1892 nov 30 17:31 hello.sql.gz
-rw-r--r-- 1 root  root  139603 nov 30 17:31 mysql.sql.gz
-rw-r--r-- 1 root  root     516 nov 30 17:31 test.sql.gz
  1. 使用for循环做备份
[root@centos7 ~]# for db in `mysql -uroot -e 'show databases'|grep -ev '^(database|information_schema|performance_schema)$'`;do mysqldump -b $db|gzip > /data/$db.sql.gz;done
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root  root     516 nov 30 17:34 test.sql.gz
-rw-r--r-- 1 root  root  139603 nov 30 17:34 mysql.sql.gz
-rw-r--r-- 1 root  root    1898 nov 30 17:34 hellodb.sql.gz
-rw-r--r-- 1 root  root    1892 nov 30 17:34 hello.sql.gz
-rw-r--r-- 1 root  root     516 nov 30 17:34 db1.sql.gz
  1. 使用sed来备份数据
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -ev '^(database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -b \1 |gzip > /data/\1.sql.gz#p'|bash
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root  root     517 nov 30 17:37 test.sql.gz
-rw-r--r-- 1 root  root  139603 nov 30 17:37 mysql.sql.gz
-rw-r--r-- 1 root  root    1898 nov 30 17:37 hellodb.sql.gz
-rw-r--r-- 1 root  root    1892 nov 30 17:37 hello.sql.gz
-rw-r--r-- 1 root  root     516 nov 30 17:37 db1.sql.gz
  1. 用sed替换grep分库备份
[root@centos7 ~]# mysql -uroot -e 'show databases'|sed -rn '/^(database|information_schema|performance_schema)$/!s#(.*)#mysqldump -b \1 |gzip > /data/\1.sql.gz#p' |bash
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root  root     516 nov 30 17:43 test.sql.gz
-rw-r--r-- 1 root  root  139600 nov 30 17:43 mysql.sql.gz
-rw-r--r-- 1 root  root    1898 nov 30 17:43 hellodb.sql.gz
-rw-r--r-- 1 root  root    1892 nov 30 17:43 hello.sql.gz
-rw-r--r-- 1 root  root     516 nov 30 17:43 db1.sql.gz
分库备份可以编写成脚本,配合计划任务使用。(最好备份的包加上时间格式要不容易覆盖掉)

mysqldump的myisam存储引擎相关的备份选项:

myisam不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--
lock-tables选项会关闭此选项功能
#注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--
skip-lock-tables选项可禁用,对备份myisam的多个库,可能会造成数据不一致
#注:以上选项对innodb表一样生效,实现温备,但不推荐使用

mysqldump的innodb存储引擎相关的备份选项:

innodb 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用
--single-transaction
#此选项innodb中推荐使用,不适用myisam,此选项会开始备份前,先执行start transaction指令开启
事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
(目前只有innodb可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:alter table,drop
table,rename table,truncate table,此选项和--lock-tables(此选项隐含提交挂起的事务)选
项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用

生产环境实战备份策略

  1. innodb建议备份策略
mysqldump –uroot -p –a –f –e –r --single-transaction --master-data=1 --flushprivileges
--triggers --default-character-set=utf8 --hex-blob
>${backup}/fullbak_${backup_time}.sql
  1. myisam建议备份策略
mysqldump –uroot -p –a –f –e –r –x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${backup}/fullbak_${backup_time}.sql

范例:完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
[root@centos8 ~]#mysqldump -uroot -p -a -f --single-transaction --master-data=2
|gzip > /backup/all-`date +%f`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
mariadb [(none)]> set sql_log_bin=off;
mariadb [(none)]> source /backup/all-2019-11-27.sql
mariadb [(none)]> set sql_log_bin=on;

范例:mysqldump 和二进制日志结合实现增量备份

[root@centos8 ~]#mysqldump -uroot -p -a -f --single-transaction --master-data=2
|gzip > /backup/all-`date +%f`.sql.gz
#观察备份文件中的二进制文件和位置,将之后的二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup
[root@centos8 ~]#mysqlbinlog --start-position=389 /backup/mariadb-bin.000003 >
/backup/inc.sql

将误删除了的某个表进行还原

  1. 先完全备份一份
[root@centos7 ~]# mysqldump -a -f --single-transaction --master-data=2 |gzip > /backup/allbackup_`date +%f_%t`.sql.gz
[root@centos7 ~]# ll /backup/
total 140
-rw-r--r-- 1 root root 141141 nov 30 18:03 allbackup_2019-11-30_18:03:08.sql.gz
  1. 在完全备份之后最一些记录变化
mariadb [db1]> create table test ( id int unsigned auto_increment primary key,name varchar(10) not null,mobile char(11) not null );  # 创建一张表
query ok, 0 rows affected (0.00 sec)

mariadb [db1]> show tables;  # 查看库里的所有表
+---------------+
| tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mariadb [db1]> insert test (name) values('rose'); # 添加记录
query ok, 1 row affected, 1 warning (0.00 sec)

mariadb [db1]> insert test (name) values('jack'); # 添加记录
query ok, 1 row affected, 1 warning (0.00 sec)

mariadb [db1]> select * from test; # 查看添加的记录
+----+------+--------+
| id | name | mobile |
+----+------+--------+
|  1 | rose |        |
|  2 | jack |        |
+----+------+--------+
2 rows in set (0.01 sec)
  1. 误删除表
mariadb [db1]> drop table test;
query ok, 0 rows affected (0.01 sec)

mariadb [db1]> show tables;
empty set (0.00 sec)
  1. 解压缩
[root@centos7 ~]# cd /backup/
[root@centos7 backup]# ll
total 140
-rw-r--r-- 1 root root 141141 nov 30 18:03 allbackup_2019-11-30_18:03:08.sql.gz
[root@centos7 backup]# gzip -d allbackup_2019-11-30_18\:03\:08.sql.gz 
[root@centos7 backup]# ll
total 520
-rw-r--r-- 1 root root 528632 nov 30 18:03 allbackup_2019-11-30_18:03:08.sql
  1. 找到上次完全备份的结束位置
[root@centos7 backup]# grep '\-\- change master to' /backup/allbackup_2019-11-30_18\:03\:08.sql 
-- change master to master_log_file='bin_log.000006', master_log_pos=245;
[root@centos7 backup]# 
  1. 导出245之后的所有二进制日志(增量备份)
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006 > /backup/inc.sql
[root@centos7 backup]# ll /backup/
total 524
-rw-r--r-- 1 root root 528632 nov 30 18:03 allbackup_2019-11-30_18:03:08.sql
-rw-r--r-- 1 root root   2791 nov 30 18:20 inc.sql
  1. 查看执行过的drop命令的时间和位置
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -i drop
drop table `test` /* generated by server */
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -c3 -i drop
# at 856   # 这个位置
#191130 18:07:32 server id 1  end_log_pos 961   query   thread_id=35    exec_time=error_code=0
set timestamp=1575108452/*!*/;
drop table `test` /* generated by server */
/*!*/;
delimiter ;
# end of log file
  1. 然后打开导出来的文件删掉或注释掉误操作
[root@centos7 backup]# vim /backup/inc.sql 
# at 856
#191130 18:07:32 server id 1  end_log_pos 961   query   thread_id=35    exec_time=0     error_code=0
set timestamp=1575108452/*!*/;
#drop table `test` /* generated by server */   # 这行注释掉其他的可以不用管
/*!*/;
delimiter ;
# end of log file
  1. 删除数据库(可以在一个新的主机上做这个实验也可以)
[root@centos7 backup]# rm -rf /var/lib/mysql/*  # 删除数据库
[root@centos7 backup]# systemctl restart mariadb.service  # 重启服务生成数据库
[root@centos7 backup]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 nov 30 18:33 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 nov 30 18:33 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 nov 30 18:33 ibdata1
-rw-rw---- 1 mysql mysql  5242880 nov 30 18:33 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 nov 30 18:33 ib_logfile1
drwx------ 2 mysql mysql     4096 nov 30 18:33 mysql
srwxrwxrwx 1 mysql mysql        0 nov 30 18:33 mysql.sock
drwx------ 2 mysql mysql     4096 nov 30 18:33 performance_schema
drwx------ 2 mysql mysql        6 nov 30 18:33 test
  1. 登录数据库关闭二进制日志
[root@centos7 backup]# mysql
welcome to the mariadb monitor.  commands end with ; or \g.
your mariadb connection id is 2
server version: 5.5.60-mariadb 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)]> set sql_log_bin=0;
query ok, 0 rows affected (0.00 sec)
  1. 还原完全备份
mariadb [(none)]> source /backup/allbackup_2019-11-30_18:03:08.sql
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)
.....(省略)

mariadb [test]> show databases; # 查看数据库是否还原
+--------------------+
| database           |
+--------------------+
| information_schema |
| db1                |
| hello              |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)
  1. 再还原增量备份(注意是修改过误操作的那个文件)
mariadb [test]> source /backup/inc.sql
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

database changed
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

charset changed
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 1 row affected, 1 warning (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.01 sec)

query ok, 1 row affected, 1 warning (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

error at line 65 in file: '/backup/inc.sql': no query specified  # 这里没有执行就是我注释掉的drop命令(报错不用管)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)
  1. 开启二进制日志
mariadb [db1]> set sql_log_bin=1; # 确保数据还原完毕再打开
query ok, 0 rows affected (0.00 sec)
  1. 查看数据是否还原
mariadb [db1]> show tables;  # 表没有被删掉
+---------------+
| tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mariadb [db1]> select * from test; # 添加的记录也在
+----+------+--------+
| id | name | mobile |
+----+------+--------+
|  1 | rose |        |
|  2 | jack |        |
+----+------+--------+
2 rows in set (0.00 sec)
做以上实验确保你的二进制日志独立于数据库之外。

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com