Linux学习之MySQL备份

xtrabackup资源下载

完全备份与恢复

# 1.物理备份与恢复
# 冷备份，需停止数据库服务 适合线下服务器。
[root@mysql50 ~]# systemctl stop mysqld
[root@mysql50 ~]# mkdir /bakdir
[root@mysql50 ~]# cp -r /var/lib/mysql /bakdir/mysql.bak
[root@mysql50 ~]# cd /var/lib/mysql
[root@mysql50 mysql]# tar -zcvf /bakdir/mysql.tar.gz ./*
			...
[root@mysql50 mysql]# ls /bakdir/
mysql.bak  mysql.tar.gz
# 删除数据
[root@mysql50 mysql]# rm -rf /var/lib/mysql/*
# 恢复数据(也可以使用cp拷贝的备份文件恢复数据)
[root@mysql50 mysql]# tar -xf /bakdir/mysql.tar.gz -C /var/lib/mysql/
[root@mysql50 mysql]# systemctl start mysqld
[root@mysql50 mysql]# mysql -uroot -p
Enter password: 
...

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| studb              |
| sys                |
| tarena             |
| tarena1            |
+--------------------+
9 rows in set (0.00 sec)
# 2.mysqldump备份与恢复
# 热备份，备份和恢复数据库服务必须是运行的
# 备份1张表
[root@mysql50 mysql]# mysqldump -uroot -p123456 tarena salary> /bakdir/tarena_salary.sql;
# 备份多张表
[root@mysql50 mysql]# mysqldump -uroot -p123456 tarena employees departments> /bakdir/tarena_departments_employees.sql;
# 备份1个库
[root@mysql50 mysql]# mysqldump -uroot -p123456 -B tarena >/bakdir/tarena.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 备份多个库
[root@mysql50 mysql]# mysqldump -uroot -p123456 -B studb db1 >/bakdir/stu_db1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 备份所有库
[root@mysql50 mysql]# mysqldump -uroot -p123456 -A > /bakdir/allbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 恢复数据（覆盖恢复数据）
[root@mysql50 mysql]# mysql -uroot -p
Enter password: 
...
mysql> 
mysql> drop database tarena;            # 删除库
Query OK, 6 rows affected (1.83 sec)
mysql> exit
Bye
[root@mysql50 mysql]# mysql -uroot -p123456 < /bakdir/tarena.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql50 mysql]# mysql -uroot -p
Enter password: 
...
mysql> use tarena; # 进库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;      # 看表
+------------------+
| Tables_in_tarena |
+------------------+
| departments      |
| employees        |
| salary           |
| user             |
| user2            |
| wage_grade       |
+------------------+
6 rows in set (0.00 sec)
# 删除表记录
mysql> delete from salary; //删除表记录
Query OK, 8055 rows affected (0.11 sec)
mysql> exit
Bye
# 使用备份文件恢复数据
[root@mysql50 ~]# mysql -uroot -p123456  tarena < /bakdir/tarena_salary.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select count(*) from tarena.salary;
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)
# 分析[Mysqldump 备份和恢复数据时会锁表，锁表期间无法对表做写访问，mysqldump适合备份数据量比较小的数据或在数据库服务器访问量少的时候备份。]

# 通过备份脚本定时备份数据库
[root@mysql50 mysql]# vim /root/backsql.sh
#!/bin/bash
mysqldump -uroot -p123456 -B tarena > /bakdir/"tarena_$(date +%F)".sql
[root@mysql50 mysql]# chmod +x /root/backsql.sh 
# 设定计划任务，每天18：00备份数据库，可以根据自己需求来设定时间
[root@mysql50 mysql]# crontab -e
00 18 * * * /root/backsql.sh
分 时 日 月 周
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

增量备份与恢复

增量备份：备份上次备份后，新产生的数据。
Percona Xtrabackup是一款强大的在线热备份工具，备份过程中不锁库表，适合生产环境。支持完全备份与恢复、增量备份与恢复、差异备份与恢复。

