我们现场测试一个场景，drop一张MyISAM表后，单独对这表进行物理恢复
首先我们看一下secure_file_priv文件目录的位置
mysql> show global variables like '%secure_file_priv%';
+------------------+---------------------+
| Variable_name Value
+------------------+---------------------+
| secure_file_priv | /u01/mysql3308/tmp/ |
+------------------+---------------------+
如果没有设置这个选项，我们可以在my.cnf配置文件里添加这一项，放在[mysqld]下
[root@mysql8_3 mysql3308]# more my.cnf | grep secure_file
secure_file_priv=/u01/mysql3308/tmp
修改好配置后需要重启一下服务
[root@mysql8_3 mysql3308]# systemctl stop mysqld83308.service
[root@mysql8_3 mysql3308]# systemctl start mysqld83308.service
登录到实例查看是否生效
mysql> show global variables like '%secure_file_priv%';
+------------------+---------------------+
| Variable_name Value
+------------------+---------------------+
| secure_file_priv | /u01/mysql3308/tmp/ |
+------------------+---------------------+
我们看一下测试表
[root@mysql8_3 test]# mysql -uroot -p
mysql> use test;
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
测试表tmyisam当前有一条数据
mysql> select * from tmyisam;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
现在我们手动备份tmyisam表的物理文件
[root@mysql8_3 test]# cp tmyisam* /tmp/
备份完后删除该表
mysql> use test;
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_test |
+----------------+
| employees|
| tarchive|
| tblackhole|
| tinnodb|
| tmemory|
| tmyisam|
+----------------+
6 rows in set (0.00 sec)
mysql> drop table tmyisam;
Query OK, 0 rows affected (0.20 sec)
mysql> exit
Bye
删除完成后，我们手动拷贝tmyisam表数据文件tmyisam.MYD和表索引文件到数据库目录并修改属性
[root@mysql8_3 test]# cp /tmp/tmyisam.MYI /u01/mysql3308/data/test/
[root@mysql8_3 test]# cp /tmp/tmyisam.MYD /u01/mysql3308/data/test/
[root@mysql8_3 test]# chown -R mysql:mysql tmyisam.MYI
[root@mysql8_3 test]# chown -R mysql:mysql tmyisam.MYD
然后拷贝表结构文件tmyisam_392.sid到安全目录，并修改该文件的属主
[root@mysql8_3 test]# cp /tmp/tmyisam_392.sdi /u01/mysql3308/tmp/
[root@mysql8_3 test]# chown -R mysql:mysql tmyisam_392.sdi
然后我们登录到数据库执行导入
[root@mysql8_3 test]# mysql -uroot -p
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> import table from '/u01/mysql3308/tmp/tmyisam_392.sdi';
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tmyisam;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
tmyisam表完成恢复
图片
