备份数据库

/*!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) */;