文章目录
MySQL数据库备份与恢复
1.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份：也称为完全备份，指对某个指定时间点的所有数据和对应的结构进行一个完全的备份。 特点： 1.数据最完备； 2.安全性最高； 3.备份和恢复时间随着数据的体量而明显增加； 4.非常重要，是差异备份和增量备份的基础； 5.备份期间会对系统性能产生一定影响。 |
| 增量备份 | 增量备份是备份的一个类型，是指在上一次备份（包含完全备份、差异备份、增量备份）后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。 第二次增量备份的对象是进行第一次增量 备份第一次增量所产生的增加和修改的文件，如此类推。 特点： 1.没有重复数据，备份的数据量不大 2.备份速度比完整备份快。 3.同时由于增量备份在做备份前会自动判断备份时间点及文件是否已作改动，所以相对于完全备份其对于节省存储空间也大有益处 4.恢复数据时必须按一定的顺序进行，效率相对较低 |
| 差异备份 | 备份自上一次完全备份之后所有有变化的数据以及文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。 在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 特点： 1.没有重复的备份数据 2.备份的数据量不大 3.备份所需时间短 |
例如：
-
有一个数据库，第一次写入了10GB的数据。然后做了一个全量备份，就等于备份了10GB的数据；
-
后面又新写入了5GB的数据，对它进行增量备份，等于第二次备份了新产生或者变化的5GB的数据；后面如果再新写入5GB的数据，对它进行增量备份，那第三次备份就是在第二次增量备份的基础上再进行备份新产生或者变化的5GB的数据。
-
那么如果想要恢复第三次增量备份的数据的话，就要先恢复第二次增量备份的数据，然后再恢复第三次增量备份的数据。如果有第四次增量备份，那就要先恢复第二次增量备份，然后再恢复第三次增量备份，最后再恢复第四次增量备份。
-
那差异备份就是，第一次写入了10GB的数据，然后做了一个全量备份，等于备份了10GB的数据；
-
后面新写入了5GB的数据，对他进行差异备份，就等于第二次备份的新产生或者变化的5GB的数据；如果又新写入了5GB的数据，对他进行第三次差异备份，就等于在第二次差异备份的基础上又备份新产生或者变化的5GB的数据。
-
那如果想要恢复第三次差异备份的数据的话，仅需将第一次的全量备份和第三次的差异备份就行；如果想要恢复第二次差异备份的数据的话，则恢复第一次的全量备份和第二次的差异备份就行。那假如有第四次差异备份，则恢复第一次全量备份和第四次差异备份。
2.1.mysql备份工具mysqldump
1. 认识mysqldump工具
mysqldump：MySQL 自带的逻辑备份工具。
备份原理：
通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。
2.使用mysqldump工具
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
全量备份所有数据库
[root@kiwi222 ~]# mysql -uroot -p
Enter password:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> USE kiwi111;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| book |
| student |
+-------------------+
2 rows in set (0.00 sec)
mysql>
[root@kiwi222 ~]# ls
anaconda-ks.cfg
[root@kiwi222 ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-databases-backup-2023-09-04.sql
Enter password:
[root@kiwi222 ~]# ls
all-databases-backup-2023-09-04.sql anaconda-ks.cfg
[root@kiwi222 ~]#
## 127.0.0.1也就是本机，如果连接不上则需要先授权
备份表
//备份kiwi111库里的表book
mysql> SHOW TABLES;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| book |
| student |
+-------------------+
2 rows in set (0.00 sec)
mysql>
[root@kiwi222 ~]# ls
anaconda-ks.cfg backup-2023-09-04.sql
[root@kiwi222 ~]# mysqldump -uroot -p -h127.0.0.1 kiwi111 book > table-book-backup-2023-09-04.sql
Enter password:
[root@kiwi222 ~]# ls
anaconda-ks.cfg all-databases-backup-2023-09-04.sql table-book-backup-2023-09-04.sql
[root@kiwi222 ~]#
备份单个数据库
//备份kiwi111数据库
[root@kiwi222 ~]# ls
anaconda-ks.cfg backup-2023-09-04.sql table-book-backup-2023-09-04.sql
[root@kiwi222 ~]# mysqldump -uroot -p -h127.0.0.1 --databases kiwi111 > database-kiwi111-backup-2023-09-04.sql
Enter password:
[root@kiwi222 ~]# ls
anaconda-ks.cfg database-kiwi111-backup-2023-09-04.sql
all-databases-backup-2023-09-04.sql table-book-backup-2023-09-04.sql
数据恢复
//删除kiwi111数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> DROP DATABASE kiwi111;
Query OK, 2 rows affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql>
##只能删自己创建的数据库，如果删了系统自带的数据库则只能初始化
//恢复kiwi111数据库
[root@kiwi222 ~]# mysql -uroot -p -h127.0.0.1 < database-kiwi111-backup-2023-09-04.sql
Enter password:
[root@kiwi222 ~]#
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql>
//恢复所有数据库
mysql> DROP DATABASE kiwi222;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE kiwi333;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE kiwi444;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
[root@kiwi222 ~]# ls
all-databases-backup-2023-09-04.sql anaconda-ks.cfg backup-2023-09-04.sql
[root@kiwi222 ~]#
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql>
//删除book表
[root@kiwi222 ~]# ls
all-databases-backup-2023-09-04.sql anaconda-ks.cfg table-book-backup-2023-09-04.sql
[root@kiwi222 ~]#
mysql> SHOW TABLES;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| book |
| student |
+-------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLES book;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| student |
+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> source table-book-backup-2023-09-04.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
·············································略
//恢复成功
mysql> SHOW TABLES;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| book |
| student |
+-------------------+
2 rows in set (0.00 sec)
mysql>
3.差异备份与恢复
MySQL差异备份
//开启MySQL服务器的二进制日志功能
[root@kiwi222 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
[root@kiwi222 ~]#
[root@kiwi222 ~]# vim /etc/my.cnf
[root@kiwi222 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=1 ## 添加这一行;设置服务器标识符
log-bin=mysql_bin ## 添加这一行;开启二进制日志功能
[root@kiwi222 ~]#
[root@kiwi222 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@kiwi222 ~]#
//对数据库进行完全备份
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> SHOW TABLES FROM kiwi111;
+-------------------+
| Tables_in_kiwi111 |
+-------------------+
| book |
| student |
+-------------------+
2 rows in set (0.00 sec)
mysql>
//先进行全量备份
[root@kiwi222 ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-databases-backup-2023-09-04.sql
Enter password:
[root@kiwi222 ~]# ls
all-databases-backup-2023-09-04.sql anaconda-ks.cfg
//往里面写入数据
mysql> INSERT INTO student (name,age) VALUES ('xiaoming',21),('xiaofeng',29);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kiwi | 20 |
| 2 | lala | 30 |
| 3 | jun | 22 |
| 4 | xiaoxiao | 17 |
| 5 | xiaoming | 21 |
| 6 | xiaofeng | 29 |
+----+----------+------+
6 rows in set (0.00 sec)
//更新数据
mysql> UPDATE student SET age = 26 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kiwi | 26 |
| 2 | lala | 30 |
| 3 | jun | 22 |
| 4 | xiaoxiao | 17 |
| 5 | xiaoming | 21 |
| 6 | xiaofeng | 29 |
+----+----------+------+
6 rows in set (0.01 sec)
mysql>
MySQL差异备份数据恢复
//删除数据
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> DROP DATABASE kiwi111;
Query OK, 2 rows affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
//刷新二进制日志
[root@kiwi222 ~]# ll /opt/data/
total 122976
-rw-r-----. 1 mysql mysql 56 Aug 31 02:13 auto.cnf
-rw-------. 1 mysql mysql 1680 Aug 31 02:14 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 client-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 31 02:14 client-key.pem
-rw-r-----. 1 mysql mysql 914 Sep 4 08:31 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 4 09:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 4 09:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 31 02:13 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 08:46 ibtmp1
drwxr-x---. 2 mysql mysql 20 Sep 4 08:04 kiwi222
-rw-r-----. 1 mysql mysql 13104 Sep 4 08:32 kiwi222.err
drwxr-x---. 2 mysql mysql 20 Sep 4 08:04 kiwi333
drwxr-x---. 2 mysql mysql 20 Sep 4 08:05 kiwi444
drwxr-x---. 2 mysql mysql 4096 Sep 4 08:05 mysql
-rw-r-----. 1 mysql mysql 899 Sep 4 09:00 mysql_bin.000003
-rw-r-----. 1 mysql mysql 19 Sep 4 08:46 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Sep 4 08:32 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Aug 31 02:14 performance_schema
-rw-------. 1 mysql mysql 1680 Aug 31 02:14 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 31 02:14 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 31 02:14 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 31 02:14 sys
//刷新日志
[root@kiwi222 ~]# mysqladmin -uroot -p flush-logs
Enter password:
//恢复全量备份数据
[root@kiwi222 ~]# mysql -uroot -p < all-databases-backup-2023-09-04.sql
Enter password:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi111 |
| kiwi222 |
| kiwi333 |
| kiwi444 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> USE kiwi111;
Database changed
mysql> SELECT * FROM student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kiwi | 20 |
| 2 | lala | 30 |
| 3 | jun | 22 |
| 4 | xiaoxiao | 17 |
+----+----------+------+
6 rows in set (0.01 sec)
//此时是进行恢复差异备份数据前的状态
//查看二进制日志
[root@kiwi222 ~]# ll /opt/data/
total 124000
-rw-r-----. 1 mysql mysql 56 Aug 31 02:13 auto.cnf
-rw-------. 1 mysql mysql 1680 Aug 31 02:14 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 client-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 31 02:14 client-key.pem
-rw-r-----. 1 mysql mysql 914 Sep 4 08:31 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 4 09:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 4 09:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 31 02:13 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 4 08:46 ibtmp1
drwxr-x---. 2 mysql mysql 90 Sep 4 09:01 kiwi111
drwxr-x---. 2 mysql mysql 20 Sep 4 08:04 kiwi222
-rw-r-----. 1 mysql mysql 13104 Sep 4 08:32 kiwi222.err
drwxr-x---. 2 mysql mysql 20 Sep 4 08:04 kiwi333
drwxr-x---. 2 mysql mysql 20 Sep 4 08:05 kiwi444
drwxr-x---. 2 mysql mysql 4096 Sep 4 09:01 mysql
-rw-r-----. 1 mysql mysql 946 Sep 4 09:00 mysql_bin.000003
-rw-r-----. 1 mysql mysql 864230 Sep 4 09:01 mysql_bin.000004
-rw-r-----. 1 mysql mysql 38 Sep 4 09:00 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 Sep 4 08:32 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Aug 31 02:14 performance_schema
-rw-------. 1 mysql mysql 1680 Aug 31 02:14 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 31 02:14 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 31 02:14 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 31 02:14 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 31 02:14 sys
//恢复差异备份数据
mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 1 | 294 | BEGIN |
| mysql_bin.000003 | 294 | Table_map | 1 | 351 | table_id: 178 (kiwi111.student) |
| mysql_bin.000003 | 351 | Write_rows | 1 | 416 | table_id: 178 flags: STMT_END_F |
| mysql_bin.000003 | 416 | Xid | 1 | 447 | COMMIT /* xid=997 */ |
| mysql_bin.000003 | 447 | Anonymous_Gtid | 1 | 512 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 512 | Query | 1 | 587 | BEGIN |
| mysql_bin.000003 | 587 | Table_map | 1 | 644 | table_id: 178 (kiwi111.student) |
| mysql_bin.000003 | 644 | Update_rows | 1 | 702 | table_id: 178 flags: STMT_END_F |
| mysql_bin.000003 | 702 | Xid | 1 | 733 | COMMIT /* xid=1001 */ |
| mysql_bin.000003 | 733 | Anonymous_Gtid | 1 | 798 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 798 | Query | 1 | 899 | drop database kiwi111 |
| mysql_bin.000003 | 899 | Rotate | 1 | 946 | mysql_bin.000004;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//可以看到在End_log_pos显示到899时做了删除数据库操作，即要把数据恢复到789
//End_log_pos：pos：position位置
[root@kiwi222 ~]# mysqlbinlog --stop-position=798 /opt/data/mysql_bin.000003 | mysql -uroot -p
Enter password:
[root@kiwi222 ~]#
mysql> USE kiwi111;
Database changed
mysql> SELECT * FROM student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | kiwi | 26 |
| 2 | lala | 30 |
| 3 | jun | 22 |
| 4 | xiaoxiao | 17 |
| 5 | xiaoming | 21 |
| 6 | xiaofeng | 29 |
+----+----------+------+
6 rows in set (0.00 sec)
mysql>
//此时是差异备份后的数据
3584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
