千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-11-16 作者：千家信息网编辑
千家信息网最后更新 2024年11月16日，本篇内容主要讲解"mysql无备份恢复创建独立表空间"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"mysql无备份恢复创建独立表空间"吧!创建恢复库点击(
千家信息网最后更新 2024年11月16日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安全错误 数据库的锁怎样保障安全 支点软件开发 删数据库跑路 表情 网络安全调查问卷报 sql数据库的复制 海南网络安全协会培训 软件开发项目设计选题 魔兽对战平台数据库出错 河北正规软件开发诚信服务 历史nba数据库排名 服务器跑流量违法吗 专科游戏软件开发学校 如何判断服务器是否为公网 hpe服务器h3c紫光 新浪邮箱 服务器 魔兽世界怀旧服猎人服务器 网络技术防御 杨浦区互联网软件开发代理品牌 网络安全法第三十 网络安全法对服务外包 敏捷软件开发框架 数据库技术应用审计代码 礼品商城软件开发 外网申请理由 软件开发 温州市软件开发那里有招工 国内网络安全公司联 倪光南谈网络安全 写一个web服务器端 滨州在线考试软件开发公司 对网络安全的做法写出方案 尚德网络技术有限公司征集广告

相关文章

0