分类 Mysql 下的文章

如果使用MYSQL数据库,不一小心误操作,删除掉不该删除的数据,同时又没有打开BIN-LOG,怎么办呢?除了后悔没有按时备份还能怎么办??本篇带来一遍恢复数据的文档,不能完全恢复只是给一个思路
此方法只用与MyISAM引擎,同时只是实用于部分数据,如果使用delete from xxx不带where条件的删除,是没有办法恢复的,使用此方法也不能完全恢复,只会找回一些数据,总比没有什么都没有要一些吧.
还有网上很多文档说delete只是修改的索引值而不是真正的删除,这个至少我没有办法也许是版本的问题就不去深究了.
**注意:当遇到数据被误删除事,请不要立马恢复之前的备份(如果可以的),而是先把服务停掉不让其继续写数据,再把数据库的数据文件备份出来,在mysql数据目录下面,已数据库名称为目录.
**
开始操作
MyISAM的存储引擎每一个表需要三个文件,如下(XXX代表表名称):

  • XXX.frm 表结构
  • XXX.MYD 数据文件
  • XXX.MYI 索引文件

恢复数据我们只需要重点关注数据文件即可.使用16进制模式打开文件,看一下文件内容隐约能看到一些内容.
在WINDOWS下面可以用UE打开,在linux下面直接使用hexdump命令即可,命令如下:

hexdump -e '8/1 "%02X ""\t"" "' -e '8/1 "%c""\n"'  test.MYD

在打开数据文件之前,先看一下我使用的数据表结构和测试数据.

mysql> desc test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| userid     | int(10)     | NO   | PRI | NULL    |       |
| username   | varchar(60) | NO   |     | NULL    |       |
| password   | varchar(60) | NO   |     | NULL    |       |
| lastupdate | date        | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from test limit 10,10;
+--------+----------+----------+------------+
| userid | username | password | lastupdate |
+--------+----------+----------+------------+
|     13 | admin13  | passwd13 | 2017-03-11 |
|     14 | admin14  | passwd14 | 2017-03-11 |
|     15 | admin15  | passwd15 | 2017-03-11 |
|     16 | admin16  | passwd16 | 2017-03-11 |
|     17 | admin17  | passwd17 | 2017-03-11 |
|     18 | admin18  | passwd18 | 2017-03-11 |
|     19 | admin19  | passwd19 | 2017-03-11 |
|     20 | admin20  | passwd20 | 2017-03-11 |
|     21 | admin21  | passwd21 | 2017-03-11 |
|     22 | admin22  | passwd22 | 2017-03-11 |
+--------+----------+----------+------------+
10 rows in set (0.00 sec)

然后打开数据文件,截取一段16进制数据.

69 6E 31 32 08 70 61 73  in12^Hpas
73 77 64 31 32 6B C2 0F  swd12kÂ^O
01 00 19 00 0D 00 00 00  ^A^@^Y^@^M^@^@^@
07 61 64 6D 69 6E 31 33  ^Gadmin13
08 70 61 73 73 77 64 31  ^Hpasswd1
33 6B C2 0F 01 00 19 00  3kÂ^O^A^@^Y^@
0E 00 00 00 07 61 64 6D  ^N^@^@^@^Gadm
69 6E 31 34 08 70 61 73  in14^Hpas
73 77 64 31 34 6B C2 0F  swd14kÂ^O
01 00 19 00 0F 00 00 00  ^A^@^Y^@^O^@^@^@
07 61 64 6D 69 6E 31 35  ^Gadmin15
08 70 61 73 73 77 64 31  ^Hpasswd1
35 6B C2 0F 01 00 19 00  5kÂ^O^A^@^Y^@
10 00 00 00 07 61 64 6D  ^P^@^@^@^Gadm
69 6E 31 36 08 70 61 73  in16^Hpas
73 77 64 31 36 6B C2 0F  swd16kÂ^O
01 00 19 00 11 00 00 00  ^A^@^Y^@^Q^@^@^@
07 61 64 6D 69 6E 31 37  ^Gadmin17
08 70 61 73 73 77 64 31  ^Hpasswd1
37 6B C2 0F 01 00 19 00  7kÂ^O^A^@^Y^@
12 00 00 00 07 61 64 6D  ^R^@^@^@^Gadm
69 6E 31 38 08 70 61 73  in18^Hpas
73 77 64 31 38 6B C2 0F  swd18kÂ^O
01 00 19 00 13 00 00 00  ^A^@^Y^@^S^@^@^@
07 61 64 6D 69 6E 31 39  ^Gadmin19
08 70 61 73 73 77 64 31  ^Hpasswd1
39 6B C2 0F 01 00 19 00  9kÂ^O^A^@^Y^@
14 00 00 00 07 61 64 6D  ^T^@^@^@^Gadm
69 6E 32 30 08 70 61 73  in20^Hpas
73 77 64 32 30 6B C2 0F  swd20kÂ^O
01 00 19 00 15 00 00 00  ^A^@^Y^@^U^@^@^@
07 61 64 6D 69 6E 32 31  ^Gadmin21
08 70 61 73 73 77 64 32  ^Hpasswd2
31 6B C2 0F 01 00 19 00  1kÂ^O^A^@^Y^@
16 00 00 00 07 61 64 6D  ^V^@^@^@^Gadm
69 6E 32 32 08 70 61 73  in22^Hpas
73 77 64 32 32 6B C2 0F  swd22kÂ^O
01 00 19 00 17 00 00 00  ^A^@^Y^@^W^@^@^@
07 61 64 6D 69 6E 32 33  ^Gadmin23
08 70 61 73 73 77 64 32  ^Hpasswd2

