DBMS出现之前数据管理的缺陷：
数据冗余：同样数据出现多次，冗余量太大
数据不一致性：不在同一位置的相同文件需要逐个修改
数据访问困难：没有强大的索引很难检索到所需要的数据
数据孤立性：数据定义的格式没有规范，不方便分类访问
完整性问题：多用户同时修改数据时，数据完整性得不到保障
原子性问题：操作数据后，数据的状态必须从一个稳定的状态转换到另一个稳定的状态
并发访问异常：多用户同时访问同一数据，文件有锁机制，无法并发
安全性问题：无法对用户实现文件部分授权
关系型数据库设计至少遵循三个范式：
1.每个表中的任何一列的每一个字段都是原子性的。
2.每个表应该有一个主键(有限个字段)标示此行的唯一性（每一行数据都是唯一的）。避免冗余
3.多个表之间依照主键建立起依赖关系(一个表的外键关联于另一个表的主键)，实现将一个大表拆成多个小表，此时用到跨表查询，需要连接两张表进行查询}
关系型数据库基本概念：
约束：constraint，向数据表提供的数据要遵守的限制； 主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT NULL;一个表只能存在一个 惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL；一个表可以存在多个 外键：一个表中的某字段可填入数据取决于另一个表的主键已有的数据； 检查性约束(check)：定义表达式检测数据是否合规，mysql尚不支持
关系型数据库的常见组件：
数据库：database 表：table 行：row 列：column 索引：将表中的一个或者多个字段中的数据复制一份另存，并且此些需要按照特定次序排序存储。index 可以加速查询，影响写入操作 视图：view虚表，一个select语句得到的结果保存为虚表，可以对这个虚表继续执行查询操作 用户：user 权限：privilege 存储过程：procedure 无返回值 存储函数：function 有返回值 触发器：trigger 监视器发现某数据改变了可以触发执行一段代码 事件调度器：event scheduler 类似于linux下crontab
支持面向过程编程：支持选择，循环
关系运算：
选择：挑选符合条件的行，单表查询时用到
投影：挑选符合条件的字段(列)，单表查询时用到
自然连接：具有相同名字的属性上所有取值相同的行；多表查询时用到
笛卡尔积：(a+b)*(c+d)=ac+ad+bc+bd；多表查询时用到
并集合运算；多表查询时用到
安装和使用MariaDB
CentOS 7直接提供；
下面介绍CentOS 6通用二进制格式安装过程
(1) 准备数据目录
以/mydata/data为例；
创建LVM使数据库使用磁盘具有扩展性：
开机自动挂载
(2) 配置mariadb
# groupadd -r -g 306 mysql
# useradd -r -g 306 -u 306 mysql
# tar xf mariadb-VERSION.tar.xz -C /usr/local
# ln -sv
# cd /usr/local/mysql
# chown -R root:mysql ./*
# scripts/mysql_install_db --datadir=/mydata/data --user=mysql
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
(3) 准备配置文件
配置格式：类ini格式，为各程序均通过单个配置文件提供配置信息；
[prog_name]
配置文件查找次序：
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE指令指明的路径 --> ~/.my.cnf 优先级次序由后向前
# mkdir /etc/mysql
# cp support-files/my-large.cnf /etc/mysql/my.cnf
编辑my.cnf文件
添加三个选项：
datadir = /mydata/data #指定数据存放目录
innodb_file_per_table = on #开启innodb
skip_name_resolve = on #只根据ip做授权，不反解主机名
安装后的设定：
(1) 为所有root用户设定密码；
mysql> SET PASSWORD
mysql> update mysql.user root SET password=PASSWORD('your_pass') WHERE cluase;需要重读授权表
# mysqlamin
修改用户密码的几种方式：
1.mysqladmin -uroot password ’new-password’ -p
2.mysqladmin -uroot -h this_host_name password ’new-password’
3.mysql>SET PASSWORD FOR ’root’@’host_name’ =PASSWORD(’new_password’);
4.mysql>UPDATE mysql.user SET Password = PASSWORD(’new_password’) WHERE User = ’root’ and Host=‘mysql.a.org’;
mysql>FLUSH PRIVILEGES;
(2) 删除所有匿名用户
mysql> DROP USER ''@'localhost';
上述两步骤可运行命令：mysql_secure_installation
(3) 建议关闭主机名反解功能；
centos6编译安装MariaDB
准备编译环境：
yum install cmake
yum groupinstall "Development Tools" "Server Platform Development"
创建用户：
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 mysql
mkdir /data/mydata
tar xf mariadb-5.5.44.tar.gz
cd mariadb-5.5.44
准备数据目录：
准备一个新分区作lvm提供数据存储：
[root@testserver ~]# 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): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4):
Value out of range.
Partition number (1-4): 3
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
Partition number (1-4): 3
Hex code (type L to list codes): 8e
Changed system type of partition 3 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
做LVM：
[root@testserver ~]# 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
[root@testserver ~]# pvcreate /dev/sda3
Physical volume "/dev/sda3" successfully created
[root@testserver ~]# vgcreate myvg /dev/sda3
Volume group "myvg" successfully created
[root@testserver ~]# lvcreate -L 10G -n mydata myvg
Logical volume "mydata" created
[root@testserver ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
mydata myvg -wi-a----- 10.00g
root vg0 -wi-ao---- 20.00g
swap vg0 -wi-ao---- 2.00g
usr vg0 -wi-ao---- 10.00g
var vg0 -wi-ao---- 20.00g
格式化：
mke2fs -t ext4 /dev/myvg/mydata
开机自动挂载：
mkdir /mydata/
在fstab文件中添加/dev/myvg/mydata /mydata ext4 defaults 0 0
mount -a
mkdir /mydata/data
chown mysql.mysql /mydata/data/ -R
执行cmake：
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
make
make indtall
chown -R root.mysql ./*
ln -sv mariadb-5.5.44 mysql
cd mysql
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/mydata/data/
[root@testserver mariadb-5.5.44]# mkdir /etc/mysql
[root@testserver mariadb-5.5.44]# cp support-files/my-large.cnf /etc/mysql/my.cnf
编辑my.cnf：在mysqld段加如下内容
datadir=/mydata/data
innodb_file_per_table = ON
skip_name_resolve = ON
[root@testserver mariadb-5.5.44]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@testserver mariadb-5.5.44]# service mysqld start
env: /etc/init.d/mysqld: Permission denied
[root@testserver mariadb-5.5.44]# chmod +x /etc/rc.d/init.d/mysqld
[root@testserver mariadb-5.5.44]# chkconfig --add mysqld
[root@testserver mariadb-5.5.44]# service mysqld start
Starting MySQL.... [ OK ]
运行初始化安全脚本：
/usr/local/mysql/bin/mysql_secure_installation
在.bash_profile中添加：
PATH=/usr/local/mysql/bin:$PATH:$HOME/bin
export PATH
运行客户端登录：
[root@testserver ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-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的程序组成：
mysql --> mysqld
客户端程序：
mysql: 交互式的CLI工具；
mysqldump: 备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中；
mysqladmin：基于mysql协议管理mysqld；
mysqlimport: 数据导入工具；
非客户端类的管理工具：只能工作在mysqld安装的主机上，不能远程管理
myisamchk, myisampack
如何获取程序默认使用的配置：
mysql --print-defaults
mysqld --print-defaults
客户端类应用程序的可用选项：
-u, --user= 用户
-h, --host= 主机
-p, --passowrd= 密码
-P, --port= 端口
--protocol= 协议
-S, --socket= 裸套接字文件位置，客户端和服务器端在同一主机时用socket不在同一主机使用3306端口
-D, --database= 数据库
-C, --compress 连接服务器端时是否压缩传输中的数据
mysql -e "SQL" 不连入mysqld，只是执行一个mysql语句
[root@node1 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
交互式模式：
可运行命令有两类：
客户端命令：\h, help \s，status
服务器端命令：SQL, 需要语句结束符；
脚本模式导入数据库：
mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql
mysql交互式模式下导入数据库：
mysql> source /path/from/somefile.sql
服务器端(mysqld，mysqld_safe,mysqld_multi)：工作特性有多种定义方式
命令行选项
配置文件参数
获取可用参数列表：
mysqld --help --verbose
获取运行中的mysql进程使用各服务器参数及其值：
mysql> SHOW GLOBAL VARIABLES; 全局有效，但对当前不生效
mysql> SHOW [SESSION] VARIABLES;对当前会话连接线程有效
注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；
有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独的设置；
修改服务器变量的值：
mysql> help SET
全局级别(全局有效)：
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
会话级别(当前会话有效)：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;
状态变量：用于保存mysqld运行中的统计数据的变量；
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;
SQL MODE：定义mysqld对约束等的响应行为；
修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
需要修改权限；仅对修改后新创建的会话有效；对已经建立的会话无效；
mysql> SET SESSION sql_mode='MODE';
mysql> SET @@session.sql_mode='MODE';
常用MODE：
TRADITIONAL：不允许对非法值做插入操作,
STRICT_TRANS_TABLES：对所有支持事物类型的表做严格约束,
STRICT_ALL_TABLES：对所有表做严格约束。
示例：
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
创建一个表
MariaDB [mydb]> CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name CHAR(5) NOT NULL);
插入数据：（故意插入一个较长的数据，大小大于CHAR(5)，会出现warning，并且插入的数据不完整）
MariaDB [mydb]> INSERT INTO t1 (name) VALUES ('tom'),('BlackBerry');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
MariaDB [mydb]> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 2 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [mydb]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | Blake |
+----+-------+
2 rows in set (0.00 sec)
改变mode为TRADITIONAL再插入：发现无法插入并提示插入数据太长
MariaDB [mydb]> INSERT INTO t1 (name) VALUES ('WhiteBerry');
ERROR 1406 (22001): Data too long for column 'name' at row 1mysql服务器监听的两种socket地址：
ip socket: 监听在tcp的3306端口，支持远程通信；
unix sock: 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock)，仅支持本地通信
查看系统信息：
MariaDB [mysql]> status
--------------
mysql Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 2
Current database: mysql
Current user: root@localhost
SSL: Notin use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.41-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhostvia UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2min 24 sec
Threads: 1 Questions: 34 Slow queries: 0 Opens: 15 Flush tables: 2 Open tables: 41 Queries per second avg: 0.236
#使用help获取帮助
MariaDB [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 thegiven name. To use this
statement, you need the CREATE privilegefor the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html
#查看存储引擎
MariaDB [mysql]> SHOW ENGINES\G;
*************************** 1. row***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, andforeign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row***************************
Engine: FEDERATED
Support: YES
Comment: FederatedX pluggable storage engine
Transactions: YES
XA: NO
Savepoints: YES
*************************** 10. row***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)
ERROR: No query specified
MariaDB [hellodb]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| 2 | root | localhost | hellodb | Sleep | 3887 | | NULL | 0.000 |
| 3 | root | localhost | hellodb | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)
#使用某个数据库
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#查看user表中的User，Host，Password三段内容
MariaDB [mysql]> SELECT User,Host,Password FROM user;
+--------+-----------------------+-------------------------------------------+
| User | Host | Password |
+--------+-----------------------+-------------------------------------------+
| root | localhost | *886308E9B2704A1DDD9D01897AE483DDCA88A218 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | *886308E9B2704A1DDD9D01897AE483DDCA88A218 |
| root | ::1 | |
| | localhost | #发现有两个匿名用户，为了安全应删除 |
| | localhost.localdomain | |
| wpuser | % | *FCE7BD77D7A85E580808A8B117943CB7DFE021FB |
+--------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
GRANT命令创建远程连接mysql授权用户test
mysql -u root -p
mysql>GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test' WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON *.* TO test@"%" IDENTIFIED BY 'test' WITH GRANT OPTION;
