首页 > 数据库 >备份数据库

备份数据库

时间：2024-01-30 19:56:13浏览次数：33  
标签：insert name 备份 version sql table backup 数据库 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DELIMITER //
CREATE PROCEDURE `backup_all_table_whether_has_insert`()
BEGIN
  DECLARE var_count INT ;
  DECLARE var_tbl_name VARCHAR (200) ;
  DECLARE in_dbname VARCHAR (200) DEFAULT DATABASE();
  DECLARE in_if_backup_insert VARCHAR (200) ;
  
  DECLARE tbnames CURSOR FOR 
  SELECT 
    table_name 
  FROM
    information_schema.tables 
  WHERE table_schema = in_dbname ;
  SELECT 
    COUNT(*) INTO var_count 
  FROM
    information_schema.tables 
  WHERE table_schema = in_dbname ;
  OPEN tbnames ;
  SET FOREIGN_KEY_CHECKS = 0 ;
  
  SET in_if_backup_insert='insert';
  
  SET @version_backup_all_table_whether_has_insert = LAST_INSERT_ID() ;
  loop_i :
  LOOP
    IF var_count = 0 
    THEN LEAVE loop_i ;
    END IF ;
    FETCH tbnames INTO var_tbl_name ;
    IF(var_tbl_name != 'backup_table') 
    THEN CALL backup_one_table_create_table_sql (
      in_dbname,
      var_tbl_name,
      @version_backup_all_table_whether_has_insert
    ) ;
    CALL backup_one_table_idx_sql (
      in_dbname,
      var_tbl_name,
      @version_backup_all_table_whether_has_insert
    ) ;
    CALL backup_one_table_fk_sql (
      in_dbname,
      var_tbl_name,
      @version_backup_all_table_whether_has_insert
    ) ;
    IF (in_if_backup_insert = 'insert') 
    THEN CALL backup_one_table_insert_sql (
      in_dbname,
      var_tbl_name,
      @version_backup_all_table_whether_has_insert
    ) ;
    END IF ;
    END IF ;
    SET var_count = var_count - 1 ;
  END LOOP ;
  CLOSE tbnames ;
