千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

mysql无备份恢复创建独立表空间

发表于：2025-02-01 作者：千家信息网编辑
千家信息网最后更新 2025年02月01日，本篇内容主要讲解"mysql无备份恢复创建独立表空间"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"mysql无备份恢复创建独立表空间"吧!创建恢复库点击(
千家信息网最后更新 2025年02月01日mysql无备份恢复创建独立表空间

本篇内容主要讲解"mysql无备份恢复创建独立表空间"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"mysql无备份恢复创建独立表空间"吧!


创建恢复库

点击(此处)折叠或打开

  1. mysql> create database helpdb default charset utf8;

  2. Query OK, 1 row affected (0.01 sec)

创建恢复表

点击(此处)折叠或打开

  1. mysql>

  2. CREATE TABLE `newaccount` (

  3. `id` int(11) NOT NULL AUTO_INCREMENT,

  4. `username` varchar(50) DEFAULT NULL,

  5. `userpwd` varchar(50) DEFAULT NULL,

  6. `createtime` datetime DEFAULT NULL,

  7. PRIMARY KEY (`id`)

  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;





创建对应函数

点击(此处)折叠或打开

  1. DELIMITER $$

  2. USE `helpdb`$$

  3. DROP FUNCTION IF EXISTS `rand_string`$$

  4. CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS VARCHAR(255) CHARSET latin1

  5. BEGIN

  6. DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

  7. DECLARE return_str VARCHAR(255) DEFAULT '';

  8. DECLARE i INT DEFAULT 0;

  9. WHILE i < n DO

  10. SET return_str = CONCAT(return_str,SUBSTRING(chars_str , FLOOR(1 + RAND()*62 ),1));

  11. SET i = i +1;

  12. END WHILE;

  13. RETURN return_str;

  14. END$$

  15. DELIMITER ;

创建存储过程

点击(此处)折叠或打开

  1. DELIMITER $$

  2. USE `helpdb` $$

  3. DROP PROCEDURE IF EXISTS `p_repeat` $$

  4. CREATE DEFINER = `root` @`%` PROCEDURE `p_repeat` ()

  5. BEGIN

  6. DECLARE v INT ;

  7. SET v = 0 ;

  8. REPEAT

  9. INSERT INTO newaccount

  10. VALUES

  11. (

  12. NULL,

  13. rand_string (5),

  14. rand_string (15),

  15. NOW()

  16. ) ;

  17. SET v = v + 1 ;

  18. UNTIL v >= 1000

  19. END REPEAT ;

  20. END $$

  21. DELIMITER ;

调用mysql存储过程插入数据

点击(此处)折叠或打开

  1. mysql> CALL p_repeat;

  2. Query OK, 1 row affected (1.62 sec)

  3. mysql> select count(*) from newaccount;

  4. +----------+

  5. | count(*) |

  6. +----------+

  7. | 1000 |

  8. +----------+


手动插入数据

点击(此处)折叠或打开

  1. mysql> insert into newaccount values(null,'netdata','pwdnetdata',now());

  2. Query OK, 1 row affected (0.00 sec)

  3. mysql> select * from newaccount where id=1001;

  4. +------+----------+------------+---------------------+

  5. | id | username | userpwd | createtime |

  6. +------+----------+------------+---------------------+

  7. | 1001 | netdata | pwdnetdata | 2017-11-12 01:37:52 |

  8. +------+----------+------------+---------------------+

  9. 1 row in set (0.00 sec)



删除表

点击(此处)折叠或打开

  1. mysql> drop table newaccount;

  2. Query OK, 0 rows affected (0.01 sec)


关闭库

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# /etc/init.d/mysql stop

  2. Shutting down MySQL.. SUCCESS!

  3. [root@mysqltest-213-2 undrop-for-innodb-master]#

  4. 1 row in set (0.00 sec)

  5. [root@mysqltest-213-2 undrop-for-innodb-master]# ./stream_parser -f /var/lib/mysql/ibdata1

  6. Opening file: /var/lib/mysql/ibdata1

  7. File information:

  8. ID of device containing file: 64768

  9. inode number: 393238

  10. protection: 100660 (regular file)

  11. number of hard links: 1

  12. user ID of owner: 498

  13. group ID of owner: 500

  14. device ID (if special file): 0

  15. blocksize for filesystem I/O: 4096

  16. number of blocks allocated: 155648

  17. time of last access: 1510422066 Sun Nov 12 01:41:06 2017

  18. time of last modification: 1510422066 Sun Nov 12 01:41:06 2017

  19. time of last status change: 1510422066 Sun Nov 12 01:41:06 2017

  20. total size, in bytes: 79691776 (76.000 MiB)

  21. Size to process: 79691776 (76.000 MiB)

  22. Worker(0): 21.03% done. 2017-11-12 01:41:49 ETA(in 00:00:07). Processing speed: 7.984 MiB/sec



All workers finished in 1 sec
解析数据文件，得到table_id=228

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql |grep newaccount

  2. 00000007A50E 030000024B0D54 SYS_TABLES "helpdb/newaccount" 71 4 1 0 80 "" 57

  3. 00000007A50E 030000024B0D54 SYS_TABLES "helpdb/newaccount" 71 4 1 0 80 "" 57

  4. SET FOREIGN_KEY_CHECKS=0;

  5. LOAD DATA LOCAL INFILE '/usr/local/tools/undrop-for-innodb-master/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

根据table_id得到index_id，index_id对应恢复数据文件

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 71

  2. SET FOREIGN_KEY_CHECKS=0;

  3. LOAD DATA LOCAL INFILE '/usr/local/tools/undrop-for-innodb-master/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

  4. 00000007A50E 030000024B0BFF SYS_INDEXES 71 74 "PRIMARY" 1 3 57 4294967295

  5. 00000007A50E 030000024B0BFF SYS_INDEXES 71 74 "PRIMARY" 1 3 57 4294967295

注意这里还要扫描逻辑卷一遍跟共享表空间恢复不一样的地方

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ./stream_parser -f /dev/mapper/vg_mysqltest2132-lv_root -t 18G

  2. Opening file: /dev/mapper/vg_mysqltest2132-lv_root

  3. File information:

  4. ID of device containing file: 5

  5. inode number: 6976

  6. protection: 60660 (block device)

  7. number of hard links: 1

  8. user ID of owner: 0

  9. group ID of owner: 6

  10. device ID (if special file): 64768

  11. blocksize for filesystem I/O: 4096

  12. number of blocks allocated: 0

  13. time of last access: 1510407590 Sat Nov 11 21:39:50 2017

  14. time of last modification: 1510407590 Sat Nov 11 21:39:50 2017

  15. time of last status change: 1510407590 Sat Nov 11 21:39:50 2017

  16. total size, in bytes: 0 (0.000 exp(+0))



  19. Size to process: 19327352832 (18.000 GiB)

  20. Worker(0): 1.08% done. 2017-11-12 01:46:39 ETA(in 00:03:10). Processing speed: 95.945 MiB/sec

  21. Worker(0): 2.12% done. 2017-11-12 01:48:14 ETA(in 00:04:42). Processing speed: 63.880 MiB/sec

  22. Worker(0): 3.16% done. 2017-11-12 01:54:30 ETA(in 00:10:51). Processing speed: 27.377 MiB/sec

  23. Worker(0): 4.20% done. 2017-11-12 01:46:45 ETA(in 00:03:04). Processing speed: 95.844 MiB/sec

  24. Worker(0): 5.24% done. 2017-11-12 01:46:45 ETA(in 00:03:02). Processing speed: 95.820 MiB/sec

  25. Worker(0): 6.28% done. 2017-11-12 01:48:16 ETA(in 00:04:30). Processing speed: 63.880 MiB/sec

  26. Worker(0): 7.32% done. 2017-11-12 01:48:16 ETA(in 00:04:27). Processing speed: 63.896 MiB/sec

  27. Worker(0): 8.36% done. 2017-11-12 01:46:47 ETA(in 00:02:56). Processing speed: 95.828 MiB/sec

  28. Worker(0): 9.40% done. 2017-11-12 01:46:47 ETA(in 00:02:54). Processing speed: 95.906 MiB/sec

  29. Worker(0): 10.44% done. 2017-11-12 01:46:47 ETA(in 00:02:52). Processing speed: 95.891 MiB/sec

  30. Worker(0): 11.49% done. 2017-11-12 01:45:20 ETA(in 00:01:24). Processing speed: 192.000 MiB/sec

  31. Worker(0): 12.53% done. 2017-11-12 01:46:46 ETA(in 00:02:48). Processing speed: 95.883 MiB/sec

  32. Worker(0): 13.57% done. 2017-11-12 01:45:22 ETA(in 00:01:23). Processing speed: 191.938 MiB/sec

  33. Worker(0): 15.09% done. 2017-11-12 01:44:55 ETA(in 00:00:55). Processing speed: 280.000 MiB/sec

  34. Worker(0): 17.13% done. 2017-11-12 01:44:41 ETA(in 00:00:40). Processing speed: 376.000 MiB/sec

  35. Worker(0): 19.04% done. 2017-11-12 01:44:44 ETA(in 00:00:42). Processing speed: 352.000 MiB/sec

  36. Worker(0): 20.99% done. 2017-11-12 01:44:43 ETA(in 00:00:40). Processing speed: 360.000 MiB/sec

  37. Worker(0): 22.90% done. 2017-11-12 01:44:44 ETA(in 00:00:40). Processing speed: 352.000 MiB/sec

  38. Worker(0): 24.81% done. 2017-11-12 01:44:44 ETA(in 00:00:39). Processing speed: 352.000 MiB/sec

  39. Worker(0): 26.46% done. 2017-11-12 01:44:50 ETA(in 00:00:44). Processing speed: 304.000 MiB/sec

  40. Worker(0): 28.54% done. 2017-11-12 01:44:41 ETA(in 00:00:34). Processing speed: 384.000 MiB/sec

  41. Worker(0): 30.54% done. 2017-11-12 01:44:42 ETA(in 00:00:34). Processing speed: 368.000 MiB/sec

  42. Worker(0): 32.45% done. 2017-11-12 01:44:44 ETA(in 00:00:35). Processing speed: 352.000 MiB/sec

  43. Worker(0): 34.05% done. 2017-11-12 01:44:51 ETA(in 00:00:41). Processing speed: 296.000 MiB/sec

  44. Worker(0): 35.49% done. 2017-11-12 01:44:56 ETA(in 00:00:45). Processing speed: 263.930 MiB/sec

  45. Worker(0): 37.44% done. 2017-11-12 01:44:44 ETA(in 00:00:32). Processing speed: 360.000 MiB/sec

  46. Worker(0): 39.39% done. 2017-11-12 01:44:44 ETA(in 00:00:31). Processing speed: 360.000 MiB/sec

  47. Worker(0): 40.95% done. 2017-11-12 01:44:51 ETA(in 00:00:37). Processing speed: 288.000 MiB/sec

  48. Worker(0): 42.95% done. 2017-11-12 01:44:43 ETA(in 00:00:28). Processing speed: 368.000 MiB/sec

  49. Worker(0): 44.86% done. 2017-11-12 01:44:44 ETA(in 00:00:28). Processing speed: 352.000 MiB/sec

  50. Worker(0): 45.90% done. 2017-11-12 01:45:09 ETA(in 00:00:52). Processing speed: 191.750 MiB/sec

  51. Worker(0): 46.94% done. 2017-11-12 01:45:08 ETA(in 00:00:50). Processing speed: 192.000 MiB/sec

  52. Worker(0): 48.90% done. 2017-11-12 01:44:45 ETA(in 00:00:26). Processing speed: 360.000 MiB/sec

  53. Worker(0): 50.89% done. 2017-11-12 01:44:44 ETA(in 00:00:24). Processing speed: 368.000 MiB/sec

  54. Worker(0): 52.80% done. 2017-11-12 01:44:45 ETA(in 00:00:24). Processing speed: 352.000 MiB/sec

  55. Worker(0): 54.71% done. 2017-11-12 01:44:45 ETA(in 00:00:23). Processing speed: 352.000 MiB/sec

  56. Worker(0): 56.27% done. 2017-11-12 01:44:50 ETA(in 00:00:27). Processing speed: 288.000 MiB/sec

  57. Worker(0): 57.66% done. 2017-11-12 01:44:54 ETA(in 00:00:30). Processing speed: 255.937 MiB/sec

  58. Worker(0): 59.70% done. 2017-11-12 01:44:44 ETA(in 00:00:19). Processing speed: 376.000 MiB/sec

  59. Worker(0): 61.66% done. 2017-11-12 01:44:45 ETA(in 00:00:19). Processing speed: 360.000 MiB/sec

  60. Worker(0): 63.61% done. 2017-11-12 01:44:45 ETA(in 00:00:18). Processing speed: 360.000 MiB/sec

  61. Worker(0): 65.69% done. 2017-11-12 01:44:44 ETA(in 00:00:16). Processing speed: 384.000 MiB/sec

  62. Worker(0): 67.34% done. 2017-11-12 01:44:48 ETA(in 00:00:19). Processing speed: 304.000 MiB/sec

  63. Worker(0): 68.38% done. 2017-11-12 01:45:00 ETA(in 00:00:30). Processing speed: 191.922 MiB/sec

  64. Worker(0): 69.95% done. 2017-11-12 01:44:50 ETA(in 00:00:19). Processing speed: 288.000 MiB/sec

  65. Worker(0): 71.94% done. 2017-11-12 01:44:46 ETA(in 00:00:14). Processing speed: 368.000 MiB/sec

  66. Worker(0): 73.98% done. 2017-11-12 01:44:45 ETA(in 00:00:12). Processing speed: 376.000 MiB/sec

  67. Worker(0): 76.02% done. 2017-11-12 01:44:45 ETA(in 00:00:11). Processing speed: 376.000 MiB/sec

  68. Worker(0): 77.76% done. 2017-11-12 01:44:47 ETA(in 00:00:12). Processing speed: 320.000 MiB/sec

  69. Worker(0): 78.80% done. 2017-11-12 01:44:56 ETA(in 00:00:20). Processing speed: 191.891 MiB/sec

  70. Worker(0): 79.84% done. 2017-11-12 01:45:16 ETA(in 00:00:38). Processing speed: 95.813 MiB/sec

  71. Worker(0): 80.88% done. 2017-11-12 01:44:57 ETA(in 00:00:18). Processing speed: 191.891 MiB/sec

  72. Worker(0): 81.92% done. 2017-11-12 01:44:57 ETA(in 00:00:17). Processing speed: 192.000 MiB/sec

  73. Worker(0): 83.31% done. 2017-11-12 01:44:53 ETA(in 00:00:12). Processing speed: 256.000 MiB/sec

  74. Worker(0): 85.35% done. 2017-11-12 01:44:49 ETA(in 00:00:07). Processing speed: 376.000 MiB/sec

  75. Worker(0): 86.91% done. 2017-11-12 01:44:51 ETA(in 00:00:08). Processing speed: 288.000 MiB/sec

  76. Worker(0): 88.69% done. 2017-11-12 01:44:50 ETA(in 00:00:06). Processing speed: 328.000 MiB/sec

  77. Worker(0): 89.73% done. 2017-11-12 01:44:54 ETA(in 00:00:09). Processing speed: 191.939 MiB/sec

  78. Worker(0): 90.77% done. 2017-11-12 01:44:54 ETA(in 00:00:08). Processing speed: 191.883 MiB/sec

  79. Worker(0): 92.51% done. 2017-11-12 01:44:51 ETA(in 00:00:04). Processing speed: 320.000 MiB/sec

  80. Worker(0): 93.55% done. 2017-11-12 01:44:54 ETA(in 00:00:06). Processing speed: 192.000 MiB/sec

  81. Worker(0): 95.29% done. 2017-11-12 01:44:51 ETA(in 00:00:02). Processing speed: 320.000 MiB/sec

  82. Worker(0): 97.11% done. 2017-11-12 01:44:51 ETA(in 00:00:01). Processing speed: 336.000 MiB/sec

  83. All workers finished in 83 sec




查看对应数据页文件

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ls pages-vg_mysqltest2132-lv_root/FIL_PAGE_INDEX/0000000000000074.page

  2. pages-vg_mysqltest2132-lv_root/FIL_PAGE_INDEX/0000000000000074.page


恢复表结构

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# cat newaccount.sql

  2. CREATE TABLE `newaccount` (

  3. `id` int(11) NOT NULL AUTO_INCREMENT,

  4. `username` varchar(50) DEFAULT NULL,

  5. `userpwd` varchar(50) DEFAULT NULL,

  6. `createtime` datetime DEFAULT NULL,

  7. PRIMARY KEY (`id`)

  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

抽取数据

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000074.page -t newaccount.sql | head -5

  2. -- Page id: 5, Format: COMPACT, Records list: Valid, Expected records: (301 301)

  3. 00000007A1B5 AC0000015F0110 newaccount 151 "0GdUH" "DTo9njhkAO9adoc" "2017-11-12 01:36:10.0"

  4. 00000007A1B6 AD000001600110 newaccount 152 "CoT8Q" "DtjZQ4Iaz9UVKOD" "2017-11-12 01:36:10.0"

  5. 00000007A1B7 AE000001610110 newaccount 153 "CZWzT" "z1f1aEyGzEnLzo7" "2017-11-12 01:36:10.0"

  6. 00000007A1B8 AF000001620110 newaccount 154 "eEpWh" "p50DYNW9J41Hkkv" "2017-11-12 01:36:10.0"




恢复数据
抽取数据转换成文件

点击(此处)折叠或打开

  1. [root@mysqltest-213-2 undrop-for-innodb-master]# ./c_parser -6f pages-vg_mysqltest2132-lv_root/FIL_PAGE_INDEX/0000000000000074.page -t newaccount.sql -o dumps/default/newaccount.dump -l dumps/default/newaccount.sql

  2. [root@mysqltest-213-2 undrop-for-innodb-master]# ls -alh dumps/default/newaccount.*

  3. -rw-r--r--. 1 root root 92K Nov 12 01:48 dumps/default/newaccount.dump

  4. -rw-r--r--. 1 root root 244 Nov 12 01:48 dumps/default/newaccount.sql

恢复数据

点击(此处)折叠或打开

  1. mysql> source dumps/default/newaccount.sql;

  2. Query OK, 0 rows affected (0.00 sec)

  3. Query OK, 6002 rows affected (0.06 sec)

  4. Records: 6002 Deleted: 0 Skipped: 0 Warnings: 0

  5. mysql> select count(*) from newaccount;

  6. +----------+

  7. | count(*) |

  8. +----------+

  9. | 1001 |

  10. +----------+

  11. 1 row in set (0.00 sec)

  12. mysql> checksum table newaccount;

  13. +-------------------+------------+

  14. | Table | Checksum |

  15. +-------------------+------------+

  16. | helpdb.newaccount | 2512700176 |

  17. +-------------------+------------+

  18. 1 row in set (0.03 sec)

到此，相信大家对"mysql无备份恢复创建独立表空间"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

很赞哦！
数据 空间 文件 备份 独立 内容 过程 存储 学习 抽取 实用 更深 兴趣 函数 地方 实用性 实际 手动 操作简单 方法 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 什么是网络技术销售厂 公众号网络安全知识 南昌linux软件开发 web服务器搭建和管理 龙之谷服务器角色查询 哈尔滨小熊软件开发公司 应用软件开发合同 软件开发过程的分析过程不包括 阳江数据链软件开发价格走势 软件开发师能叫程序员吗 使命召唤17正在连接在线服务器 长沙涟云网络技术公司 银行网络安全工程师工作地点 跳跃数据库 数据链软件开发费用 网络安全对国家安全重要性 软件开发兴趣小组迎新策划书 中学网络安全工作领导小组的通知 影响服务器安全性的因素有 数据库迁移 技术实现 在华为做软件开发环境 单机游戏 服务器 南京进口软件开发产品介绍 四川定制化上位机软件开发 网络安全工程师的工资 我的世界邪恶服务器管理员 列出几种常见的软件开发模型 专业数据库库存管理 软件开发兴趣小组迎新策划书 艺术数据库建设

相关文章

0