# 数据增量备份
# 解压源码（在50,51两台机器上安装）
[root@mysql50 ~]# tar xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz 
# 安装依赖
[root@mysql50 ~]# yum install -y perl-DBD-MySQL
# 移动并改名
[root@host50 ~ ]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
//把命令添加到系统环境变量
[root@host50 ~ ]# vim /etc/bashrc
export PATH=/usr/local/percona/bin:$PATH  添加在文件末尾
:wq
[root@host50 ~ ]# source  /etc/bashrc
//查看帮助信息
[root@host50 ~ ]# man xtrabackup (按q 退出)
#对数据做增量备份前，必须先有一次备份，也就是首次备份，通常是备份所有数据；比如每周周日完全备份，接下来进行增量备份。

# ---周日完全备份
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/fullbak --datadir=/var/lib/mysql
...
xtrabackup: Transaction log of lsn (23970132) to (23970152) was copied.
230913 14:37:47 completed OK!
# ---开始增量备份
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new2 --incremental-basedir=/fullbak --datadir=/var/lib/mysql
# ----在50服务器上创建一个数据库并创建一个表
mysql> create database first;
Query OK, 1 row affected (0.09 sec)

mysql> use first;
Database changed
mysql> create table s1(id int,name varchar(30));
Query OK, 0 rows affected (0.78 sec)

# 数据增量恢复
1.准备恢复数据
2.合并数据
3.清空数据库目录
4.拷贝数据
5.修改数据库目录所有者/组为mysql
6.重启数据库服务
# 将50服务器上备份的数据拷贝到51服务器
[root@mysql51 ~]# scp -r 192.168.88.50:/fullbak /root/
[root@mysql51 ~]# scp -r 192.168.88.50:/new2 /root/
# 准备恢复数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak
# 合并数据，如果有多个增量，同理，将所有增量数据依次进行合并，都合并至完全备份目录
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak --incremental-dir=/root/new2/
# 清空数据库目录
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
# 拷贝数据
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/fullbak
# 修改数据库目录所有者/组
[root@mysql51 ~]# chown -R mysql.mysql /var/lib/mysql
# 重启数据库
[root@mysql51 ~]# systemctl start mysqld
# 连接数据库查看信息，能查看到first数据库
[root@mysql51 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| first              |
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| studb              |
| sys                |
| tarena             |
| tarena1            |
+--------------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

差异备份与恢复

差异备份：备份完全备份后，新产生的数据。

# 对数据做差异备份前，必须先有一次备份，也就是首次备份，通常是备份所有数据；比如每周周一完全备份，周二到周日差异备份。
# 完全备份数据
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --datadir=/var/lib/mysql --target-dir=/allbak
# 添加增量数据
[root@mysql50 ~]# mysql -uroot -p
Enter password: 

mysql> create database second;
Query OK, 1 row affected (0.49 sec)

mysql> use second;
Database changed
mysql> create table a1(id int,name char(10));
Query OK, 0 rows affected (1.09 sec)
# 差异备份和增量备份类似，只是每次备份是基于完全备份的
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --datadir=/var/lib/mysql --target-dir=/dir2 --incremental-basedir=/allbak
# 在second库的a1表中插入数据
mysql> insert into a1 values(1,'a'),(2,'b');
# 再次差异备份
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --datadir=/var/lib/mysql --target-dir=/dir3 --incremental-basedir=/allbak

# 差异恢复
1.准备恢复数据
2.合并数据
3.清空数据库目录
4.拷贝数据
5.修改数据库目录所有者/组用户为mysql
6.重启数据库服务
# MySQL51 拷贝 MySQL50 的备份文件到 本机的根目录下，拷贝时只需要拷贝完全备份和最后一次差异备份的目录即可
[root@mysql51 ~]# scp -r 192.168.88.50:/allbak /root/
[root@mysql51 ~]# scp -r 192.168.88.50:/dir3 /root/
# 准备恢复数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak
# 合并数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak --incremental-dir=/root/dir3
# 清空数据库目录
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
# 拷贝数据
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/allbak
# 修改数据库目录所有者/组
[root@mysql51 ~]# chown -R mysql.mysql /var/lib/mysql
# 启动数据库服务器
[root@mysql51 ~]# systemctl start mysqld
# 连接服务查看数据
[root@mysql51 ~]# mysql -uroot -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| first              |
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| second             |
| studb              |
| sys                |
| tarena             |
| tarena1            |
+--------------------+
11 rows in set (0.01 sec)

mysql> select * from second.a1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72