--  SELECT *   FROM    backup_table 
--  WHERE `version` = @version_backup_all_table_whether_has_insert ;
  SET FOREIGN_KEY_CHECKS = 1 ;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `backup_one_table_create_table_sql`(
  in_dbname VARCHAR (200),
  in_table_name VARCHAR (200),
  in_version VARCHAR (200)
)
BEGIN
  SET @version_backup_one_table_create_table_sql = IF(
    in_version = '',
    MD5(UUID()),
    in_version
  ) ;
  SET @version_backup_one_table_create_table_sql = IFNULL(
    @version_backup_one_table_create_table_sql,
    MD5(UUID())
  ) ;
  SET SESSION group_concat_max_len = 4294967295 ;
  SELECT 
    GROUP_CONCAT(t.createTable SEPARATOR "\r\n") INTO @createTable_backup_one_table_create_table_sql 
  FROM
    (SELECT 
      CONCAT_WS(
        '',
        'create table ',
        in_table_name,
        '('
      ) AS createTable 
    UNION
    ALL 
    SELECT 
      CONCAT_WS(
        '',
        '`',
        COLUMN_NAME,
        '` ',
        COLUMN_TYPE,
        ' ',
        IF(
          IS_NULLABLE = 'NO',
          'NOT NULL',
          'NULL'
        ),
        ' ',
        extra,
        ' ',
        IF(
          COLUMN_DEFAULT = NULL,
          '',
          CONCAT('default ', COLUMN_DEFAULT)
        ),
        ' comment',
        ' ',
        "'",
        COLUMN_COMMENT,
        "' ,"
      ) 
    FROM
      information_schema.COLUMNS t1 
    WHERE t1.table_schema = in_dbname 
      AND t1.TABLE_NAME = in_table_name 
    UNION
    ALL 
    SELECT 
      'PRIMARY KEY (`id`) )' 
    UNION
    ALL 
    SELECT 
      CONCAT_WS(
        '',
        ' ENGINE=',
        ENGINE,
        ' DEFAULT CHARSET=',
        SUBSTRING(
          TABLE_COLLATION,
          1,
          LOCATE('_', TABLE_COLLATION) - 1
        ),
        ' comment=',
        "'",
        table_comment,
        "';"
      ) 
    FROM
      information_schema.TABLES 
    WHERE TABLE_SCHEMA = in_dbname 
      AND TABLE_NAME = in_table_name) t ;
  CREATE TABLE IF NOT EXISTS backup_table (
    `id` INT (10) NOT NULL AUTO_INCREMENT COMMENT '物理主键',
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `table_name` VARCHAR (200) NOT NULL COMMENT '表名',
    `version` VARCHAR (200) NOT NULL COMMENT '版本',
    `type` VARCHAR (200) NOT NULL COMMENT '类型',
    `md5` VARCHAR (200) COMMENT 'md5值',
    `str` LONGTEXT COMMENT '语句',
    PRIMARY KEY (`id`)
  ) ENGINE = MYISAM AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 0 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '备份记录表' KEY_BLOCK_SIZE = 0 MAX_ROWS = 0 MIN_ROWS = 0 ROW_FORMAT = DYNAMIC ;
  INSERT INTO backup_table SET table_name = in_table_name,
  `type` = 'create table',
  str = @createTable_backup_one_table_create_table_sql,
  `md5` = MD5(
    @createTable_backup_one_table_create_table_sql
  ),
  `version` = @version_backup_one_table_create_table_sql ;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `backup_one_table_fk_sql`(
  in_dbname VARCHAR (200),
  in_table_name VARCHAR (200),
  in_version VARCHAR (200)
)
BEGIN
  SET @version_backup_one_table_fk_sql = IF(
    in_version = '',
    MD5(UUID()),
    in_version
  ) ;
  SET @version_backup_one_table_fk_sql = IFNULL(
    @version_backup_one_table_fk_sql,
    MD5(UUID())
  ) ;
  SET SESSION group_concat_max_len = 4294967295 ;
  SELECT 
    GROUP_CONCAT(t.fk_str SEPARATOR "\r\n") INTO @fk_backup_one_table_fk_sql 
  FROM
    (SELECT 
      CONCAT_WS(
        '',
        'alter table `',
        t.TABLE_NAME,
        '` add constraint ',
        t.CONSTRAINT_NAME,
        ' foreign key (`',
        k.COLUMN_NAME,
        '`) ',
        ' REFERENCES ',
        k.REFERENCED_TABLE_NAME,
        '(`',
        k.REFERENCED_COLUMN_NAME,
        '`);'
      ) AS fk_str 
    FROM
      information_schema.TABLE_CONSTRAINTS t 
      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
        ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
        AND t.TABLE_NAME = k.TABLE_NAME 
        AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 
    WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' 
      AND t.table_schema = in_dbname 
      AND t.TABLE_NAME = in_table_name) t ;
  CREATE TABLE IF NOT EXISTS backup_table (
    `id` INT (10) NOT NULL AUTO_INCREMENT COMMENT '物理主键',
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `table_name` VARCHAR (200) NOT NULL COMMENT '表名',
    `version` VARCHAR (200) NOT NULL COMMENT '版本',
    `type` VARCHAR (200) NOT NULL COMMENT '类型',
    `md5` VARCHAR (200) COMMENT 'md5值',
    `str` LONGTEXT COMMENT '语句',
    PRIMARY KEY (`id`)
  ) ENGINE = MYISAM AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 0 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '备份记录表' KEY_BLOCK_SIZE = 0 MAX_ROWS = 0 MIN_ROWS = 0 ROW_FORMAT = DYNAMIC ;
  INSERT INTO backup_table SET table_name = in_table_name,
  `type` = 'foreign key',
  str = @fk_backup_one_table_fk_sql,
  `md5` = MD5(@fk_backup_one_table_fk_sql),
  `version` = @version_backup_one_table_fk_sql ;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `backup_one_table_idx_sql`(
  in_dbname VARCHAR (200),
  in_table_name VARCHAR (200),
  in_version VARCHAR (200)
)
BEGIN
  SET @version_backup_one_table_idx_sql = IF(
    in_version = '',
    MD5(UUID()),
    in_version
  ) ;
  SET @version_backup_one_table_idx_sql = IFNULL(
    @version_backup_one_table_idx_sql,
    MD5(UUID())
  ) ;
  SET SESSION group_concat_max_len = 4294967295 ;
  SELECT 
    GROUP_CONCAT(t.create_index SEPARATOR "\r\n") INTO @createIndex_backup_one_table_idx_sql 
  FROM
    (SELECT 
      tt.create_index 
    FROM
      (SELECT 
        IF(
          t.INDEX_NAME = 'PRIMARY',
          NULL,
          CONCAT_WS(
            '',
            'alter table ',
            in_table_name,
            ' add ',
            IF(
              t.NON_UNIQUE = 0,
              'unique index ',
              ' index '
            ),
            t.INDEX_NAME,
            ' (`',
            REPLACE(
              GROUP_CONCAT(
                t.column_name 
                ORDER BY t.seq_in_index
              ),
              ',',
              '`,`'
            ),
            '`);'
          )
        ) AS create_index 
      FROM
        information_schema.statistics t 
      WHERE table_schema = in_dbname 
        AND table_name = in_table_name 
      GROUP BY TABLE_NAME,
        INDEX_NAME) tt 
    WHERE tt.create_index IS NOT NULL) t ;
  CREATE TABLE IF NOT EXISTS backup_table (
    `id` INT (10) NOT NULL AUTO_INCREMENT COMMENT '物理主键',
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `table_name` VARCHAR (200) NOT NULL COMMENT '表名',
    `version` VARCHAR (200) NOT NULL COMMENT '版本',
    `type` VARCHAR (200) NOT NULL COMMENT '类型',
    `md5` VARCHAR (200) COMMENT 'md5值',
    `str` LONGTEXT COMMENT '语句',
    PRIMARY KEY (`id`)
  ) ENGINE = MYISAM AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 0 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '备份记录表' KEY_BLOCK_SIZE = 0 MAX_ROWS = 0 MIN_ROWS = 0 ROW_FORMAT = DYNAMIC ;
  INSERT INTO backup_table SET table_name = in_table_name,
  `type` = 'create index',
  str = @createIndex_backup_one_table_idx_sql,
  `md5` = MD5(
    @createIndex_backup_one_table_idx_sql
  ),
  `version` = @version_backup_one_table_idx_sql ;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `backup_one_table_insert_sql`(
    IN `in_dbname` VARCHAR (200),
    IN `in_table_name` VARCHAR (200),
    IN `in_version` VARCHAR (200)
)
BEGIN
  SET @version_backup_one_table_insert_sql = IF(in_version = '',MD5(UUID()),in_version) ;
  SET @version_backup_one_table_insert_sql = IFNULL(@version_backup_one_table_insert_sql,MD5(UUID())) ;
  
  CREATE TABLE IF NOT EXISTS backup_one_table_insert_sql_temp (str LONGTEXT) ENGINE = MYISAM DEFAULT CHARACTER SET = utf8 ;
  DELETE FROM backup_one_table_insert_sql_temp ;
  SET SESSION group_concat_max_len = 4294967295 ;
  
  -- ifnull(id,"NULL"),ifnull(order_tech_service_id,"NULL"),ifnull(event_id,"NULL"),ifnull(time,"NULL"),
  SELECT 
    GROUP_CONCAT(
      column_name 
      ORDER BY ordinal_position
    ) INTO @c_backup_one_table_insert_sql 
  FROM
    (SELECT 
      CONCAT_WS(
        '',
        'ifnull(`',
        t1.COLUMN_NAME,
        '`,"NULL")'
      ) AS column_name,
      ordinal_position 
    FROM
      information_schema.COLUMNS t1 
    WHERE t1.table_schema = in_dbname 
      AND t1.TABLE_NAME = in_table_name) t ;
      
