Mysql生产案例

Mysql生产案例

在没有备份数据的情况下，突然断电导致表损坏的修复方法。

[root@db01 ~]# cd  /usr/local/mysql/data 

#打包库目录
[root@db01 data]# tar czf world.tar.gz world

#查看库目录
[root@db01 data]# ll /usr/local/mysql/data 
total 227524
drwx------ 2 mysql mysql      144 Jul 16 09:58 world
-rw-r--r-- 1 root  root    164896 Jul 16 19:18 world.tar.gz

#将打包好的world库目录远程拷贝到db04(10.0.0.53)
[root@db01 data]# scp world.tar.gz 10.0.0.53:/tmp/
The authenticity of host '10.0.0.53 (10.0.0.53)' can't be established.
ECDSA key fingerprint is SHA256:KUGHqLcxD0N8xp81uH8qyAu+/AndEmwYkCYOC0K8CfQ.
ECDSA key fingerprint is MD5:cb:6c:73:26:13:95:05:a0:60:24:e3:c6:cb:c3:e6:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.53' (ECDSA) to the list of known hosts.
root@10.0.0.53's password: 
world.tar.gz                                                                     100%  
161KB  31.6MB/s   

#在db04上查看库目录包是否过来
[root@db04 data]# ll
total 110776
-rw-r--r-- 1 root  root    164896 Jul 16 19:19 world.tar.gz

#解压库目录包
[root@db04 data]# tar xf world.tar.gz 

#启动数据库
[root@db04 data]# /etc/init.d/mysqld start

#登录数据库查看
mysql> show databases;

#查询表中数据
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

#找到以前的表结构在新库中创建表
mysql> show create table world.city;
##删除外键创建语句

CREATE TABLE `city_new` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

#查询表数据
mysql> select * from city_new;
Empty set (0.00 sec)

#删除表空间
mysql> alter table city_new discard tablespace;
Query OK, 0 rows affected (0.01 sec)

#拷贝旧表空间文件
[root@db04 world]# cp city.ibd city_new.ibd

#授权
[root@db04 world]# chown -R mysql.mysql city_new.ibd

#导入表空间
mysql> alter table city_new import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)