linux第九周总结-wx5c8232723cb2c的博客-51CTO博客，

文章由LinuxBoy分享于2019-06-13 06:06:23热评（485）

linux第九周总结-wx5c8232723cb2c的博客-51CTO博客，

linux学习第九周总结

本周的内容重中之重，数据库MySQL，

一、简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。

应用环境

与其他的大型数据库例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本

系统特性编辑

二、安装方式

1.RPM安装
    RPM包安装
    CentOS 7：安装光盘直接提供
        mariadb-server   服务器包
        mariadb          客户端工具包
CentOS 6
提高安全性
    mysql_secure_installation
        设置数据库管理员root口令
        禁止root远程登录
        删除anonymous用户帐号
        删除test数据库
客户端程序：
    mysql: 交互式的CLI工具
    mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
    mysqladmin：基于mysql协议管理mysqld
    mysqlimport：数据导入工具
MyISAM存储引擎的管理工具：
    myisamchk：检查MyISAM库
    myisampack：打包MyISAM表，只读
服务器端程序
    mysqld_safe
    mysqld
    mysqld_multi 多实例 ，示例：mysqld_multi --example
mysql用户账号由两部分组成：
    'USERNAME'@'HOST‘
说明：
    HOST限制此用户可通过哪些远程主机连接mysql服务器
        支持使用通配符：
            % 匹配任意长度的任意字符
            172.16.0.0/255.255.0.0 或 172.16.%.%
            _ 匹配任意单个字符
服务器监听的两种socket地址：
    ip socket: 监听在tcp的3306端口，支持远程通信
    unix sock: 监听在sock文件上，仅支持本机通信
        如：/var/lib/mysql/mysql.sock)
    说明：host为localhost,127.0.0.1时自动使用unix sock
侦听3306/tcp端口可以在绑定有一个或全部接口IP上
vim /etc/my.cnf
    [mysqld]
    skip-networking=1
关闭网络连接，只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改

2.二进制安装

二进制格式安装过程
(1) 准备用户
    groupadd -r -g 306 mysql
    useradd -r -g 306 -u 306 –d /data/mysql mysql
(2) 准备数据目录，建议使用逻辑卷
    mkdir /data/mysql
    chown mysql:mysql /data/mysql
(3) 准备二进制程序
    tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local
    cd /usr/local
    ln -sv mariadb-VERSION mysql
    chown -R root:mysql /usr/local/mysql/
(5)创建数据库文件
    cd /usr/local/mysql/
    ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
(6)准备服务脚本，并启动服务
    cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
    chkconfig --add mysqld
    service mysqld start
(7)PATH路径
    echo ‘PATH=/user/local/mysql/bin:$PATH’ > /etc/profile.d/mysql
(8)安全初始化
    /user/local/mysql/bin/mysql_secure_installation

3.编译安装

安装包
yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel
做准备用户和数据目录
    useradd -r -s /sbin/nologin -d /data/mysql/ mysql
    mkdir /data/mysql
    chown mysql.mysql /data/mysql
    tar xvf mariadb-10.2.18.tar.gz
cmake 编译安装
cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译
编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

cd mariadb-10.2.18/
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/mysql \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
提示：如果出错，执行rm -f CMakeCache.txt
准备环境变量
    echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
    . /etc/profile.d/mysql.sh
生成数据库文件
    cd /app/mysql/
    scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
准备配置文件
    cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
准备启动脚本
    cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
启动服务
    chkconfig --add mysqld ;service mysqld start

三、管理数据库和表

关系型数据库的常见组件

数据库操作

表

创建表：CREATE TABLE

字段信息

查看所有的引擎：SHOW ENGINES

四、数据类型：

数据长什么样数值类型日期/时间类型字符串(字符)类型 https://dev.mysql.com/doc/refman/5.5/en/data-types.html 1、整型

2、浮点型(float和double)，近似值

4、字符串(char,varchar,_text)

5.二进制数据：BLOB

6.日期时间类型

示例

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

DROP TABLE [IF EXISTS] 'tbl_name';
ALTER TABLE 'tbl_name'
字段：
    添加字段：add
        ADD col1 data_type [FIRST|AFTER col_name]
    删除字段：drop
    修改字段：
        alter（默认值）, change（字段名）, modify（字段属性）
索引:
    添加索引：add index
    删除索引：drop index
表选项
修改:
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助：Help ALTER TABLE

修改表示例

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
Help ALTER TABLE 查看

多表查询

交叉连接：笛卡尔乘积

用于FROM子句中的子查询

五、函数，存储过程和触发器

1.函数：系统函数和自定义函数

DELIMITER //
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = uid;
RETURN (SELECT COUNT(stuid) FROM students);
END//
DELIMITER ;

2.存储过程

delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;
CALL showTime

创建含参存储过程：只有一个IN参数

delimiter //
CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE stuid = uid;
END//
delimiter ;
call selectById(2);

示例

delimiter //
CREATE PROCEDURE dorepeat(n INT)
BEGIN
SET @i = 0;
SET @sum = 0;
REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
UNTIL @i > n END REPEAT;
END//
delimiter ;
CALL dorepeat(100);
SELECT @sum;

创建含参存储过程:包含IN参数和OUT参数

delimiter //
CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= uid;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(2,@Line);
SELECT @Line;

说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

3.流程控制

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

说明：

触发器示例

CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);

示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少

CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

查看触发器

六、mysql用户和权限管理

创建用户：CREATE USER

七、存储引擎

MyISAM引擎特点

InnoDB数据库文件

所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON
参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
ON (>= MariaDB 5.5)
两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

其它存储引擎

MySQL中的系统数据库

mysqld选项，服务器系统变量和服务器状态变量

服务器配置

八、mysql架构

查询缓存

哪些查询可能不会被缓存

查询缓存相关的服务器变量

SELECT语句的缓存控制

命中率和内存使用率估算 100% 100%

InnoDB存储引擎

索引

索引类型：

B+TREE索引

B+Tree索引的限制：

索引优化策略：

索引优化建议

SQL语句性能优化就不用，尽量写全字段名

管理索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;

删除索引：

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

并发控制

锁策略：在锁粒度及数据安全性寻求的平衡机制
显式使用锁
    LOCK TABLES 加锁
        tbl_name [[AS] alias] lock_type
        [, tbl_name [[AS] alias] lock_type] ...
        lock_type: READ ， WRITE
        UNLOCK TABLES 解锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
    关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
    查询时加写或读锁

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
    A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
    C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
    I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
    D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

启动事务：

事务隔离级别

并发控制还有日志管理，备份还原，mysql集群在下周总结

推荐文章：

linux第九周总结-wx5c8232723cb2c的博客-51CTO博客，