mysql基础之数据库备份和恢复实操
2024-10-16 01:39:33
一、基于二进制文件的恢复***
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!
--查看现有数据库
MariaDB [ren]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ren |
| test1 |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
--模拟故障
MariaDB [ren]> drop database ren;
Query OK, 11 rows affected (0.04 sec) MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
| test2 |
+--------------------+
5 rows in set (0.01 sec)
（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]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ren |
| test1 |
| test2 |
| yang |
+--------------------+
7 rows in set (0.00 sec) MariaDB [yang]> drop database yang;
Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ren |
| test1 |
| test2 |
+--------------------+
6 rows in set (0.00 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
最新文章
- b/s结构的物业管理系统（一）-------登录篇
- Tabio – 轻松，高效的管理 Chrome 标签页
- 图割Graph-Cut的最大流实现
- 介绍Android HAL的一篇好文章
- 黑马程序员——JAVA基础之异常处理机制
- SpringMVC常用注解,返回方式,路径匹配形式,验证
- bzoj2005 能量采集 gcd 容斥
- Android真机网络adb联机调试初探
- MyEclipse下一个XFire发展Webservice示例
- Node.js学习 - Install and Configure
- 本站已稳定运行了XX天，网页时间显示功能实现方法
- mpvue 小程序开发爬坑汇总
- ORACLE——获取随机数
- peewee基本操作
- Oarcle的开始
- .Net进阶系列(12)-异步多线程(Thread和ThreadPool)（被替换）
- 3.2 git命令大全
- Java 管道PipedInputStream PipedOutStream PipedReader PipedWriter
- Eclipse 4.3 Kepler最快汉化方法
- Python 零基础 快速入门 趣味教程 (咪博士 海龟绘图 turtle) 3. 循环