一、安装以及初始化
软件包来源
1. vendor, rpm
2. 官网，source code 编译
3. 官网， 二进制包
二进制包安装过程
1. 下载二进制包，解压缩到
# tar -xf mariadb-5.5.46-linux-x86_64.tar.gz -C /usr/local/
2. 创建链接
# ln -sv mariadb-5.5.46-linux-x86_64.tar.gz mysql
3. 库导出， 在ld.so.conf.d 下面穿件文件，写有/usr/local/mysql/lib， 然后重新导入库
# idconfig -v
4. 二进制文件导出， 在/etc/profile.d/下面创建一个写有二进制目录的文件即可
5. 头文件导出， 把/usr/local/mysql/include/mysql 链接至/usr/local/include 下面
初始化数据库， 运行安装目录，/usr/local/mysql/scripts 下的mysql_install_db
1. mysql_install_db: 下面三个选项通常要指定
--basedir : 安装根目录 --datadir : 数据库存放目录 --user : 服务器进程用户, 要提前创建用来运行mysqld服务的系统用户
2. 预备服务脚本： 如果是用二进制文件安装，在support-files 目录下可以找到
# cp usr/local/mysql/support_files/mysql.server /etc/rc.d/init.d/mysqld
修改其中一下变量： basedir = datadir =
3. 预备配置文件
# cp /usr/local/mysql/support_files/my_huge.cnf /etc/
4. 配置文件默认读取顺序
/etc/mysql/my.cnf –> /etc/my.cnf –> ~/my.cnf
生效规则：
1） 不同变量结果取所有文件的并集
2） 同一变量去最后一个配置文件的取值生效（~/my.cnf）
启动服务后删除匿名用户
mysql> DROP user ""@"localhost"; mysql> DROP user ""@"127.0.0.1"; mysql> DROP user ""$"::1";
设置管理员账户和密码
第一种方式
mysql> SET PASSWORD FOR "root"@"localhost" = PASSWORD("root")
第二种方式
mysql> USE user;
mysql> UPDATE user SET password = PASSWORD('root') WHERE User = "root";
mysql> FLUSH PRIVILEGES;
第三种， 使用mysqladmin 命令
添加远程管理账号；
mysql> GRANT ALL on *.* TO 'root'@'192.168.98.129' IDENTIFIED BY "rootpass";
二、数据库客户端连接命令
mysql: 常用选项如下
-u 用户名 -h 服务器主机名或IP -p 用户密码 --port 端口号 --socket 套接字文件所在目录
常用服务器端命令以及快捷键：
CLEAR \c : 取消当前行 HELP \h: 帮助文档 SYSTEM \!: 执行系统shell命令 QUIT\q : 退出 STATUS\s : 返回状态 EGO \G : 列显示结果 SOURCE \. : 读取sql 脚本 CHARSET \C : 修改字符集
mysql shell 的组合快捷键
ctrl + u: 删除光标之前的所有内容 ctrl + w: 删除光标之前的一个单词 ctrl + y: 粘贴使用上面两个命令删除的内容 ctrl + a: 跳到行首 ctrl + e: 跳到行尾
远程控制命令mysqladmin
mysqladmin [OPTIONS] command command
command: create DB_NAME drop DB_NAME debug:打开调试日志并记录于error log中； status：显示简要状态信息 --sleep #: 间隔时长 --count #: 显示的批次 extended-status:输出mysqld的各状态变量及其值，相当于执行“mysql> SHOW GLOBAL STATUS” variables:输出mysqld的各服务器变量 flush-hosts:清空主机相关的缓存：DNS解析缓存，此前因为连接错误次数过多而被拒绝访问mysqld的主机列表 flush-logs:日志滚动，二进制日志和中继日志 refresh:相当于同时使用flush-logs和flush-hosts flush-privileges: ：载入授权 reload:功能同flush-privileges flush-status:重置状态变量的值 flush-tables:关闭当前打开的表文件句柄 flush-threads：清空线程缓存 kill:杀死指定的线程，可以一次杀死多个线程，以逗号分隔，但不能有多余空格 password:修改当前用户的密码； ping processlist：显示mysql线程列表 shutdown:关闭mysqld进程； start-slave :启动从服务器 stop-slave: :关闭从服务器 mysql数据类型
三、数据类型相关
数据类型
1. 数值型
TINYINT SMALINT MEDIUMINT INT BIGINT DECIMAL FLOAT DOUBAL BIT
2. 字符型
CHAR VARCHAR TINYTEXT MEDIUMTEXT TEXT LONGTEXT
3. 二进制型
BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB ENUM SET
4.日期时间型
DATE TIME DATETIME TIMESTAMP YEAR
数据类型修饰符
1. CHAR, VARCHAR, TEXT 常用修饰
NOT NULL NULL DEFAULT 'strings' CHARACTER SET 'charset' mysql> SHOW VARIABLES LIKE '%CHAR%' ; ## 显示当前字符集 COLLATION '某排序规则' mysql> SHOW COLLATION ; ## 显示所有排序规则
2. BINARY, TEXT, BLOB 常用修饰
NOT NULL NULL DEFAULT, 不适用与BLOB
3. 整型类：
AUTO_INCREMENT UNSIGNED NOT NULL NULL DEFAULT
4. 浮点类
UNSIGNED NOT NULL NULL DEFAULT
5.时间日期类
NOT NULL NULL DEFAULT
7. ENUM , SET
NOT NULL NULL DEFAULT
四、mysql内置变量查询和修改
变量类型：
1. GLOBAL: 全局变量,当前会话中修改以后不生效，只有开启新会话才生效
2. SESSION: 只在当前会话中生效
无论以上哪种，在重启服务后都消失，若要永久生效，需要写入配置文件的服务器段
3. STATUS VARIABLES: 状态变量，无法修改，但是记录的服务器各个统计状态参数
变量查看方式：
mysql> SHOW [SESSION|GLOBAL|STATUS] LIKE "%engine%"; mysql> SELECT @@GLOBAL.variable_name ; mysql> SELECT @@SESSION.variable_name ; mysql> SELECT * FROM INFORMATION_SHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE "PATTEN" ; mysql> SELECT * FROM INFORMATION_SHEMA.SESSION_VARIABLES WHERE SESSION_VARIABLES LIKE "PATTEN" ;
修改变量：
1. 临时修改：
mysql> SET [GLOBAL|SESSION] VARIABLE_NAME == "value" GOLBAL: 修改后需要开启新的会话才能生效 SESSION: 只在当前会话生效
2.永久修改： 编辑配置文件的server段
五、数据库增删查改基本操作
数据库创建删除修改
mysql> CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name ;
mysql> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
mysql> ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']
表创建删除
1. 直接创建表：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [create_specification] create_definition
2.复制已经存在表的表结构：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] LIKE old_tbl_name
3. 复制表数据：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] [create_definition] select_statement
create_definition:
1) 字符集，排序规则
CHARACTER SET [=] charset_name | COLLATE [=] collation_name
2) column_definition:
表修改
UPDATE： UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=val1 [, col_name2=val2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
UPDATE通常情况下，必须要使用WHERE子句，或者使用LIMIT限制要修改的行数；
– -safe-updates 可以只修改符合条件的第一个
删除行
DELETE: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
清空表
TRUNCATE tb_name ： 用于清空表，但是保留表结构
MySQL SQL_MODE: SQL模式
通过修改全局变量修改 TRADITIONAL： STRICT_TRANS_TABLES： 仅对支持事物的表使用严格模式 STRICT_ALL_TABLES： 对于所有表使用严格模式 严格模式： 如果插入的数据超出表定义的范围，对插入内容进行截短，使其满足字段定义时的属性
表属性修改
ALTER TABLE tb_name
CHANGE [column_name] old_name new_name col_defination [FIRST AFTER column_name]
MODIFY [column_name] col_name column_definition [FIRST AFTER column_name]
ADD [COLUMN] col_name column_defination [FIRST AFTER col_defination]
ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option
当然这个命令其他字命令很多
mysql的基本查询：
1. 选择语句基本格式；
投影： SELECT col_name1,col_name2 …. FROM tb_name;
选择： SELECT * FROM tb_name WHERE clause
WHERE clasue:
2. 布尔表达式：
< > =[=] >= <= IS NULL IS NOT NULL LIKE: 支持的通配符: %(任意长度的任意字符)， _（任意单个字符） RLIKE,REGEXP: 支持使用正则表达式 IN BETWEEN ... AND ..... : 在某一范围内
组合条件测试：
NOT ! AND && OR ||
统计函数：
SUM(), AVG(), MAX(), MIN(), COUNT()
排序
ORDER BY col_name [DESC] DESC : 是否倒叙
SELECT语句的执行流程：
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
SELECT语句的一些选项:
DISTINCT：指定的结果相同的只显示一次； SQL_CACHE：缓存于查询缓存中； SQL_NO_CACHE：不缓存查询结果；
举例导入hellodb.sql，以下操作在students表上执行
1、以ClassID分组，显示每班的同学的人数； mysql>SELECT ClassID,COUNT(*) FROM students GROUP BY ClassID; 2、以Gender分组，显示其年龄之和； mysql>SELECT Gender,SUM(Age) FROM students GROUP BY Gender; 3、以ClassID分组，显示其平均年龄大于25的班级； mysql>SELECT ClassID FROM students GROUP BY ClassID HAVING AVG(Age) >25; 4、以Gender分组，显示各组中年龄大于25的学员的年龄之和； mysql>SELECT Gender, SUM(Age) FROM students WHERE Age > 20 Group by Gender ;
mysql多表查询
1. 左外联结：只保留出现在左外连接运算之前（左边）的关系中的元组；
left_tb LEFT JOIN right_tb ON 连接条件
2. 右外联结：只保留出现在右外连接运算之后（右边）的关系中的元组；
left_tb RIGHT JOIN right_tb ON 连接条件
3. 别名：表别名
字段别名 使用AS 关键字定义
举例 ：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩； mysql>SELECT Name,Course FROM (SELECT Name, CourseID, Score FROM ( students AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID) WHERE Score IS NOT NULL ORDER BY Score DESC ) AS ts LEFT JOIN courses AS cs ON ts.CourseID = cs.CourseID LIMIT 5 2、显示其成绩高于80的同学的名称及课程； mysql>SELECT Name,Course FROM (SELECT StuID, Course FROM scores AS sc LEFT JOIN courses AS cs ON sc.CourseID = cs.CourseID WHERE Score > 80) AS cc LEFT JOIN students as st ON cc.StuID = st.StuID ; 3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列； mysql>SELECT Name,AVG(Score) FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID GROUP BY Name ORDER BY AVG(Score) DESC ; 4、显示每门课程课程名称及学习了这门课的同学的个数； mysql>SELECT Course,COUNT(Name) FROM (SELECT Name,CourseID FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID ) tp1 LEFT JOIN courses AS cs ON tp1.CourseID = cs.CourseID GROUP BY Name ;
MySQL的联合查询
SELECT clauase UNION SELECT clause UNION ... 把两个或多个查询语句的结果合并成一个结果进行输出； 按照字段列拼接
MySQL视图: 储下来的SELECT语句；
CREATE VIEW view_name [(column_list)] AS select_statement 看起来像一个表，不过使用 SHOW TABLE STATUS 查看表属性时所有都为空
六、MySQL·的锁
什么是锁
1. 锁主要实现多用户访问相同资源时，实现并发访问控制。例如两个用户同时修改同一表等
2. 执行操作时施加的锁模式
读锁：共享锁，可同时实加操作
防止其他人修改正在查询的数据
写锁：独占锁，排它锁
如果一个用户在执行写操作，则其他读写都需等待
锁粒度：
1. 表锁：table lock
锁定了整张表
2.行锁：row lock
锁定了需要的行
粒度越小，开销越大，但并发性越好；
粒度越大，开销越小，但并发性越差；
锁的实现位置：
1.MySQL锁：可以使用显式锁
2.存储引擎锁：自动进行的（隐式锁）；
显式锁(表级锁)：
1. 手动施加锁
LOCK TABLES tbl_name lock_type [, tbl_name lock_type] ... lock_type锁类型：READ|WRITE
2. 取消枷锁UNLOCK TABLES
显示行级锁
1. InnoDB存储引擎也支持另外一种显式锁(锁定挑选出的部分行，行级锁 )：
SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE;
2. 只是在这个SELECT语句执行过程中施加所， 当这个语句执行结束后，释放所
七、transaction 事务的支持：
事务定义：
Mysql所支持的事务: 事务简单来说，是一组Mysql*查询语句*的语句，要么全部执行，要么全部不执行。把多个查询语句当做一个工作单元。
事务所满足的ACID测试：
1）Atomicity: 一个事物是不可分割的， 包含的语句要么同时执行，要么同时不执行
2）Consistency: 数据库总是，从一个一致性状态到另一个一致性状态
3）Isolation: 一个事物所做的操作，在提交之前是不可见的。
4）Durability: 一旦事物提交了，其所作的修改就将永远保存在数据库中而永久有效， 不会因为其他操作产生数据丢失，即便是数据库崩溃。
从内存同步硬盘的时间差还是有可能丢失的。
事物主要保证数据安全性，数据安全性越高并发性越差。主要体现在隔离性。
mysql的隔离级别：修改tx_isolation来调整隔离级别
1. READ-UNCOMMITTED: 读未提交，脏读, 不可重复读， 幻读。 在同一事物中，可以读到未提交的数据修改。
2.READ-COMMITTED: 在一个事物中， 不能读到尚未提交的事物。 但是当另一个事物提交修改后，即便在同一事物中，依然可以读到其他事物提交后的修改。这就是不可重复读
3.REPEATABLE-READ: 在同一事物中，即便另一事物调提交了对当前数据的修改， 读到的数据是相同的。 但开启新的事物时，会发现数据已经被修改， 这个是所谓的幻读。
4.SERIALIZABLE : 串行化，事物和事物之间严格隔离， 当一个事物在对某一表操作时，另一事物对此表无论读写都将阻塞，直到前一事物操作完成。
MVCC：多版本并发控制
1. 每个事务启动时，InnoDB为会每个启动的事务提供一个当下时刻的快照；
2. 为了实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间；里面存储的是系统版本号；（system version number）
3. MVCC只在两个隔离级别下有效：READ COMMITTED和REPEATABLE READ
跟事务相关的常用命令
mysql> START TRANSACTION mysql> COMMIT mysql> ROLLBACK mysql> SAVEPOINT identifier mysql> ROLLBACK [WORK] TO [SAVEPOINT] identifier 如果没有显式启动事务，每个语句都会当作一个独立的事务，其执行完成后会被自动提交； mysql> SELECT @@global.autocommit; mysql> SET GLOBAL autocommit = 0; 关闭自动提交，请记得手动启动事务，手动进行提交；
查看MySQL的事务隔离级别
mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; mysql> SELECT @@global.tx_isolation;
八、MySQL存储引擎
查看MySQL所支持的存储引擎和表使用引擎
1. 查询所支持的引擎
mysql> SHOW ENGINES ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM 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 | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2. 查看表属性
mysql> SHOW TABLE STATUS IN db_name [LIKE pattern] [WHERE clause]; mysql> SHOW TABLE STATUS IN hellodb LIKE "stu%" \G;
Name: students Engine: InnoDB Version: 10 Row_format: Compact Rows: 25 Avg_row_length: 655 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 26 Create_time: 2015-11-23 16:06:55 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:
各项解释：
Name: 表名
Engine: 存储引擎
Version: 版本（表的当前版本）
Row_format: 行格式，创建表的命令中可以定义 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
Rows: 表中的行数
Avg_row_length: 平均每行所包含的字节数；
Data_length: 表中数据总体大小，单位是字节
Max_data_length: 表能够占用的最大空间，单位为字节，0表示没有上限。
Index_length: 索引的大小，单位为字节
Data_free: 对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间
Auto_increment: 下一个AUTO_INCREMENT的值；
Create_time: 表的创建时间；
Update_time：表数据的最近一次的修改时间；
Check_time：使用CHECK TABLE或myisamchk最近一次检测表的时间；
Collation: 排序规则
Checksum: 如果启用，则为表的checksum；
Create_options: 创建表时指定使用的其它选项；
Comment: 表的注释信息
Innodb存储引擎，所产生的文件格式：
1.参数innodb_file_per_table=OFF时：
每张表具有单独的表结构文件tb_name.frm
数据内容使用共享表空间文件， ibdata#
2.参数innodb_file_per_table=ON时候：
每张表具有两个独立文件
tb.name.frm 表空间文件
tb_name.ibd
表空间文件(table space)： 由innodb管理的特有格式的数据文件，内部存储索引和数据，支持聚簇索引
MyISAM存储引擎产生的文件: 每个表都在数据库目录下存储三个文件：
1. tb_name.frm ： 表结构
2. tb_name.MYD ： 数据
3. tb_name.MYI ： 索引
通过修改default_storage_engine来修改默认存储引擎，需要写在配置文件中。
各引擎特性
1. Innodb:
事物： 事务日志 外键： ＭＶＣＣ： 多版本并发机制，主要是用于支持事务 聚簇索引： 索引和表存在一起，创建表时候必须创建一个聚簇索，索引怎么排序，数据也会跟着怎么排序 聚簇索引只能有一个 聚簇索引之外的其他索引，通常称为辅助索引 所有的辅助索引是指向聚簇索引的。而非指向元数据。 通常使用主键用于聚簇索引 无论据簇索引还是辅助索引都是Ｂ+树索引 行级锁： 间隙锁，用来格隔离行 支持辅助索引： 自适应的hash索引： 支持热备份:
2 MyISAM:
全文索引 支持表压缩，但是压缩后不能修改，用于制作数据仓库，可节约空间提高性能 空间索引 表级锁 延迟更新索引： 每当数据更新时，不需要立即更新索引，以降低I/O压力 不支持事务，外键和行级锁 崩溃无法安全可靠的恢复数据。 适用场景： 只读数据， 较小的表， 崩溃后可以忍受数据恢复时间和数据丢失。
3.ARCHIVE：
仅支持INSERT和SELECT，支持很好压缩功能； 适用于存储日志信息，或其它按时间序列实现的数据采集类的应用； 不支持事务，不能很好的支持索引；
4. CSV：
将数据存储为CSV格式；不支持索引；仅适用于数据交换场景，另外貌似精度很难保持；
5. BLACKHOLE：
没有存储机制，任何发往此引擎的数据都会丢弃；其会记录二进制日志，因此，常用于多级复制架构中作中转服务器；
6. MEMORY：
保存数据在内存中，内存表；常用于保存中间数据，如周期性的聚合数据等；也用于实现临时表
支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型
7.MRG_MYISAM：
是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表；
8.NDB：
是MySQL CLUSTER中专用的存储引擎
第三方的存储引擎：
1. OLTP类：
XtraDB: 增强的InnoDB，由Percona提供； 编译安装时，下载XtraDB的源码替换MySQL存储引擎中的InnoDB的源码 PBXT: MariaDB自带此存储引擎 支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持； 支持事务、MVCC
2. TokuDB: 使用Fractal Trees索引，没有碎片问题，性能与换从无关，适用存储大数据，拥有很压缩比；已经被引入MariaDB；
列式存储引擎： Infobright: 目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计； 如果用于MySQL, 需要对Mysql做定制
开源社区存储引擎：
1. Aria：前身为Maria，可理解为增强版的MyISAM(支持崩溃后安全恢复，支持数据缓存)
2.Groona：全文索引引擎，Mroonga是基于Groona的二次开发版，适用于搜索引擎
3.OQGraph: 由Open Query研发，支持图结构的存储引擎
4.SphinxSE: 为Sphinx全文搜索服务器提供了SQL接口
5.Spider: 能数据切分成不同分片，比较高效透明地实现了分片(shared)，并支持在分片上支持并行查询；
如何选择数据存储引擎：
1.是否需要事务
2.备份的类型的支持
3.崩溃后的恢复
4.特有的特性
九、MySQL的用户以及权限管理：
mysql用户管理：
1.用户格式：
username@{host_IP|host_name}
{host_IP|host_name} : 需要登录mysql服务器的主机IP
2.mysql用户管理的相关命令：
mysql> CREATE USER user_name IDENTIFIED BY {'auth_string' | PASSWORD 'hash_string'}
mysql> DROP USER user_name
mysql> RENAME USER old_user TO new_user
mysql> SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');
mysql的权限控制：
1. 权限管理命令：
权限授予：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]
权限回收：
REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE PROXY ON user FROM user [, user] ...
查看用户能够使用的权限：
mysql>SHOW GRANTS FOR username@'hostname'
作用对象类型（priv_type）:
1. TABLE(默认)
2.FUNCTION
3.PROCEDURE
权限目标级别（priv_level）
1. * 所有： 库，表，函数
2. *.* 所有库的所有[TABLE|FUNCTION|PROCEDURE]
3. db_name.* db_name库的所有
4. db_name.tbl_name
5. tbl_name
6. db_name.routine_name
WITH GRANT OPTION: 权限授予选项
1. MAX_QUERIES_PER_HOUR count
2. MAX_UPDATES_PER_HOUR count
3.MAX_CONNECTIONS_PER_HOUR count
4.MAX_USER_CONNECTIONS count
权限类型(priv_type)：
1. 管理类权限：
CREATE TEMPORARY TABLES：使用或者创建临时表 CREATE USER：创建，删除，重命名用户 FILE：在服务器上读或者写, 包括备份，以及source 文件 LOCK TABLES：是否可以锁表 PROCESS：是否能执行SHOW PROCESSLIST 命令查看mysql内部运行的线程 RELOAD：是否能使用FLUSH 和 RESET 命令 REPLICATION SLAVE：是否可以查询主服务器有哪些从服务器 REPLICATION CLIENT：是否有权限成为从服务器 SHOW DATABASES：是否可以查询服务器中有哪些数据库 SHUTDOWN：关闭 SUPER：其它
2.库级别和表级别：
ALTER：是否可以执行ALTER TABLE 命令 ALTER ROUTINE：修改存储历程，包括存储函数 CREATE：创建表和库 CREATE ROUTINE：创建历程和函数 CREATE VIEW：创建视图 DROP：删除表和库 EXECUTE：执行存储历程 GRNAT OPTION：把自己的权限转赠给他人 INDEX：建立索引 SHOW VIEW：查看视图
3.数据操作（表级别）：
SELECT:查询 INSERT：增加 UPDATE：修改 DELETE：删除 字段级别： SELECT(col1,...) UPDATE(col1,...) INSERT(col1,...)
4.所有权限：
ALL [PRIVILEGES]
ssl选项（ssl_option）：
SSL X509：证书格式 CIPHER 'cipher'加密方式 ISSUER 'issuer'证书颁发者 SUBJECT 'subject'拒绝某证书
几个存储用户权限信息的表，全都存储在mysql库中
db: 库级别权限； host: 主机级别权限，已废弃 tables_priv: 表级别权限 colomns_priv：列级别的权限 procs_priv：存储过程和存储函数相关的权限 proxies_priv：代理用户权限
事例：
1. 授予testuser能够通过192.168.98.0/24网络内的任意主机访问当前mysql服务器的权限； mysql> GRANT SELECT ON TABLE *.* TO 'testuser'@'192.168.98.%' IDENTIFIED BY 'testuser'; 2. 让此用户能够创建及删除testdb数据库，及库中的表； GRANT CREATE,DROP ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 3. 让此用户能够在testdb库上执行创建和删除索引； GRANT INDEX ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 4. 让此用户能够在testdb.t2表上查询id和name字段，并允许其将此权限转授予其他用户； GRANT GRANT OPTION,SELECT(id,name) ON TABLE testdb.t2 TO 'testuser'@'192.168.98.%' ; 5、让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作； GRANT SELECT,DELETE,UPDATE,INSERT ON TABLE testdb.tb1 TO 'testuser'@'192.168.98.%';
十、MySQL查询缓存
用于保存MySQL查询语句返回的完整结果。被命中时，MySQL会立即返回结果，省去解析、优化和执行等阶段。
如何检查缓存
1. MySQL保存结果于缓存中：
2. 把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value
什么样的语句不会被缓存？
查询语句中有一些不确定数据时，不会缓存：例如NOW(), CURRENT_TIME()；一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存；
缓存会带来额外开销：
1、每个查询都得先检查是否命中；
2、查询结果要先缓存；
缓存相关的服务器变量
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
query_cache_type: 查询缓存类型；是否开启缓存功能，开启方式有三种{ON|OFF|DEMAND}；
DEMAND：意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；
query_cache_size: 总空间，单位为字节，大小必须是1024的整数倍。MySQL启动时，会一次分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的。
query_cache_min_res_unit: 存储缓存的最小内存块；(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值。
query_cache_limit: 单个缓存对象的最大值，超出时则不预缓存；手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句。
query_cache_wlock_invalidate: 如果某个表被其它用户连接锁住了，是否仍然从缓存中返回结果。OFF表示返回。
如何判断命令率(缓存相关的状态变量)：
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16757008 | | Qcache_hits | 4 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 18 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------------------+----------+ Qcache_hits： 命中次数 Qcache_free_memory： 剩余缓存空间，尚未划分成块的空间 Qcache_free_blocks： 空闲的块数，划分完成但还没使用的空间 Qcache_total_blocks： 总块数 Qcache_queries_in_cache： 在缓存中，缓存插入的次数。 Qcache_not_cached： 没有缓存的 Qcache_lowmem_prunes： 因为内存太少而修剪内存的次数。 碎片整理：FLUSH QUERY_CACHE 清空缓存：RESET QUERY_CACHE
计算命中率：
MariaDB [hellodb]> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 24 | | Qcache_hits | 4 | +---------------+-------+ Com_select: 非缓存查询次数 Qcache_hits： 缓存命中次数 Qcache_hits/(Com_select+Qcache_hits) 也应该参考另外一个指标：命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的。能达到10:1，为比较理想的情况。
缓存优化使用思路：
1、批量写入而非多次单个写入；
2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；
3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；
4、对写密集型的应用场景来说，禁用缓存反而能提高性能；
十一、MySQL日志：
日志分类
1.查询日志： 繁忙的服务器不建议记录查询日志
2.慢查询日志：查询执行时长超过指定时长的查询，即为慢查询
3.错误日志：
4.事务日志：ib_logfile0 ib_logfile1
随机I/O转换为顺序I/O从而保证ACID的持久性。 只要事务提交，马上写入事务日志中。
事务日志有可能承担读操作，innodb_buffer可能会把装不下的内容放入事务日志
5.日志文件组：
特性：
1）至少应该有两个日志文件。
2）第一个满了以后，启动第二个。 第一个日志文件开始向磁盘同步。
3）如果事务刚写到事务日志中数据库崩溃，在重启后，会把事务日志继续同步到数据文件，从而达到一致性。
4）但如果事务日志所在的硬盘损坏导致崩溃，则无法恢复。所以要保证事务日志所在的存储足够可靠
5）为了分摊事务日志I/O和同步数据文件的I/O， 数据文件和事务日志要分开存放
相关参数：
innodb_log_file_size 事务日志大小
innodb_log_files_in_group 事务日志组个数
innodb_log_group_home_dir 事务日志所在位置，"./"是安装目录
innodb_flush_log_at_trx_commit 是否事务提交后马上同步日志
可以放在固态硬盘上从而提高性能。
注意：尽可能使用小事务以提升事务引擎的性能。以保证尽量在回滚时，降低硬盘I/O开销
查询查询日志： 繁忙的服务器不建议开启
log={ON|OFF}:是否记录所有语句的日志信息于一般查询日志文件(general_log),5.6以后弃用，重复选项;
log_output={TABLE|FILE|NONE}
TABLE和FILE可以同时出现，用逗号分隔即可；
TABLE : 记录到表中，在mysql库中的general_log 表
FILE : 记录到文件中，在general_log_file 选项指定日志存放位置
general_log：是否启用查询日志；
general_log_file：定义一般查询日志保存的文件
慢查询日志： 用于评估系统性能，procona有工具用来分析慢查寻日志
long_query_time： 10.000000
定义多长时间算是慢，单位为秒
slow_query_log={ON|OFF}
设定各用户级别是否启用慢查询日志；它的输出位置也取决log_output={TABLE|FILE|NONE}；
slow_query_log_file=www-slow.log
定义日志文件路径及名称；
log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
哪些查询不记录日志
log_slow_queries=ON
只有管理员才能修改，全局是否记录慢查日志
log_slow_rate_limit=1
记录速率
log_slow_verbosity
是否记录详细的慢查日志
错误日志：
1.服务器启动和关闭过程中的信息；
2. 服务器运行过程中的错误信息；
3. 事件调度器运行一个事件时产生的信息；
4. 在复制架构中的从服务器上启动从服务器线程时产生的信息；
log_error = /path/to/error_log_file
直接指向日志文件
log_warnings = {1|0}
是否记录警告信息于错误日志中；
中继日志:
从主服务器复制了来的二进制日志
二进制日志：有以下用途
1.引起mysql服务器改变的任何操作。
2.复制功能依赖于此日志。
3.从服务器通过复制主服务器的二进制日志完成主从复制，在执行之前保存于中继日志中。
4.从服务器通常可以关闭二进制日志以提升性能。
5.主要用于时间点恢复
5.数据库复制
总结： 这部分内容总结，基本上来源于听课的视频和笔记。比较琐碎，读起来估计也没有美感可言，希望读者见谅。 后面会继续总结备份，主从复制，高可用等内容
原创文章，作者：以马内利，如若转载，请注明出处：http://www.178linux.com/8033
