mysql数据库的备份和还原的总结

mysql数据库的备份和还原的总结

（来自一运维同事的总结）

1. 备份方式：

1. 热备：数据库在线进行备份，不影响读和写的在线备份方式！
2. 温备：数据库在线进行备份，对表备份时先锁定写操作，仅可以执行读操作的备份方式！
3. 冷备：离线备份，也就是关闭mysql后备份数据库。

2. 备份策略：

1. 完全备份：备份全部数据。（可能备份某一个库的数据，也可能备份N个库的数据）。
2. 增量备份：仅备份上次完全备份或增量备份以后发生了变化的数据。
3. 差异备份：第一次完全备份后，将后续发生变化的数据进行备份，时间长了，会很占磁盘空间。

3. 物理备份和逻辑备份及区别：

1. 物理：直接复制数据文件，备份和恢复操作都比较简单，能k够跨mysql的版本，恢复速度快，属于文件系统级别。
2. 逻辑：备份表中的数据和代码，将数据导出至文本文件中，必要时还原。# 备份或恢复都需要mysql服务器进程参与，速度慢，数据转换成文本字符时，丢失浮点数在精度方面使用文本处理工具直接对其处理，可移植性强，恢复简单、备份的结果为ASCII文件，可以编辑，与存储引擎无关，可以通过网络备份和恢复。

 

一、备份/还原表            # 语法：mysqldump [options] [db_name [tbl_name ...]]

        

a)     备份单个表：

Mysqldump -uroot -p db_1 tb_1 >/xxx/xxxtb.sql    

#备份db_1库的tb_1表到/xx/目录下，并命名为xxtb.sql文件。

b)  备份多个表

Mysqldump -uroot -p db_1 tb_1 tb_2 tb_3 >/xxx/xxxtb.sql   

#备份db_1库中的tb_1,tb_2,tb_3表。

 

c)  恢复表：                    # mysql [OPTIONS] [database]

         1.  mysql -p yq_1 < /xxtb.sql  #此时，备份xxtb.sql中的语句将被执行，开始使用xxtb.sql恢复yq_1库中的表。

 

二、备份/还原数据库 

mysqldump [options] [db_name [tbl_name ...]]

1. 备份单个数据库：         示例：mysqldump -uusername -p yq_1 > /xxx/xxxdb.sql

2. 备份多个数据库：          示例：mysqldump -uusername -p --databases db_1 db_2 > db.sql

3. 备份整个数据库：          示例：mysqldump -uusername -p --all-databases > /root/db.sql

 

还原数据库

mysql -u root -p [dbname] < backup.sq

1. 还原单个数据库：          示例：mysql -uusername -p yq_1 < /db.sql

# 还原时必须要指定要还原的数据库yq_1且须存在（因为单个库备份不备份数据库信息，只备   份数据库中表的信息。），不存在则须手动创建后，才可进行还原。

2. 还原多个数据库：          示例：mysql -uusername -p < db.sql

#还原多个数据库时，无需指定要还原哪些数据库，因为备份中备份了数据库信息，有数据库不存在的话会自动创建，存在则将其覆盖。

3. 还原整个数据库：         示例：mysql -uusername -p < /db.sql

三、单表恢复

有时候会面临这样一些情况，备份了整个数据库，却只需要恢复其中的某个table或某几个tables。

1.备份整个数据库，恢复单张表。

比如备份一个库test，相当于将test库中的所有表备份。mysql也是这样做的，在查看整个sp2p库的备份时，可以发现，整个备份中全部为创建表结构和插入表数据的sql语句，其中对每个表的备份分为2部分，如下

#备份表结构

--

-- Table structure for table `t_banks`

--

......创建表结构语句........

#备份表数据

--

-- Dumping data for table `t_banks`

--

 

 

可使用正则表达式抽取要恢复的单表内容

从全库备份中抽取出webuser表的表结构

[root@develop database]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `webuser`/!d;q' back20220629.sql

DROP TABLE IF EXISTS `webuser`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `webuser` (
`STAFF_NO` char(10) DEFAULT NULL,
`LINK_CODE` char(20) DEFAULT NULL,
`PWD` char(50) DEFAULT NULL,
`LANGUAGE` char(1) DEFAULT NULL,
`PWD_OPTIONS` char(1) DEFAULT NULL,
`PWD_DATES` date DEFAULT NULL,
`USER_EXPIRE` date DEFAULT NULL,
UNIQUE KEY `WEBUSER_SN` (`STAFF_NO`),
KEY `WEBUSER_C` (`LINK_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

 

从全库备份中抽取出t表的内容

[root@develop database]# grep 'INSERT INTO `webuser`' back20220629.sql
INSERT INTO `webuser` VALUES ('Admin','S2205644055604916107','','C','','2022-06-21',NULL),('Admin2','S2521877513468574057','','C','','2022-06-22',NULL),('Admin1','S2216119024871668258','','C','','2022-06-24',NULL),('01030249','S0407085631504005794','','C','','2022-06-28',NULL);

 

 

四、使用source命令恢复数据库和表

1. 恢复备份单个表的备份： mysql>source /tb.sql 

注意：使用source恢复单个表，则需要进入所在数据库中，且如果表不存在需手动创建。

  使用source恢复单个库，同理。

  使用source恢复多个库，也无需手动创建数据库。

注意：

1. mysqldump命令备份单个库时（也就是不使用--databases/--all-databases等系列的参数时），只备份单个库下的所有表，注意其备份不包括备份（创建/删除）数据库的操作。

 

例如：

备份好单个库后，还原时，如mysqldump -p db1 > /xxx/db.sql，备份单个库，msyqldump命令只备份db1数据库下的所有表，而不包括创建数据库的CREATE DATABASE语句，因此如果把db1删了使用db.sql还原时会出现：“No database selected”的错误信息，因此在还原前必须先创建db1（也可以自定义数据库名为db2、db3等）数据库，或者可以直接用mysqldump -u root -p --databases db_1>db.sql，这样不仅备份了所有数据库下的表结构，而且包括了创建数据库的语句。

 

 

 

线上实例：2021-10月

mysql 5.7.22

全备

mysqldump -uroot -p -R --opt -A > dump.sql

删除某库

还原

mysql -uroot -p <dump.sql

连接检查数据无误

 

部分库备份

mysqldump -uroot -p -R --opt --databases whatshr > dump1.sql

删除whatshr库

还原

mysql -uroot -p <dump1.sql

连接检查数据无误

注：备份时可加上参数--flush-logs刷新binlog日志，结合全量+增量备份完成数据恢复。

 

参数解释：

--opt：此mysqldump命令参数是可选的，如果带上这个选项代表激活了mysqldump命令的quick，add-drop-table，add-locks，extended-insert，lock-tables参数，
也就是通过--opt参数在使用Mysqldump导出Mysql数据库信息时不需要再附加上述这些参数。

　　–quick：代表忽略缓冲输出，Mysqldump命令直接将数据导出到指定的SQL文件。

　　–add-drop-table：顾名思义，就是在每个CREATE TABEL命令之前增加DROP-TABLE IF EXISTS语句，防止数据表重名。

　　–add-locks：表示在INSERT数据之前和之后锁定和解锁具体的数据表，你可以打开Mysqldump导出的SQL文件，在INSERT之前会出现LOCK TABLES和UNLOCK TABLES语句。

　　–extended-insert (-e)：此参数表示可以多行插入。

--all-databases  , -A 导出全部数据库。

--routines, -R 导出存储过程以及自定义函数。