mysql基础之数据库备份和恢复实操
一、基于二进制文件的恢复***
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