userid=13的明细对应着一下16进制数据快.

01 00 19 00 0D 00 00 00  ^A^@^Y^@^M^@^@^@
07 61 64 6D 69 6E 31 33  ^Gadmin13
08 70 61 73 73 77 64 31  ^Hpasswd1
33 6B C2 0F

简单解析一下这些数据结构的意思.

  • 01 00 19 00 :这里是4位数据头,不代表头就是4为,这个数据头不固定
  • 0D 00 00 00 :为userid的值13,int占4位.
  • 07 61 64 6D 69 6E 31 33 :07代表长度 后面7位为数据,varchar数据这样表示.
  • 08 70 61 73 73 77 64 31 33 :varchar和上面一样.
  • 6B C2 0F :为date类型,占位为3.值为2017,计算方法是"day + month32 + year16*32'"
  • 具体其他数据字段长度和结构可以详见MYSQL官方文档
    https://dev.mysql.com/doc/internals/en/myisam-column-attributes.html

咱们删除一条数据看一下数据快的变化:

mysql> delete from test where userid = 13;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test limit 9,10;
+--------+----------+----------+------------+
| userid | username | password | lastupdate |
+--------+----------+----------+------------+
|     12 | admin12  | passwd12 | 2017-03-11 |
|     14 | admin14  | passwd14 | 2017-03-11 |
|     15 | admin15  | passwd15 | 2017-03-11 |
|     16 | admin16  | passwd16 | 2017-03-11 |
|     17 | admin17  | passwd17 | 2017-03-11 |
|     18 | admin18  | passwd18 | 2017-03-11 |
|     19 | admin19  | passwd19 | 2017-03-11 |
|     20 | admin20  | passwd20 | 2017-03-11 |
|     21 | admin21  | passwd21 | 2017-03-11 |
|     22 | admin22  | passwd22 | 2017-03-11 |
+--------+----------+----------+------------+
10 rows in set (0.00 sec)
删除后的数据块如下:
69 6E 31 32 08 70 61 73  in12^Hpas
73 77 64 31 32 6B C2 0F  swd12kÂ^O
00 00 00 1C 00 00 00 00  ^@^@^@^\^@^@^@^@
00 00 0A D4 FF FF FF FF  ^@^@Ôÿÿÿÿ
FF FF FF FF 73 77 64 31  ÿÿÿÿswd1
33 6B C2 0F 01 00 19 00  3kÂ^O^A^@^Y^@
0E 00 00 00 07 61 64 6D  ^N^@^@^@^Gadm
69 6E 31 34 08 70 61 73  in14^Hpas
73 77 64 31 34 6B C2 0F  swd14kÂ^O
01 00 19 00 0F 00 00 00  ^A^@^Y^@^O^@^@^@
07 61 64 6D 69 6E 31 35  ^Gadmin15
08 70 61 73 73 77 64 31  ^Hpasswd1
35 6B C2 0F 01 00 19 00  5kÂ^O^A^@^Y^@
10 00 00 00 07 61 64 6D  ^P^@^@^@^Gadm
69 6E 31 36 08 70 61 73  in16^Hpas
73 77 64 31 36 6B C2 0F  swd16kÂ^O
01 00 19 00 11 00 00 00  ^A^@^Y^@^Q^@^@^@
07 61 64 6D 69 6E 31 37  ^Gadmin17
08 70 61 73 73 77 64 31  ^Hpasswd1
37 6B C2 0F 01 00 19 00  7kÂ^O^A^@^Y^@
12 00 00 00 07 61 64 6D  ^R^@^@^@^Gadm

