Board logo

标题: MySQL的表的存储引擎 [打印本页]
作者: admin    时间: 2019-12-7 19:27     标题: MySQL的表的存储引擎

笺注：这是在 CentOS6.9编译安装Zabbix4.4.5 的基础上进行的。 查看当前MySQL的版本： mysql> select version(); +------------+ | version() | +------------+ | 5.5.48-log | +------------+ 1 row in set (0.00 sec) mysql> 查看当前MySQL已提供什么存储引擎： mysql> show engines; +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ 7 rows in set (0.00 sec) mysql> 注释： 支持 MyISAM 默认使用的是 InnoDB InnoDB支持事务，MyISAM不支持； InnoDB支持行级锁，MyISAM不支持； InnoDB支持外键，MyISAM不支持； 存储引擎一般选择 InnoDB 看当前MySQL默认使用的存储引擎： mysql> show variables like '%default_storage_engine%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) mysql> ############ 查看某个表使用了什么存储引擎： use zabbix; create table t1(id int); show create table t1; 图片8.png ############ 创建表的时候可以指定使用什么存储引擎： create table zabbix.t2(id int) engine=myisam; show create table zabbix.t2; 图片9.png ############ ############ MySQL的主配置文件：（初始状态） [root@Zabbix_server_01 ~]# cat /etc/my.cnf |grep -v "^$" [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /usr/local/mysql/var skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 8 query_cache_size = 8M tmp_table_size = 16M #skip-networking max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 default_storage_engine = InnoDB innodb_data_home_dir = /usr/local/mysql/var innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@Zabbix_server_01 ~]# 关于默认存储引擎、存储引擎 InnoDB的配置： [root@Zabbix_server_01 ~]# grep -A9 "default_storage_engine" /etc/my.cnf default_storage_engine = InnoDB innodb_data_home_dir = /usr/local/mysql/var innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [root@Zabbix_server_01 ~]# 注释：这些代码是在[mysqld]下面。 相关文章： MySQL的字符集

图片附件: 图片8.png (2022-4-12 10:10, 15.87 KB) / 下载次数 89
http://blog.zhuohua.store/attachment.php?aid=19422&k=3927e7502b2b31c687f815c117bf68c9&t=1714619985&sid=3T3SI5



图片附件: 图片9.png (2022-4-12 10:10, 14.91 KB) / 下载次数 80
http://blog.zhuohua.store/attachment.php?aid=19423&k=91e3c1b9464223b23758bd3e89b8e802&t=1714619985&sid=3T3SI5





欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/) Powered by Discuz! 7.2