mysql数据库备份与恢复

mysql数据库备份与恢复

---

1.数据库备份与恢复

冷备份: 数据库服务关闭，把数据库对应的数据目录下的数据文件拷贝一份 物理备份 不建议使用
温备份: 温备份介于热备份与冷备份之间，温备份允许MySQL服务实例继续运行，备份数据期间，温备份借助读锁机制保证备份期间，没有新的数据写入。
热备份: 数据库服务正常运行的时候，直接对数据库备份

冷备份

[root@localhost ~]# mkdir /tmp/mysql-back
[root@localhost ~]# mv /opt/data/* /tmp/mysql-back
[root@localhost ~]# ls /opt/data/
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mv /tmp/mysql-back/* /opt/data/
[root@localhost ~]# ls /opt/data/
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  localhost.localdomain.err  mysql.pid           private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysql                      performance_schema  public_key.pem   server-key.pem   yunwei
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

数据库备份的方式手段：

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

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

–databases：指定要备份的数据库
–all-databases：备份 mysql 服务器上的所有数据库

[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> use yunwei;
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_yunwei |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -p123456 yunwei tb_course > tb_course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[mysqldump]
user=root
password=123456
[root@localhost ~]# mysqldump --databases yunwei > yunwei.sql
[root@localhost ~]# ls
anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password  tb_course.sql  yunwei.sql
[root@localhost ~]# mysqldump --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729103039.sql  anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password  tb_course.sql  yunwei.sql
[root@localhost ~]# mysql -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yunwei;
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_yunwei |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 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        |
+----+-------------+
6 rows in set (0.00 sec)
mysql> delete from tb_course where id = 1 or id = 6;
Query OK, 2 rows affected (0.03 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
4 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p123456 yunwei < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

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

mysql> delete from tb_course where id = 1 or id = 6;
Query OK, 2 rows affected (0.