mysql数据库备份与恢复

最新推荐文章于 2024-03-25 19:34:40 发布

阿干tkl

最新推荐文章于 2024-03-25 19:34:40 发布

阅读量129

点赞数

文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/m0_58805648/article/details/125546100

版权

mysql数据库备份与恢复

- 1. mysql数据库备份与恢复

1. mysql数据库备份与恢复

1.1 数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

1.2 mysql备份工具mysqldump

# 语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
# 常用的OPTIONS：
 -uUSERNAME      # 指定数据库用户名
 -hHOST          # 指定服务器主机，请使用ip地址
 -pPASSWORD      # 指定数据库用户的密码
 -P#             # 指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
 
 
 
    
# 备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan   # 这我创建的  |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> use agan;
Database changed
mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+
2 rows in set (0.01 sec)


[root@localhost ~]# ls
anaconda-ks.cfg

# 备份所有数据库
[root@localhost ~]# mysqldump -uroot -p --all-databases > all-$(date '+%Y-%m-%d:%H-%M-%S').sql
Enter password: # 输入你的密码
[root@localhost ~]# ls
all-2022-06-30:16-49-17.sql  anaconda-ks.cfg



# 备份agan库的song表
[root@localhost ~]# mysqldump -uroot -p agan song > table-$(date '+%Y-%m-%d:%H-%M-%S').sql
Enter password:
[root@localhost ~]# ls
all-2022-06-30:16-49-17.sql  anaconda-ks.cfg  table-2022-06-30:16-55-24.sql

注意： 备份多张表 mysqldump -uroot -p agan song 表名 表名 > table-$(date '+%Y-%m-%d:%H-%M-%S').sql



# 备份agan库
[root@localhost ~]# mysqldump -uroot -p --databases agan > data-agan-$(date '+%Y-%m-%d:%H-%M-%S').sql
Enter password:
[root@localhost ~]# ls
all-2022-06-30:16-49-17.sql  data-agan-2022-06-30:17-01-19.sql
anaconda-ks.cfg              table-2022-06-30:16-55-24.sql



# 模拟误删agan数据库
[root@localhost ~]# mysql -uroot -p -e 'drop database agan'
Enter password: # 输入密码
[root@localhost ~]# mysql -uroot -p -e 'show databases'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

1.3 mysql数据恢复

# 恢复agan数据库
[root@localhost ~]# ls
all-2022-06-30:16-49-17.sql  data-agan-2022-06-30:17-01-19.sql
anaconda-ks.cfg              table-2022-06-30:16-55-24.sql

# 先把数据库agan创建出来再恢复
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 因为它恢复的是数据库里面的两张表

[root@localhost ~]# mysql -uroot -p < data-agan-2022-06-30\:17-01-19.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'show databases'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 查看两张表是否还在
mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+
2 rows in set (0.00 sec)


# 模拟误操作，把song表删了
mysql> drop table song;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
+----------------+
1 row in set (0.00 sec)


# 恢复agan数据库的song表

[root@localhost ~]# ls
all-2022-06-30:16-49-17.sql  data-agan-2022-06-30:17-01-19.sql
anaconda-ks.cfg              table-2022-06-30:16-55-24.sql
                            # 这个是我刚才备份song表把它恢复到数据库里
             
# 备份   (第一种方式)          
[root@localhost ~]# mysql -uroot -ptkl9639@G agan < table-2022-06-30\:16-55-24.sql
                             
# 进到数据库里查看                             
mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from song;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 浪子心声           |
|  2 | 好人一生平安       |
|  3 | 突然自我           |
|  4 | 安妮               |
|  5 | 一生何求           |
+----+--------------------+
5 rows in set (0.00 sec)
                             
# 演示第二种方式
mysql> drop table song;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
+----------------+
1 row in set (0.00 sec)


# 恢复agan数据库的song表
mysql> use agan;
Database changed
mysql> source  table-2022-06-30:16-55-24.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)
.....省略N

mysql> show tables;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+
2 rows in set (0.00 sec)

# 查看数据是还在的
mysql> select * from song;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 浪子心声           |
|  2 | 好人一生平安       |
|  3 | 突然自我           |
|  4 | 安妮               |
|  5 | 一生何求           |
+----+--------------------+
5 rows in set (0.00 sec)





# 模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> drop database agan;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


