XtraDB存储引擎

XtraDB存储引擎是percona公司对于innodb存储引擎进行改进加强后的产品,第一个版本为1.0.2-1,发布于2008年底。XtraDB兼容innodb的所有特性,并且在IO性能,锁性能,内存管理等多个方面进行了增强。

下载MySQL源代码,当前最新的5.1版本为5.1.40
http://dev.mysql.com/downloads/mysql/5.1.html#source

下载XtraDB源代码,当前最新的版本为1.0.4-8
http://launchpad.net/percona-xtradb/release-8/1.0.4-8/+download/percona-xtradb-1.0.4-8.tar.gz

解压MySQL

tar -zxvf mysql-5.1.40.tar.gz 

解压XtraDB

tar -zxvf percona-xtradb-1.0.4-8.tar.gz

将MySQL原来的innodb源码删除或者重命名

cd mysql-5.1.40/storage
mv innobase innobase_bak

将XtraDB拷贝为innobase

cp -r ../../percona-xtradb-1.0.4-8 innobase/

重新编译安装MySQL

cd ../

./configure --prefix=/home/mysql/mysql --with-extra-charsets=all 
--enable-assembler --enable-profiling --enable-community-features 
--with-plugins=max --with-client-ldflags=-all-static --with-plugins=all

make && make install

安装完成后,就可以按照普通的MySQL安装创建数据库流程执行相应操作即可。XtraDB实际上是基于innodb plugin的代码修改而来,所以以上安装过程和innodb plugin的安装一致。

root@information_schema 04:19:48>show plugins;
+--------------------------------+----------+--------------------+---------+---------+
| Name                           | Status   | Type               | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                      | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_RSEG                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TRX                     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLE_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEX_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ENHANCEMENTS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                     | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
+--------------------------------+----------+--------------------+---------+---------+

这里显示的innodb其实已经是XtraDB引擎了。可以简单看一下XtraDB引擎的特性来验证,例如XtraDB在information_schema中引入了xtradb_enhancements来记录其相对于innodb的增强特性。

root@information_schema 04:18:02>desc information_schema.xtradb_enhancements;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(255) | NO   |     |         |       |
| description | varchar(255) | NO   |     |         |       |
| comment     | varchar(100) | NO   |     |         |       |
| link        | varchar(255) | NO   |     |         |       |
+-------------+--------------+------+-----+---------+-------+

root@information_schema 04:19:45>select * from information_schema.xtradb_enhancements\G
*************************** 1. row ***************************
       name: xtradb_show_enhancements
description: I_S.XTRADB_ENHANCEMENTS
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 2. row ***************************
       name: innodb_show_status
description: Improvements to SHOW INNODB STATUS
    comment: Memory information and lock info fixes
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 3. row ***************************
       name: innodb_io
description: Improvements to InnoDB IO
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 4. row ***************************
       name: innodb_opt_lru_count
description: Fix of buffer_pool mutex
    comment: Decreases contention on buffer_pool mutex on LRU operations
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 5. row ***************************
       name: innodb_buffer_pool_pages
description: Information of buffer pool content
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 6. row ***************************
       name: innodb_expand_undo_slots
description: expandable maximum number of undo slots
    comment: from 1024 (default) to about 4000
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 7. row ***************************
       name: innodb_extra_rseg
description: allow to create extra rollback segments
    comment: When create new db, the new parameter allows to create more rollback segments
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 8. row ***************************
       name: innodb_overwrite_relay_log_info
description: overwrite relay-log.info when slave recovery
    comment: Building as plugin, it is not used.
       link: http://www.percona.com/docs/wiki/percona-xtradb:innodb_overwrite_relay_log_info
*************************** 9. row ***************************
       name: innodb_thread_concurrency_timer_based
description: use InnoDB timer based concurrency throttling (backport from MySQL 5.4.0)
    comment: 
       link: 
*************************** 10. row ***************************
       name: innodb_expand_import
description: convert .ibd file automatically when import tablespace
    comment: the files are generated by xtrabackup export mode.
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 11. row ***************************
       name: innodb_dict_size_limit
description: Limit dictionary cache size
    comment: Variable innodb_dict_size_limit in bytes
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 12. row ***************************
       name: innodb_split_buf_pool_mutex
