逻辑备份:
应用场景 | 备份语句 | 案例 |
全库级别逻辑备份(会到出创库语句) | mysqldump -uroot -pRenxiaoanan@123 -A > 保存路径.sql | mysqldump -uroot -pRenxiaoanan@123 -A > shujuk/a.sql |
逻辑备份数据导入 | mysql -pRenxiaonan@123 < 备份数据路径 | mysql -pRenxiaonan@123 < /root/shujuk/quanku.sql |
逻辑备份数据导入 | source 备份路径.sql | |
库级逻辑备份(会到出创库语句) | mysqldump -uroot -pRenxiaonan@123 -B 库名l > 备份路径 | mysqldump -uroot -pRenxiaonan@123 -B school > danku.sql |
只备份库的表结构 | mysqldump -pRenxiaonan@123 -B school -d | |
表级逻辑备份 | ysqldump -pRenxiaonan@123 -B 库 表 | ysqldump -pRenxiaonan@123 -B school teacher |
表数据导入 | mysql -uroot -p密码 库名 <表路径 | |
查看数据库执行过的命令 | show processlist | |
查看导出数据保存路径(查看安全目录位置) | show variables like "secure_file_priv" | |
修改保存路径(配置文件/etc/my.cnf) | secure_file_priv | |
导出数据(只导出表的内容) | select * from 表 into outfile '路径' where 字段='内容' limit 量; | |
导入数据表 | load data infil "路径" into table 表 | |
导出数据并制定间隔符号(只导出表的内容) | select * from 表 into outfile '路径' where 字段='内容' limit 量 fields teminated by ‘#’ lines terminated by '@'; |
bin -log日志数据恢复
bin-log日志开启后,会记录每条写入的操作。
bin-log开启方法:
cat >> /etc/my.cnf <<EOF
log-bin=/var/log/binlog/mysqlbin-log
server-id=1
EOF
丢失数据后,使用bin-log日志恢复数据,在mysql库中执行刷新bin-log日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
全库备份最近数据进行恢复,后续为本分的几小时数据进行恢复。。
查看bin-log日志内容
mysqlbinlog bin-log日志的名称 --base64-output=decode-rows -v
at 1028
#240727 14:46:13 server id 1 end_log_pos 1107 CRC32 0x51aaf222 Table_map: `company`.`employee5` mapped to number 86
# has_generated_invisible_primary_key=0
# at 1107
#240727 14:46:13 server id 1 end_log_pos 1589 CRC32 0x143eea4f Write_rows: table id 86 flags: STMT_END_F
### INSERT INTO `company`.`employee5`
### SET
### @1=1
### @2='jack'
### @3=1
### @4='2018:02:02'
### @5='instructor'
### @6='teach'
### @7=5000
### @8=501
### @9=100
### INSERT INTO `company`.`employee5`
### SET
### @1=2
### @2='tom'
### @3=1
### @4='2018:02:03'
### @5='instructor'
### @6='teach'
### @7=5500
### @8=501
### @9=100
### INSERT INTO `company`.`employee5`
### SET
### @1=3
### @2='robin'
### @3=1
### @4='2018:02:02'
### @5='instructor'
### @6='teach'
### @7=8000
### @8=501
### @9=100
### INSERT INTO `company`.`employee5`
### SET
### @1=4
### @2='alice'
### @3=2
### @4='2018:02:02'
### @5='instructor'
### @6='teach'
### @7=7200
### @8=501
### @9=100
### INSERT INTO `company`.`employee5`
### SET
### @1=5
### @2='tianyun'
### @3=1
### @4='2018:02:02'
### @5='hr'
### @6='hrcc'
### @7=600
### @8=502
### @9=101
### INSERT INTO `company`.`employee5`
### SET
### @1=6
### @2='harry'
### @3=1
### @4='2018:02:02'
### @5='hr'
### @6=NULL
### @7=6000
### @8=502
### @9=101
### INSERT INTO `company`.`employee5`
### SET
### @1=7
### @2='emma'
### @3=2
### @4='2018:02:06'
### @5='sale'
### @6='salecc'
### @7=20000
### @8=503
### @9=102
### INSERT INTO `company`.`employee5`
### SET
### @1=8
### @2='christine'
### @3=2
### @4='2018:02:05'
### @5='sale'
### @6='salecc'
### @7=2200
### @8=503
### @9=102
### INSERT INTO `company`.`employee5`
### SET
### @1=9
### @2='zhuzhu'
### @3=1
### @4='2018:02:05'
### @5='sale'
### @6=NULL
### @7=2200
### @8=503
### @9=102
### INSERT INTO `company`.`employee5`
### SET
### @1=10
### @2='gougou'
### @3=1
### @4='2018:02:05'
### @5='sale'
### @6=''
### @7=2200
### @8=503
### @9=102
# at 1589
#240727 14:46:13 server id 1 end_log_pos 1620 CRC32 0x44e79e63 Xid = 8
COMMIT/*!*/;
# at 1620
#240727 14:54:22 server id 1 end_log_pos 1670 CRC32 0x2b65daea Rotate to bing-date %F.000002 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
从1028到1670 结束
恢复语句
mysqlbinlog --start-position 1028 --stop-position 1670 bing-date\ %F.000001 |mysql -uroot -pRenxiaonan@123 company
#mysqlbinlog --start-position 1028 --stop-position 1670 bin-log日志 |mysql -uroot -p密码 需恢复的库
物理备份:
安装xtrabackup工具
sudo yum install \
https://repo.percona.com/yum/percona-release-latest.\
noarch.rpm
sudo percona-release enable-only tools release
sudo percona-release enable-only tools
sudo yum install percona-xtrabackup-80
sudo yum install lz4
完全备份流程:
#创建备份目录:
mkdir /xtrabackup/full -p
#备份:
innobackupex --user=root --password='123' /xtrabackup/full
完全备份恢复流程
1. 停止数据库 systemctl stop mysqld&&systemctl stop firewalld&&setenforce 0
2. 清理环境
3. 重演回滚--> 恢复数据
4. 修改权限
5. 启动数据库
1.关闭数据库:
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# rm -rf /var/log/mysqld.log #可选操作
[root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log #可选操作
2.恢复之前的验证恢复:
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49
3.确认数据库目录:
恢复之前需要确认配置文件内有数据库目录指定,不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
4.恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
5.修改权限:
[root@mysql-server ~]# chown mysql.mysql /var/lib/mysql -R
启动数据库:
[root@mysql-server ~]# systemctl start mysqld
增量备份流程
完整备份:周一
[root@mysql-server ~]# rm -rf /xtrabackup/*
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35
[root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/
[root@mysql-server 2019-08-20_14-51-35]# ls
backup-my.cnf ib_buffer_pool mysql sys testdb xtrabackup_info
company ibdata1 performance_schema test xtrabackup_checkpoints xtrabackup_logfile
增量备份:周二 —— 周三
在数据库中插入周二的数据:
mysql> insert into testdb.t1 values(2); #模拟周二
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35 2019-08-20_15-04-29 ---相当于周二的增量备份
增量备份恢复流程
1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log--> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
依次重演回滚redo log:
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35
周二 --- 周三
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/
恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
登陆上去看一下:
差异备份流程
[root@mysql-server ~]# rm -rf /xtrabackup/*
登陆数据库,准备环境
mysql> create database testdb;
mysql> delete from testdb.t1;
mysql> insert into testdb.t1 values(1); #插入数据1,模拟周一
mysql> select * from testdb.t1;
+------+
| id |
+------+
| 1 |
+------+
mysql> \q
查看时间:
[root@mysql-server ~]# date
Tue Aug 20 15:39:59 CST 2019
1、完整备份:周一
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
2、差异备份:周二 —— 周三
语法: # innobackupex --user=root --password=123 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录(周一)
3.修改时间:
[root@mysql-server ~]# date 08211543
Wed Aug 21 15:43:00 CST 2019
4.在登陆mysql:
mysql> insert into testdb.t1 values(2); #插入数据2,模拟周二
差异备份周二的
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #备份目录基于周一的备份
5.再次登陆mysql
mysql> insert into testdb.t1 values(3); #插入数据,模拟周三
6.在次修改时间
[root@mysql-server ~]# date 08221550
Thu Aug 22 15:50:00 CST 2019
7.再次差异备份
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份
8.延伸到周四
mysql> insert into testdb.t1 values(4);
9.修改时间
[root@mysql-server ~]# date 08231553
Fri Aug 23 15:53:00 CST 2019
10.差异备份周四
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份
11.查看一下备份目录
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_15-42-02 2019-08-21_15-46-53 2019-08-22_15-51-15 2019-08-23_15-53-28
周一 周二 周三 周四
差异备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚redo log(周一,某次差异)--> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
停止数据库
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
1.恢复全量的redo log
语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一)
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/
2.恢复差异的redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一)--incremental-dir=/xtrabacku/某个差异备份
这里我们恢复周三的差异备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/ #我们恢复周三的差异备份
3.恢复数据
语法:# innobackupex --copy-back /xtrabackup/完全备份目录(周一)
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/
修改权限:
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld