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)
