mysql基础之数据库备份和恢复实操

阅读原文时间：2023年07月09日阅读：3

一、基于二进制文件的恢复***

1、算好要恢复数据的时间段，重定向输入到bin.sql文件中

[root@ren7 mysql]# mysqlbinlog --start-datetime="2019-09-07 20:07:32" --stop-datetime="2019-09-07 20:12:00" mysql-bin.000001 > bin.sql
[root@ren7 mysql]# chown mysql.mysql bin.sql

执行bin.sql文件还原（sql语句下）

MariaDB [ren]> source /var/lib/mysql/bin.sql

2、根据最后所处的位置恢复

MariaDB [ren]> delete from test where id is null;
Query OK, 1 row affected (0.00 sec)
MariaDB [ren]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 690 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[root@ren7 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --stop-position=518 | mysql -uroot -proot

二、基于备份工具mariabackup进行备份***

1、简介（针对事务日志备份和恢复）

　　Mariabackup是MariaDB提供的一个开源工具，用于对InnoDB，Aria和MyISAM表进行物理在线备份。这个工具是基于Percona的XtraBackup（版本2.3.8）的解决方案。
　　这里有一点需要注意，在MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。

　　Percona的官方文档：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
　　Mariabackup的官方文档：https://mariadb.com/kb/en/library/mariabackup-overview/

2、常用选项

　　安装方法：yum install MariaDB-backup

--语法格式：
mariabackup [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]
--target-dir=name 　　　　　　　　　　 <<目标目录
--backup 　　　　　　　　　　 <<备份到目标目录--prepare 　　　　　　　　　　 <<准备备份数据--copy-back 　　　　　　　　　　 <<还原数据--incremental-basedir=name <<仅适用于backup，增量备份目录
--incremental-dir=name <<仅适用于prepare，恢复指定目录下的.delta文件和日志文件
--apply-log-only　　　　　　　　　　　　<<(做增量备份时必须要加的参数，这个参数是为了防止增量备份合并的时候回滚事务，这个参数是为了保留未提交的事务，因为有可能在下一次增量的时候提交)
--xtrabackupd的选项：
--apply-log <<从备份恢复。
--redo-only <<该选项强制跳过rollback阶段，只进行redo。这是有必要使用的，如果备份后，要使用增量改变的。

3、全备+恢复

（1）全量备份

[root@ren7 ~]# mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root
[00] 2019-09-08 09:33:37 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
…
[00] 2019-09-08 09:33:39 completed OK!

（2）准备全备数据

[root@ren7 ~]# mariabackup --prepare --target-dir=/root/fullbackup --user=root --password=root
mariabackup based on MariaDB server 10.2.26-MariaDB Linux (x86_64)
[00] 2019-09-08 09:38:15 cd to /root/fullbackup/
…
[00] 2019-09-08 09:38:15 completed OK!

（3）还原数据（确保数据目录为空）

MariaDB [(none)]> quit
Bye
[root@ren7 ~]# systemctl stop mariadb　　#可以不手动关闭
[root@ren7 ~]# ss -tnl |grep 3306
[root@ren7 ~]# rm -rf /var/lib/mysql/*
[root@ren7 ~]# ls -ltr /var/lib/mysql　　#删除数据库文件内容
总用量 0

[root@ren7 ~]# mariabackup --copy-back --target-dir=/root/fullbackup --user=root --password=root
mariabackup based on MariaDB server 10.2.26-MariaDB Linux (x86_64)
[01] 2019-09-08 09:43:15 Copying ibdata1 to /var/lib/mysql/ibdata1
[00] 2019-09-08 09:43:16 completed OK!

（4）修改属主和属组

[root@ren7 ~]# ls -tlr /var/lib/mysql
总用量 77860
-rw-r-----. 1 root root 79691776 9月 8 09:43 ibdata1
drwx------. 2 root root 4096 9月 8 09:43 mysql
drwx------. 2 root root 4096 9月 8 09:43 ren
drwx------. 2 root root 20 9月 8 09:43 performance_schema
drwx------. 2 root root 20 9月 8 09:43 test1
drwx------. 2 root root 20 9月 8 09:43 test2
-rw-r-----. 1 root root 52 9月 8 09:43 aria_log_control
-rw-r-----. 1 root root 16384 9月 8 09:43 aria_log.00000001
-rw-r-----. 1 root root 529 9月 8 09:43 xtrabackup_info
-rw-r-----. 1 root root 2539 9月 8 09:43 ib_buffer_pool
[root@ren7 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@ren7 ~]# service mariadb restart
Redirecting to /bin/systemctl restart mariadb.service

4、全备+增量+二进制-->恢复

（1）全量备份

[root@ren7 ~]# mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

（2）新增数据库

MariaDB [(none)]> create database yang character set utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use yang
Database changed
MariaDB [yang]> create table qinqin(id int primary key ,name varchar(20));Query OK, 0 rows affected (0.00 sec)

MariaDB [yang]> insert into qinqin values(0,'任彦忠');
Query OK, 1 row affected (0.00 sec)

MariaDB [yang]> select * from qinqin;
+----+-----------+
| id | name |
+----+-----------+
| 0 | 任彦忠 |
+----+-----------+
1 row in set (0.00 sec)

（3）增量备份

[root@ren7 ~]# mariabackup --backup --target-dir=/root/increase --incremental-basedir=/root/fullbackup --user=root --password=root
[00] 2019-09-08 10:08:20 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
…
[00] 2019-09-08 10:08:22 completed OK!

（4）模拟损坏

MariaDB [yang]> drop database yang;
Query OK, 1 row affected (0.01 sec)

（5）准备全备数据

[root@ren7 ~]# mariabackup --prepare --target-dir=/root/fullbackup --user=root --password=root

（6）增量和全量备份数据合并

[root@ren7 ~]# mariabackup --prepare --target-dir=/root/fullbackup --user=root --password=root --incremental-dir=/root/increase --apply-log-only
mariabackup based on MariaDB server 10.2.26-MariaDB Linux (x86_64)
[00] 2019-09-08 10:15:22 incremental backup from 2210550 is enabled.
…
[00] 2019-09-08 10:15:23 completed OK!

（7）恢复数据

[root@ren7 ~]# rm -rf /var/lib/mysql/*
[root@ren7 ~]# ls -tlr /var/lib/mysql
总用量 0
[root@ren7 ~]# mariabackup --copy-back --target-dir=/root/fullbackup --user=root --password=root
mariabackup based on MariaDB server 10.2.26-MariaDB Linux (x86_64)
[01] 2019-09-08 10:19:18 Copying ibdata1 to /var/lib/mysql/ibdata1
…
[00] 2019-09-08 10:19:19 completed OK!

（8）修改属组和属主

[root@ren7 ~]# ls -tlr /var/lib/mysql
总用量 77860
-rw-r-----. 1 root root 79691776 9月 8 10:19 ibdata1
drwx------. 2 root root 4096 9月 8 10:19 mysql
drwx------. 2 root root 4096 9月 8 10:19 ren
drwx------. 2 root root 20 9月 8 10:19 test2
drwx------. 2 root root 20 9月 8 10:19 test1
drwx------. 2 root root 20 9月 8 10:19 performance_schema
-rw-r-----. 1 root root 52 9月 8 10:19 aria_log_control
-rw-r-----. 1 root root 16384 9月 8 10:19 aria_log.00000001
-rw-r-----. 1 root root 2539 9月 8 10:19 ib_buffer_pool
drwx------. 2 root root 56 9月 8 10:19 yang
-rw-r-----. 1 root root 573 9月 8 10:19 xtrabackup_info
[root@ren7 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@ren7 ~]# ls -tlr /var/lib/mysql
总用量 77860
-rw-r-----. 1 mysql mysql 79691776 9月 8 10:19 ibdata1
drwx------. 2 mysql mysql 4096 9月 8 10:19 mysql
drwx------. 2 mysql mysql 4096 9月 8 10:19 ren
drwx------. 2 mysql mysql 20 9月 8 10:19 test2
drwx------. 2 mysql mysql 20 9月 8 10:19 test1
drwx------. 2 mysql mysql 20 9月 8 10:19 performance_schema
-rw-r-----. 1 mysql mysql 52 9月 8 10:19 aria_log_control
-rw-r-----. 1 mysql mysql 16384 9月 8 10:19 aria_log.00000001
-rw-r-----. 1 mysql mysql 2539 9月 8 10:19 ib_buffer_pool
drwx------. 2 mysql mysql 56 9月 8 10:19 yang
-rw-r-----. 1 mysql mysql 573 9月 8 10:19 xtrabackup_info

三、基于mysqldump工具进行备份(逻辑备份工具)***

#备份testdb数据库(-l代表备份单个数据库时锁定该库的所有表；-x当对所有数据库备份时锁定所有数据库的所有表）
mysqldump -uroot -proot -l --databases testdb > testdb.sql
#备份所有数据库
mysqldump -uroot -proot --all-databases > all_databases.sql
#备份testdb数据库下的students表
mysqldump -uroot -proot testdb students > students.sql
#备份testdb数据库下的students表和classes表
mysqldump -uroot -proot testdb students classes > both_tables.sql
#还原数据（sql命令行下）
source testdb.sql

其它常用选项：
--master-data: 表示标记备份开始时的binlog所对应的position
0：表示在使用mysqldump进行备份时，不记录对应二进制日志文件位置，将此值显式的设置为0与不使用此选项的效果相同
1：表示在使用mysqldump进行备份时，记录对应二进制日志文件位置，此值为默认值；使用--master-data与使用--master-data=1的效果相同；如果将此选项的值设置为1，则会在备份文件中生成对应的“CHANGE MASTER TO”语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的position，生成此语句的目的是，在主从复制结构中的“从服务器”中通过备份sql还原数据以后，告诉“从库”，从“主库”的二进制日志文件中的哪个位置开始“同步”。
2：表示在使用mysqldump进行备份时，记录对应二进制日志文件的位置，此值为2时，也会生成“CHENGE MASTER TO”语句，但是该语句会被注释，而此值为1时，该语句不会被注释；所以，如果只是单纯的为了记录备份时的二进制日志文件位置，那么将此值设置为2即可。
--flush-logs:　　　　表示备份开始时，就会滚动一次二进制日志
--routines: 表示备份时，存储过程和存储函数也会被备份
--triggers: 表示备份时，触发器会被备份
--events: 表示备份时，事件表会被备份

四、基于lvm2的备份

　　因为mariadb的默认数据文件位置是/var/lib/mysql目录里，并不是文件系统，因此这里用新加磁盘来创建文件系统测试

--开始配置：
1.添加磁盘
2.重启服务器识别硬盘
3.[root@localhost ~]#fdisk /dev/sdb #分区
4.[root@localhost ~]#pvcreate /dev/sdb3 #创建pv
5.[root@localhost ~]#pvdisplay #查看pv
"/dev/sdb3" is a new physical volume of "100.00 GiB"
--- NEW Physical volume ---
PV Name /dev/sdb3
VG Name
PV Size 100.00 GiB
Allocatable NO
PE Size 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID zYUBTH-Eqfa-ZYf7-q8pZ-gWBQ-UtUL-qeZzyd
6.[root@localhost ~]# vgcreate test3vg /dev/sdb3 #创建vg
Volume group "test3vg" successfully created
7.[root@localhost ~]# lvcreate -L 99G -n testlv3 test3vg #创建lv
Logical volume "testlv3" created.
8.[root@localhost ~]# mkfs.ext4 /dev/test3vg/testlv3 #格式化lv
9.[root@localhost ~]# mkdir /test3 #根目录下创建一个test3目录
10.[root@localhost ~]# mount /dev/test3vg/testlv3 /test3/ #挂在刚才所创建的目录
11.如果是yum安装默认数据目录在/var/lib/mysql,如果需要更改
vim /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/test3/
pid_file=/test3/localhost.pid
socket=/test3/mysql.sock
wsrep_data_home_dir=/test3/
log-bin=mysql-bin
[client]
socket=/test3/mysql.sock
12.[root@localhost ~]#chown -R mysql.mysql /test3 #修改目录下面所有文件的权限，必须为mysql不然数据库起不来
13.[root@localhost ~]#service mariadb restart #重启数据库
14.MariaDB [(none)]> flush tables with read lock; #将所有表锁住(只能读，不能写)
15.[root@localhost ~]# lvcreate -L 100G -s -p r -n snap_test3 /dev/test3vg/testlv3 #创建快照lv(这里要注意，test3vg中必须有足够的空间，不然创建lv会失败)
备注:
-L: --size
-s: --snap
-p: --permission rw|r
-n: --name
16.MariaDB [(none)]> unlock tables; #释放锁
17.[root@localhost testvg]# mkdir /snap_test3 #创建快照目录
18.[root@localhost ~]# mount /dev/testvg/snap_test3 /snap_test3/ #挂载快照lv
19.这时候你就可以在快照目录下看到跟源文件系统一模一样的内容了，此时就可以用cp拷贝了！
note:
还原的时候确保属组和属主是mysql:mysql

手机扫一扫

移动阅读更方便

你可能感兴趣的文章

基于KVM、Xen、OpenVZ等虚拟化技术的WEB在线管理工具

阿里云服务器 ECS Ubuntu系统安装配置

centos 7 部署 open-falcon 0.2.0

centos linux安全和调优第四十一节课

mongoDB 学习笔记纯干货（mongoose、增删改查、聚合、索引、连接、备份与恢复、监控等等）

深度长文整理-Redis进阶

Util应用框架Web Api开发快速入门

万字长文浅析配置对MySQL服务器的影响

一键安装lnmp 环境

openNebula集群搭建