description: More fix of buffer_pool mutex
    comment: Spliting buf_pool_mutex and optimizing based on innodb_opt_lru_count
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 13. row ***************************
       name: innodb_stats
description: Additional features about InnoDB statistics/optimizer
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 14. row ***************************
       name: innodb_recovery_patches
description: Bugfixes and adjustments about recovery process
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 15. row ***************************
       name: innodb_purge_thread
description: Enable to use purge devoted thread
    comment: 
       link: http://www.percona.com/docs/wiki/percona-xtradb
15 rows in set (0.00 sec)

可以看到引入了很多新的innodb控制参数,允许用户对xtradb引擎的工作方式做更多的控制

root@information_schema 04:22:01>show variables like '%innodb%';
+---------------------------------------+------------------------+
| Variable_name                         | Value                  |
+---------------------------------------+------------------------+
| have_innodb                           | YES                    |
| ignore_builtin_innodb                 | OFF                    |
| innodb_adaptive_checkpoint            | none                   |
| innodb_adaptive_flushing              | ON                     |
| innodb_adaptive_hash_index            | ON                     |
| innodb_additional_mem_pool_size       | 8388608                |
| innodb_autoextend_increment           | 8                      |
| innodb_autoinc_lock_mode              | 1                      |
| innodb_buffer_pool_size               | 134217728              |
| innodb_change_buffering               | inserts                |
| innodb_checksums                      | ON                     |
| innodb_commit_concurrency             | 0                      |
| innodb_concurrency_tickets            | 500                    |
| innodb_data_file_path                 | ibdata1:10M:autoextend |
| innodb_data_home_dir                  |                        |
| innodb_dict_size_limit                | 0                      |
| innodb_doublewrite                    | ON                     |
| innodb_enable_unsafe_group_commit     | 0                      |
| innodb_expand_import                  | 0                      |
| innodb_extra_rsegments                | 0                      |
| innodb_extra_undoslots                | OFF                    |
| innodb_fast_recovery                  | OFF                    |
| innodb_fast_shutdown                  | 1                      |
| innodb_file_format                    | Antelope               |
| innodb_file_format_check              | Antelope               |
| innodb_file_io_threads                | 4                      |
| innodb_file_per_table                 | OFF                    |
| innodb_flush_log_at_trx_commit        | 1                      |
| innodb_flush_method                   |                        |
| innodb_flush_neighbor_pages           | 1                      |
| innodb_force_recovery                 | 0                      |
| innodb_ibuf_accel_rate                | 100                    |
| innodb_ibuf_active_contract           | 0                      |
| innodb_ibuf_max_size                  | 67092480               |
| innodb_io_capacity                    | 200                    |
| innodb_lock_wait_timeout              | 50                     |
| innodb_locks_unsafe_for_binlog        | OFF                    |
| innodb_log_buffer_size                | 8388608                |
| innodb_log_file_size                  | 5242880                |
| innodb_log_files_in_group             | 2                      |
| innodb_log_group_home_dir             | ./                     |
| innodb_max_dirty_pages_pct            | 75                     |
| innodb_max_purge_lag                  | 0                      |
| innodb_mirrored_log_groups            | 1                      |
| innodb_open_files                     | 300                    |
| innodb_overwrite_relay_log_info       | OFF                    |
| innodb_read_ahead                     | linear                 |
| innodb_read_ahead_threshold           | 56                     |
| innodb_read_io_threads                | 4                      |
| innodb_replication_delay              | 0                      |
| innodb_rollback_on_timeout            | OFF                    |
| innodb_show_locks_held                | 10                     |
| innodb_show_verbose_locks             | 0                      |
| innodb_spin_wait_delay                | 6                      |
| innodb_stats_auto_update              | 1                      |
| innodb_stats_method                   | nulls_equal            |
| innodb_stats_on_metadata              | ON                     |
| innodb_stats_sample_pages             | 8                      |
| innodb_stats_update_need_lock         | 1                      |
| innodb_strict_mode                    | OFF                    |
| innodb_support_xa                     | ON                     |
| innodb_sync_spin_loops                | 30                     |
| innodb_table_locks                    | ON                     |
| innodb_thread_concurrency             | 0                      |
| innodb_thread_concurrency_timer_based | OFF                    |
| innodb_thread_sleep_delay             | 10000                  |
| innodb_use_purge_thread               | OFF                    |
| innodb_use_sys_malloc                 | ON                     |
| innodb_version                        | 1.0.4-7                |
| innodb_write_io_threads               | 4                      |
+---------------------------------------+------------------------+
70 rows in set (0.00 sec)

