mysql数据库备份与恢复

1.mysql数据库备份与恢复

数据库常用备份方案有以下几种：

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

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

• 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

• 全量备份

[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > all-$(date '+%Y%m%f%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-202206%f183142.sql  anaconda-ks.cfg
// 备份school库的tb_course
[root@localhost ~]# mysqldump -uroot -p123 school tb_course > tb_course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-202206%f183142.sql  anaconda-ks.cfg  tb_course.sql


// 备份school库
[root@localhost ~]# mysqldump -uroot -p123456 school > school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-202206%f183142.sql  anaconda-ks.cfg  school.sql  tb_course.sql

• mysql数据恢复
  • 首先删除school库

mysql> drop database school;
Query OK, 2 rows affected (0.71 sec)

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

恢复数据库

[root@localhost ~]# mysql -uroot -p'123456' < all-20220630045011.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# mysql -uroot -p'123456' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

• 备份数据表以及恢复
  删除数据表

mysql> drop table tb_course;
Query OK, 0 rows affected (0.01 sec)

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


//恢复数据表表 可以将表恢复到指定库中
[root@localhost ~]# mysql -uroot -p'123456' school < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

• 差异备份与恢复

开启MySQL服务器的二进制日志功能
 [root@localhost local]# 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@localhost ~]# systemctl restart mysqld.service           //重启一下服务

首先完全备份一次

[root@localhost ~]# mysqldump -uroot -p'123456' --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql


#添加数据
mysql> select * from tb_course
    -> ;
+----+-------------+
| id | course_name |
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Pytho,n     |
|  4 | Go          |
|  5 | C++         |
|  7 | Html        |
+----+-------------+
6 rows in set (0.01 sec)

mysql> insert tb_course (course_name) values ('Linux');
Query OK, 1 row affected (0.00 sec)


mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Pytho,n     |
|  4 | Go          |
|  5 | C++         |
|  7 | Html        |
|  8 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

#更新数据
mysql> update tb_course  set course_name = 'Php' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Php         |
|  4 | Go          |
|  5 | C++         |
|  7 | Html        |
|  8 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)



#删除表
mysql> drop table tb_students_info;
Query OK, 0 rows affected (0.10 sec)

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

• 删除数据库

mysql> drop database school;
Query OK, 1 row affected (0.10 sec)

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

• 然后马上刷新创建新的二进制日志以免导致数据太多而招不到删除命令

[root@localhost ~]# mysqladmin -uroot -p‘123456’ flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ll
总用量 123020
-rw-r-----. 1 mysql mysql    46186 6月  30 19:46 localhost.168.183.139.err
-rw-r-----. 1 mysql mysql       56 6月  28 20:32 auto.cnf
-rw-------. 1 mysql mysql     1680 6月  28 20:32 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 6月  28 20:32 ca.pem
-rw-r--r--. 1 mysql mysql     1112 6月  28 20:32 client-cert.pem
-rw-------. 1 mysql mysql     1680 6月  28 20:32 client-key.pem
-rw-r-----. 1 mysql mysql     1084 6月  30 19:46 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 6月  30 19:46 ibdata1
-rw-r-----. 1 mysql mysql 50331648 6月  30 19:46 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 6月  28 20:32 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月  30 20:21 ibtmp1
-rw-r-----. 1 mysql mysql     4224 6月  30 08:39 localhost.err
-rw-r-----. 1 mysql mysql    15963 6月  28 21:50 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 6月  30 19:14 mysql
-rw-r-----. 1 mysql mysql      154 6月  30 20:21 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 6月  30 20:21 mysql_bin.index
-rw-r-----. 1 mysql mysql        7 6月  30 19:46 mysql.pid
drwxr-x---. 2 mysql mysql     8localhost 6月  28 20:32 performance_schema
-rw-------. 1 mysql mysql     1680 6月  28 20:32 private_key.pem
-rw-r--r--. 1 mysql mysql      452 6月  28 20:32 public_key.pem
drwxr-x---. 2 mysql mysql      118 6月  30 19:27 school
-rw-r--r--. 1 mysql mysql     1112 6月  28 20:32 server-cert.pem
-rw-------. 1 mysql mysql     1676 6月  28 20:32 server-key.pem

• 恢复完全备份

[root@localhost ~]# mysql -uroot -p‘123456’ < all-202206%f202155.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

• 检查误删数据库的位置在什么地方

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    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                                                        |
| mysql_bin.000003 |  123 | Previous_gtids |        10 |         154 |                                                                                              |
| mysql_bin.000003 |  154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 |  219 | Query          |        10 |         375 | use `school`; create table info(id int not null primary key auto_increment,name varchar(10)) |
| mysql_bin.000003 |  375 | Anonymous_Gtid |        10 |         440 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 |  440 | Query          |        10 |         514 | BEGIN                                                                                        |
| mysql_bin.000003 |  514 | Table_map      |        10 |         566 | table_id: 176 (school.info)                                                                  |
| mysql_bin.000003 |  566 | Write_rows     |        10 |         635 | table_id: 176 flags: STMT_END_F                                                              |
| mysql_bin.000003 |  635 | Xid            |        10 |         666 | COMMIT /* xid=955 */                                                                         |
| mysql_bin.000003 |  666 | Anonymous_Gtid |        10 |         731 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 |  731 | Query          |        10 |         805 | BEGIN                                                                                        |
| mysql_bin.000003 |  805 | Table_map      |        10 |         862 | table_id: 174 (school.tb_course)                                                             |
| mysql_bin.000003 |  862 | Write_rows     |        10 |         908 | table_id: 174 flags: STMT_END_F                                                              |
| mysql_bin.000003 |  908 | Xid            |        10 |         939 | COMMIT /* xid=958 */                                                                         |
| mysql_bin.000003 |  939 | Anonymous_Gtid |        10 |        1004 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 | 1004 | Query          |        10 |        1078 | BEGIN                                                                                        |
| mysql_bin.000003 | 1078 | Table_map      |        10 |        1148 | table_id: 175 (school.tb_students_info)                                                      |
| mysql_bin.000003 | 1148 | Update_rows    |        10 |        1224 | table_id: 175 flags: STMT_END_F                                                              |
| mysql_bin.000003 | 1224 | Xid            |        10 |        1255 | COMMIT /* xid=961 */                                                                         |
| mysql_bin.000003 | 1255 | Anonymous_Gtid |        10 |        1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 | 1320 | Query          |        10 |        1394 | BEGIN                                                                                        |
| mysql_bin.000003 | 1394 | Table_map      |        10 |        1464 | table_id: 175 (school.tb_students_info)                                                      |
| mysql_bin.000003 | 1464 | Delete_rows    |        10 |        1521 | table_id: 175 flags: STMT_END_F                                                              |
| mysql_bin.000003 | 1521 | Xid            |        10 |        1552 | COMMIT /* xid=963 */                                                                         |
| mysql_bin.000003 | 1552 | Anonymous_Gtid |        10 |        1617 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000003 | 1617 | Query          |        10 |        1715 | drop database school                                                                         |
| mysql_bin.000003 | 1715 | Rotate         |        10 |        1762 | mysql_bin.000004;pos=4                                                                       |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)

• 使用mysqlbinlog恢复差异备份

[root@localhost ~]# mysqlbinlog --stop-position=1617 /opt/data/mysql_bin.000003 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

• 检查数据是否已经修复

mysql> use school;
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_school |
+------------------+
| info             |
| tb_course        |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)
mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | tom      |
|  2 | jerry    |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   23 | F    |    190 |         5 |
| 11 | Liming |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)