12. 存储引擎¶
1. MySQL存储引擎种类¶
1.InnoDB
2.MyISAM
3.MEMORY
4.ARCHIVE
5.FEDERATED
6.EXAMPLE
7.BLACKHOLE
8.MERGE
9.NDBCLUSTER
10.CSV
11.第三方存储引擎TokuDB,myrocks
MySQL5.5以后默认存储引擎InnoDB
2. InnoDB与MyISAM区别¶
| InnoDB | MyISAM |
|---|---|
| MVCC(多版本并发控制) | 不支持 |
| 锁（行级别） | 锁（表级别） |
| 外键 | 不支持 |
| 事务 | 不支持 |
| 热备 | 温备 |
| CSR（断电故障自动恢复） | 不支持 |
# myisam的插入性能比innodb强点
3. 查看存储引擎¶
1.show engines;
2.show create table world.city;
3.select table_schema,table_name,engine from information_schema.tables;
4.select @@default_storage_engine;
5.show table status like 'world.city'\G # 这一句有问题！
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
mysql> show create table world.city;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city | CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `idx_id` (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `idx_co_po` (`CountryCode`,`Population`),
KEY `idx_dis` (`District`(10)),
KEY `idx_po` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select table_schema,table_name,engine from information_schema.tables;
+--------------------+------------------------------------------------------+--------------------+
| table_schema | table_name | engine |
+--------------------+------------------------------------------------------+--------------------+
| information_schema | CHARACTER_SETS | MEMORY |
| information_schema | COLLATIONS | MEMORY |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY |
| information_schema | COLUMNS | InnoDB |
| information_schema | COLUMN_PRIVILEGES | MEMORY |
| information_schema | ENGINES | MEMORY |
| information_schema | EVENTS | InnoDB |
| information_schema | FILES | MEMORY |
| information_schema | GLOBAL_STATUS | MEMORY |
| information_schema | GLOBAL_VARIABLES | MEMORY |
| information_schema | KEY_COLUMN_USAGE | MEMORY |
| information_schema | OPTIMIZER_TRACE | InnoDB |
| information_schema | PARAMETERS | InnoDB |
| information_schema | PARTITIONS | InnoDB |
| information_schema | PLUGINS | InnoDB |
| information_schema | PROCESSLIST | InnoDB |
| information_schema | PROFILING | MEMORY |
| information_schema | REFERENTIAL_CONSTRAINTS | MEMORY |
| information_schema | ROUTINES | InnoDB |
| information_schema | SCHEMATA | MEMORY |
| information_schema | SCHEMA_PRIVILEGES | MEMORY |
| information_schema | SESSION_STATUS | MEMORY |
| information_schema | SESSION_VARIABLES | MEMORY |
| information_schema | STATISTICS | MEMORY |
| information_schema | TABLES | MEMORY |
| information_schema | TABLESPACES | MEMORY |
| information_schema | TABLE_CONSTRAINTS | MEMORY |
| information_schema | TABLE_PRIVILEGES | MEMORY |
| information_schema | TRIGGERS | InnoDB |
| information_schema | USER_PRIVILEGES | MEMORY |
| information_schema | VIEWS | InnoDB |
| information_schema | INNODB_LOCKS | MEMORY |
| information_schema | INNODB_TRX | MEMORY |
| information_schema | INNODB_SYS_DATAFILES | MEMORY |
| information_schema | INNODB_FT_CONFIG | MEMORY |
| information_schema | INNODB_SYS_VIRTUAL | MEMORY |
| information_schema | INNODB_CMP | MEMORY |
| information_schema | INNODB_FT_BEING_DELETED | MEMORY |
| information_schema | INNODB_CMP_RESET | MEMORY |
| information_schema | INNODB_CMP_PER_INDEX | MEMORY |
| information_schema | INNODB_CMPMEM_RESET | MEMORY |
| information_schema | INNODB_FT_DELETED | MEMORY |
| information_schema | INNODB_BUFFER_PAGE_LRU | MEMORY |
| information_schema | INNODB_LOCK_WAITS | MEMORY |
| information_schema | INNODB_TEMP_TABLE_INFO | MEMORY |
| information_schema | INNODB_SYS_INDEXES | MEMORY |
| information_schema | INNODB_SYS_TABLES | MEMORY |
| information_schema | INNODB_SYS_FIELDS | MEMORY |
| information_schema | INNODB_CMP_PER_INDEX_RESET | MEMORY |
| information_schema | INNODB_BUFFER_PAGE | MEMORY |
| information_schema | INNODB_FT_DEFAULT_STOPWORD | MEMORY |
| information_schema | INNODB_FT_INDEX_TABLE | MEMORY |
| information_schema | INNODB_FT_INDEX_CACHE | MEMORY |
| information_schema | INNODB_SYS_TABLESPACES | MEMORY |
| information_schema | INNODB_METRICS | MEMORY |
| information_schema | INNODB_SYS_FOREIGN_COLS | MEMORY |
| information_schema | INNODB_CMPMEM | MEMORY |
| information_schema | INNODB_BUFFER_POOL_STATS | MEMORY |
| information_schema | INNODB_SYS_COLUMNS | MEMORY |
| information_schema | INNODB_SYS_FOREIGN | MEMORY |
| information_schema | INNODB_SYS_TABLESTATS | MEMORY |
| bookmanage | admin | MyISAM |
| bookmanage | books | MyISAM |
| bookmanage | borrow_book | MyISAM |
| bookmanage | history | MyISAM |
| bookmanage | return_book | MyISAM |
| bookmanage | student | InnoDB |
| db1 | stu | InnoDB |
| db1 | t1 | InnoDB |
| mysql | columns_priv | MyISAM |
| mysql | db | MyISAM |
| mysql | engine_cost | InnoDB |
| mysql | event | MyISAM |
| mysql | func | MyISAM |
| mysql | general_log | CSV |
| mysql | gtid_executed | InnoDB |
| mysql | help_category | InnoDB |
| mysql | help_keyword | InnoDB |
| mysql | help_relation | InnoDB |
| mysql | help_topic | InnoDB |
| mysql | innodb_index_stats | InnoDB |
| mysql | innodb_table_stats | InnoDB |
| mysql | ndb_binlog_index | MyISAM |
| mysql | plugin | InnoDB |
| mysql | proc | MyISAM |
| mysql | procs_priv | MyISAM |
| mysql | proxies_priv | MyISAM |
| mysql | server_cost | InnoDB |
| mysql | servers | InnoDB |
| mysql | slave_master_info | InnoDB |
| mysql | slave_relay_log_info | InnoDB |
| mysql | slave_worker_info | InnoDB |
| mysql | slow_log | CSV |
| mysql | tables_priv | MyISAM |
| mysql | time_zone | InnoDB |
| mysql | time_zone_leap_second | InnoDB |
| mysql | time_zone_name | InnoDB |
| mysql | time_zone_transition | InnoDB |
| mysql | time_zone_transition_type | InnoDB |
| mysql | user | MyISAM |
| oldboy | t1 | InnoDB |
| performance_schema | accounts | PERFORMANCE_SCHEMA |
| performance_schema | cond_instances | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_current | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_history | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_history_long | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_summary_by_account_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_summary_by_host_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_summary_by_user_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_stages_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_current | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_history | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_history_long | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_account_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_digest | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_host_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_program | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_by_user_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_statements_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_current | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_history | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_history_long | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_summary_by_account_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_summary_by_host_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_summary_by_user_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_transactions_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_current | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_history | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_history_long | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_by_account_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_by_host_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_by_instance | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_by_user_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | events_waits_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | file_instances | PERFORMANCE_SCHEMA |
| performance_schema | file_summary_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | file_summary_by_instance | PERFORMANCE_SCHEMA |
| performance_schema | global_status | PERFORMANCE_SCHEMA |
| performance_schema | global_variables | PERFORMANCE_SCHEMA |
| performance_schema | host_cache | PERFORMANCE_SCHEMA |
| performance_schema | hosts | PERFORMANCE_SCHEMA |
| performance_schema | memory_summary_by_account_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | memory_summary_by_host_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | memory_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | memory_summary_by_user_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | memory_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | metadata_locks | PERFORMANCE_SCHEMA |
| performance_schema | mutex_instances | PERFORMANCE_SCHEMA |
| performance_schema | objects_summary_global_by_type | PERFORMANCE_SCHEMA |
| performance_schema | performance_timers | PERFORMANCE_SCHEMA |
| performance_schema | prepared_statements_instances | PERFORMANCE_SCHEMA |
| performance_schema | replication_applier_configuration | PERFORMANCE_SCHEMA |
| performance_schema | replication_applier_status | PERFORMANCE_SCHEMA |
| performance_schema | replication_applier_status_by_coordinator | PERFORMANCE_SCHEMA |
| performance_schema | replication_applier_status_by_worker | PERFORMANCE_SCHEMA |
| performance_schema | replication_connection_configuration | PERFORMANCE_SCHEMA |
| performance_schema | replication_connection_status | PERFORMANCE_SCHEMA |
| performance_schema | replication_group_member_stats | PERFORMANCE_SCHEMA |
| performance_schema | replication_group_members | PERFORMANCE_SCHEMA |
| performance_schema | rwlock_instances | PERFORMANCE_SCHEMA |
| performance_schema | session_account_connect_attrs | PERFORMANCE_SCHEMA |
| performance_schema | session_connect_attrs | PERFORMANCE_SCHEMA |
| performance_schema | session_status | PERFORMANCE_SCHEMA |
| performance_schema | session_variables | PERFORMANCE_SCHEMA |
| performance_schema | setup_actors | PERFORMANCE_SCHEMA |
| performance_schema | setup_consumers | PERFORMANCE_SCHEMA |
| performance_schema | setup_instruments | PERFORMANCE_SCHEMA |
| performance_schema | setup_objects | PERFORMANCE_SCHEMA |
| performance_schema | setup_timers | PERFORMANCE_SCHEMA |
| performance_schema | socket_instances | PERFORMANCE_SCHEMA |
| performance_schema | socket_summary_by_event_name | PERFORMANCE_SCHEMA |
| performance_schema | socket_summary_by_instance | PERFORMANCE_SCHEMA |
| performance_schema | status_by_account | PERFORMANCE_SCHEMA |
| performance_schema | status_by_host | PERFORMANCE_SCHEMA |
| performance_schema | status_by_thread | PERFORMANCE_SCHEMA |
| performance_schema | status_by_user | PERFORMANCE_SCHEMA |
| performance_schema | table_handles | PERFORMANCE_SCHEMA |
| performance_schema | table_io_waits_summary_by_index_usage | PERFORMANCE_SCHEMA |
| performance_schema | table_io_waits_summary_by_table | PERFORMANCE_SCHEMA |
| performance_schema | table_lock_waits_summary_by_table | PERFORMANCE_SCHEMA |
| performance_schema | threads | PERFORMANCE_SCHEMA |
| performance_schema | user_variables_by_thread | PERFORMANCE_SCHEMA |
| performance_schema | users | PERFORMANCE_SCHEMA |
| performance_schema | variables_by_thread | PERFORMANCE_SCHEMA |
| sys | host_summary | NULL |
| sys | host_summary_by_file_io | NULL |
| sys | host_summary_by_file_io_type | NULL |
| sys | host_summary_by_stages | NULL |
| sys | host_summary_by_statement_latency | NULL |
| sys | host_summary_by_statement_type | NULL |
| sys | innodb_buffer_stats_by_schema | NULL |
| sys | innodb_buffer_stats_by_table | NULL |
| sys | innodb_lock_waits | NULL |
| sys | io_by_thread_by_latency | NULL |
| sys | io_global_by_file_by_bytes | NULL |
| sys | io_global_by_file_by_latency | NULL |
| sys | io_global_by_wait_by_bytes | NULL |
| sys | io_global_by_wait_by_latency | NULL |
| sys | latest_file_io | NULL |
| sys | memory_by_host_by_current_bytes | NULL |
| sys | memory_by_thread_by_current_bytes | NULL |
| sys | memory_by_user_by_current_bytes | NULL |
| sys | memory_global_by_current_bytes | NULL |
| sys | memory_global_total | NULL |
| sys | metrics | NULL |
| sys | processlist | NULL |
| sys | ps_check_lost_instrumentation | NULL |
| sys | schema_auto_increment_columns | NULL |
| sys | schema_index_statistics | NULL |
| sys | schema_object_overview | NULL |
| sys | schema_redundant_indexes | NULL |
| sys | schema_table_lock_waits | NULL |
| sys | schema_table_statistics | NULL |
| sys | schema_table_statistics_with_buffer | NULL |
| sys | schema_tables_with_full_table_scans | NULL |
| sys | schema_unused_indexes | NULL |
| sys | session | NULL |
| sys | session_ssl_status | NULL |
| sys | statement_analysis | NULL |
| sys | statements_with_errors_or_warnings | NULL |
| sys | statements_with_full_table_scans | NULL |
| sys | statements_with_runtimes_in_95th_percentile | NULL |
| sys | statements_with_sorting | NULL |
| sys | statements_with_temp_tables | NULL |
| sys | sys_config | InnoDB |
| sys | user_summary | NULL |
| sys | user_summary_by_file_io | NULL |
| sys | user_summary_by_file_io_type | NULL |
| sys | user_summary_by_stages | NULL |
| sys | user_summary_by_statement_latency | NULL |
| sys | user_summary_by_statement_type | NULL |
| sys | version | NULL |
| sys | wait_classes_global_by_avg_latency | NULL |
| sys | wait_classes_global_by_latency | NULL |
| sys | waits_by_host_by_latency | NULL |
| sys | waits_by_user_by_latency | NULL |
| sys | waits_global_by_latency | NULL |
| sys | x$host_summary | NULL |
| sys | x$host_summary_by_file_io | NULL |
| sys | x$host_summary_by_file_io_type | NULL |
| sys | x$host_summary_by_stages | NULL |
| sys | x$host_summary_by_statement_latency | NULL |
| sys | x$host_summary_by_statement_type | NULL |
| sys | x$innodb_buffer_stats_by_schema | NULL |
| sys | x$innodb_buffer_stats_by_table | NULL |
| sys | x$innodb_lock_waits | NULL |
| sys | x$io_by_thread_by_latency | NULL |
| sys | x$io_global_by_file_by_bytes | NULL |
| sys | x$io_global_by_file_by_latency | NULL |
| sys | x$io_global_by_wait_by_bytes | NULL |
| sys | x$io_global_by_wait_by_latency | NULL |
| sys | x$latest_file_io | NULL |
| sys | x$memory_by_host_by_current_bytes | NULL |
| sys | x$memory_by_thread_by_current_bytes | NULL |
| sys | x$memory_by_user_by_current_bytes | NULL |
| sys | x$memory_global_by_current_bytes | NULL |
| sys | x$memory_global_total | NULL |
| sys | x$processlist | NULL |
| sys | x$ps_digest_95th_percentile_by_avg_us | NULL |
| sys | x$ps_digest_avg_latency_distribution | NULL |
| sys | x$ps_schema_table_statistics_io | NULL |
| sys | x$schema_flattened_keys | NULL |
| sys | x$schema_index_statistics | NULL |
| sys | x$schema_table_lock_waits | NULL |
| sys | x$schema_table_statistics | NULL |
| sys | x$schema_table_statistics_with_buffer | NULL |
| sys | x$schema_tables_with_full_table_scans | NULL |
| sys | x$session | NULL |
| sys | x$statement_analysis | NULL |
| sys | x$statements_with_errors_or_warnings | NULL |
| sys | x$statements_with_full_table_scans | NULL |
| sys | x$statements_with_runtimes_in_95th_percentile | NULL |
| sys | x$statements_with_sorting | NULL |
| sys | x$statements_with_temp_tables | NULL |
| sys | x$user_summary | NULL |
| sys | x$user_summary_by_file_io | NULL |
| sys | x$user_summary_by_file_io_type | NULL |
| sys | x$user_summary_by_stages | NULL |
| sys | x$user_summary_by_statement_latency | NULL |
| sys | x$user_summary_by_statement_type | NULL |
| sys | x$wait_classes_global_by_avg_latency | NULL |
| sys | x$wait_classes_global_by_latency | NULL |
| sys | x$waits_by_host_by_latency | NULL |
| sys | x$waits_by_user_by_latency | NULL |
| sys | x$waits_global_by_latency | NULL |
| world | city | InnoDB |
| world | country | InnoDB |
| world | countrylanguage | InnoDB |
+--------------------+------------------------------------------------------+--------------------+
292 rows in set (0.01 sec)
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
4. InnoDB存储引擎物理存储方式------表空间（TBS）管理模式¶
4.1 共享表空间¶
类似于oracle管理模式
1.磁盘文件：ibdata1
2.存了什么：
2.1 系统数据
2.2 undo日志
2.3 磁盘临时表
4.2 独立表空间¶
# 每个表单独使用表空间存储
1.磁盘文件：
1.1 t1.ibd -->t1表的数据和索引
1.2 t1.frm -->t1表的列相关信息
5. 表空间操作¶
5.1 共享表空间¶
1.查看默认共享表空间
show variables like 'innodb_data_file_path';
默认12M，自动增长64M
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
2.一般，共享表空间后写的情况很少，都是在初始化之前就做，后写的话必须按ibdata1的实际大小写入
# 企业中，建议在初始化数据的时候就配置好，一般2-3个，512M一个
vim /etc/my.cnf
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
5.2 独立表空间¶
# 一个表一个ibd文件，存储表数据和索引
1.查看是否是独立表空间
show variables like 'innodb_file_per_table'; #on是开启
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
2.删除独立表空间
alter table t1 discard tablespace;
3.添加独立表空间
alter table t1 import tablespace;
4.innodb的表
# 表数据和索引： t1.ibd
# 列的基本信息： t1.frm
其他属性信息：基表（这个基表是啥？）
6. 案例¶
6.1 背景信息¶
1.lnmt环境
2.数据库2个表：jira（bug追踪）、confluence（内部知识库）
3.联想服务器：IBM
4.磁盘500G，无raid
5.centos6.8
6.mysql 5.6.33 innodb 独立表空间
7.没有备份，未开日志
8.编译制作rpm，所有软件和数据都在“/”
6.2 事故¶
1.断电了，重启完成后，根目录“/”只读
2.按照提示，进行fsck，重启后，启动mysql启动不了
3.查看磁盘文件，发现confulence库有，但是jira库不见了
6.3 分析¶
1.没有备份，没有主从，jira需要硬盘恢复
2.能否暂时把confulence库先打开用着
解决方案：
1.create table xxx
2.alter table xxx discard tablespace;
3.alter table xxx import tablespace;
# 面临的问题，
1.confulnece库中，一共有107张表,如果手动建，很麻烦，而且不知道字段定义
2.有2年前的历史库，可以知道所有库的字段
#解决办法：
1)根据历史库
mysqldump -uroot -p -B confulence --no-data >confulence.sql
得到所有的表结构
2)得到所有的discard语句
select concat('alter table ',table_schema,'.',table_name,' discard tablespace;')from information_schema.tables where table_schema='confluence' into outfile '/tmp/discard.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。因为存在主外键关系。一个一个分析表结构的话很痛苦
跳过外键检查：set foreign_key_checks=0
3)拷贝最新的confulence库中的所有表的ibd文件
4)得到所有的import语句
select concat('alter table ',table_schema,'.',table_name,' import tablespace;')from information_schema.tables where table_schema='confluence' into outfile '/tmp/import.sql';
source /tmp/import.sql
5）验证数据
由此案例可以看出：备份的重要性！！！
最后更新:
2022-02-20 12:36:30