对比一下原版的5.1.40,innodb相关参数只有37个,整整多了33个参数。

mysql> show variables like '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.00 sec)

Innodb monitor介绍

学习oracle的时候,如果想了解一些内部原理和机制,可以使用一些event来生成trace文件,通过分析这些trace文件可以获得很多的信息。而MySQL在这方面比较弱,当然,另一方面来说,MySQL是开源的,可以去直接研究源代码的实现。不过源代码架构庞大,不是每个人都有足够的精力和毅力去解读。InnoDB引擎提供了一个monitor,可以通过monitor一窥其内部的一些统计信息,也可以说是了解InnoDB引擎的一个很好的窗口。

我们最熟悉的,应当就是show innodb status命令,可以直接在客户端输出很多的信息。其实InnoDB monitor一共有四种模式,show innodb status只是其一种模式的直接展现,并且只能交互式开启,无法自动循环捕获信息。另外还有一种适合四种模式的开启方式,则是通过创建一张特殊的innodb表来开启,开启后会按照固定的时间间隔循环,输出信息到log-error参数指定的错误日志文件中,通过drop对应的表,可以停止monitor。

四种monitor分别是:

  • innodb_monitor:create table innodb_monitor(x int) engine=innodb;
  • innodb_lock_monitor:create table innodb_lock_monitor(x int) engine=innodb;
  • innodb_table_monitor:create table innodb_table_monitor(x int) engine=innodb;
  • innodb_tablespace_monitor:create table innodb_tablespace_monitor(x int) engine=innodb;

根据我在5.1.36版本中实际观察到的结果,innodb_monitor/innodb_lock_monitor开启后的执行周期是16s参考手册上说是15s),而innodb_table_monitor/innodb_tablespace_monitor的执行周期是64s。开启monitor后因为是持续周期性的运行的,在不需要的时候一定要记得drop相关表来停止monitor。如果在开启monitor的中间服务器有重启,monitor不会自动重启,并且在下次启动monitor之前,必须先执行停止操作。

其中innodb_monitor/innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show innodb status。innodb_table_monitor则会将系统中所有innodb的表的一些结构和内部信息输出,而innodb_tablespace_monitor则输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。

以下是一些简单的示例:
innodb_monitor/innodb_lock_monitor:

=====================================
090805 22:24:48 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 312921, signal count 308229
Mutex spin waits 0, rounds 18209349, OS waits 111906
RW-shared spins 287775, OS waits 142204; RW-excl spins 175036, OS waits 19318
------------
TRANSACTIONS
------------
Trx id counter 0 121675664
Purge done for trx's n:o < 0 121675662 undo n:o < 0 0
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 121462143, not started, process no 8452, OS thread id 1160767840
mysql tables in use 1, locked 1
MySQL thread id 8056144, query id 78206864 localhost root
---TRANSACTION 0 137229, not started, process no 8452, OS thread id 1158199648
MySQL thread id 50, query id 377 Has read all relay log; waiting for the slave I/O thread to update it
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
34 OS file reads, 80820900 OS file writes, 1263117 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.16 writes/s, 0.63 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 8850487, node heap has 233 buffer(s)
0.11 hash searches/s, 0.42 non-hash searches/s
---
LOG
---
Log sequence number 4 3697502095
Log flushed up to   4 3697502095
Last checkpoint at  4 3697502095
0 pending log writes, 0 pending chkp writes
79595438 log i/o's done, 0.47 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4851752298; in additional pool allocated 13195520
Dictionary memory allocated 145784
Buffer pool size   262144
Free buffers       193334
Database pages     68577
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 70, created 120513, written 2829967
0.00 reads/s, 0.21 creates/s, 0.84 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 8452, id 1157658976, state: waiting for server activity
Number of rows inserted 12233742, updated 57497659, deleted 1, read 69720050
0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 0.05 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

innodb_table_monitor:

===========================================
090805 22:26:56 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 0
  COLUMNS: ID: DATA_VARCHAR prtype 1835012 len 0; FOR_NAME: DATA_VARCHAR prtype 1835012 len 0; 
