Linux自学笔记——mysql基础、备份和恢复、主从复制、MHA

RDBMS：关系型数据库管理系统

       C/S：通过专有协议

       关系模型：表（行，列），二维关系；

       范式：第一范式、第二范式、第三范式（在之前的博客中已经做过说明）

       关系运算：

              选择      投影

       数据库：表，索引，视图（虚表）

              SQL：Structure Query Language

                     DDL，DML

                     编程接口：

                            存储过程

                            存储函数

                            触发器

                            事件调度器

                            过程式编程：选择、循坏

       三层模型：

              物理层  逻辑层  视图层

       解决方案：

              Oracle, Sybase, Infomix, DB2

              MySQL, MariaDB, PostgreSQL, SQLite

MariaDB

       插件式存储引擎

       单进程多线程

              连接线程

              守护线程

配置文件：集中式的配置，能够为mysql的各应用程序提供配置信息

       [mysqld]

       [mysqld_safe]

       [mysqld_multi]

       [server]

       [mysql]

       [mysqldump]

       [client]

启动查找路径：/etc/my.cnf à /etc/mysql/my.cnf à $MYSQL_HOME/my.cnf à --default-extra-file=/path/to/somedir/my.cnf à ~/.my.cnf

安装方法：

       os vendor：rpm

       MYSQL：

              rpm

              展开可用

              源码

安装后的设定：

1)      为所有root用户设定密码；

mysql>SET PASSWORD FOR

mysql> update mysql.user SET password=PASSWORD(‘your_pass’) WHERE clause；

#mysqladmin

2)      删除所有匿名用户

Mysql>DROP USER ‘’@’localhost’；

上述两步骤可运行命令：mysql_secure_installation

3)      建议关闭主机名反解功能；

skip_name_resolve = ON

mysql --> mysqld

客户端程序：

       mysql：交互式的CLI工具；

mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中；

mysqladmin：基于mysql协议管理mysqld；

mysqlimport：数据导入工具；

非客户端类的管理工具：

       myisamchk，myisampack

如何获取程序默认使用的配置；

       mysql --print-defaults

       mysqld --print-defaults

客户端类应用程序的可用选项：

       -u, --user=   

       -h, --host=   

       -p, --passowrd=  

       -P, --port=   

       --protocol=

       -S, --socket=

       -D, --database=

       -C, --compress

       mysql -e "SQL"

mysql的使用模式：

       交互模式：

              可运行命令有两类：

                     客户端命令：

                            \h，help

                     服务器端命令：

                            SQL，需要语句结束符（冒号；）

       脚本模式：

              # mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql

              mysql> source /path/from/somefile.sql

服务器端（msyqld）：工作特性有多种定义方式

       命令行选项

       配置文件参数

              获取可用参数列表：

                     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：ANSI SQL

       SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

MYSQL数据类型：

       字符型

       数值型

       日期时间型

       內建类型

       字符型：

              CHAR, BINARY：定长数据类型；

              VARCHAR, VARBINARY：变长数据类型；需要结束符；

              TEXT：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

              BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

              ENUM, SET

       字符型修饰符：

              NOT NULL：非空约束；

              NULL：

              DEFAULT ‘STRING’：指明默认值；

              CHARACTER SET ‘’：使用的字符集；

              COLLATION：使用的排序规则；

              mysql > SHOW CHARACTER SET；

              msyql> SHOW COLLATION;

       数值型：

              精确数值型：

                     整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

                     十进制型：DECIMAL

              近似数值型

                     浮点型：

                            FLOAT

                            DOUBLE

                     BIT

       整型数据修饰符：

              NOT NULL

              NULL

              DEFAULT NUMBER

              AUTO_INCREMENT:

                     UNSIGNED

                     PRIMARY KEY|UNIQUE KEY

                     NOT NULL

                    mysql> SELECT LAST_INSERT_ID();

       日期时间型：

              DATE

              TIME

              DATETIME

              TIMESTAMP

              YEAR(2), YEAR(4)

       日期时间型修饰符：

              NOT NULL

              NULL

              DEFAULT

       內建类型SET和ENUM的修饰符：

              NOT NULL

              NULL

              DEFAULT

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, or STRICT_ALL_TABLES

       DDL：数据定义语言；

              CREATE,ALTER,DROP

              DB组件：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器等；

              CREATE相关的常用命令：

                     CREATE DATABASE

                       CREATE EVENT

                        CREATE FUNCTION

                        CREATE FUNCTION UDF

                        CREATE INDEX

                        CREATE PROCEDURE

                        CREATE SERVER

                        CREATE TABLE

                        CREATE TABLESPACE

                        CREATE TRIGGER

                        CREATE USER

                        CREATE VIEW

       DML：数据操作语言；

              INSERT,DELETE,UPDATE,SELECT

       数据库：

              CREATE,ALTER,DROP

                     {DATABASE|SCHEMA}

                     [IF EXISTS]

                     [IF NOT EXISTS]

       表：二维关系

              设计表：遵循规范；

              定义：字段，索引

                     字段：字段名，字段数据类型，修改符

                     约束，索引：应该创建在经常用作查询条件的字段上；

                            索引：实现级别在存储引擎；

                                   分类：

                                          稠密索引、稀疏索引；

                                          B+索引、hash索引、R树索引、FULLTEXT索引

                                          聚集索引、非聚集索引

                                          简单索引、组合索引

       创建表：CREATE TABLE