-- `id`,`app_code`,`app_name`,`status`,`description`,`create_time`,`last_update_time`
 SELECT 
    GROUP_CONCAT(
      column_name 
      ORDER BY ordinal_position
    )  INTO @col_list_sql 
  FROM
    (SELECT 
      CONCAT_WS(
        '',
        '`',
        t1.COLUMN_NAME,
        '`'
      ) AS column_name,
      ordinal_position 
    FROM
      information_schema.COLUMNS t1 
    WHERE t1.table_schema = in_dbname 
      AND t1.TABLE_NAME = in_table_name) t ;

 -- insert into backup_one_table_insert_sql_temp SELECT concat_ws("','",ifnull(`id`,"NULL"),ifnull(`app_code`,"NULL"),ifnull(`app_name`,"NULL"),ifnull(`status`,"NULL"),ifnull(`description`,"NULL"),ifnull(`create_time`,"NULL"),ifnull(`last_update_time`,"NULL")) FROM tbl_app;
  SET @tb_backup_one_table_insert_sql = CONCAT_WS(
    '',
    'insert into backup_one_table_insert_sql_temp SELECT concat_ws("\',\'",',
    @c_backup_one_table_insert_sql,
    ') FROM ',
    in_table_name,
    ';'
  ) ;
  
  
  PREPARE stmt FROM @tb_backup_one_table_insert_sql ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;
  
  -- 表中数据：一行一行的
  -- '1','10','华岭应用','2','华岭为业务主体的芯片测试云应用','2014-01-10 00:00:00','2014-01-10 00:00:00'
  
  UPDATE 
    backup_one_table_insert_sql_temp 
  SET
    str = CONCAT_WS(
      '',
      'insert into ',
      in_table_name,
       ' (',
      @col_list_sql ,
      ') ',
      " values('",
      REPLACE(str, "'NULL'", 'NULL'),
      "');"
    ) ;
    
