彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > ibdata1文件损坏时恢复InnoDB单表测试

ibdata1文件损坏时恢复InnoDB单表测试

发布时间:2019-10-11 18:24编辑:工作委员会浏览(161)

     

    Preface

     

        ibdata1 file is a shared system tablespace of innodb engine.Although we always set variable of "innodb_file_per_彩世界平台,table" to "on" which means put the data of table into their individual ibd files.The ibdata file will still store undo log of our innodb tables.As the ibdata1 file is inevitable to make the MySQL database running properly.Today,I'm gonna demonstrate a way to rescue the table in an instance whose ibdata file is destroied by a certain way such as write several unmeaningful characters into it.

    彩世界开奖app苹果下载,彩世界时时app, 

    Porcedure

     

    Backup the instance using Xtrabackup first.

     1 [root@zlm1 13:46:27 ~]
     2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
     3 180805 13:46:40 innobackupex: Starting the backup operation
     4 ... //Omitted.
     5 
     6 [root@zlm1 13:47:14 /data/backup]
     7 #ls -l
     8 total 4
     9 drwxr-x--- 7 root root 4096 Aug  5 13:47 2018-08-05_13-46-40
    10 
    11 [root@zlm1 13:47:17 /data/backup]
    

     

    Check the target table which we want to rescue in plan.

     1 zlm@192.168.56.100:3306 [sysbench]>show tables;
     2 +--------------------+
     3 | Tables_in_sysbench |
     4 +--------------------+
     5 | sbtest1            |
     6 | sbtest2            |
     7 | sbtest3            |
     8 | sbtest4            |
     9 +--------------------+
    10 4 rows in set (0.00 sec)
    11 
    12 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
    13 +----------+
    14 | count(*) |
    15 +----------+
    16 |    10000 |
    17 +----------+
    18 1 row in set (0.05 sec)
    19 
    20 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest4 limit 1000;
    21 Query OK, 1000 rows affected (0.17 sec)
    22 
    23 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
    24 +----------+
    25 | count(*) |
    26 +----------+
    27 |     9000 |
    28 +----------+
    29 1 row in set (0.00 sec)
    

     

    Destroy the ibdata1 file with "dd" command.

     1 [root@zlm1 13:57:01 ~]
     2 #cd /data/mysql/mysql3306/data
     3 
     4 [root@zlm1 13:59:35 /data/mysql/mysql3306/data]
     5 #ls -l
     6 total 433892
     7 -rw-r----- 1 mysql mysql        56 Mar 18 15:10 auto.cnf
     8 -rw-r----- 1 mysql mysql     81490 Aug  5 13:50 error.log
     9 -rw-r----- 1 mysql mysql       882 Jul 31 16:36 ib_buffer_pool
    10 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ibdata1
    11 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile0
    12 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile1
    13 -rw-r----- 1 mysql mysql 104857600 Jul  1 14:32 ib_logfile2
    14 -rw-r----- 1 mysql mysql  12582912 Aug  5 13:45 ibtmp1
    15 -rw-r----- 1 mysql mysql      3924 Aug  5 13:53 innodb_status.3799
    16 -rw-r----- 1 mysql mysql         0 Jul 14 02:52 innodb_status.3828
    17 -rw-r----- 1 mysql mysql      4008 Jun  1 21:38 innodb_status.4131
    18 drwxr-x--- 2 mysql mysql      4096 Jul  7 09:57 mrbs
    19 drwxr-x--- 2 mysql mysql      4096 Mar 18 15:10 mysql
    20 -rw-r----- 1 mysql mysql         5 Aug  5 13:45 mysql.pid
    21 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 performance_schema
    22 -rw-r----- 1 mysql mysql       177 Jun  4 16:48 relay-bin.000001
    23 -rw-r----- 1 mysql mysql        19 Jun  4 16:48 relay-bin.index
    24 -rw-r----- 1 mysql mysql    526773 Aug  5 13:45 slow.log
    25 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 sys
    26 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:35 sysbench
    27 -rw-r----- 1 mysql mysql  11610419 Jul 29 09:52 zlm1.log
    28 
    29 [root@zlm1 13:59:38 /data/mysql/mysql3306/data]
    30 #dd if=/dev/zero of=./ibdata1 bs=1024 count=262144
    31 262144+0 records in
    32 262144+0 records out
    33 268435456 bytes (268 MB) copied, 1.61997 s, 166 MB/s
    34 
    35 [root@zlm1 14:02:34 /data/mysql/mysql3306/data]
    36 #ls -l|grep ibdata1
    37 -rw-r----- 1 mysql mysql 268435456 Aug  5 14:06 ibdata1 //The ibdata1 turned out to be 256M and filled with zero.
    

     

    Restart the MySQL instance.

     1 zlm@192.168.56.100:3306 [sysbench]>exit
     2 Bye
     3 
     4 [root@zlm1 14:13:06 ~]
     5 #mysqladmin shutdown
     6 
     7 [root@zlm1 14:13:10 ~]
     8 #ps aux|grep mysqld
     9 root      4002  0.0  0.0 112640   960 pts/0    R+   14:13   0:00 grep --color=auto mysqld
    10 
    11 [root@zlm1 14:13:15 ~]
    12 #./mysqld.sh
    13 
    14 [root@zlm1 14:13:53 ~]
    15 #mysql
    16 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
    17 
    18 [root@zlm1 14:13:56 ~]
    19 #mysql
    20 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
    21 
    22 [root@zlm1 14:13:58 ~]
    23 #cd /data/mysql/mysql3306/data
    24 
    25 [root@zlm1 14:14:07 /data/mysql/mysql3306/data]
    26 #tail error.log
    27 ... /Omitted.
    28 2018-08-05T12:13:53.242723Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
    29 2018-08-05T12:13:53.242806Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 4008 ...
    30 2018-08-05T12:13:53.249168Z 0 [Note] InnoDB: PUNCH HOLE support available
    31 2018-08-05T12:13:53.249207Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    32 2018-08-05T12:13:53.249213Z 0 [Note] InnoDB: Uses event mutexes
    33 2018-08-05T12:13:53.249218Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    34 2018-08-05T12:13:53.249222Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
    35 2018-08-05T12:13:53.249227Z 0 [Note] InnoDB: Using Linux native AIO
    36 2018-08-05T12:13:53.249426Z 0 [Note] InnoDB: Number of pools: 1
    37 2018-08-05T12:13:53.249507Z 0 [Note] InnoDB: Using CPU crc32 instructions
    38 2018-08-05T12:13:53.251488Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    39 2018-08-05T12:13:53.256630Z 0 [Note] InnoDB: Completed initialization of buffer pool
    40 2018-08-05T12:13:53.257913Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    41 2018-08-05T12:13:53.280321Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    42 12:13:53 UTC - mysqld got signal 11 ;
    43 This could be because you hit a bug. It is also possible that this binary
    44 or one of the libraries it was linked against is corrupt, improperly built,
    45 or misconfigured. This error can also be caused by malfunctioning hardware.
    46 Attempting to collect some information that could help diagnose the problem.
    47 As this is a crash and something is definitely wrong, the information
    48 collection process might fail.
    49 
    50 key_buffer_size=8388608
    51 read_buffer_size=2097152
    52 max_used_connections=0
    53 max_threads=100
    54 thread_count=0
    55 connection_count=0
    56 It is possible that mysqld could use up to 
    57 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 227127 K  bytes of memory
    58 Hope that's ok; if not, decrease some variables in the equation.
    59 
    60 Thread pointer: 0x0
    61 Attempting backtrace. You can use the following information to find out
    62 where mysqld died. If you see no messages after this, something went
    63 terribly wrong...
    64 stack_bottom = 0 thread_stack 0x30000
    65 mysqld(my_print_stacktrace+0x35)[0xf4a495]
    66 mysqld(handle_fatal_signal+0x4a4)[0x7ce2f4]
    67 /lib64/libpthread.so.0(+0xf130)[0x7f6bbee76130]
    68 mysqld(_Z26page_cur_search_with_matchPK11buf_block_tPK12dict_index_tPK8dtuple_t15page_cur_mode_tPmS9_P10page_cur_tP8rtr_info+0x148)[0x1074478]
    69 mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t+0x1598)[0x11806d8]
    70 mysqld(_Z30btr_pcur_open_on_user_rec_funcP12dict_index_tPK8dtuple_t15page_cur_mode_tmP10btr_pcur_tPKcmP5mtr_t+0x212)[0x1184b62]
    71 mysqld[0x11df28d]
    72 mysqld(_Z19dict_load_sys_tableP12dict_table_t+0x69)[0x11e0609]
    73 mysqld(_Z9dict_bootv+0xdfb)[0x11bf48b]
    74 mysqld(_Z34innobase_start_or_create_for_mysqlv+0x3212)[0x11150a2]
    75 mysqld[0x100023a]
    76 mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x51)[0x819721]
    77 mysqld[0xd39226]
    78 mysqld(_Z40plugin_register_builtin_and_init_core_sePiPPc+0x3e4)[0xd397a4]
    79 mysqld[0x7c48f7]
    80 mysqld(_Z11mysqld_mainiPPc+0x92f)[0x7c7e9f]
    81 /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f6bbd857af5]
    82 mysqld[0x7be479]
    83 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    84 information that should help you find out what is causing the crash.
    85 
    86 [root@zlm1 14:14:36 /data/mysql/mysql3306/data]
    87 
    88 //Okay,becasue of the lack of normal ibdata1 file in datadir,the instance cannot be started again.
    89 //Then,how can we resuce the data in target table 'sbtest4'?
    90 //First of all,let's get the ibd and frm file of it from the crashed server.
    

     

    Copy ibd and frm file of table "sbtest4" to another newly initialized instance on zlm2.

    1 [root@zlm1 14:20:39 /data/mysql/mysql3306/data/sysbench]
    2 #scp sbtest4.* zlm2:/data/backup
    3 sbtest4.frm                                                                                                        100% 8632     8.4KB/s   00:00    
    4 sbtest4.ibd                                                                                                        100%   10MB  10.0MB/s   00:01
    

     

    Install mysql-utilities package on zlm2.

     1 [root@zlm2 14:23:13 ~]
     2 #mysqlfrm --help
     3 -bash: mysqlfrm: command not found
     4 
     5 [root@zlm2 14:23:17 ~]
     6 #yum install mysql-utilities
     7 Loaded plugins: fastestmirror
     8 base                                                                                                                          | 3.6 kB  00:00:00     
     9 epel/x86_64/metalink                                                                                                          | 7.0 kB  00:00:00     
    10 epel                                                                                                                          | 3.2 kB  00:00:00     
    11 extras                                                                                                                        | 3.4 kB  00:00:00     
    12 updates                                                                                                                       | 3.4 kB  00:00:00     
    13 (1/5): epel/x86_64/group_gz                                                                                                   |  88 kB  00:00:00     
    14 (2/5): epel/x86_64/updateinfo                                                                                                 | 932 kB  00:00:00     
    15 (3/5): extras/7/x86_64/primary_db                                                                                             | 173 kB  00:00:00     
    16 (4/5): epel/x86_64/primary                                                                                                    | 3.6 MB  00:00:01     
    17 (5/5): updates/7/x86_64/primary_db                                                                                            | 4.3 MB  00:00:04     
    18 Loading mirror speeds from cached hostfile
    19  * base: mirrors.shu.edu.cn
    20  * epel: mirrors.tongji.edu.cn
    21  * extras: mirrors.163.com
    22  * updates: mirrors.163.com
    23 epel                                                                                                                                     12629/12629
    24 Resolving Dependencies
    25 --> Running transaction check
    26 ---> Package mysql-utilities.noarch 0:1.3.6-1.el7 will be installed
    27 --> Processing Dependency: mysql-connector-python for package: mysql-utilities-1.3.6-1.el7.noarch
    28 --> Running transaction check
    29 ---> Package mysql-connector-python.noarch 0:1.1.6-1.el7 will be installed
    30 --> Finished Dependency Resolution
    31 
    32 ... //Omitted.
    33 
    34 Installed:
    35   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
    36 
    37 Dependency Installed:
    38   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
    39 
    40 Complete!
    

     

    Restore the table structure by mysqlfrm.

     1 [root@zlm2 14:30:41 ~]
     2 #cd /data/backup/
     3 
     4 [root@zlm2 14:30:48 /data/backup]
     5 #ls -l|grep sbtest4
     6 -rw-r----- 1 root  root      8632 Aug  5 14:30 sbtest4.frm
     7 -rw-r----- 1 root  root  10485760 Aug  5 14:30 sbtest4.ibd
     8 
     9 [root@zlm2 14:31:25 /data/backup]
    10 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic ./sbtest4.frm
    11 # WARNING The --port option is not used in the --diagnostic mode.
    12 # WARNING: Cannot generate character set or collation names without the --server option.
    13 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
    14 # Reading .frm file for ./sbtest4.frm:
    15 # The .frm file is a TABLE.
    16 # CREATE TABLE Statement:
    17 
    18 CREATE TABLE `sbtest4` (
    19   `id` int(11) NOT NULL AUTO_INCREMENT, 
    20   `k` int(11) NOT NULL, 
    21   `c` char(360) NOT NULL, 
    22   `pad` char(180) NOT NULL, 
    23 PRIMARY KEY `PRIMARY` (`id`),
    24 KEY `k_4` (`k`)
    25 ) ENGINE=InnoDB;
    26 
    27 #...done.
    

     

    Create a same table structure in the new instance using the restored "create table" statement.

     1 [root@zlm2 14:39:02 /data/backup]
     2 #mysql
     3 Welcome to the MySQL monitor.  Commands end with ; or g.
     4 Your MySQL connection id is 7
     5 Server version: 5.7.21-log MySQL Community Server (GPL)
     6 
     7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
     8 
     9 Oracle is a registered trademark of Oracle Corporation and/or its
    10 affiliates. Other names may be trademarks of their respective
    11 owners.
    12 
    13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    14 
    15 zlm@192.168.56.101:3306 [(none)]>show databases;
    16 +--------------------+
    17 | Database           |
    18 +--------------------+
    19 | information_schema |
    20 | mrbs               |
    21 | mysql              |
    22 | performance_schema |
    23 | sys                |
    24 +--------------------+
    25 5 rows in set (0.00 sec)
    26 
    27 zlm@192.168.56.101:3306 [(none)]>create database sysbench;
    28 Query OK, 1 row affected (0.00 sec)
    29 
    30 zlm@192.168.56.101:3306 [(none)]>use sysbench
    31 Database changed
    32 zlm@192.168.56.101:3306 [sysbench]>show tables;
    33 Empty set (0.00 sec)
    34 
    35 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
    36     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
    37     ->   `k` int(11) NOT NULL, 
    38     ->   `c` char(360) NOT NULL, 
    39     ->   `pad` char(180) NOT NULL, 
    40     -> PRIMARY KEY `PRIMARY` (`id`),
    41     -> KEY `k_4` (`k`)
    42     -> ) ENGINE=InnoDB;
    43 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
    44 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
    45     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
    46     ->   `k` int(11) NOT NULL, 
    47     ->   `c` char(120) NOT NULL, 
    48     ->   `pad` char(60) NOT NULL, 
    49     -> PRIMARY KEY `PRIMARY` (`id`),
    50     -> KEY `k_4` (`k`)
    51     -> ) ENGINE=InnoDB;
    52 ERROR 1146 (42S02): Table 'sysbench.sbtest4' doesn't exist
    53 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4_bak` (
    54     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
    55     ->   `k` int(11) NOT NULL, 
    56     ->   `c` char(120) NOT NULL, 
    57     ->   `pad` char(60) NOT NULL, 
    58     -> PRIMARY KEY `PRIMARY` (`id`),
    59     -> KEY `k_4` (`k`)
    60     -> ) ENGINE=InnoDB;
    61 Query OK, 0 rows affected (0.01 sec)
    62 
    63 
    64 zlm@192.168.56.101:3306 [sysbench]>rename table sbtest4_bak to sbtest4;
    65 Query OK, 0 rows affected (0.00 sec)
    66 
    67 zlm@192.168.56.101:3306 [sysbench]>show tables;
    68 +--------------------+
    69 | Tables_in_sysbench |
    70 +--------------------+
    71 | sbtest4            |
    72 +--------------------+
    73 1 row in set (0.00 sec)
    74 
    75 zlm@192.168.56.101:3306 [sysbench]>show create table sbtest4G
    76 *************************** 1. row ***************************
    77        Table: sbtest4
    78 Create Table: CREATE TABLE `sbtest4` (
    79   `id` int(11) NOT NULL AUTO_INCREMENT,
    80   `k` int(11) NOT NULL,
    81   `c` char(120) NOT NULL,
    82   `pad` char(60) NOT NULL,
    83   PRIMARY KEY (`id`),
    84   KEY `k_4` (`k`)
    85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    86 1 row in set (0.00 sec)
    87 
    88 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
    89 +----------+
    90 | count(*) |
    91 +----------+
    92 |        0 |
    93 +----------+
    94 1 row in set (0.00 sec)
    

     

    Discard the tablesapce of new table "sbtest4".

     1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 discard tablespace;
     2 Query OK, 0 rows affected (0.00 sec)
     3 
     4 [root@zlm2 15:01:45 /data/mysql/mysql3306/data/sysbench]
     5 #ls -l
     6 total 16
     7 -rw-r----- 1 mysql mysql   61 Aug  5 14:39 db.opt
     8 -rw-r----- 1 mysql mysql 8632 Aug  5 14:54 sbtest4.frm
     9 
    10 //After the discard operation,the new ibd file has gone.
    

     

    Copy the ibd file of table "sbtest4" to the sysbench directory in datadir(notice the owner of file).

     1 [root@zlm2 15:05:42 /data/backup]
     2 #cp sbtest4.ibd /data/mysql/mysql3306/data/sysbench/
     3 
     4 [root@zlm2 15:05:54 /data/backup]
     5 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
     6 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
     7 -rw-r----- 1 root  root  10485760 Aug  5 15:05 sbtest4.ibd
     8 
     9 [root@zlm2 15:06:12 /data/backup]
    10 #chown mysql.mysql /data/mysql/mysql3306/data/sysbench/sbtest4.ibd
    11 
    12 [root@zlm2 15:06:39 /data/backup]
    13 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
    14 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
    15 -rw-r----- 1 mysql mysql 10485760 Aug  5 15:05 sbtest4.ibd
    

     

    Import the original tablespace of table "sbtest4".

     1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 import tablespace;
     2 Query OK, 0 rows affected, 1 warning (1.77 sec)
     3 
     4 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
     5 +----------+
     6 | count(*) |
     7 +----------+
     8 |     9000 |
     9 +----------+
    10 1 row in set (0.01 sec)
    11 
    12 //Now the table has been rescued.
    13 //Because of the destroying of ibdata1 in the original instance,it should be restored by Xtrabackup again.
    

     

    Summary

    • This method is only used to resuce a single table without backup when MySQL instance cannot startup beause of the destoryed ibdata file.
    • mysqlfrm is a tool which can load table structure from .frm files.We need to install the mysql-utilities package first.
    • If we don't have a properly full Xtrabackup and binlog,the .ibd file may lose the undo information on target table.In this situation,it's an incompletely recovery.

     

    本文由彩世界平台发布于工作委员会,转载请注明出处:ibdata1文件损坏时恢复InnoDB单表测试

    关键词: