• MySQL学习笔记21

    MySQL逻辑备份：

    mysqldump基本备份：

    本质：导出的是sql语句文件。

    优点：无论是什么存储引擎，都可以用mysqldump备份成sql语句。

    缺点：速度较慢，导入的时候出现格式不兼容的突发情况，自己本身无法直接做增量备份。

    提供三种级别的备份：表级、库级和全库级。

    不停止业务、不区分引擎。

    基本语法：

    表级别备份
    mysqldump [OPTIONS] database [tables]
    库级别备份
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    全库级别备份
    mysqldump [OPTIONS] --all-databases [OPTIONS]

     

    3、mysqldump表级备份和还原：

    案例：把db_itheima数据库中的tb_student数据表进行备份。

    1. [root@mysql-server ~]# mkdir /tmp/sqlbak
    2. [root@mysql-server ~]# mysqldump -uroot db_itheima tb_student > /tmp/sqlbak/tb_student.sql -p
    3. Enter password:
    4.  
    5.  
    6. [root@mysql-server sqlbak]# pwd
    7. /tmp/sqlbak
    8. [root@mysql-server sqlbak]# ll
    9. total 4
    10. -rw-r--r-- 1 root root 2226 Sep 28 08:25 tb_student.sql

    说明：

    如果导出的是数据库，就用数据库的名称作为sql文件的文件名。

    如果导出的是数据表，就用数据表的名称作为sql文件的文件名。

    然后我们再检查下这个tb_student.sql文件的内容：

    1. -- MySQL dump 10.13  Distrib 5.7.43, for linux-glibc2.12 (x86_64)
    2. --
    3. -- Host: localhost    Database: db_itheima
    4. -- ------------------------------------------------------
    5. -- Server version       5.7.43-log
    6.  
    7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    10. /*!40101 SET NAMES utf8 */;
    11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    12. /*!40103 SET TIME_ZONE='+00:00' */;
    13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    17.  
    18. --
    19. -- Table structure for table `tb_student`
    20. --
    21.  
    22. DROP TABLE IF EXISTS `tb_student`;
    23. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    24. /*!40101 SET character_set_client = utf8 */;
    25. CREATE TABLE `tb_student` (
    26.   `id` int(11) NOT NULL AUTO_INCREMENT,
    27.   `name` varchar(20) DEFAULT NULL,
    28.   `age` tinyint(3) unsigned DEFAULT '0',
    29.   `gender` enum('male','female') DEFAULT NULL,
    30.   `subject` enum('ui','java','yunwei','python') DEFAULT NULL,
    31.   PRIMARY KEY (`id`)
    32. ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    33. /*!40101 SET character_set_client = @saved_cs_client */;
    34.  
    35. --
    36. -- Dumping data for table `tb_student`
    37. --
    38.  
    39. LOCK TABLES `tb_student` WRITE;
    40. /*!40000 ALTER TABLE `tb_student` DISABLE KEYS */;
    41. INSERT INTO `tb_student` VALUES (1,'刘备',33,'male','java'),(2,'关羽',32,'male','yunwei'),(3,'张飞',30,'male','python'),(4,'貂蝉',18,'female','ui'),(5,'大乔',18,'female','ui');
    42. /*!40000 ALTER TABLE `tb_student` ENABLE KEYS */;
    43. UNLOCK TABLES;
    44. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    45.  
    46. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    47. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    48. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    49. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    50. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    51. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    52. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    53.  
    54. -- Dump completed on 2023-09-28  8:25:55
    55. [root@mysql-server ~]#

    所谓的逻辑备份，就是我们把数据库的增删改的SQL语句导出到一个sql文件。

    还可以多张表进行备份。

    还原：（恢复数据表）：

    有两种方式：

    # mysql < .sql文件位置 -p
    Enter password:123
    或
    # mysql -uroot -p
    Enter password:123
    mysql> source .sql文件的位置

    说明：source，这个是mysql里面的，用于导入sql文件的。

    先将tb_student数据表进行删除。然后再进行备份操作。

    1. [root@mysql-server ~]# mysql db_itheima < /tmp/sqlbak/tb_student.sql -p
    2. Enter password:
    3. [root@mysql-server ~]#
    4. [root@mysql-server ~]# mysql -uroot -p
    5. Enter password:
    6. Welcome to the MySQL monitor.  Commands end with ; or \g.
    7. Your MySQL connection id is 8
    8. Server version: 5.7.43-log MySQL Community Server (GPL)
    9.  
    10. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
    11.  
    12. Oracle is a registered trademark of Oracle Corporation and/or its
    13. affiliates. Other names may be trademarks of their respective
    14. owners.
    15.  
    16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    17.  
    18. mysql> use db_itheima;
    19. Reading table information for completion of table and column names
    20. You can turn off this feature to get a quicker startup with -A
    21.  
    22. Database changed
    23. mysql> show tables;
    24. +----------------------+
    25. | Tables_in_db_itheima |
    26. +----------------------+
    27. | tb_student           |
    28. +----------------------+
    29. 1 row in set (0.00 sec)
    30.  
    31. mysql> select * from tb_student;
    32. +----+--------+------+--------+---------+
    33. | id | name   | age  | gender | subject |
    34. +----+--------+------+--------+---------+
    35. |  1 | 刘备   |   33 | male   | java    |
    36. |  2 | 关羽   |   32 | male   | yunwei  |
    37. |  3 | 张飞   |   30 | male   | python  |
    38. |  4 | 貂蝉   |   18 | female | ui      |
    39. |  5 | 大乔   |   18 | female | ui      |
    40. +----+--------+------+--------+---------+
    41. 5 rows in set (0.00 sec)
    42.  
    43.  
    44. [root@mysql-server ~]# mysql -e "select * from db_itheima.tb_student" -uroot -p
    45. Enter password:
    46. +----+--------+------+--------+---------+
    47. | id | name   | age  | gender | subject |
    48. +----+--------+------+--------+---------+
    49. |  1 | 刘备   |   33 | male   | java    |
    50. |  2 | 关羽   |   32 | male   | yunwei  |
    51. |  3 | 张飞   |   30 | male   | python  |
    52. |  4 | 貂蝉   |   18 | female | ui      |
    53. |  5 | 大乔   |   18 | female | ui      |
    54. +----+--------+------+--------+---------+
    55.

    经过检查，看到还原成功。

    1. mysql> use db_itheima;
    2. Database changed
    3.  
    4. mysql> show tables;
    5. Empty set (0.00 sec)
    6.  
    7. mysql> source /tmp/sqlbak/tb_student.sql
    8. Query OK, 0 rows affected (0.00 sec)
    9.  
    10. Query OK, 0 rows affected (0.00 sec)
    11.  
    12. Query OK, 0 rows affected (0.00 sec)
    13.  
    14. Query OK, 0 rows affected (0.00 sec)
    15.  
    16. Query OK, 0 rows affected (0.00 sec)
    17.  
    18. Query OK, 0 rows affected (0.00 sec)
    19.  
    20. Query OK, 0 rows affected (0.00 sec)
    21.  
    22. Query OK, 0 rows affected (0.00 sec)
    23.  
    24. Query OK, 0 rows affected, 1 warning (0.00 sec)
    25.  
    26. Query OK, 0 rows affected (0.00 sec)
    27.  
    28. Query OK, 0 rows affected (0.00 sec)
    29.  
    30. Query OK, 0 rows affected (0.00 sec)
    31.  
    32. Query OK, 0 rows affected (0.00 sec)
    33.  
    34. Query OK, 0 rows affected (0.05 sec)
    35.  
    36. Query OK, 0 rows affected (0.00 sec)
    37.  
    38. Query OK, 0 rows affected (0.00 sec)
    39.  
    40. Query OK, 0 rows affected (0.00 sec)
    41.  
    42. Query OK, 5 rows affected (0.01 sec)
    43. Records: 5  Duplicates: 0  Warnings: 0
    44.  
    45. Query OK, 0 rows affected (0.00 sec)
    46.  
    47. Query OK, 0 rows affected (0.00 sec)
    48.  
    49. Query OK, 0 rows affected (0.00 sec)
    50.  
    51. Query OK, 0 rows affected, 1 warning (0.00 sec)
    52.  
    53. Query OK, 0 rows affected (0.00 sec)
    54.  
    55. Query OK, 0 rows affected (0.00 sec)
    56.  
    57. Query OK, 0 rows affected (0.00 sec)
    58.  
    59. Query OK, 0 rows affected (0.00 sec)
    60.  
    61. Query OK, 0 rows affected (0.00 sec)
    62.  
    63. Query OK, 0 rows affected (0.00 sec)
    64.  
    65.  
    66. mysql> select * from tb_student;
    67. +----+--------+------+--------+---------+
    68. | id | name   | age  | gender | subject |
    69. +----+--------+------+--------+---------+
    70. |  1 | 刘备   |   33 | male   | java    |
    71. |  2 | 关羽   |   32 | male   | yunwei  |
    72. |  3 | 张飞   |   30 | male   | python  |
    73. |  4 | 貂蝉   |   18 | female | ui      |
    74. |  5 | 大乔   |   18 | female | ui      |
    75. +----+--------+------+--------+---------+
    76. 5 rows in set (0.00 sec)

    表级的备份和还原。

    可以对单表和多表进行备份与还原。

    mysqldump库级备份和还原：

    案例：把db_itheima数据库进行备份。

    1. [root@mysql-server ~]# mysqldump --databases db_itheima > /tmp/sqlbak/db_itheima.sql -p
    2. Enter password:
    3.  
    4. [root@mysql-server ~]# ll /tmp/sqlbak
    5. total 8
    6. -rw-r--r-- 1 root root 2381 Sep 28 08:52 db_itheima.sql
    7. -rw-r--r-- 1 root root 2226 Sep 28 08:25 tb_student.sql
    8.

    还原：

    1. mysql> drop database db_itheima;
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4.  
    5. mysql> source /tmp/sqlbak/db_itheima.sql
    6. Query OK, 0 rows affected (0.00 sec)
    7.  
    8. Query OK, 0 rows affected (0.00 sec)
    9.  
    10. Query OK, 0 rows affected (0.00 sec)
    11.  
    12. Query OK, 0 rows affected (0.00 sec)
    13.  
    14. Query OK, 0 rows affected (0.00 sec)
    15.  
    16. Query OK, 0 rows affected (0.00 sec)
    17.  
    18. Query OK, 0 rows affected (0.00 sec)
    19.  
    20. Query OK, 0 rows affected (0.00 sec)
    21.  
    22. Query OK, 0 rows affected, 1 warning (0.00 sec)
    23.  
    24. Query OK, 0 rows affected (0.00 sec)
    25.  
    26. Query OK, 1 row affected (0.00 sec)
    27.  
    28. Database changed
    29. Query OK, 0 rows affected (0.00 sec)
    30.  
    31. Query OK, 0 rows affected (0.00 sec)
    32.  
    33. Query OK, 0 rows affected (0.00 sec)
    34.  
    35. Query OK, 0 rows affected (0.05 sec)
    36.  
    37. Query OK, 0 rows affected (0.00 sec)
    38.  
    39. Query OK, 0 rows affected (0.00 sec)
    40.  
    41. Query OK, 0 rows affected (0.00 sec)
    42.  
    43. Query OK, 5 rows affected (0.00 sec)
    44. Records: 5  Duplicates: 0  Warnings: 0
    45.  
    46. Query OK, 0 rows affected (0.00 sec)
    47.  
    48. Query OK, 0 rows affected (0.00 sec)
    49.  
    50. Query OK, 0 rows affected (0.00 sec)
    51.  
    52. Query OK, 0 rows affected, 1 warning (0.00 sec)
    53.  
    54. Query OK, 0 rows affected (0.00 sec)
    55.  
    56. Query OK, 0 rows affected (0.00 sec)
    57.  
    58. Query OK, 0 rows affected (0.00 sec)
    59.  
    60. Query OK, 0 rows affected (0.00 sec)
    61.  
    62. Query OK, 0 rows affected (0.00 sec)
    63.  
    64. Query OK, 0 rows affected (0.00 sec)
    65.  
    66. mysql> show databases;
    67. +--------------------+
    68. | Database           |
    69. +--------------------+
    70. | information_schema |
    71. | db_itheima         |
    72. | mysql              |
    73. | performance_schema |
    74. | sys                |
    75. +--------------------+
    76. 5 rows in set (0.00 sec)
    77.  
    78. mysql> use db_itheima;
    79. Database changed
    80.  
    81.  
    82. mysql> select * from tb_student;
    83. +----+--------+------+--------+---------+
    84. | id | name   | age  | gender | subject |
    85. +----+--------+------+--------+---------+
    86. |  1 | 刘备   |   33 | male   | java    |
    87. |  2 | 关羽   |   32 | male   | yunwei  |
    88. |  3 | 张飞   |   30 | male   | python  |
    89. |  4 | 貂蝉   |   18 | female | ui      |
    90. |  5 | 大乔   |   18 | female | ui      |
    91. +----+--------+------+--------+---------+
    92. 5 rows in set (0.00 sec)

    MySQL 全库级备份：

    在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志！！！

    开启二进制日志：

    1. # vim my.cnf
    2. [mysqld]
    3. ...
    4. server-id=10
    5. log-bin=/mysql_3306/data/binlog
    6.  
    7. # service mysql_3306 restart

    注意：其中是mysqld，代表服务端。

     

    mysqldump高级选项说明：

    刷新日志：

    就是看到日志比较大 了，就人为地进行日志刷新。

    锁表的概念，不是把表锁住，而是指阻止对表的增加、删除、修改操作。在这个备份期间里，是阻止的，但是备份结束后，又可以进行操作了。在备份阶段，这个时候比较慢。

    如果是innodb引擎，那么备份的时候，就需要加上--single-transaction。

    --master-data参数其他说明：

    1）恢复时会执行，默认是1

    2）需要RELOAD privilege并必须打开二进制文件

    3）这个选项会自动打开--lock-all-tables，关闭--lock-tables

    备份：

    前提：是一定要打开二进制日志：

    1. [root@mysql-server ~]# mysqldump --all-databases --master-data --single-transaction > /tmp/sqlbak/all.sql -p
    2. Enter password:
    3.  
    4.  
    5. [root@mysql-server ~]# ll /tmp/sqlbak
    6. total 880
    7. -rw-r--r-- 1 root root 890065 Sep 28 09:15 all.sql
    8. -rw-r--r-- 1 root root   2381 Sep 28 08:52 db_itheima.sql
    9. -rw-r--r-- 1 root root   2226 Sep 28 08:25 tb_student.sql

    其中：--master-data无所谓加还是不加。

    --single-transaction：innoDB引擎，就是必须要加的。

    还原：

    1. # mysql < all.sql -p
    2. Enter password:123

    总结：

    1. mysqldump工具备份的是==SQL语句==，故备份不需要停服务

    2. 使用备份文件==恢复==时，要保证==数据库处于运行状态==

    3. 只能实现全库，指定库，表级别的==某一时刻的备份==，本身==不能增量备份==

    4. 适用于==中小型==数据库

  • 相关阅读:
    认证授权与JWT
    vue2（vuecli5）+预渲染插件prerender-spa-plugin-next解决seo问题
    UWB NI框架嵌入式实现——Qorvo示例
    Qt(day5)
    课堂练习13 网络编程
    实现 3D 倒计时器
    SpringBoot SpringBoot 基础篇 4 基于 SpringBoot 的SSMP 整合案例 4.2 SSMP 整合案例模块创建
    从编译内核、制作initramfs到使用QEMU运行内核
    java 二维数组与稀疏数组之间相互转换
    【ARM Coresight 系列文章 3.3 - ARM Coresight SWD 协议详细介绍】
  • 原文地址：https://blog.csdn.net/chang_chunhua/article/details/133382357