目录
一、直接拷贝数据库文件
二、mysqldump备份数据库
1、备份
2、备份还原
(1)完全备份还原
(2)增量备份
#eg
(1)创建一个新的数据库表resume.test
(2)此时完全备份
(3)接着再往数据库表里面插入两条数据
(4)删除数据库resume,刷新日志
(5)此时会增加一个新的二进制日志
(6)备份还原
#GTID全局事务标识符
#binlog日志的GTID新特性
(1)启动gtid
(2)使用DDL和DML语句之后查看gtid会发生改变
#查看binlog
(3)数据库还原
三、mydumper备份数据库
1、备份 mydumper
2、还原 myloader
eg：备份数据库表 db.course，删除表后还原
四、借用windows下的软件备份
1、首先先授权一个用户
2、此时软件登录数据库
五、lvm备份、恢复
1、数据迁移到逻辑卷
（1）进行逻辑卷管理
（2） 将/var/lib/mysql/下所有内容压缩打包到/backup/MySQL/
（3）将lv_mysql挂载到/var/lib/mysql下此时mysql下内容将自动被清空，接着进行解压
（4） 接着检查所属用户和组是否为mysql，如果不是则需要修改
（5）登录数据库
#创建用户并授权方法
2、快照备份数据库
(1)锁表（读锁）
(2)逻辑卷创建快照
(3)解锁
(4)挂载
#如果没有rsync需要进行安装（yum install rsync -y）
(5)备份
(6)此时关掉mysql 配置/etc/my.cnf
(7)接着启动mysql并登录可以看到数据库表和开始一样
(8)卸载快照并删除
#脚本方式进行（1）-（8）操作
首先关闭数据库 [root@host1 mysql]# pwd /var/lib/mysql 压缩、拷贝到host2 [root@host1 mysql]# tar czf db.tar.gz * [root@host1 mysql]# scp db.tar.gz 192.168.157.11:/beifen host2 首先关闭数据库，清理/var/lib/mysql/下的内容 [root@host2 beifen]# tar xf db.tar.gz /var/lib/mysql/ 将/beifen/db.tar.gz解压到/var/lib/mysql
此时登录host2的数据库可以看到两个数据库中的内容相同
帮助 mysqldump --help [root@host1 ~]# mysqldump -u登录用户 -p'密码' 数据库 表名 > 备份文件名称.sql #数据库 表名:这里可以是指定数据库或者是某个库表 -A备份所有数据库， -B会将创建数据库语句也备份， -b备份数据库表的结构不备份数据。 备份数据库 [root@host1 ~]# mysqldump -uroot -p'MySQL@123' db -B > beifen1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@host1 ~]# ls beifen1.sql 备份成压缩包形式 [root@host1 ~]# mysqldump -uroot -p'MySQL@123' db -B | gzip > beifen1.sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份数据库表 [root@host1 ~]# mysqldump -uroot -p'MySQL@123' db course > beifen2.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@host1 ~]# ls beifen* beifen1.sql beifen1.sql.gz beifen2.sql
备份还原 还原数据库 方法一 在mysql中source后跟备份文件存放路径 mysql> source /backup/MySQL/beifen1.sql 方法二 [root@host1 ~]# mysql -uroot -p'MySQL@123' < /backup/MySQL/beifen1.sql mysql: [Warning] Using a password on the command line interface can be insecure. 这个时候就可以看到被还原的数据库 还原数据库表 mysql> source /backup/MySQL/beifen2.sql [root@host1 MySQL]# mysql -uroot -p'MySQL@123' db < ./beifen2.sql mysql: [Warning] Using a password on the command line interface can be insecure. 压缩包还原 [root@host1 MySQL]# zcat ./beifen1.sql.gz | mysql -uroot -p'MySQL@123' mysql: [Warning] Using a password on the command line interface can be insecure.
首先是要先开启数据库的二进制日志的log_bin
方法参考MySQL日志管理_spring℡的博客-CSDN博客
第一步先做完全备份
mysql> select * from test;
+--------+------+------+------------+------+
| name | weig | age | hobby | sex |
+--------+------+------+------------+------+
| 白熊 | 80.0 | 10 | dance,sing | m |
| 胖达 | 75.0 | 10 | dance,sing | m |
+--------+------+------+------------+------+
2 rows in set (0.00 sec)
mysql> insert test values ('克洛伊','35','8','sing','f'),('棕熊','76','9','dance','m');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
[root@host1 MySQL]# mysqldump -uroot -p'MySQL@123' --opt -F --single-transaction -B resume > /backup/MySQL/resume.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql> insert test values ('小白','45','8','sing','f'),('小胖','66','9','dance','m');
mysql> drop database resume; Query OK, 1 row affected (0.01 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec)
[root@host1 mysql]# ll mysql-bin.00000* -rw-r-----. 1 mysql mysql 177 Apr 17 14:34 mysql-bin.000002 -rw-r-----. 1 mysql mysql 177 Apr 17 14:54 mysql-bin.000003 -rw-r-----. 1 mysql mysql 177 Apr 17 14:59 mysql-bin.000004 -rw-r-----. 1 mysql mysql 177 Apr 17 15:28 mysql-bin.000005 -rw-r-----. 1 mysql mysql 3303 Apr 17 15:37 mysql-bin.000006 -rw-r-----. 1 mysql mysql 18407 Apr 20 15:03 mysql-bin.000007 -rw-r-----. 1 mysql mysql 2059 Apr 20 16:44 mysql-bin.000008 -rw-r-----. 1 mysql mysql 665 Apr 20 16:46 mysql-bin.000009
第一步还原完全备份
mysql> source /backup/MySQL/resume.sql
第二步还原增量备份
[root@host1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000009
方法一：根据时间备份还原 [root@host1 MySQL]# mysqlbinlog --start-datetime="2022-04-20 16:45:32" --stop-datetime="2022-04-20 16:45:57" /var/lib/mysql/mysql-bin.000009 -r resume1.sql [root@host1 MySQL]# mysql -uroot -p'MySQL@123' < resume1.sql mysql: [Warning] Using a password on the command line interface can be insecure. 方法二：根据位置点备份还原 [root@host1 MySQL]# mysqlbinlog --start-position=293 --stop-position=455 /var/lib/mysql/mysql-bin.000009 -r resume2.sql mysql> source /backup/MySQL/resume2.sql
mysql> select * from test; +-----------+------+------+------------+------+ | name | weig | age | hobby | sexs | +-----------+------+------+------------+------+ | 白熊 | 80.0 | 10 | dance,sing | m | | 胖达 | 75.0 | 10 | dance,sing | m | | 克洛伊 | 35.0 | 8 | sing | f | | 棕熊 | 76.0 | 9 | dance | m | | 小白 | 45.0 | 8 | sing | f | | 小胖 | 66.0 | 9 | dance | m | +-----------+------+------+------------+------+ 6 rows in set (0.00 sec)
5.6以后添加5.7做了加强
mysql> desc mysql.gtid_executed -> ; +----------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+-------+ | source_uuid | char(36) | NO | PRI | NULL | | | interval_start | bigint(20) | NO | PRI | NULL | | | interval_end | bigint(20) | NO | | NULL | | +----------------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | OFF | | gtid_executed_compression_period | 1000 | | gtid_mode | OFF | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec)
由于gtid_mode=OFF所以需要开启
[root@host1 ~]# vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=on
DDL mysql> create database dba; Query OK, 1 row affected (0.01 sec) mysql> use dba Database changed mysql> create table test (sno char(12),name char(13),cno char(8)); Query OK, 0 rows affected (0.01 sec)
DML
开启事务mysql> begin;
mysql> insert test(sno,name) values('122110','小白');
Query OK, 1 row affected (0.00 sec)
#不管是中间进行几次插入
事务提交mysql> commit;
最后79c7390e-ad1f-11ec-9593-000c29ae9e04:只会增一
mysql> SHOW binlog EVENTS in 'mysql-bin.000012';
首先有一个数据库 dba 库里面有表tb1,且表内插入了一些简单数据。
mysql> select * from tb1; +-----+--------+ | sno | name | +-----+--------+ | 100 | 小明 | | 101 | 小李 | +-----+--------+ 2 rows in set (0.00 sec) 创完之后删除tb1
mysql> show binlog events in 'mysql-bin.000012';
此时在/backup/MySQL下编写备份还原部分内容
因为9是删表部分因此恢复1-8
（1）数据备份 因为GTID幂等性，这里我添加了--skip-gtids [root@host1 MySQL]# mysqlbinlog --skip-gtids --include-gtids="79c7390e-ad1f-11ec-9593-000c29ae9e04:1-8" mysql-bin.000012 -r gtid_1.sql 此时就有了gtid_1.sql （2）数据恢复 mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) [root@host1 MySQL]# mysql -u root -p'MySQL@123'< gtid_1.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
此时在数据库中就可以查看被删除的数据库已经库表
安装：Gitee 极速下载/mydumper
yum install https://github.com/mydumper/mydumper/releases/download/v0.11.5/mydumper-0.11.5-1.el7.x86_64.rpm
mydumper -uroot -pMySQL@123 -B 数据库名 -T 表名 -o /存放路径
备份数据库db [root@localhost ~]# mydumper -u root -p MySQL@123 -B db -o /db [root@localhost ~]# tree /db /db ├── db.course.00000.sql ├── db.course-metadata ├── db.course-schema.sql ├── db-schema-create.sql ├── db.student.00000.sql ├── db.student-metadata ├── db.student-schema.sql └── metadata 0 directories, 8 files
myloader -u root -p MySQL@123 -d /db -o -B db
先右边再左边
备份 [root@localhost db]# mydumper -u root -p MySQL@123 -B db -T course -o /db11 #这里-B后面跟数据库名，-T后面跟表名，-o代表输出到那个位置 [root@localhost db]# cd /db11 [root@localhost db11]# ls db.course.00000.sql db.course-metadata db.course-schema.sql db-schema-create.sql metadata 删除表course mysql> drop table course; Query OK, 0 rows affected (0.02 sec) 恢复表course [root@localhost db11]# myloader -u root -p MySQL@123 -d /db11 -B db -o 这里直接恢复数据库就会恢复表内容
grant all on . to jim@'%' identified by 'Jim@12345';
将需要备份的数据库如下备份，后删除再导入即可
先关闭mysql服务，虚拟机添加一块新的磁盘（这里添加6G）
lvs：查看磁盘分区情况
lsblk：磁盘剩余内存
此时重启虚拟机即可
创建物理卷 [root@localhost ~]# pvcreate /dev/sdb Physical volume "/dev/sdb" successfully created. 创建卷组 [root@localhost ~]# vgcreate vg1 /dev/sdb Volume group "vg1" successfully created 创建逻辑卷 [root@localhost ~]# lvcreate -n lv_mysql -L 4G vg1 Logical volume "lv_mysql" created. 格式化逻辑卷（xfs的文件系统只支持扩大逻辑卷，ext4的文件系统支持扩大和缩小逻辑卷） [root@localhost ~]# mkfs.xfs /dev/vg1/lv_mysql
[root@localhost mysql]# tar -czvf /backup/MySQL/mysql.tar.gz . 查看 [root@localhost mysql]# tar -tvf /backup/MySQL/mysql.tar.gz
[root@localhost mysql]# mount /dev/vg1/lv_mysql /var/lib/mysql [root@localhost mysql]# tar xf /backup/MySQL/mysql.tar.gz -C .
[root@localhost lib]# ls -ld mysql drwxr-x--x. 15 mysql mysql 4096 May 4 16:42 mysql 此时启动mysql即可 systemctl start mysqld
show databases; 和原来数据库内容一样
mysql> grant all on *.* to admin@'192.168.157.%' identified by 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec)
[root@host1 ~]# lvcreate -n lv_mysql_s -L 500M -s /dev/vg1/lv_mysql Logical volume "lv_mysql_s" created.
[root@host1 ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert root centos -wi-ao---- <17.00g swap centos -wi-ao---- 2.00g lv_mysql vg1 owi-aos--- 4.00g lv_mysql_s vg1 swi-a-s--- 500.00m lv_mysql 0.00
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
[root@host1 ~]# mkdir /mnt/mysql -p [root@host1 ~]# mount -o nouuid /dev/vg1/lv_mysql_s /mnt/mysql
[root@host1 mnt]# rsync -av /mnt/mysql/ /mysql_back/
[root@host1 ~]# umount /mnt/mysql/ && lvremove /dev/vg01/lv_mysql_s
cd /mysql/scripts
[root@host1 scripts]# vim my.sh
#!/bin/bash back_dir=/backup/`date +%F` [ -d $back_dir ]|| mkdir -p $back_dir echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 500M -s /dev/vg1/lv_mysql;unlock tables;" |mysql -uroot -pMySQL@123 &>/dev/null [ -d /mnt/mysql/ ] || mkdir -p /mnt/mysql/ mount -o nouuid /dev/vg1/lv_mysql_s /mnt/mysql/ rsync -a /mnt/mysql/ $back_dir if [ $? -eq 0 ];then umount /mnt/mysql/ && lvremove -f /dev/vg01/lv_mysql_s &>/dev/null fi
执行脚本
[root@host1 scripts]# sh my.sh
