天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > MySQL数据库 > mysql无备份恢复创建独立表空间

mysql无备份恢复创建独立表空间
更新：HHH   时间：2023-1-7

本篇内容主要讲解“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无备份恢复创建独立表空间”有了更深的了解，不妨来实际操作一番吧！这里是天达云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
返回MySQL数据库教程...
400-805-1963
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-805-1963
Copyright © 2011-2026 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7