USERID=13数据块变化对比

delete后
00 00 00 1C 00 00 00 00  ^@^@^@^\^@^@^@^@
00 00 0A D4 FF FF FF FF  ^@^@Ôÿÿÿÿ
FF FF FF FF 73 77 64 31  ÿÿÿÿswd1
33 6B C2
delete前
01 00 19 00 0D 00 00 00  ^A^@^Y^@^M^@^@^@
07 61 64 6D 69 6E 31 33  ^Gadmin13
08 70 61 73 73 77 64 31  ^Hpasswd1
33 6B C2 0F
前20位被破坏了,但是后面的数据还可以,只是数据很少了,如果没有数据写入的话,还有一线恢复的生机,主要找到后面数据的对应关系才能恢复
如果是大批量的数据被删除,恢复就需要看运气了,大家还是定时做好备份和开始mysql的log-bin模式.
关于头长度和delete头长度请阅读mysql官方
https://dev.mysql.com/doc/internals/en/layout-record-storage-frame.html

关于解析test.MYD文件格式,写了一些代码希望对理解MDY文件结构有些帮助.

key1088@key1088-host:~/code/repari$ cat repariMYD.c
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <stdlib.h>
#include <errno.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include <arpa/inet.h>
void writeSQL(char *filebuf,unsigned int flen);
int main(int argc, char **argv)
{
    int fd=0;
    unsigned flen = 0;
    unsigned char *filebuf = NULL;
    struct stat fsbuf;
    char    *filename = NULL;
    if ( argc < 2 ) {
        printf("repari Mysql MyISAM DataFile\n");
        printf("%s [MyISAM DATA .MYD File] \n", argv[0]);
        exit(-1);
    }
    filename = argv[1];
    if ( stat(filename,&fsbuf) == -1 ) {
        printf("open file test.MYD error,msg = %s\n", strerror(errno));
        exit(-1);
    }
    flen = fsbuf.st_size;
    printf("Read %s filesize is %u\n", argv[1], flen);
    if ( flen == 0 ) {
        printf("file is empty\n");
        exit(-1);
    }
    if( (fd=open(filename,O_RDONLY) ) == -1 ) {
        printf("open file test.MYD error,msg = %s\n", strerror(errno));
        exit(-1);
    }
    filebuf = (unsigned char *)malloc(flen*sizeof(char)+1);
    if ( filebuf == NULL ) {
        printf("malloc error,msg = %s\n", strerror(errno));
        close(fd);
        exit(-1);
    }
    read(fd,filebuf,flen);
    close(fd);
    writeSQL(filebuf,flen);
    free(filebuf);
    return 0;
}
void writeSQL(char *filebuf,unsigned int flen)
{
    char    c;
    unsigned i = 0;
    long tmplen = 0;
    unsigned char tmp[5];
    unsigned char tmpchar[255+1];
    int y,m,d;
    while ( i < flen ) {
        if ( filebuf[i] == 0x00 ) {
            i++;
        }
        printf("head=[%02x]", filebuf[i]);
        if( filebuf[i] == 0x03 ) {
            i = i + 1 + 4; /* 1 + 4head */
        } else if ( filebuf[i] == 0x01 ) {
            i = i + 1 + 3; /* 1 + 3head */
        }
        /* int 4B */
        memset(tmp,0x00,sizeof(tmp));
        if ( (i+4) >  flen ) {
            printf("\nover\n");
            return;
        }
        memcpy(tmp,filebuf + i, 4);
        /*需要倒序*/
        sprintf(tmp,"%02x%02x%02x%02x",tmp[3],tmp[2],tmp[1],tmp[0]);
        printf("[%s] %ld - ", tmp, strtol(tmp,NULL, 16));
        i = i + 4;
        /*varchar */
            /* 1位长度 */
        memset(tmp,0x00,sizeof(tmp));
        if ( (i+1) >  flen ) {
            printf("\nover\n");
            return;
        }
        memcpy(tmp,filebuf + i, 1);
        sprintf(tmp,"%02x",tmp[0]);
        tmplen = strtol(tmp,NULL,16);
        i = i + 1;
            /* DATA */
        memset(tmpchar,0x00,sizeof(tmpchar));
        if ( (i + tmplen)>  flen ) {
            printf("\nover\n");
            return;
        }
        memcpy(tmpchar,filebuf + i, tmplen);
        printf("%s - ", tmpchar);
        i = i + tmplen;
        /*varchar*/
            /* 1位长度 */
        memset(tmp,0x00,sizeof(tmp));
        if ( (i+1) >  flen ) {
            printf("\nover\n");
            return;
        }
        memcpy(tmp,filebuf + i, 1);
        sprintf(tmp,"%02x",tmp[0]);
        tmplen = strtol(tmp,NULL,16);
        i = i + 1;
            /* DATA */
        memset(tmpchar,0x00,sizeof(tmpchar));
        if ( (i+tmplen) >  flen ) {
            printf("\nover\n");
            return;
        }
        memcpy(tmpchar,filebuf + i, tmplen);
        printf("%s - ", tmpchar);
        i = i + tmplen;
        /*date 3位 */
        memset(tmp,0x00,sizeof(tmp));
        memset(tmpchar,0x00,sizeof(tmpchar));
        if( (i+3) > flen) {
            printf("\nover\n");
            return;
        }
        memcpy(tmp,filebuf + i,3);
        sprintf(tmpchar,"%02x%02x%02x",tmp[2],tmp[1],tmp[0]);
        tmplen = strtol(tmpchar,NULL,16);
        y = m = d = 0;
        y = tmplen / 32 / 16;
        m = (tmplen - (32*16*y)) / 32;
        d = tmplen - (32*16*y) - (32*m);
        printf("%d-%d-%d",y,m,d);
        i = i + 3;
        printf("\n");
    }
}
key1088@key1088-host:~/code/repari$