-- 表中数据：一行一行的。 
-- insert into tbl_app values('1','10','华岭应用','2','华岭为业务主体的芯片测试云应用','2014-01-10 00:00:00','2014-01-10 00:00:00');
      
  CREATE TABLE IF NOT EXISTS backup_table (
    `id` INT (10) NOT NULL AUTO_INCREMENT COMMENT '物理主键',
    `create_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `table_name` VARCHAR (200) NOT NULL COMMENT '表名',
    `version` VARCHAR (200) NOT NULL COMMENT '版本',
    `type` VARCHAR (200) NOT NULL COMMENT '类型',
    `md5` VARCHAR (200) COMMENT 'md5值',
    `str` LONGTEXT COMMENT '语句',
    PRIMARY KEY (`id`)
  ) ENGINE = MYISAM AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 0 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '备份记录表' KEY_BLOCK_SIZE = 0 MAX_ROWS = 0 MIN_ROWS = 0 ROW_FORMAT = DYNAMIC ;
  
  -- 多行聚合成一行
  SELECT 
    GROUP_CONCAT(str SEPARATOR "\r\n") INTO @ttt_backup_one_table_insert_sql 
  FROM
    backup_one_table_insert_sql_temp ;
    
   
    -- 聚合数据插入到表中
  INSERT INTO backup_table SET table_name = in_table_name,
  `type` = 'insert',
  str = @ttt_backup_one_table_insert_sql,
  `md5` = MD5(
    @ttt_backup_one_table_insert_sql
  ),
  `version` = @version_backup_one_table_insert_sql ;
  DROP TABLE backup_one_table_insert_sql_temp ;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `backup_one_table_whether_has_insert`(
  in_dbname VARCHAR (200),
  in_table_name VARCHAR (200),
  in_if_backup_insert VARCHAR (200)
)
BEGIN
  SET @version_backup_one_table_whether_has_insert = MD5(UUID()) ;
  CALL backup_one_table_create_table_sql (
    in_dbname,
    in_table_name,
    @version_backup_one_table_whether_has_insert
  ) ;
  CALL backup_one_table_idx_sql (
    in_dbname,
    in_table_name,
    @version_backup_one_table_whether_has_insert
  ) ;
  CALL backup_one_table_fk_sql (
    in_dbname,
    in_table_name,
    @version_backup_one_table_whether_has_insert
  ) ;
  IF (in_if_backup_insert = 'insert') 
  THEN CALL backup_one_table_insert_sql (
    in_dbname,
    in_table_name,
    @version_backup_one_table_whether_has_insert
  ) ;
  END IF ;
  SELECT 
    * 
  FROM
    backup_table 
  WHERE `version` = @version_backup_one_table_whether_has_insert ;
END//
DELIMITER ;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
View Code

 

标签：insert,name,备份,version,sql,table,backup,数据库
From： https://www.cnblogs.com/wanglifeng717/p/17997837

相关文章

  • 今年接到一个根据excel来更新数据库的需求，用php写个小脚本
    需求大概内容是，excel中有些条目需要删除、有些需要新增，就需要基于这份excel生成删、增的SQL。要求是这样的：蓝色要删除的，黄色是要新增的，白色和灰色的不用管。我第一时间就在想：还得识别单元格颜色？excel长这样： 这种小需求用php来处理就很方便，用的框架是yii。发现读取到的只......
  • 不同服务器数据库，数据表数据同步方案
    代码片段：1usingDataSync.Core;2usingFurion.Logging.Extensions;3usingMicrosoft.Data.SqlClient;4usingMicrosoft.Extensions.Logging;5usingSystem.Data;6namespaceDataSync.Application.DataSync.Services7{8publicclassDataSyn......
  • ContosoRetailDW数据库恢复问题
    https://www.microsoft.com/en-us/download/details.aspx?id=18279下载ContosoBIdemoBAK.exe和ContosoBIdemoABF.exe解压后，无法恢复数据库的问题sqlserverexpress2008直接gui恢复时，提示标题:MicrosoftSQLServerManagementStudio------------------------------路径......
  • XFS文件系统的备份和恢复
    XFS文件系统的备份和恢复1.概念梳理： 扩展（常规策略：每天晚上一次增量备份，每周一次完全备份）：完全备份：每次把指定的备份目录完整的复制一遍，不管目录下得文件有没有变化增量备份：每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份。......
  • Zabbix升级MySQL数据库的故障
    收到一个MySQL安全漏洞告警，需要修复OracleMySQLJAN2024CriticalPatchUpdate因此想着升级MySQL，步骤如下：sudoapt-getupdatesudoapt-getupgrademysql-serversudosystemctlrestartmysqlmysql--version完成后Zabbix网页界面能打开，但是提示错误如下：TheZabbixdatabaseve......
  • etcd v2 版本数据备份恢复脚本
    importrequestsimportjsonimportsysaction=sys.argv[1]etcdaddr=sys.argv[2]defbackup_data():url=f"{etcdaddr}/v2/keys/?recursive=true"response=requests.get(url)ifresponse.status_code==200:data=res......
  • 把git当作一个小型最终一致性的 json 数据库
    这几天写了一些有趣的代码：把git当作json数据库。做法是这样的：创建一个git仓库为每个最小粒度的数据创建一个独立的json文件({table}.json)客户端通过Python写git操作代码，实现几个数据库操作接口。数据库操作接口最小集：初始化：把git仓库拉下来（这个后面可以优化为只拉取指......
  • 科技云报道：新趋势下，国产数据库或“春山可望”
    科技云报道原创。从540亿元到1286亿元——这是中国通信标准化协会大数据技术标准推进委员会针对中国数据库行业给出的一份预测报告。报告指出，未来五年，中国数据库行业将从百亿级市场跨越成为千亿级市场。最近两年，中国的数据库行业似乎也在迎来一轮新热潮，而这种热潮是伴随着5G、云计......
  • 从数据库更新模型时出现System.ArgumentException
    尝试从数据库进行更新时,遇到类型未system.argumentexception的异常 来自热心网友的提醒：初看这个问题的时候以为有相同的表、主键啊之类的冲突排除了很久后检查了一下EntitySetMapping发现存在相同的节点呢删除了就ok了检查了一下EntitySetMapping发现存在相同的节......
  • 分库分表已成为过去式，使用分布式数据库才是未来
    转载至我的博客https://www.infrastack.cn，公众号：架构成长指南当我们使用Mysql数据库到达一定量级以后，性能就会逐步下降，而解决此类问题，常用的手段就是引入数据库中间件进行分库分表处理，比如使用Mycat、ShadingShpere、tddl，但是这种都是过去式了，现在使用分布式数据库可以避免......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99