1)      直接创建；

2)      通过查询现存的表创建；新表会被直接插入查询而来的数据；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

   [(create_definition,...)]

   [table_options]

   [partition_options]

   select_statement

3)      通过复制现存的表的表结构创建；不复制数据；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

       注意：Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎；同一个库中表要使用同一种存储引擎类型；

查看表结构：

  DESCRIBE tb1_name；

查看表状态信息：

  SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] 

修改表：ALTER TABLE

删除表：DROP TABLE

MYSQL数据文件类型：

       数据文件、索引文件

       重做日志、撤销日志、二进制日志、错误日志、查询日志、慢查询日志、中继日志；

DDL&&DML：

       索引管理：

              按特定数据结构存储的数据；

       索引类型：

              聚集索引、非聚集索引：数据是否与索引存储在一起；

              主键索引、辅助索引

              稠密索引、稀疏索引：是否索引了每一个数据项；

              B+ TREE、HASH、R TREE

              简单索引、组合索引

              左前缀索引；

              覆盖索引；

       管理索引的途径：

              创建索引：创建表时指定：CREATE INDEX

              创建或删除索引：修改表的命令

              删除索引：DROP INDEX

       查看表上的索引：

              SHOW {INDEX | INDEXES | KEYS}

                                       {FROM | IN} tbl_name

                                       [{FROM | IN} db_name]

                                       [WHERE expr]

视图：VIEW

       虚表

       创建方法：

              CREATE VIEW view_name [(column_list)] AS select_statement  [WITH [CASCADED | LOCAL] CHECK OPTION]

       删除视图：

              DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

       视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制；

