MySQL数据库备份和恢复
程序员文章站
2022-11-07 16:36:44
MySQL数据库备份和恢复 [toc] 备份恢复概述 为什么要备份 灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景 备份注意要点 能容忍最多丢失多少数据 恢复数据需要在多长时间内完成 需要恢复哪些数据 还原要点 做还原测试，用于测试备份的可用性 还原演练 备份类型： 完全 ......
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
- 确定数据库是否关闭，没关闭，关闭掉。（适合于可以停止访问的公司类型）
[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)
- 打包并压缩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
- 发送到另一台服务器(最好服务器上的数据库版本和以前的相同)
[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备份工具
- 逻辑备份工具：
mysqldump, mydumper, phpmyadmin
schema和数据存储在一起、巨大的sql语句、单个巨大的备份文件
mysqldump:是mysql的客户端命令，通过mysql协议连接至mysql服务器进行备份
- 命令格式:
mysqldump [options] database [tables] mysqldump [options] –b db1 [db2 db3...] mysqldump [options] –a [options]
mysqldump参考：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html- 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 #不缓存查询，直接输出，加快备份速度
- 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的分库备份
- 先查看库名
mariadb [(none)]> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | hello | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec)
- 挑出数据库备份(用grep)
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -ev '^(database|information_schema|performance_schema)$' db1 hello hellodb mysql test
- 使用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
- 使用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
- 使用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
- 用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结合一起使用
生产环境实战备份策略
- 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
- 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
将误删除了的某个表进行还原
- 先完全备份一份
[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
- 在完全备份之后最一些记录变化
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)
- 误删除表
mariadb [db1]> drop table test; query ok, 0 rows affected (0.01 sec) mariadb [db1]> show tables; empty set (0.00 sec)
- 解压缩
[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
- 找到上次完全备份的结束位置
[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]#
- 导出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
- 查看执行过的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
- 然后打开导出来的文件删掉或注释掉误操作
[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
- 删除数据库（可以在一个新的主机上做这个实验也可以）
[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
- 登录数据库关闭二进制日志
[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)
- 还原完全备份
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)
- 再还原增量备份(注意是修改过误操作的那个文件)
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)
- 开启二进制日志
mariadb [db1]> set sql_log_bin=1; # 确保数据还原完毕再打开 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]> select * from test; # 添加的记录也在 +----+------+--------+ | id | name | mobile | +----+------+--------+ | 1 | rose | | | 2 | jack | | +----+------+--------+ 2 rows in set (0.00 sec)
做以上实验确保你的二进制日志独立于数据库之外。