REF_NAME: DATA_VARCHAR prtype 1835012 len 0; N_COLS: DATA_INT len 4; 
DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: 
DATA_SYS prtype 258 len 7;   INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3
   root page 46, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
  INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0
   root page 47, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  FOR_NAME ID
  INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0
   root page 48, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  REF_NAME ID
...省略若干输出
--------------------------------------
TABLE: name test/test, id 0 81, columns 4, indexes 1, appr.rows 3
  COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; 
DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name GEN_CLUST_INDEX, id 0 23, fields 0/4, uniq 1, type 1
   root page 3, appr.key vals 3, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

innodb_tablespace_monitor:

================================================
090805 22:28:16 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 65536, free limit 6208, free extents 89
not full frag extents 6: used pages 69, full frag extents 0
first seg id not used 0 1067667
SEGMENT id 0 1067666 space 0; page 903; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...省略若干输出
SEGMENT id 0 144216 space 0; page 1307; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 37
Validating tablespace
Validation ok
---------------------------------------
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================

使用Xtrabackup备份MySQL数据库(续)

上一篇里,演示了使用xtrabackup对InnoDB进行在线物理热备,使用innobackupex脚本,可以很简单的一条命令行搞定InnoDB和MyISAM的物理备份。

本文则演示如何从xtrabackup的备份中进行恢复。本次恢复的是一个600GB大小的InnoDB数据库,备份的时候没有使用gzip压缩。

首先将备份好的tar文件解开到目标数据库的数据路径下,这一步类似oracle的restore database:

tar -ixvf mysqlbak.tar

注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境,则需要修改/etc/my.cnf,将innodb_data_file_path等参数设置和原备份的库一致。然后执行:

$innobackupex-1.5.1 --apply-log /opt/mysqldata

这一步类似于oracle的recover database,从日志来看,差不多一个小时执行完毕,该InnoDB数据库分配空间600GB,实际使用空间约590GB,并且数据的更新量还是比较大的,大约一个小时apply-log完成。运行的日志简单记录如下:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackup
           prints "innobackup completed OK!".

090708 09:50:44  innobackupex: Starting ibbackup with command: 
xtrabackup --prepare --target-dir=/opt/mysqldata

xtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2882535424, start_lsn=(514 2288109039)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M...;ibdata60:10000M
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2882535424
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Log scan progressed past the checkpoint lsn 514 2288109039
090708  9:50:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 514 2293351424 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2298594304 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2303837184 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2309080064 (0 %)
090708  9:50:47  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 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 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 514 2314322944 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2319565824 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2324808704 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2330051584 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2335294464 (1 %)
...这里省略若干行
InnoDB: Doing recovery: scanned up to log sequence number 514 3881944064 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887186944 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887732530 (62 %)
090708 10:52:00  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 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 99 
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:17  InnoDB: Started; log sequence number 514 3887732530

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:17  InnoDB: Starting shutdown...
090708 10:52:24  InnoDB: Shutdown completed; log sequence number 514 3887732530

090708 10:52:24  innobackupex: Restarting xtrabackup with command: 
xtrabackup --prepare --target-dir=/opt/mysqldata
for creating ib_logfile*

xtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M;...;ibdata60:10000M
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
090708 10:52:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:25  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:26  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:26  InnoDB: Log file ./ib_logfile3 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile3 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090708 10:52:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:27  InnoDB: Started; log sequence number 514 3887732748

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:27  InnoDB: Starting shutdown...
090708 10:52:29  InnoDB: Shutdown completed; log sequence number 514 3887732748
090708 10:52:29  innobackupex: innobackup completed OK!

运行完毕后,启动mysql即可。

MySQL InnoDB存储引擎的事务隔离级别

我们知道,在关系数据库标准中有四个事务隔离级别:

  • 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据
  • 提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别
  • 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读
  • 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

查看InnoDB系统级别的事务隔离级别:

mysql> SELECT @@global.tx_isolation;
+———————–+
| @@global.tx_isolation |
+———————–+
| REPEATABLE-READ |
+———————–+
1 row in set (0.00 sec)

查看InnoDB会话级别的事务隔离级别:

mysql> SELECT @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)

修改事务隔离级别:

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

Read more of this post

无觅相关文章插件,快速提升流量