DML：

       INSERT,DELETE,UPDATE,SELECT

       INSERT：

              一次插入一行或多行数据；

              INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

                         [INTO] tbl_name [(col_name,...)]

                         {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

                         [ ON DUPLICATE KEY UPDATE

                           col_name=expr

                             [, col_name=expr] ... ]

              INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

       UPDATE：

              UPDATE [LOW_PRIORITY] [IGNORE] table_reference

                     SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

                     [WHERE where_condition]

                     [ORDER BY ...]

                     [LIMIT row_count]

              注意：一定要有限制条件，否则将修改所有行的指定字段；

                     限制条件：

                            WHERE

                            LIMIT

       SELECT：

              Query Cache

              查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎；

              SELECT语句的执行流程：

                     FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause --> ORDER BY --> SELECT --> LIMIT

              单表查询：

                     SELECT

                                [ALL | DISTINCT | DISTINCTROW ]

                                  [SQL_CACHE | SQL_NO_CACHE]

                                select_expr [, select_expr ...]

                                [FROM table_references

                                [WHERE where_condition]

                                [GROUP BY {col_name | expr | position}

                                  [ASC | DESC], ... [WITH ROLLUP]]

                                [HAVING where_condition]

                                [ORDER BY {col_name | expr | position}

                                  [ASC | DESC], ...]

                                [LIMIT {[offset,] row_count | row_count OFFSET offset}]

                                [FOR UPDATE | LOCK IN SHARE MODE]

                            DISTINCT：数据去重；

                            SQL_CACHE：显示指定存储查询结果于缓存之中；

                            SQL_NO_CACHE：显示查询结果不予缓存；

query_cache_type的值为'ON'时，查询缓存功能打开；SELECT的结果符合缓存条件即会缓存，否则，不予缓存；显式指定SQL_NO_CACHE，不予缓存；

query_cache_type的值为'DEMAND'时，查询缓存功能按需进行；显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存；

                     字段显示可以使用别名：

                            col1 AS alias1, col2 AS alias2, ...

                     WHERE子句：指明过滤条件以实现“选择”的功能：

                            过滤条件：布尔型表达式；

                                   算术操作符：+, -, *, /, %

                                   比较操作符：=, !=, <>, <=>, >, >=, <, <=

                                   BETWEEN min_num AND max_num

                                   IN (element1, element2, ...)

                                   IS NULL

                                   IS NOT NULL

                                   LIKE:

                                          %: 任意长度的任意字符；

                                          _：任意单个字符；

                                   RLIKE：

                                   REGEXP：匹配字符串可用正则表达式书写模式；

                     逻辑操作符：

                            NOT

                            AND

                            OR

                            XOR

                     GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算：

                            avg(), max(), min(), count(), sum()

                            HAVING: 对分组聚合运算后的结果指定过滤条件；

                     ORDER BY: 根据指定的字段对查询结果进行排序；

                            升序：ASC

                                                降序：DESC 

                                    LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制；

                                        对查询结果中的数据请求施加“锁”：

                                                        FOR UPDATE: 写锁，排他锁；

                                                        LOCK IN SHARE MODE: 读锁，共享锁

                        多表查询：

                                    交叉连接，笛卡尔乘积；

                                    内连接：

                                                等值连接：让表之间的字段以“等值”建立连接关系；

                                                不等值连接

                                                自然连接；

                                                    自连接；

                                    外连接：

                                                    左外连接：

                                                            FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

                                                    右外连接：

                                                            FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

                        子查询：在查询语句嵌套着查询语句

                            基于某语句的查询结果再次进行查询

                            用在WHERE子句中的子查询：

1)      用于比较表达式中的子查询；子查询仅能返回单个值：

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

2)      用于IN中的子查询：子查询应该单键查询并返回一个或多个值构成列表；

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

3)      用于EXISTS；

                            用于FROM子句中的子查询：

                                    使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

                        联合查询：UNION将两外或多个返回值字段相同的查询的结果合并输出

                            SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

练习1：导入hellodb.sql生成数据库

    1)  在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

    2)  以ClassID为分组依据，显示每组的平均年龄；

    3)  显示第2题中平均年龄大于30的分组及平均年龄；

    4)  显示以L开头的名字的同学的信息；

    5)  显示TeacherID非空的同学的相关信息；

    6)  以年龄排序后，显示年龄最大的前10位同学的信息；

    7)  查询年龄大于等于20岁，小于等于25岁的同学的信息，用三种方法；

        方法一：

        方法二：

        方法三：

练习2：导入hellodb.sql，以下在students表上进行；

    1)  以ClassID分组，显示每班的同学的人数；

    2)  以Gender分组，显示其年龄之和；  

    3)  以ClassID分组，显示其平均年龄大于25的班级； 

    4)  以Gender分组，显示各组中年龄大于25的学员的年龄之和；

Mysql的用户和权限：

创建用户账号：

        CREATE USER 'username'@'host' IDENTIFIED BY 'your_password'；

删除用户账号：

        DROP USER 'username'@'host';

查看用户获得的权限：

        SHOW GRANTS FOR 'username'@'host';

Mysql的权限级别：

        库级别

        表级别

        字段级别

        管理类

        程序类

管理类：

        CREATE TEMPORARY TABLES

CREATE USER

FILE

SUPER

SHOW DATABASES

RELOAD

SHUTDOWN

REPLICATION SLAVE

REPLICATION CLIENT

LOCK TABLES

PROCESS

Storage routine：存储例程

                                storage procedure

                                storage function

库和表级别：

ALTER

ALTER ROUTINE

