MySQL
一、CentOS6二进制格式安装MariaDB
1、首先停止老版本的MySQL，并删除
[root@qq ~]# service mysqld stop
Stopping mysqld: [ OK ]
[root@qq ~]#
[root@qq ~]# rpm -e mysql-server
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
[root@qq ~]#
日志清除
[root@qq ~]# rm /var/log/mysqld.log.rpmsave
rm: remove regular file `/var/log/mysqld.log.rpmsave'? y
[root@qq ~]#
2、创建mysql系统组和mysql系统用户
[root@qq source-packet]# groupadd -r mysql
[root@qq source-packet]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
3、解压MariaDB目录到/usr/local
[root@qq source-packet]# tar xf mariadb-5.5.43-linux-x86_64.tar.gz -C /usr/local
[root@qq source-packet]# cd /usr/local/
[root@qq local]# ll
total 76
drwxr-xr-x. 12 root root 4096 Aug 4 13:10 mariadb-5.5.43-linux-x86_6
安装要求在/usr/local有mysql的连接
`mysql' -> `mariadb-5.5.43-linux-x86_64'
[root@qq local]# ll
total 76
drwxr-xr-x. 12 root root 4096 Aug 4 13:10 mariadb-5.5.43-linux-x86_64
lrwxrwxrwx. 1 root root 27 Aug 4 13:12 mysql -> mariadb-5.5.43-linux-x86_64
查看目录文件
[root@qq local]# cd mysql/
[root@qq mysql]# ll
total 220
drwxr-xr-x. 2 root root 4096 Aug 4 13:10 bin
-rw-r--r--. 1 qq qq 17987 Apr 30 2015 COPYING
-rw-r--r--. 1 qq qq 26545 Apr 30 2015 COPYING.LESSER
drwxr-xr-x. 3 root root 4096 Aug 4 13:10 data
-rw-r--r--. 1 qq qq 8245 Apr 30 2015 EXCEPTIONS-CLIENT
drwxr-xr-x. 3 root root 4096 Aug 4 13:10 include
-rw-r--r--. 1 qq qq 8694 Apr 30 2015 INSTALL-BINARY
drwxr-xr-x. 3 root root 4096 Aug 4 13:10 lib
drwxr-xr-x. 4 root root 4096 Aug 4 13:10 man
drwxr-xr-x. 11 root root 4096 Aug 4 13:10 mysql-test
-rw-r--r--. 1 qq qq 108813 Apr 30 2015 README
drwxr-xr-x. 2 root root 4096 Aug 4 13:10 scripts
drwxr-xr-x. 27 root root 4096 Aug 4 13:10 share
drwxr-xr-x. 4 root root 4096 Aug 4 13:10 sql-bench
drwxr-xr-x. 3 root root 4096 Aug 4 13:10 support-files
4、更改所有文件权限属主为root，属组为mysql
[root@qq mysql]# chown -R root:mysql ./*
[root@qq mysql]# ll
total 220
drwxr-xr-x. 2 root mysql 4096 Aug 4 13:10 bin
-rw-r--r--. 1 root mysql 17987 Apr 30 2015 COPYING
-rw-r--r--. 1 root mysql 26545 Apr 30 2015 COPYING.LESSER
drwxr-xr-x. 3 root mysql 4096 Aug 4 13:10 data
-rw-r--r--. 1 root mysql 8245 Apr 30 2015 EXCEPTIONS-CLIENT
drwxr-xr-x. 3 root mysql 4096 Aug 4 13:10 include
-rw-r--r--. 1 root mysql 8694 Apr 30 2015 INSTALL-BINARY
drwxr-xr-x. 3 root mysql 4096 Aug 4 13:10 lib
drwxr-xr-x. 4 root mysql 4096 Aug 4 13:10 man
drwxr-xr-x. 11 root mysql 4096 Aug 4 13:10 mysql-test
-rw-r--r--. 1 root mysql 108813 Apr 30 2015 README
drwxr-xr-x. 2 root mysql 4096 Aug 4 13:10 scripts
drwxr-xr-x. 27 root mysql 4096 Aug 4 13:10 share
drwxr-xr-x. 4 root mysql 4096 Aug 4 13:10 sql-bench
drwxr-xr-x. 3 root mysql 4096 Aug 4 13:10 support-files
[root@qq mysql]#
5、创建一个LVM逻辑卷保存MySQL数据
5.1 创建1个20G分区
5.1.1
fdisk /dev/sda
Command (m for help): n
First cylinder (18511-32636, default 18511): 24134
Last cylinder, +cylinders or +size{K,M,G} (24134-32636, default 32636): +20G
Command (m for help): p
Disk /dev/sda: 268.4 GB, 268435456000 bytes
255 heads, 63 sectors/track, 32635 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0005665b
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 6400 51200000 83 Linux
/dev/sda3 6400 11500 40960000 83 Linux
/dev/sda4 11500 32636 169778176 5 Extended
/dev/sda5 11500 15962 35840000 83 Linux
/dev/sda6 15962 18511 20480000 83 Linux
/dev/sda7 18512 20424 15360000 83 Linux
/dev/sda8 20424 22336 15360000 83 Linux
/dev/sda9 22336 23611 10240000 83 Linux
/dev/sda10 23611 24133 4194304 82 Linux swap / Solaris
/dev/sda11 24134 26745 20980890 83 Linux
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: Device or resource busy.
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@qq mysql]#
[root@qq mysql]# partx -a /dev/sda
BLKPG: Device or resource busy
error adding partition 1
BLKPG: Device or resource busy
error adding partition 2
BLKPG: Device or resource busy
error adding partition 3
BLKPG: Device or resource busy
error adding partition 4
BLKPG: Device or resource busy
error adding partition 5
BLKPG: Device or resource busy
error adding partition 6
BLKPG: Device or resource busy
error adding partition 7
BLKPG: Device or resource busy
error adding partition 8
BLKPG: Device or resource busy
error adding partition 9
BLKPG: Device or resource busy
error adding partition 10
[root@qq mysql]# partx -a /dev/sda
BLKPG: Device or resource busy
error adding partition 1
BLKPG: Device or resource busy
error adding partition 2
BLKPG: Device or resource busy
error adding partition 3
BLKPG: Device or resource busy
error adding partition 4
BLKPG: Device or resource busy
error adding partition 5
BLKPG: Device or resource busy
error adding partition 6
BLKPG: Device or resource busy
error adding partition 7
BLKPG: Device or resource busy
error adding partition 8
BLKPG: Device or resource busy
error adding partition 9
BLKPG: Device or resource busy
error adding partition 10
BLKPG: Device or resource busy
error adding partition 11
[root@qq mysql]#
5.1.2 改变文件类型
Command (m for help): t
Partition number (1-11): 11
Hex code (type L to list codes): 8e
Changed system type of partition 11 to 8e (Linux LVM)
Command (m for help): p
Disk /dev/sda: 268.4 GB, 268435456000 bytes
255 heads, 63 sectors/track, 32635 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0005665b
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 6400 51200000 83 Linux
/dev/sda3 6400 11500 40960000 83 Linux
/dev/sda4 11500 32636 169778176 5 Extended
/dev/sda5 11500 15962 35840000 83 Linux
/dev/sda6 15962 18511 20480000 83 Linux
/dev/sda7 18512 20424 15360000 83 Linux
/dev/sda8 20424 22336 15360000 83 Linux
/dev/sda9 22336 23611 10240000 83 Linux
/dev/sda10 23611 24133 4194304 82 Linux swap / Solaris
/dev/sda11 24134 26745 20980890 8e Linux LVM
Command (m for help): w
5.2 创建LVM文件系统
5.2.1 创建PV
[root@qq mysql]# pvcreate /dev/sda11
Physical volume "/dev/sda11" successfully created
[root@qq mysql]# pvs
PV VG Fmt Attr PSize PFree
/dev/sda11 lvm2 --- 20.01g 20.01g
[root@qq mysql]#
5.2.2 以/dev/sda11创建一个名为myvg的vg，
[root@qq mysql]# vgcreate myvg /dev/sda11
Volume group "myvg" successfully created
[root@qq mysql]# vgs
VG #PV #LV #SN Attr VSize VFree
myvg 1 0 0 wz--n- 20.01g 20.01g
[root@qq mysql]#
5.2.3 创建真正能存放数据的LV
[root@qq mysql]# lvcreate -L 10G -n mydata myvg #10G大小，名字为mydata，基于名叫myvg的VG创建
Logical volume "mydata" created
[root@qq mysql]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
mydata myvg -wi-a----- 10.00g
[root@qq mysql]#
5.3 格式化lv为xfs文件系统
5.3.1 安装xfs的rpm包
[root@qq ~]# yum install -y xfsprogs
5.3.2 查看内核中的xfs模块信息
[root@qq ~]# modprobe xfs
[root@qq ~]# modinfo xfs
filename: /lib/modules/2.6.32-504.el6.x86_64/kernel/fs/xfs/xfs.ko
license: GPL
description: SGI XFS with ACLs, security attributes, large block/inode numbers, no debug enabled
author: Silicon Graphics, Inc.
srcversion: 4392D4D583B9D2781E4F61E
depends: exportfs
vermagic: 2.6.32-504.el6.x86_64 SMP mod_unload modversions
5.3.3 格式为xfs文件系统
[root@qq mysql]# mkfs.xfs /dev/myvg/mydata
meta-data=/dev/myvg/mydata isize=256 agcount=4, agsize=655360 blks
= sectsz=512 attr=2, projid32bit=0
data = bsize=4096 blocks=2621440, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@qq mysql]#
[root@qq mysql]# blkid /dev/myvg/mydata
/dev/myvg/mydata: UUID="82842935-2d9c-413b-83cc-8040a1542ded" TYPE="xfs"
[root@qq mysql]#
5.3.4 开机挂载改xfs分区
5.3.4.1 创建挂载目录
[root@qq mysql]# mkdir /mydata
5.3.4.2 编辑/etc/fstab文件挂载xfs分区；
[root@qq mysql]# vim /etc/fstab
/dev/myvg/mydata /mydata xfs defaults 0 0
5.3.4.3 使用mount -a挂载
[root@qq mysql]# mount -a
[root@qq mysql]# df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/myvg-mydata
10G 33M 10G 1% /mydata
[root@qq mysql]#
[root@qq mysql]# mount
……
/dev/mapper/myvg-mydata on /mydata type xfs (rw)
[root@qq mysql]#
5.3.4.3 在/mydata分区创建一个目录专门存放mysql的数据，并将属主属组都改为mysql；因为运行mysql数据库的是mysql用户，所以它需要对该目录有读写执行权限
[root@qq mysql]# cd /mydata/
[root@qq mydata]# mkdir data
[root@qq mydata]# chown mysql.mysql data
[root@qq mydata]# ll
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 4 13:47 data
[root@qq mydata]#
6、初始化配置
6.1 安装默认的数据库，可使用文件 --help查看所有选项
通过mysql_install_db安装几个默认的数据库，并指明--user用户未mysql，数据库安装目录是/mydata/data
[root@qq mysql]# pwd
/usr/local/mysql
[root@qq mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
Installing MariaDB/MySQL system tables in '/mydata/data/' ...
160804 15:14:08 [Note] ./bin/mysqld (mysqld 5.5.43-MariaDB) starting as process 26114 ...
OK
Filling help tables...
160804 15:14:10 [Note] ./bin/mysqld (mysqld 5.5.43-MariaDB) starting as process 26124 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'./bin/mysqladmin' -u root password 'new-password'
'./bin/mysqladmin' -u root -h qq password 'new-password'
Alternatively you can run:
'./bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '.' ; ./bin/mysqld_safe --datadir='/mydata/data/'
You can test the MariaDB daemon with mysql-test-run.pl
cd './mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from MariaDB
Corporation Ab. You can contact us about this at sales@mariadb.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
查看安装目录，对于MySQL而言数据库就是目录，目录和数据库是映射关系；
[root@qq mysql]# ls /mydata/data/
aria_log.00000001 aria_log_control mysql performance_schema test
[root@qq mysql]#
6.2 复制support-files目下的mysql.server的脚本，重名名为mysqld到/etc/rc.d/init.d下作为服务脚本
6.2.1 复制并重命名
[root@qq mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
6.2.2 把脚本通过chkconfig命令加入服务
[root@qq mysql]# chkconfig --add mysqld
[root@qq mysql]#
[root@qq mysql]# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@qq mysql]#
7、限定MySQL配置文件的生效范围
ps MySQL配置文件解读
[root@qq support-files]# ll
total 80
-rwxr-xr-x. 1 root mysql 1153 Apr 30 2015 binary-configure
-rw-r--r--. 1 root mysql 1328 Apr 30 2015 magic
-rw-r--r--. 1 root mysql 4914 Apr 30 2015 my-huge.cnf
-rw-r--r--. 1 root mysql 20418 Apr 30 2015 my-innodb-heavy-4G.cnf #4G内存
-rw-r--r--. 1 root mysql 4901 Apr 30 2015 my-large.cnf #512M内存
-rw-r--r--. 1 root mysql 4914 Apr 30 2015 my-medium.cnf #256M内存
-rw-r--r--. 1 root mysql 2840 Apr 30 2015 my-small.cnf #64M内存
-rwxr-xr-x. 1 root mysql 1061 Apr 30 2015 mysqld_multi.server
-rwxr-xr-x. 1 root mysql 839 Apr 30 2015 mysql-log-rotate
-rwxr-xr-x. 1 root mysql 12196 Apr 30 2015 mysql.server
drwxr-xr-x. 3 root mysql 4096 Aug 4 13:10 SELinux
[root@qq support-files]#
7.1 创建目录
[root@qq mysql]# mkdir /etc/mysql
[root@qq mysql]#
7.2 复制support-file目下的配置文件到/etc/mysql并命名为my.cnf
[root@qq mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf
[root@qq mysql]# ls /etc/mysql/
my.cnf
[root@qq mysql]#
8、调整MySQL配置文件
[root@qq ~]# vim /etc/mysql/my.cnf
# The MariaDB server
[mysqld]
datadir = /mydata/data
innodb_file_per_table = on
skip_name_resolve = on
9、启动测试
[root@qq ~]# service mysqld start
Starting MySQL........ [ OK ]
[root@qq ~]# ss -tnl | grep :3306
LISTEN 0 50 *:3306 *:*
[root@qq ~]#
[root@qq mysql]# ps aux | grep mysql
root 27412 0.0 0.2 11472 1380 pts/1 S 22:44 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/qq.pid
二、MariaDB
查看用户表
mysql> use mysql
Database changed
mysql> SELECT User,Host,Password FROM user;
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | qq | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | qq | |
+------+-----------+----------+
6 rows in set (0.02 sec)
安全加固（删除匿名用户，设置root密码）
[root@qq bin]# cd /usr/local/mysql/bin/n
[root@qq bin]# ./mysql_secure_installation
./mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n #是否禁止管理员远程登陆，生成环境应该禁止；
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] n
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
使用root账号密码连接测试
[root@qq bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.43-MariaDB-log MariaDB Server
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> use mysql
Database changed
mysql> SELECT User,Host,Password FROM user;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| root | qq | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| root | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| root | ::1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
三、MariaDB使用什锦
查看数据库版本
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 20
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.43-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 10 hours 8 min 58 sec
Threads: 1 Questions: 37 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.001
--------------
mysql>
查看可执行的客户端命令
mysql>
mysql> help
For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
服务端命令查看版本
mysql> SELECT VERSION();
+--------------------+
| VERSION() |
+--------------------+
| 5.5.43-MariaDB-log |
+--------------------+
1 row in set (0.06 sec)
mysql>
简单运算
mysql> SELECT 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
mysql>
命令使用帮助
mysql> help CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html
mysql>
查看mysql支持的字符集
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
查看所有支持的引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
mysql>
创建数据库
mysql> CREATE DATABASE IF NOT EXISTS testdb;
Query OK, 1 row affected (0.34 sec)
使用默认数据库，创建表格
mysql> use testdb
Database changed
mysql> CREATE TABLE tbl1 (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.34 sec)
mysql>
查看所有表
mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| tbl1 |
+------------------+
1 row in set (0.00 sec)
mysql>
查看表结构
mysql> DESC tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.14 sec)
mysql>
