南京网站设计制作/关键词分为哪三类

数据误删了怎么办？本文也许能给您一定的提示。

一、查看日志

数据无意中发现不见了，怎么办？

也许首先想到的是去查日志，找到问题原因，但是这个时间有可能会比较长，并且线上的业务在这段时间会收到影响。

因此，先不要去管什么原因，首先应该做的第一件事情应当是数据恢复，保证正常的业务不受影响，而后再回过来查找原因即可。

那么，想要恢复数据，首先想到的也是日志：有无所有SQL操作的记录日志，有无开启binlog同步日志。这样才能根据SQL记录重新恢复数据，否则，数据将无法找回。

其次，我们需要查以下MySQL服务器开启了哪些日志，于是首先查看MySQL的配置文件 /etc/my.cnf，发现开启了errorlog、slowlog和binlog，这时就可以松一口气了，因为只要有binlog，所有数据都将找得回来，slowlog对我们当前的问题来说并没有什么用，errorlog后面我们拿来查找问题原因也许会有用。

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysql]

prompt="MySQL [\d]> "

no-auto-rehash

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1916

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 1024

max_allowed_packet = 500M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 128M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 256M

thread_cache_size = 64

query_cache_type = 1

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 7

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 1024M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 500M

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

[client]

port=3306

socket=/tmp/mysql.sock

default-character-set=utf8mb4

[mysql]

prompt="MySQL [\d]> "

no-auto-rehash

[mysqld]

port=3306

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql

pid-file=/data/mysql/mysql.pid

user=mysql

bind-address=0.0.0.0

server-id=1

init-connect='SET NAMES utf8mb4'

character-set-server=utf8mb4

skip-name-resolve

#skip-networking

back_log=300

max_connections=1916

max_connect_errors=6000

open_files_limit=65535

table_open_cache=1024

max_allowed_packet=500M

binlog_cache_size=1M

max_heap_table_size=8M

tmp_table_size=128M

read_buffer_size=2M

read_rnd_buffer_size=8M

sort_buffer_size=8M

join_buffer_size=8M

key_buffer_size=256M

thread_cache_size=64

query_cache_type=1

query_cache_size=64M

query_cache_limit=2M

ft_min_word_len=4

log_bin=mysql-bin

binlog_format=mixed

expire_logs_days=7

log_error=/data/mysql/mysql-error.log

slow_query_log=1

long_query_time=1

slow_query_log_file=/data/mysql/mysql-slow.log

performance_schema=0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine=InnoDB

innodb_file_per_table=1

innodb_open_files=500

innodb_buffer_pool_size=1024M

innodb_write_io_threads=4

innodb_read_io_threads=4

innodb_thread_concurrency=0

innodb_purge_threads=1

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=2M

innodb_log_file_size=32M

innodb_log_files_in_group=3

innodb_max_dirty_pages_pct=90

innodb_lock_wait_timeout=120

bulk_insert_buffer_size=8M

myisam_sort_buffer_size=64M

myisam_max_sort_file_size=10G

myisam_repair_threads=1

interactive_timeout=28800

wait_timeout=28800

[mysqldump]

quick

max_allowed_packet=500M

[myisamchk]

key_buffer_size=256M

sort_buffer_size=8M

read_buffer=4M

write_buffer=4M

此外，我们通过 show variables 命令查看当前MySQL变量发现，其实MySQL还有另外一类日志，叫做generallog

| general_log | OFF |

| general_log_file | /data/mysql/localhost.log |

1

2

|general_log|OFF|

|general_log_file|/data/mysql/localhost.log|

当前服务器没有开启该日志，该日志用以记录所有执行的SQL语句，它和binlog的区别在于：

1、generallog仅仅是记录的日志文本文件，而binlog原本是拿来进行服务器之间的数据同步用的，是一个二进制文件；

2、binlog记录了比generallog更全面的SQL执行记录，而generallog提供的文本日志可读性更强；

二、恢复数据

找到了binlog，那么我们需要从binlog中恢复数据。

1、查看当前有哪些binlog文件

MySQL [hhzl_gdg]> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000005 | 17160082 |

| mysql-bin.000006 | 890082 |

| mysql-bin.000007 | 27160082 |

+------------------+-----------+

3 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

MySQL[hhzl_gdg]>showbinarylogs;

+------------------+-----------+

|Log_name|File_size|

+------------------+-----------+

|mysql-bin.000005|17160082|

|mysql-bin.000006|890082|

|mysql-bin.000007|27160082|

+------------------+-----------+

3rowsinset(0.00sec)

发现有3个binlog文件，我们再来查看当前正在使用的binlog文件是哪个：

MySQL [hhzl_gdg]> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000007 | 27160082 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

MySQL[hhzl_gdg]>showmasterstatus;

+------------------+----------+--------------+------------------+-------------------+

|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|

+------------------+----------+--------------+------------------+-------------------+

|mysql-bin.000007|27160082||||

+------------------+----------+--------------+------------------+-------------------+

1rowinset(0.00sec)

发现是 mysql-bin.000007文件。了解的信息差不多了，我们进入到MySQL的数据存放目录 /data/mysql/，先将这几个binlog文件备份起来，以便防止数据丢失，并且后面也许还需要再从这几个文件查找数据丢失原因。

cd /data/mysql/

mkdir -p ~/mysql-bin-backup

cp mysql-bin.* ~/mysql-bin-backup/ -r

1

2

3

cd/data/mysql/

mkdir-p~/mysql-bin-backup

cpmysql-bin.*~/mysql-bin-backup/-r

由于对于我们当前的业务来说，被删除的数据仅仅是2017-08-01到2017-08-25之间的重要数据，因此，我们仅需要恢复这段时间的数据即可，从mysql-bin.000007这个文件导出这个时间段的sql：

mysqlbinlog --no-defaults --start-datetime='2017-08-01 00:00:00' --stop-datetime='2017-08-25 00:00:00' -d hhzl_gdg /root/mysql-bin-backup/mysql-bin.000007 > ~/2017-08-01_2047-08-25.sql

1

mysqlbinlog--no-defaults--start-datetime='2017-08-01 00:00:00'--stop-datetime='2017-08-25 00:00:00'-dhhzl_gdg/root/mysql-bin-backup/mysql-bin.000007>~/2017-08-01_2047-08-25.sql

执行完成之后使用mysql命令进入mysql控制台，use 需要恢复数据的数据库，执行source命令即可：

use hhzl_gdg;

source /root/2017-08-01_2047-08-25.sql

1

2

usehhzl_gdg;

source/root/2017-08-01_2047-08-25.sql

参考链接：