MySQL存储引擎：
表类型：
CREATE TABLE ... ENGINE=
注意：不建议使用交叉的存储引擎
InnoDB：默认
处理大量的短期事务；
数据存储于“表空间(table space)”中；
(1) 所有InnoDB表的数据和索引放置于同一个表空间中；（不推荐）
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
(2) 每个表单独使用一个表空间存储表的数据和索引；
innodb_file_per_table=ON
MariaDB [(none)]> show global variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name | Value |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
4 rows in set (0.01 sec)
查看默认引擎
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
7 rows in set (0.00 sec)
数据文件(存储数据和索引)：tbl_name.ibd,
表格式定义：tbl_name.frm
MariaDB [mydb]> create table t2 (id int, name char(30));
Query OK, 0 rows affected (0.69 sec)
[root@node3 data]# cd mydb/
[root@node3 mydb]# ls
db.opt t1.frm t1.ibd t2.frm t2.ibd
MariaDB [mydb]> show table status\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2015-10-23 19:33:56
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: t2
Rows: 0
Avg_row_length: 0
Create_time: 2015-10-26 07:02:07
特性：
- 基于MVCC来支持高并发，支持所有的四个隔离级别，默认级别为REPEATABLE READ; 间隙锁防止幻读；
- 使用聚集索引
- 支持“自适应hash索引”
- 锁粒度：行级锁
总结：
- 数据存储：表空间
- 并发：MVCC, 间隙锁
- 索引：聚集索引、辅助索引
- 性能：预计操作、自适应hash、插入缓存区
- 备份：支持热备(xtrabacup)
-----------------------------------------------------------------------------------------
MyISAM：
- 支持全文索引(FULLTEXT index)、压缩、空间函数(GIS); 但不支持事务，且为表级锁；
- 崩溃后无法安全恢复
适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
替代者：Aria：crash-safe
文件：
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件
[root@node3 hellodb]# ls
classes.frm coc.frm courses.frm db.opt scores.MYI students.MYI teachers.MYI toc.MYD
classes.MYD coc.MYD courses.MYD scores.frm students.frm teachers.frm test.frm toc.MYI
classes.MYI coc.MYI courses.MYI scores.MYD students.MYD teachers.MYD toc.frm
加锁和并发：表级锁
修复：手工或自动修复、但可能丢失数据
索引：非聚集索引
延迟更新索引键：
压缩表
行格式：dynamic, fixed, compressed, compact, redundent
------------------------------------------------------------------------
其它的存储引擎：
CSV：将普通的CSV(字段通过逗号分隔)文本文件作为MySQL表使用；
MRG_MYISAM：将多个MyISAM表合并成为一个虚拟表；
BLACKHOLE：类似于/dev/null，不真正存储任何数据；级联表有用
MEMORY：所有数据都保存于内存中，内存表；支持hash索引；表级锁；临时表
PERFORMANCE_SCHEMA：伪存储引擎；
ARCHIVE：只支持SELECT和INSERT操作；支持行级锁和专用缓存区；用作归档的存储引擎，很少使用
FEDERATED：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取；在MariaDB的上实现是FederatedX
MariaDB支持的其它存储引擎：OQGraph；SphinxSE；TokuDB；Cassandra；CONNECT；SQUENCE
------------------------------------------------------------------------------------
编译安装MYSQL
步骤：
- 安装cmake
- 安装包组"Development Tools" "Server Platform Development"
- 创建用户
- 编译安装mysql-5.5.33
注意：5.5过后就要使用cmake
一、安装cmake
跨平台编译器
# tar xf cmake-2.8.8.tar.gz
# cd cmake-2.8.8
# ./bootstrap
# make
# make install
[root@node200 ~]# yum install cmake -y
安装包组
[root@node200 ~]# yum groupinstall "Development Tools" "Server Platform Development"
指定用户
[root@node200 ~]# groupadd -r -g 306 mysql
[root@node200 ~]# useradd -r -g 306 -u 306 mysql
[root@node200 ~]# id mysql
uid=306(mysql) gid=306(mysql) 组=306(mysql)
二、编译安装mysql-5.5.33
1、使用cmake编译mysql-5.5
cmake指定编译选项的方式不同于make，其实现方式对比如下：
./configure cmake .
./configure --help cmake . -LH or ccmake .
指定安装文件的安装路径时常用的选项：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFDIR=/etc
默认编译的存储引擎包括：csv、myisam、myisammrg和heap。若要安装其它存储引擎，可以使用类似如下编译选项：
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
若要明确指定不编译某存储引擎，可以使用类似如下的选项：
-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
比如：
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1
如若要编译进其它功能，如SSL等，则可使用类似如下选项来实现编译时使用某库或不使用某库：
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
其它常用的选项：
-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0
-DENABLE_PROFILING=1
如果想清理此前的编译所生成的文件，则需要使用如下命令：
make clean
rm CMakeCache.txt
2、编译安装
# groupadd -r mysql
# useradd -g mysql -r -d /mydata/data mysql
# tar xf mysql-5.5.33.tar.gz
# cd mysql-5.5.33
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/mydata/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.44 -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_
[root@node200 mariadb-5.5.48]# tar xf mariadb-5.5.48.tar.gz
[root@node200 ~]# cd mariadb-5.5.48
[root@node200 mariadb-5.5.48]# ls
BUILD cmd-line-utils dbug include libmysqld packaging scripts strings vio
BUILD-CMAKE config.h.cmake debian INSTALL-SOURCE libservices plugin sql support-files win
client configure.cmake Docs INSTALL-WIN-SOURCE man randgen sql-bench tests zlib
cmake COPYING EXCEPTIONS-CLIENT KNOWN_BUGS.txt mysql-test README sql-common unittest
CMakeLists.txt COPYING.LESSER extra libmysql mysys regex storage VERSION
[root@node200 mariadb-5.5.48]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.42 -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
[root@node200 mariadb-5.5.42]# make
[root@node200 ~]# mkdir /mydata/date -pv ###数据目录
mkdir: 已创建目录 "/mydata"
mkdir: 已创建目录 "/mydata/date"
创建逻辑卷
[root@node200 ~]# fdisk /dev/sda
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): p
Disk /dev/sda: 128.8 GB, 128849018880 bytes
255 heads, 63 sectors/track, 15665 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physic
al): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0005a089
Device Boot Start End Blocks Id System
/dev/sda1 * 1 26 204800 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 26 7859 62914560 8e Linux LVM
Command (m for help): n
e extended
p primary partition (1-4)
p
Partition number (1-4): 3
First cylinder (7859-15665, default 7859): +20G
Value out of range.
First cylinder (7859-15665, default 7859):
Using default value 7859
Last cylinder, +cylinders or +size{K,M,G} (7859-15665, default 15665): +20G
Command (m for help): t
Hex code (type L to list codes): 8e
Changed system type of partition 3 to 8e (Linux LVM)
Sector size (logical/physical): 512 bytes / 512 bytes
/dev/sda3 7859 10470 20979891 8e Linux LVM
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: 设备或资源忙.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@node200 ~]# partx -a /dev/sda
[root@node200 ~]# pvcreate /dev/sda3
Physical volume "/dev/sda3" successfully created
[root@node200 ~]# vgcreate myvg /dev/sda3
Volume group "myvg" successfully created
[root@node200 ~]# lvcreate -L 10G -n mydata myvg
Logical volume "mydata" created.
[root@node200 ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
mydata myvg -wi-a----- 10.00g
lv0 vg0 -wi-ao---- 2.00g
lv1 vg0 -wi-ao---- 10.00g
lv2 vg0 -wi-ao---- 10.00g
lv3 vg0 -wi-ao---- 38.00g
[root@node200 ~]# mke2fs -t ext4 /dev/myvg/mydata
mke2fs 1.41.12 (17-May-2010)
文件系统标签=
操作系统:Linux
块大小=4096 (log=2)
分块大小=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
655360 inodes, 2621440 blocks
131072 blocks (5.00%) reserved for the super user
第一个数据块=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632
正在写入inode表: 完成
Creating journal (32768 blocks): 完成
Writing superblocks and filesystem accounting information: 完成
This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
创建目录（自动挂载）
[root@node200 ~]# mkdir -pv /mydata
自动挂载
[root@node200 ~]# vim /etc/fstab
/dev/myvg/mydata /mydata ext4 defaults 0 0
[root@node200 ~]# mount -a
[root@node200 ~]# mount
/dev/mapper/vg0-lv3 on / type ext4 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0")
/dev/sda1 on /boot type ext4 (rw)
/dev/mapper/vg0-lv1 on /usr type ext4 (rw)
/dev/mapper/vg0-lv2 on /var type ext4 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
gvfs-fuse-daemon on /root/.gvfs type fuse.gvfs-fuse-daemon (rw,nosuid,nodev)
/dev/sr0 on /media/CentOS_6.7_Final type iso9660 (ro,nosuid,nodev,uhelper=udisks,uid=0,gid=0,iocharset=utf8,mode=0400,dmode=0500)
/dev/mapper/myvg-mydata on /mydata type ext4 (rw)
[root@node200 ~]# df -hT
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/vg0-lv3 ext4 38G 2.0G 34G 6% /
tmpfs tmpfs 491M 228K 491M 1% /dev/shm
/dev/sda1 ext4 190M 36M 145M 20% /boot
/dev/mapper/vg0-lv1 ext4 9.8G 3.0G 6.3G 33% /usr
/dev/mapper/vg0-lv2 ext4 9.8G 523M 8.8G 6% /var
/dev/sr0 iso9660 3.7G 3.7G 0 100% /media/CentOS_6.7_Final
/dev/mapper/myvg-mydata
ext4 9.8G 23M 9.2G 1% /mydata
修改文件属性
[root@node200 ~]# mkdir /mydata/data
[root@node200 ~]# chown mysql.mysql /mydata/data
安装文件
[root@node200 mariadb-5.5.42]# make install
查看安装文件
[root@node200 mariadb-5.5.42]# cd /usr/local/mariadb-5.5.42/
[root@node200 mariadb-5.5.42]# ls
bin COPYING.LESSER EXCEPTIONS-CLIENT INSTALL-BINARY man README share support-files
COPYING data include lib mysql-test scripts sql-bench
[root@node200 mariadb-5.5.42]# chown .mysql ./*
[root@node200 mariadb-5.5.42]# ll
总用量 220
drwxr-xr-x. 2 root mysql 4096 2月 17 20:32 bin
-rw-r--r--. 1 root mysql 17987 2月 13 2015 COPYING
-rw-r--r--. 1 root mysql 26545 2月 13 2015 COPYING.LESSER
drwxr-xr-x. 3 root mysql 4096 2月 17 20:32 data
-rw-r--r--. 1 root mysql 8245 2月 13 2015 EXCEPTIONS-CLIENT
drwxr-xr-x. 3 root mysql 4096 2月 17 20:32 include
-rw-r--r--. 1 root mysql 8694 2月 13 2015 INSTALL-BINARY
drwxr-xr-x. 3 root mysql 4096 2月 17 20:32 lib
drwxr-xr-x. 4 root mysql 4096 2月 17 20:33 man
drwxr-xr-x. 11 root mysql 4096 2月 17 20:33 mysql-test
-rw-r--r--. 1 root mysql 108813 2月 13 2015 README
drwxr-xr-x. 2 root mysql 4096 2月 17 20:32 scripts
drwxr-xr-x. 27 root mysql 4096 2月 17 20:32 share
drwxr-xr-x. 4 root mysql 4096 2月 17 20:33 sql-bench
drwxr-xr-x. 3 root mysql 4096 2月 17 20:32 support-files
链接
[root@node200 local]# ln -sv mariadb-5.5.42/ mysql
"mysql" -> "mariadb-5.5.42/"
[root@node200 local]# ls
bin etc games include lib lib64 libexec mariadb-5.5.42 mysql sbin share src
[root@node200 local]# cd mysql/
[root@node200 mysql]# ls
[root@node200 mysql]# ll
初始化
[root@node200 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
复制配置文件
[root@node200 mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf
修改配置文件
[root@node200 mysql]# vim /etc/mysql/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
datadir = /mydata/data
innodb_file_per_table = ON
skip_name_resolve = ON
复制服务
[root@node200 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
添加启动目录
[root@node200 mysql]# chkconfig --add mysqld
[root@node200 mysql]# service mysqld start
Starting MySQL... [确定]
[root@node200 mysql]# ss -tnl |grep 3306
LISTEN 0 50 *:3306 *:*
#做安全配置
[root@node200 mysql]# /usr/local/mysql/bin/mysql_secure_installation
[root@node200 mysql]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.42-MariaDB-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "%ssl%"
-> ;
+---------------+----------+
| Variable_name | Value |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
7 rows in set (0.06 sec)
添加到变量中
[root@node200 mysql]# vim /etc/profile.d/mysql.sh
[root@node200 mysql]# . /etc/profile.d/mysql.sh
[root@node200 mysql]# mysql
Your MariaDB connection id is 3
MariaDB [(none)]>
并发控制：
锁：
读锁：共享锁，阻止写操作
写锁：独占锁
锁粒度：
表级锁
行级锁，为了更多并发
锁策略：在锁粒度及数据安全性寻求的平衡机制；
每种存储引擎都可以自行实现其锁策略和锁粒度；
MySQL在服务器级也实现了锁，表级锁；用户可显式请求；
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
读锁
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> lock table students read;
Query OK, 0 rows affected (0.27 sec)
显示锁
MariaDB [hellodb]> insert students (Name,Age,Gender) values ('aaa',2,'F');
解锁
MariaDB [hellodb]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (1 min 14.13 sec)
写锁
MariaDB [hellodb]> lock table students write;
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | jingjiao king | 100 | F | NULL | 1 |
| 27 | yingjiao King | 98 | M | NULL | 2 |
| 28 | aaa | 2 | F | NULL | NULL |
![【可靠性评估】电力系统可靠性评估matlab仿真1.软件版本2.本算法理论知识[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)