# 恢复整个数据库
[root@localhost ~]# mysql -uroot -ptkl9639@G  < all-2022-06-30\:16-49-17.sql
[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

1.4 差异备份与恢复

1.4.1. mysql差异备份

开启MySQL服务器的二进制日志功能

[root@localhost ~]# vim /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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=1         # 设置服务器标识符
log-bin=mysql_bin   # 开启二进制日志功能

# 重启mysql服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

对数据库进行完全备份

# 登录mysql
[root@localhost ~]# mysql -uroot -p
Enter password: # 输入密码
......省略

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 不进入agan数据库的情况下查看里面的表
mysql> show tables from agan;
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from agan.jerry;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | 叶倩文    | 女  |
|  2 | 刘德华    | 男  |
|  3 | 王杰      | 男  |
|  4 | 关之琳    | 女  |
|  5 | 陈冠希    | 男  |
+----+-----------+-----+
5 rows in set (0.03 sec)

mysql> select * from agan.song;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 浪子心声           |
|  2 | 好人一生平安       |
|  3 | 突然自我           |
|  4 | 安妮               |
|  5 | 一生何求           |
+----+--------------------+
5 rows in set (0.00 sec)



# 一定要设置开启MySQL服务器的二进制日志功能才做备份，不然没用
# 完全备份
[root@localhost ~]#  mysqldump -uroot -ptkl9639@G --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y-%m-%d:%H-%M-%S').sql

[root@localhost ~]# ll
total 864
-rw-r--r--  1 root root 877333 Jun 30 17:46 all-2022-06-30:17-46-26.sql
-rw-------. 1 root root   1093 Jun 29 06:09 anaconda-ks.cfg



# 增加新内容
[root@localhost ~]# mysql -uroot -p
Enter password:
......省略

mysql> use agan;

# 增
mysql> insert song(name) values('情已逝'),('敢爱敢做'),('当年情');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from song;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 浪子心声           |
|  2 | 好人一生平安       |
|  3 | 突然自我           |
|  4 | 安妮               |
|  5 | 一生何求           |
|  6 | 情已逝             |
|  7 | 敢爱敢做           |
|  8 | 当年情             |
+----+--------------------+
8 rows in set (0.00 sec)

# 改
mysql> update song set name = '爱如潮水' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from song;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 浪子心声     |
|  2 | 爱如潮水     |
|  3 | 突然自我     |
|  4 | 安妮         |
|  5 | 一生何求     |
|  6 | 情已逝       |
|  7 | 敢爱敢做     |
|  8 | 当年情       |
+----+--------------+
8 rows in set (0.00 sec)

# 删
mysql> delete from song where id = 4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from song;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 浪子心声     |
|  2 | 爱如潮水     |
|  3 | 突然自我     |
|  5 | 一生何求     |
|  6 | 情已逝       |
|  7 | 敢爱敢做     |
|  8 | 当年情       |
+----+--------------+
7 rows in set (0.00 sec)

1.4.2. mysql差异备份恢复

模拟误删数据

[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'drop database agan'

[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'show databases'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 由上可以看到wangqing这个数据库已被删除
# 误删之后立马刷新创建新的二进制日志

刷新创建新的二进制日志

# 这是放数据的目录
[root@localhost ~]# ll /opt/data/
total 123036
-rw-r-----. 1 mysql mysql       56 Jun 29 06:36 auto.cnf
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 client-key.pem
-rw-r-----  1 mysql mysql      883 Jun 30 17:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 30 18:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 30 18:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 29 06:36 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jun 30 17:46 ibtmp1
-rw-r-----. 1 mysql mysql    74925 Jun 30 17:45 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jun 30 17:28 mysql
-rw-r-----  1 mysql mysql     1173 Jun 30 18:00 mysql_bin.000002
-rw-r-----  1 mysql mysql       19 Jun 30 17:46 mysql_bin.index
-rw-r-----  1 mysql mysql        5 Jun 30 17:32 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 performance_schema
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jun 29 06:36 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 sys

# 刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -ptkl9639@G flush-logs

[root@localhost ~]# ll /opt/data/
total 123040
-rw-r-----. 1 mysql mysql       56 Jun 29 06:36 auto.cnf
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 client-key.pem
-rw-r-----  1 mysql mysql      883 Jun 30 17:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 30 18:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 30 18:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 29 06:36 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jun 30 17:46 ibtmp1
-rw-r-----. 1 mysql mysql    74925 Jun 30 17:45 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jun 30 17:28 mysql
-rw-r-----  1 mysql mysql     1220 Jun 30 18:03 mysql_bin.000002
-rw-r-----  1 mysql mysql      154 Jun 30 18:03 mysql_bin.000003 #新增的
-rw-r-----  1 mysql mysql       38 Jun 30 18:03 mysql_bin.index
-rw-r-----  1 mysql mysql        5 Jun 30 17:32 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 performance_schema
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jun 29 06:36 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -ptkl9639@G < all-2022-06-30\:17-46-26.sql

[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'show databases'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| agan               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'show tables from agan'
+----------------+
| Tables_in_agan |
+----------------+
| jerry          |
| song           |
+----------------+


[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'select * from agan.song'
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 浪子心声           |
|  2 | 好人一生平安       |
|  3 | 突然自我           |
|  4 | 安妮               |
|  5 | 一生何求           |
+----+--------------------+


[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'select * from agan.jerry'
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | 叶倩文    | 女  |
|  2 | 刘德华    | 男  |
|  3 | 王杰      | 男  |
|  4 | 关之琳    | 女  |
|  5 | 陈冠希    | 男  |
+----+-----------+-----+

恢复差异备份

[root@localhost ~]# ll /opt/data
total 124060
drwxr-x---  2 mysql mysql       86 Jun 30 18:06 agan
-rw-r-----. 1 mysql mysql       56 Jun 29 06:36 auto.cnf
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 client-key.pem
-rw-r-----  1 mysql mysql      883 Jun 30 17:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 30 18:08 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 30 18:08 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 29 06:36 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jun 30 17:46 ibtmp1
-rw-r-----. 1 mysql mysql    74925 Jun 30 17:45 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jun 30 18:06 mysql
-rw-r-----  1 mysql mysql     1220 Jun 30 18:03 mysql_bin.000002
-rw-r-----  1 mysql mysql   857597 Jun 30 18:06 mysql_bin.000003
-rw-r-----  1 mysql mysql       38 Jun 30 18:03 mysql_bin.index
-rw-r-----  1 mysql mysql        5 Jun 30 17:32 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 performance_schema
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jun 29 06:36 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 29 06:36 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 29 06:36 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jun 29 06:36 sys


# 检查误删数据库的位置在什么地方 (看不清楚下面有截图、最底)
[root@localhost ~]# mysql -uroot -ptkl9639@G
mysql> show binlog events in 'mysql_bin.000002';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  219 | Query          |         1 |         291 | BEGIN                                 |
| mysql_bin.000002 |  291 | Table_map      |         1 |         341 | table_id: 143 (agan.song)             |
| mysql_bin.000002 |  341 | Write_rows     |         1 |         424 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 |  424 | Xid            |         1 |         455 | COMMIT /* xid=497 */                  |
| mysql_bin.000002 |  455 | Anonymous_Gtid |         1 |         520 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  520 | Query          |         1 |         592 | BEGIN                                 |
| mysql_bin.000002 |  592 | Table_map      |         1 |         642 | table_id: 143 (agan.song)             |
| mysql_bin.000002 |  642 | Update_rows    |         1 |         720 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 |  720 | Xid            |         1 |         751 | COMMIT /* xid=499 */                  |
| mysql_bin.000002 |  751 | Anonymous_Gtid |         1 |         816 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  816 | Query          |         1 |         888 | BEGIN                                 |
| mysql_bin.000002 |  888 | Table_map      |         1 |         938 | table_id: 143 (agan.song)             |
| mysql_bin.000002 |  938 | Delete_rows    |         1 |         985 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 |  985 | Xid            |         1 |        1016 | COMMIT /* xid=502 */                  |
| mysql_bin.000002 | 1016 | Anonymous_Gtid |         1 |        1081 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 1081 | Query          |         1 |        1173 | drop database agan # 此处就是删除数据库的位置，对应的pos位置是1081                   |
| mysql_bin.000002 | 1173 | Rotate         |         1 |        1220 | mysql_bin.000003;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
20 rows in set (0.00 sec)


# 使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=1081 /opt/data/mysql_bin.000002 | mysql -uroot -ptkl9639@G

# 前面插入、改、删的都恢复
[root@localhost ~]# mysql -uroot -ptkl9639@G -e 'select * from agan.song'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 浪子心声     |
|  2 | 爱如潮水     |
|  3 | 突然自我     |
|  5 | 一生何求     |
|  6 | 情已逝       |
|  7 | 敢爱敢做     |
|  8 | 当年情       |
+----+--------------+