今天发现一台近期MYSQL机器CPU占用很高。
进入mysql模式
show processlist;
工作量也不大啊,就几条语句。
copy tmp table。。。。。
观察了一个
写临时的表的和GROUP BY语句很多
解决方案:添加临时表大小或者优化sql语句
增加临时表MYSQL最后增加 tmp_table_size值
默认tmp_table_size 32M
tmp_table_size = 128M
重启,负载好多了

今天,(vmware里的)网站程序怎么也连接不上了,提示连接不上数据库,之前好好的,没有做什么操作啊。
密码是正确的,终端能连接上,就是php连接不上,php也没有什么问题啊。郁闷死了。怒之,删mysql数据库,重建。
rm -rf /usr/local/mysql/var/mysql             #直接删除的数据
mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var
mysql -u root -p                                     #进入mysql模式
show databases;                                #有其他数据库
可笑的是,现在网站能访问了,网站连接数据库的用户密码没有修改。
快速进入mysql表
use mysql
select user,host,password from user;         #确定是创建的,没有其他的用户。
mysql -u 任意用户 -p                                           #任意用户,空密码能进入
mysqladmin -uroot password                               #初始化密码,提示连接localhost 失败,0.0.0.0:3306正常监听。
看mysql日志正常,有点小郁闷。没有初始化密码前,不需要验证码。只要有对应的数据库就可以吗。晕。
 

编译Mysql时configure: error: No curses/termcap library found 的错误解决方法
在编译Mysql时
./configure –prefix=DIR
,如果出现了以下错误:
……
checking for tgetent in -ltermcap… no
checking for termcap functions library… configure: error: No curses/termcap library found
debian:
说明 curses/termcap 库没有安装
apt-cache search curses | grep lib
安装 libncurses5-dev ,然后重新运行配置
apt-get install libncurses5-dev
centos: 
光盘里面有libtermcap包,安装就OK。

#!/bin/sh
#This is check msyqld shell
#by:key1088@163.com
#守护mysqld脚本,防止意外崩溃。
#如果连接启动10次,依旧启动不来了。停止(可以写成运行某个程序),解决内存。
 
ERRLOG=/videodata/mysqldata/data/CHINASOFT.err
load_mysqld ()
{
/etc/init.d/mysqld start
}

while  true
do
  i=1
  PRO=ps aux|grep mysqld|grep -v grep
  if [ -z "$PRO" ]
  then
      while true
      do
          if [ -z "$PRO" ]
          then
            load_mysqld
            echo "date +'%y%m%d %H:%M:%S'   $0 LOAD MSYQLD-[$i]" >> $ERRLOG
            sleep 20
            PRO=ps aux|grep mysqld|grep -v grep
            if [ -z "$PRO" ]
            then   
                i=$((i+1))
            fi
          else
            break
          fi
         
          if [ $i -eq 10 ]
          then
          echo "date +'%y%m%d %H:%M:%S' HR_MYSQL_SAFE.SH LOAD MSQQLD --STOP" >> $ERRLOG
          exit 1
          fi
      done
  fi
  sleep 20
done