首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
中的回滚,ibdata1文件损坏时恢复InnoDB单表测试
发布时间:2019-12-28 20:44
浏览次数:
USE [TestDB]
GO
/****** 对象:  Table [dbo].[Person]    脚本日期: 11/23/2008 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
    [PersonId] [nchar](18) NOT NULL,
    [PersonName] [nchar](20) NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

在SQL Server 2012(11.0.7001.0)下面在还原一个数据库(备份文件40多G大小,实际数据库大小300G),在还原过程中,出现一直等待ASYNC_IO_COMPLETION,如下测试截图所示,已经等待了72分钟了,但是还原比例依然为0%

system默认:manager

默认情况下如果执行一个事务中出现错误,则只回滚错误操作语句(就是说这句不执行了,算不上回滚),错误处之前或之后的正确操作语句还是会被提交。如:

Preface

 

 

Use TestDB

Begin TransAction
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('3','Name3')
Commit TransAction
/*
    Select 一下 有'1','Name1'和'3','Name3',
    说明只有第二句的错误被取消了
*/

 

SELECT  r.session_id ,

        r.command ,

        r.start_time,

        r.status,

        r.wait_type,

        CONVERT(NUMERIC(6, 2), r.percent_complete)   AS [Percent Complete(%)] ,

        CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,

                                     GETDATE()), 20) AS [ETA Completion Time] ,

        CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0

        / 60.0) AS [ETA Hours] ,

        CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,

                                                  r.statement_start_offset / 2,

                                                  CASE WHEN r.statement_end_offset = -1

                                                       THEN 1000

                                                       ELSE ( r.statement_end_offset

                                                              - r.statement_start_offset )

                                                            / 2

                                                  END)

                                 FROM   sys.dm_exec_sql_text(sql_handle)

                               )) AS CommandText

FROM    sys.dm_exec_requests r

WHERE   command IN ( 'RESTORE DATABASE', 'BACKUP DATABASE','RESTORE LOG' );

sys默认:change_on_install

全部回滚的方法1:打开 XACT_ABORT

    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.

 

 

Use TestDB
SET XACT_ABORT ON -- 打开
Begin TransAction
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('3','Name3')
Commit TransAction
/*
    当 SET XACT_ABORT 为 ON 时,
    如果执行 Transact-SQL 语句产生运行时错误,
    则整个事务将终止并回滚。 
    默认情况下它是OFF状态。
*/

 

 

使用SQL Plus登录数据库时,system使用密码manager可直接登录。

全部回滚方法2:使用Try...Catch

Porcedure

 

由于为自己的密码时更改过的,所以我的密码不是使用manager登陆,但是代码格式依然是差不多的。

Use TestDB
Begin Try
    Begin TransAction
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('3','Name3')
    Commit TransAction
End Try
Begin Catch
    Rollback TransAction
End Catch
/*
    使用TryCatch来捕获异常。
    如果 TRY 块内生成的错误导致当前事务的状态失效,
    则将该事务归类为不可提交的事务。
    如果通常在 TRY 块外中止事务的错误在 TRY 内发生时,
    就会导致事务进入不可提交状态。
    不可提交的事务只能执行读操作或 ROLLBACK TRANSACTION。
    该事务不能执行任何可能生成写操作或 COMMIT TRANSACTION 的 Transact-SQL 语句。
    如果事务被分类为不可提交的事务,则 XACT_STATE 函数会返回值 -1。
*/

www.64222.com, 

www.64222.com 1

代码示例:

全部回滚方法3:自定义错误变量

Backup the instance using Xtrabackup first.

 

conn system
输入密码:1234
conn system/输入密码
Use TestDB
Declare @tranError int -- 定义变量
Set @tranError=0
    Begin TransAction
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values('3','Name3')
            Set @tranError = @tranError + @@Error
    If @tranError = 0
        Commit TransAction
    Else
        Rollback TransAction
/*
    自定义一个变量来判断最后是否发生过错误。
*/
 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]

当然,这里是实验,如果还要继续等待的话,相信这个时间会更长。这个是比较让人奇怪的现象。后面查了一下这个跟即时文件初始化(Instant File Initialization (IFI))有关。关于这个概念,可以参考官方文档数据库文件初始化 ,摘抄部分内容如下所示:

 

最后要注意的是:如果一个事务写了 Begin TransAction 而没写 Commit TransAction 或 Rollback TransAction 则相关操作的数据(也许是表,也许是列,这我还没测试。。。)会被锁住。。。而对于锁住的解决办法就是单独执行一下Commit TransAction 或 Rollback TransAction

 

 

但如果是sys用户,密码必须加上as sysdba,即完整密码为:change_on_install as sysdba 

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)

 

1  conn sys/change_on_install as sysdba
conn 用户sys/密码

 

数据库文件初始化

连接scott用户,密码使用tiger可以直接登陆。【该情况只适用于你没有更改过Scott用户密码的设置】

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.

初始化数据和日志文件以覆盖之前删除的文件遗留在磁盘上的任何现有数据。 执行以下其中一项操作时,应首先通过零填充(用零填充)数据和日志文件来初始化这些文件:

1 conn scott/1234
conn 用户scott/密码

 

 

 

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.

·         向现有数据库添加数据或日志文件。

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有