CREATE

CREATE ROUTINE

CREATE VIEW

DROP

EXECUTE

INDEX

GRANT OPTION: 是否可以转授权限的权限；

SHOW VIEW

数据操作（表级别）：

SELECT

INSERT

UPDATE

DELETE

字段级别：

SELECT(col1,...)

UPDATE(col1,...)

INSERT(col1,...)

所有权限：

ALL [PRIVILEGES]

用户重命名：RENAME USER

            RENAME USER old_user_name TO new_user_name

修改密码：

1)      SET PASSWORD FOR

2)      UPDATE mysql.user SET password=PASSWORD('your_password') WHERE clause;

3)      mysqladmin password

mysqladmin [OPTIONS] command command....

-u, -h, -p

忘记管理员密码的解决方法：

1)      启动mysqld进程时，为其使用：--skip-grant-tables --skip-networking

2)      使用UPDATE命令修改管理员密码；

3)      关闭mysqld进程，移除上面两个选项，重启mysqld；

mysql中的授权相关的表：

db、host、user

columns_priv、tables_priv, procs_priv

授权与取消授权：GRANT, REVOKE

GRANT priv1, priv2, ... ON [TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine TO 'username'@'host' [IDENTIFIED BY 'password'] [REQUIRE ssl_option] [WITH with_option]

GRANT OPTION

    | MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

    | MAX_USER_CONNECTIONS count

REVOKE priv1,priv2,... ON [TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine FROM 'username'@'host',...;

Mysql cache

         SELECT --> QUERY CACHE --> PARSER --> OPTIMIZER --> EXECUTING ENGINE --> STORAGE ENGINE

         缓存：hit（命中），miss（未命中）

                    Key-value

                    衡量缓存的有效性：命中率，hit/(hit+miss)

                   次数；

       query_cache_type

              ON,OFF,DEMAND

       SQL_CACHE | SQL_NO_CACHE

              QUERY CACHE：

                     Key：查询语句的hash码；

                     Value：查询语句的执行结果；

       什么样的语句不会缓存？

              查询语句中有不确定数据时不会缓存；

一般来讲，如果查询中包含用户自定义的函数（UDF）、存储函数、用户变量、临时表、mysql库中表、或者任何包含权限信息表，都不会缓存；

       缓存什么场景下比较有效？

              对于存在需要大量资源的查询非常适合启用缓存；

       与缓存功能相关的服务器变量：

              Query_cache_limit：mysql能够缓存的最大查询结果；如果某查询的结果大于此值，则不会被缓存；

              Query_cache_min_res_unit：查询缓存中分配内存的最小单位

                     计算公式：（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache

              Query_cache_size：查询缓存的总体可用空间；其必须是1024的倍数；

              Query_cache_wlock_incalidate：当其他会话锁定此次查询用到的资源时，是否不能再从缓存中返回数据；

与缓存相关的状态变量：

              Qcache_hits/Com_select

              衡量缓存是否足够有效的另一种思路：Qcache_hits/Qcache_inserts

                     如果此比值大于3:1，说明缓存也是有效的；如果高于10:1，相当理想；

              缓存优化的思路：

1)      批量写入比单次写入对缓存的影响要小很多；

2)      缓存空间不宜过大，大量缓存的同时失效会导致Mysql假死；

3)      必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；

4)      对写密集型的应用场景，禁用缓存反而能提高性能；

碎片整理：FLUSH QUERY_CACHE

清空缓存：RESET QUERY_CACHE

Mysql的存储引擎

       表类型：

              CREATE TABLE ... ENGINE=

       InnoDB：

              处理大量的短期事务：

              数据存储于“表空间”中；

1)      所有InnoDB表的数据和索引放置于同一个表空间中；

表空间文件：datadir定义的目录下；

     数据文件：ibddata1，ibddata2，…

2)      每个表单独使用一个表空间存储表的数据和索引；

innodb_file_per_table=ON

数据文件（存储数据和索引）：tb1_name.ibd，

表格式定义：tb1_name.frm

              基于MVCC来支持高并发，支持所有的四个隔离级别，默认级别为REPEATABLE READ，间隙锁防止幻读；

              使用聚集索引

              支持“自适应hash索引”