数据库崩溃迁移实例(没有备份,有表结构,表空间)。mysql8.0
目标数据库3307 迁移到3308
1、锁住3307数据库表;
flush tables tables test.t100w with read lock;
2、获取表结构;
show create table test.t100w;
CREATE TABLE `t100w` (
`id` int DEFAULT NULL,
`num` int DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
3、目标数据库创建数据库与表结构
创库:create database test charset utf8mb4 collate utf8mb4_0900_ai_ci;
创表:CREATE TABLE `t100w` (
`id` int DEFAULT NULL,
`num` int DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
4、删除目标库的表空间
alter table test.t100w discard tablespace;
将表空间复制到指定目录下:例如
cp /data/3307/data/oldboy/t100w.ibd /data/3308/data/oldboy/
授权:chown -R mysql.mysql /data/3308/data/oldboy/
5、加入新的表空间
alter table test.t100w import tablespace
6.表解锁
unlock tables;