恢复表空间数据场景1

原创

wx5d4124a358e8a 2022-09-02 23:55:31 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者wx5d4124a358e8a的原创作品，请联系作者获取转载授权，否则将追究法律责任

一，故障处理（小插曲）：

不小心把目录/data/mysql/data下的表空间文件ibdata1删除了，幸好我做了回收站，将回收站中的ibdata1恢复至原目录后，需要执行一个重启命令systemctl restart mysqld后才能登陆数据库

二，演示恢复表空间

1开启一个新的3307端口的数据库（A窗口）

[root@instance-r5y0pf5d ~]# systemctl start mysqld3307.service
[root@instance-r5y0pf5d ~]# mysql -S /data/3307/mysql.sock

mysql>

2模拟3306数据库停止，表空间丢失，数据库无法启动（B窗口）

[root@instance-r5y0pf5d ~]# systemctl stop mysqld
[root@instance-r5y0pf5d ~]# rm -rf /data/mysql/data/ibdata1

[root@instance-r5y0pf5d ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

3想办法解决

1）把坏掉的3306库目录拷贝到3307这个新开的数据库里，然后进入到拷贝后的目录给一个权限（B窗口）

[root@instance-r5y0pf5d ~]# cd /data/mysql/data
[root@instance-r5y0pf5d /data/mysql/data]# ls
20210113-1817  ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql.log  performance_schema  sys       testku  world.bak
auto.cnf       ib_logfile0     mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.index   oldboy     runoob              tangTest  world
[root@instance-r5y0pf5d /data/mysql/data]# cp -r world /data/3307/data

[root@instance-r5y0pf5d /data/mysql/data]# cd /data/3307/data
[root@instance-r5y0pf5d /data/3307/data]# chown -R mysql. world/

2）在3307这个数据库中查看是否导入成功，是否能够查表（A窗口）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist
mysql>