Mysql从入门到精通整理

 目录

• mysql基础
• mysql进阶
• mysql高级
• mysql优化

 

正文

数据库是信息化产业的最基础的软件之一，各种管理系统，网站，在线游戏，背后基本都会有数据库的支持。

回到顶部

mysql基础

数据库基本概念

MariaDB or MySQL官网：https://db-engines.com/en/ranking

磁盘文件--> 层次模型 --> 网状模型 --> (Codd) 关系模型
DBMS是管理数据库的系统软件，它实现数据库系统的各种功 能。是数据库系统的核心

DBA：负责数据库的规划、设计、协调、维护和管理等工作

关系 ：关系就是二维表。并满足如下性质： 表中的行、列次序并不重要

行row：表中的每一行，又称为一条记录

列column：表中的每一列，称为属性，字段

主键（Primary key）：用于惟一确定一个记录的字段；复合主键：多个字段组合成一个主键(NOT NULL)；

惟一键：一个或多个字段的组合，填入的数据必须能在本表 中唯一标识本行；允许为NULL，一个表可以存在多个

约束：
　　主键约束：惟一、非空；一张表只能有一个；
　　惟一键约束：惟一，可以存在多个；
　　外键约束：参考性约束；
　　检查性约束：check；

三层模型：
　　物理层 --> SA (决定数据的存储格式，即RDBMS在磁盘上如何组织文件)
　　逻辑层 --> DBA(描述存储什么数据，以及数据间存在什么样的关系)
　　视图层 --> Coder(描述DB中的部分数据)

域domain：属性的取值范围，如，性别只能是‘男’和‘女’ 两个值

表：row, column；
关系运算：选择、投影
数据库：表、索引、视图（虚表）、SQL、存储过程procedure，过程无返回值、存储函数function，过程有返回值、触发器trigger、事件调度器event scheduler，任务计划；
事务transaction：多个操作被当作一个整体对待 ACID: 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性：两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。

持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

数据的操作：• 数据提取：在数据集合中提取感兴趣的内容。（SELECT）• 数据更新：变更数据库中的数据。（INSERT、DELETE、 UPDATE）

应用程序指以数据库为基础的应用程序：DBMS --> RDBMS（关系型数据库管理系统）

设计关系数据库时，遵从不同的规范要求，设计出合理的 关系型数据库，这些不同的规范要求被称为不同的范式，各种范 式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式 （2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第 四范式(4NF）和第五范式（5NF，又称完美范式）。满足最 低要求的范式是第一范式（1NF）。在第一范式的基础上进 一步满足更多规范要求的称为第二范式（2NF），其余范式 以次类推。一般说来，数据库只需满足第三范式(3NF）即可
RDBMS：
范式：第一范式、第二范式、第三范式；

1NF：无重复的列，每一列都是不可分割的基本数据项，同 一列中不能有多个值

2NF：属性完全依赖于主键，第二范式必须先满足第一范式 ，要求表中的每个行必须可以被唯一地区分。

3NF：属性不依赖于其它非主属性，满足第三范式必须先满 足第二范式。第三范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

SQL: Structure Query Language：结构化查询语言--- SQL解释器---数据存储协议：应sq用层协议，C/S

olacle(tcp:1521),mysql(tcp:3306),sql  server(tcp:1433)

mysql基础和应用结构

mysql特性：插件式存储引擎、单进程多线程

mysql体系结构：

 

安装：服务端mysql-server和客户端mysql，yum安装，二进制安装，源码编译安装

提高安全性 ：运行mysql_secure_installation

 

服务端程序：
mysqld, mysqld_safe, mysqld_multi
客户端程序：
mysql, mysqldump, mysqlbinlog, mysqladmin, ...
非客户端类管理程序：
myisamchk, myisampack, ...

配置文件： /etc/my.cnf 和 /etc /my.cnf.d/ *.cnf
mysql命令选项：

-uUSERNAME: 用户名；默认为root

-hHOST: 服务器主机; 默认为localhost

-pPASSWORD：用户的密码;建议使用-p,默认为空密码

mysql用户账号由两部分组成： 'USERNAME'@'HOST' HOST用于限制此用户可通过哪些远程主机连接mysql服务

支持使用通配符： % 匹配任意长度的任意字符 172.16.64.0/16 或 172.16.%.% _ 匹配任意单个字符

运行mysql前通常修改的参数

在配置文件的[mysqld]中添加两个选项： 

innodb_file_per_table = on  启用innodb存储引擎

skip_name_resolve = on 禁止主机名解析

MYSQL语言基础

在数据库系统中，SQL语句不区分大小写(建议用大写) ，但字符串常量区分大小写

SQL语句可单行或多行书写，以“;”结尾，关键词不能跨多行或简写

用空格和缩进来提高语句的可读性 ，句通常位于独立行，便于编辑，提高可读性

注释： SQL标准： /*注释内容*/ 多行注释      -- 注释内容 单行注释，注意有空格

MySQL注释： #

 

数据库对象的命名规则 ：1.必须以字母开头 ；2.可包括数字和三个特殊字符（# _ $）；3.不要使用MySQL的保留字 ；4.同一Schema下的对象不能同名

MySQL的数据类型：字符型、数值型、日期时间型、内建类型
字符型：CHAR(#)， BINARY(#)：定长型；CHAR不区分字符大小写，而BINARY区分；

               VARCHAR(#)， VARBINARY(#)：变长型TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT

               BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
数值型：
浮点型：近似FLOAT、DOUBLE、REAL、BIT
整型：精确：INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT；DECIMAL
日期时间型：日期：DATE；时间：TIME；日期j时间：DATETIME；间戳：TIMESTAMP；年份：YEAR(2), YEAR(4)
内建：ENUM：枚举：ENUM('Sun','Mon','Tue','Wed')；SET：集合

类型修饰符：
　　　　字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
　　　　整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED（使用unsigned会使数据可变长度增长一倍）
　　　　日期时间型：NOT NULL， NULL， DEFAULT
DML:
INSERT/REPLACE（增）, DELETE（删）, SELECT（查）, UPDATE（改），ALTER。

创建数据库：CREATE    DATABASE  |SCHEMA   [IF NOT EXISTS]   'DB_NAME'; 

删除数据库：DROP    DATABASE   |   SCHEMA   [IF EXISTS]   'DB_NAME';

查看支持所有字符集：SHOW CHARACTER SET;  

查看支持所有排序规则：SHOW COLLATION;

查看数据库列表：SHOW DATABASES;  

创建表：CREATE  TABLE  'DB_TABLE_NAME' ;

删除表：DROP TABLE [IF EXISTS] tb_name;

查看表： USE   'DB_NAME'  ;  SHOW  TABLES ;

查看支持的engine类型：SHOW ENGINES;

查看表结构：DESC [db_name . ]tb_name;

查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'\G

实例：

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));

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 students ADD gender ENUM('m','f')    （枚举，gender的域值为m和f）

ALETR TABLE students  CHANGE id   sid  int  UNSIGNED NOT NULL PRIMARY KEY;

ALTER TABLE students ADD UNIQUE KEY(name);  （唯一键）

ALTER TABLE students ADD INDEX(age);  （添加age的索引）

SHOW INDEXES FROM students;

ALTER TABLE students DROP age;

索引：定义在查找时作为查找条件的字段 ----优点：提高查询速度 ；缺点：占用额外空间，影响插入速度  （创建索引必须要有索引名称）

创建索引： CREATE INDEX   index_name   ON tbl_name (index_col_name,...);

删除索引： DROP INDEX index_name ON tbl_name;

查看索引： SHOW INDEXES FROM [db_name.]tbl_name;

INSERT：（插入，增）
INSERT    [INTO]    tbl_name    [(col_name,...)]   {VALUES | VALUE}     (val1,...),(...),...

UPDATE：（改）

UPDATE   tbl_name  SET col1=val1, col2=val2, ... [WHERE clause]    [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

DELETE:：（删）

DELETE   FROM    tbl_name   [WHERE clause]   [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 可先排序再指定删除的行数

SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];  Limit m,n 跳过m个，要n个

SELECT字段表示法：  *： 所有字段      as：字段别名， col1 AS alias1  例：

WHERE clause:

操作符： >, <, >=, <=, ==, != , BETWEEN ... AND ...    

LIKE:  %：任意长度的任意字符 ;  _：任意单个字符；

RLIKE：正则表达式模式匹配

IS NULL ，IS NOT NULL IN （val1,val2,…）

条件逻辑操作： and，or，not

示例：

DESC students;

INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');

INSERT INTO students  (id,name) VALUES(3,'jack'),(4,'allen');

SELECT * FROM students WHERE id < 3;

SELECT * FROM students WHERE gender='m';

SELECT * FROM students WHERE gender IS NULL;

SELECT * FROM students WHERE gender IS NOT NULL;

SELECT * FROM students ORDER BY name DESC LIMIT 2;  （符合条件的前两个）

SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

SELECT * FROM students  WHERE id >=2 and id <=4;

SELECT * FROM students  WHERE BETWEEN 2 AND 4 ;

SELECT * FROM students WHERE name LIKE ‘t%’

SELECT * FROM students WHERE name RLIKE '.*[lo].*';

SELECT   id   stuid ,  name as stuname FROM students

查询执行路径：
　　请求-->查询缓存
　　请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应
SELECT语句的执行流程：FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit

mysql用户与授权管理

用户创建、查看、删除与密码管理

创建用户： CREATE USER   'username'@'host'   [IDENTIFIED BY 'password'];

查看当前用户: SELECT  user();

查看用户： SELECT   User,Host,Password  FROM user;

删除用户：DROP USER 'username'@'host'; 示例：删除默认的空用户 DROP USER ''@'localhost';

更改口令： 1)SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');

　　　　　 2) UPDATE user SET password=PASSWORD('magedu') WHERE User='root' ;

                   注意：上面修改表的命令不会马上生效，需执行FLUSH PRIVILEGES生效

　　　　     3) /usr/local/mysql/bin/mysqladmin -u root –poldpassword password 'newpassword

                   4）删除mysql数据库目录/var/lib/mysql/mysql/下的mysql数据库，此时所有用户信息都丢失，且可以空口令登录

忘记管理员密码的解决办法：
(1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项；
　　CentOS 7：mariadb.service
　　CentOS 6：/etc/init.d/mysqld
(2) 通过UPDATE命令修改管理员密码； 
(3) 以正常 方式启动mysqld进程；
　　

mysql授权

权限级别：管理权限、数据库、表、字段、存储例程

GRANT   priv_type,... ON    [object_type]    db_name.tb_name    TO    'user'@'host'   [IDENTIFIED   BY   'password'] [WITH GRANT OPTION];

priv_type: ALL [PRIVILEGES] ,select,insert,update,delete,alter

示例： GRANT   SELECT,DELETE  on  testdb.*   TO  'testuser'@'%'   IDENTIFIED  BY  'testpass‘;(一步就包含创建用户和授权)

查看指定用户获得的授权：

Help SHOW

GRANTS SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER();

回收授权：  REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host

示例： REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘

注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服 务进程通常会自动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进 程重读授权表：mysql> FLUSH PRIVILEGES;

练习与思考：

导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；
SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M';
(2) 以ClassID为分组依据，显示每组的平均年龄；
SELECT ClassID,avg(age) FROM students GROUP BY ClassID;
(3) 显示第2题中平均年龄大于30的分组及平均年龄；
SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;
(4) 显示以L开头的名字的同学的信息；
SELECT * FROM students WHERE Name LIKE 'L%';
(5) 显示TeacherID非空的同学的相关信息；
SELECT * FROM students WHERE TeacherID IS NOT NULL;
(6) 以年龄排序后，显示年龄最大的前10位同学的信息；
SELECT * FROM students ORDER BY Age DESC LIMIT 10;
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

练习：导入hellodb.sql，以下操作在students表上执行
1、以ClassID分组，显示每班的同学的人数；
SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;
2、以Gender分组，显示其年龄之和；
SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
3、以ClassID分组，显示其平均年龄大于25的班级；
SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25;
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

练习：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩；
2、显示其成绩高于80的同学的名称及课程；
3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
4、显示每门课程课程名称及学习了这门课的同学的个数；

 

思考：
1、如何显示其年龄大于平均年龄的同学的名字？
2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？
3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？
4、统计各班级中年龄大于全校同学平均年龄的同学。 

回到顶部

mysql进阶

存储引擎

SHOW TABLE STATUS
常见的存储引擎：SHOW ENGINES;
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
InnoDB：InnoBase

SHOW ENGINE INNODB STATUS;
MyISAM：

行格式：{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
其它的存储引擎：
CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件；
MRG_MYISAM：将多个MyISAM表合并成的虚拟表；
BLACKHOLE：类似于/dev/null，不真正存储数据；
MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表；
FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口；

并发控制：锁机制：Lock 

事务

事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；
事务日志：
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups
ACID测试：
A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态；
I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；
D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；
自动提交：单语句事务
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0；
手动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务支持savepoints：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
事务隔离级别：
READ-UNCOMMITTED：读未提交 --> 脏读；
READ-COMMITTED：读提交--> 不可重复读；
REPEATABLE-READ：可重复读 --> 幻读；
SERIALIZABLE：串行化；

mysql> SELECT @@session.tx_isolation;
+----------------------------------+
| @@session.tx_isolation |
+----------------------------------+
| REPEATABLE-READ |
+----------------------------------+
查看InnoDB存储引擎的状态信息：
SHOW ENGINE innodb STATUS;

查看授权： SHOW GRANTS [FOR 'user'@'host']
取消授权：REVOKE    priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level    FROM 'user'@'host' [, 'user'@'host'] ...
REVOKE ALL PRIVILEGES, GRANT OPTION   FROM user [, user] ..

MySQL的索引：

索引：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构；
索引的作用：加速查询操作；副作用：降低写操作性能；
表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
某个字段或某些字段：WHERE子句中用到的字段；
索引类型：B+ TREE，HASH，B- TREE
B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；（mysql默认索引）
适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
　　全值匹配：精确匹配某个值；    WHERE COLUMN = 'value';        

　　匹配最左前缀：只精确匹配起头的部分；      WEHRE COLUMN LIKE 'PREFIX%';
匹配范围值：精确匹配某一列，范围匹配另一列；只用访问索引的查询：覆盖索引；
index(Name)
SELECT Name FROM students WHERE Name LIKE 'L%';
不适用B+ TREE索引：
如果查询条件不是从最左侧列开始，索引无效；
index(age,Fname), WHERE Fname='Jerry'; , WHERE age>30 AND Fname='Smith';
不能跳过索引中的某列；
index(name,age,gender)
WHERE name='black' and age > 30;
WHERE name='black' AND gender='F';
如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；WHERE age>30 AND Fname='Smith';
Hash索引：基于哈希表实现，特别适用于值的精确匹配查询；
适用场景：只支持等值比较查询，例如=, IN(), <=>
不用场景：所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引；
索引优点：
　　降低需要扫描的数据量，减少IO次数；
　　可以帮助避免排序操作，避免使用临时表；
　　帮助将随机IO转为顺序IO；
高性能索引策略：
(1) 在WHERE中独立使用列，尽量避免其参与运算； 如，WHERE age+2 > 32 ;
(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估索引选择性：不重复的索引值和数据表的记录总数的比值；
(3) 多列索引：AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
(4) 选择合适的索引列次序：选择性最高的放左侧；

EXPLAIN来分析索引有效性：
EXPLAIN [explain_type] SELECT select_options
输出结果：
id：当前查询语句中，第个SELECT语句的编号；
select_type：查询类型：
table：查询针对的表；
type：关联类型，或称为访问类型，即MySQL如何去查询表中的行
　　ALL：全表扫描；
　　index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
　　range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
　　ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
　　eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；
　　const，system：与某个常数比较，且只返回一行；
possiable_keys：查询中可能会用到的索引；
key：查询中使用的索引；
key_len：查询中用到的索引长度；
ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；
rows：MySQL估计出的为找到所有的目标项而需要读取的行数；
Extra：额外信息
Using index：使用了覆盖索引进行的查询；
Using where：拿到数据后还要再次进行过滤；
Using temporary：使用了临时表以完成查询；
Using filesort：对结果使用了一个外部索引排序；

回到顶部

mysql高级

日志

• 查询日志：general_log
• 慢查询日志：log_slow_queries
• 错误日志：log_error， log_warnings
• 二进制日志：binlog
• 中继日志：relay_log
• 事务日志：innodb_log

1、查询日志

记录查询语句，日志存储位置：FILE  

表：table (mysql.general_log)
general_log={ON|OFF}
general_log_file=HOSTNAME.log 
log_output={FILE|TABLE|NONE}

2、慢查询日志

慢查询：运行时间超出指定时长的查询；  long_query_time
存储位置：文件：FILE        

表：TABLE，mysql.slog_log
log_slow_queries={ON|OFF}
slow_query_log={ON|OFF}
slow_query_log_file=
log_output={FILE|TABLE|NONE}
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_rate_limit
log_slow_verbosity

3、错误日志

记录如下几类信息：
(1) mysqld启动和关闭过程中输出的信息； 
(2) mysqld运行中产生的错误信息； 
(3) event scheduler运行时产生的信息；
(4) 主从复制架构中，从服务器复制线程启动时产生的日志；

log_error=
/var/log/mariadb/mariadb.log|OFF
log_warnings={ON|OFF｝

4、二进制日志

用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合；
功用：“重放”
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：语句；
ROW：行；
MIXED：混编；
查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件：
SHOW MASTER STATUS；
查看二进制 日志文件中的事件：
SHOW BINLOG EVENTS    [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服务器变量：
log_bin=/PATH/TO/BIN_LOG_FILE
只读变量；
session.sql_log_bin={ON|OFF}
控制某会话中的“写”操作语句是否会被记录于日志文件中；
max_binlog_size=1073741824
sync_binlog={1|0}

查询日志命令：
mysqlbinlog：
　　YYYY-MM-DD hh:mm:ss
　　--start-datetime=
　　--stop-datetime=
　　-j, --start-position=#
　　--stop-position=#
　　--user, --host, --password
二进制日志事件格式：

# at 553
#160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;
事件的起始位置：# at 553
事件发生的日期时间：#160831 9:56:08
事件发生的服务器id：server id 1
事件的结束位置：end_log_pos 624
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID： thread_id=2 
语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
错误代码：error_code=0
设定事件发生时的时间戳：SET TIMESTAMP=1472608568/*!*/;
事件内容：BEGIN

中继日志：
从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
事务日志：
事务型存储引擎innodb用于保证事务特性的日志文件

MySQL：备份和恢复（数据）

时间点恢复：binary logs;
备份类型：
全量备份、增量备份、差异备份：
　　完全备份
　　增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
　　差异备份：仅备份自上一次完全备份以来变量的那部数据；
物理备份、逻辑备份：
　　物理备份：复制数据文件进行的备份；
　　逻辑备份：从数据库导出数据另存在一个或多个文件中；
根据数据服务是否在线：
　　热备：读写操作均可进行的状态下所做的备份；
　　温备：可读但不可写状态下进行的备份；
　　冷备：读写操作均不可进行的状态下所做的备份；

备份策略：
全量+差异 + binlogs
全量+增量 + binlogs
备份手段：物理、逻辑

备份工具：
　　mysqldump：mysql服务自带的备份工具；逻辑备份工具；   完全、部分备份；
　　　　InnoDB：热备；
　　　　MyISAM：温备；
　　cp/tar
　　l　　vm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
　　　　注意：不能仅备份数据文件；要同时备份事务日志；
　　　　前提：要求数据文件和事务日志位于同一个逻辑卷；
　　xtrabackup：由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
　　mysqlhotcopy ：（几乎冷备，没人用）
　　select：
　　　　备份：SELECT cluase INTO OUTFILE 'FILENAME';
　　　　恢复：CREATE TABLE
　　　　导入：LOAD DATA
InnoBase：Innodb --> XtraDB, Innobackup --> Xtrabackup

备份策略：
　　xtrabackup：全量+差异+binlog 或全量+增量+binlog
　　mysqldump：全量+binlog
mysqldump：

　　Usage: 
　　mysqldump [OPTIONS] database [tables] # 备份单库，可以只备份其中的一部分表（部分备份）；
　　OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] # 备份多库；
　　OR mysqldump [OPTIONS] --all-databases [OPTIONS] # 备份所有库；

　　MyISAM存储引擎：支持温备，备份时要锁定表；
　　　　-x, --lock-all-tables：锁定所有库的所有表，读锁；
　　　　-l, --lock-tables：锁定指定库所有表；
　　InnoDB存储引擎：支持温备和热备；
　　　　--single-transaction：创建一个事务，基于此快照执行备份；
其它选项：
　　-R, --routines：备份指定库的存储过程和存储函数；
　　--triggers：备份指定库的触发器；
　　-E, --events：

基于lvm2的备份：

前提：要求数据文件和事务日志位于同一个逻辑卷；

(1) 请求锁定所有表；
mysql> FLUSH TABLES WITH READ LOCK;

(2) 记录二进制文件事件位置；
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;

mysql -e 'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE

(3) 创建快照卷
lvcreate -L # -s -p r - SNAM-NAME /dev/VG-NAME/LV-NAME 

(4) 释放锁
mysql> UNLOCK TABLES

(5) 挂载快照卷，并执行备份，备份完成后删除快照卷；

(6) 周期性备份二进制日志；

Innobackup --> Xtrabackup
Xtrabackup：
　　MyISAM：温备，不支持增量备份；
　　InnoDB：热备，增量；
　　物理备份，速率快、可靠；备份完成后自动校验备份结果集是否可用；还原速度快；
　　Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
　　备份 --> 应用日志 --> 还原
　　应用日志：--apply-log
　　还原：--copy-back
完全备份：
　　完全+binlog(总结)：
　　备份：innobackupex --user=    --password=   --host= 　　 /PATH/TO/BACKUP_DIR
　　准备：innobackupex   --apply-log    /PATH/TO/BACKUP_DIR
　　恢复：innobackupex --copy-back
　　注意：--copy-back需要在mysqld主机本地进行，mysqld服务不能启动；且innodb_log_file_size可能要重新设定；

MySQL Replication：

Master/Slave
　　Master: write/read
　　Slaves: read

配置：
主服务器：

　　配置文件my.cnf
　　server_id=#
　　log_bin=log-bin
　　启动服务：
　　mysql> GRANT REPLICATION   SLAVE, REPLICATION  CLIENT ON  *.* TO   'USERNAME'@'HOST'   IDENTIFIED  BY  'YOUR_PASSWORD';
　　mysql> FLUSH PRIVILEGES;

从服务器：　　

配置文件my.cnf
　　server_id=#
　　relay_log=relay-log 
　　read_only=ON
　　启动服务：
　　mysql> CHANGE  MASTER TO     MASTER_HOST='HOST',   MASTER_USER='USERNAME' ,    MASTER_PASSWORD='YOUR_PASSWORD', 　　　　MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
　　mysql> START SLAVE    [  IO_THREAD    |    SQL_THREAD   ];
　　mysql> SHOW SLAVE STATUS;

主主复制：互为主从：两个节点各自都要开启binlog和relay log；
1、数据不一致；
2、自动增长id；
定义一个节点使用奇数id
auto_increment_offset=1
auto_increment_increment=2
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
配置：
1、server_id必须要使用不同值；
2、均启用binlog和relay log；
3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；
服务启动后执行如下两步：
4、都授权有复制权限的用户账号；
5、各把对方指定为主节点；

复制时应该注意的问题：
1、从服务设定为“只读”；
在从服务器启动read_only，但仅对非SUPER权限的用户有效；
阻止所有用户：
mysql> FLUSH TABLES WITH READ LOCK;
2、尽量确保复制时的事务安全
在master节点启用参数：
sync_binlog = ON
如果用到的是InnoDB存储引擎：
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
3、从服务器意外中止时尽量避免自动启动复制线程
4、从节点：设置参数
sync_master_info=ON
sync_relay_log_info=ON

半同步复制
支持多种插件：/usr/lib64/mysql/plugins/
需要安装方可使用：
mysql> INSTALL PLUGIN   plugin_name   SONAME   'shared_library_name'; 

半同步复制：semisync_master.so，semisync_slave.so
主节点：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master';

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON;    

从节点：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; 
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+    

MariaDB [mydb]> STOP SLAVE IO_THREAD;
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
MariaDB [mydb]> START SLAVE IO_THREAD,SQL_THREAD;

SHOW GLOBAL VARIABLES  LIKE '%rpl%';

SHOW GLOBAL STATUS LIKE '%rpl%';

回到顶部

mysql优化

mysql复制过滤器与监控维护

仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；
有两种实现思路：
(1) 主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；
问题：其它库的time-point recovery将无从实现； 
binlog_do_db=
binlog_ignore_db=

(2) 从服务器

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；
问题：网络IO和磁盘IO；
Replicate_Do_DB=
Replicate_Ignore_DB=

Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=    

复制的监控和维护：
(1) 清理日志：PURGE
PURGE    { BINARY | MASTER }   LOGS   { TO   'log_name' | BEFORE datetime_expr };
(2) 复制监控
MASTER:  SHOW MASTER STATUS;  SHOW BINLOG EVENTS;  SHOW BINARY LOGS;
SLAVE:   SHOW SLAVE STATUS;　　

(3) 确定主从节点数据是否一致,通过表的CHECKSUM检查,使用percona-tools中pt-table-checksum；
(4) 主从数据不一致时的修复方法：重新复制；

主从复制的读写分离

主从复制的读写分离：
ProxySQL
http://www.proxysql.com/, ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB).
https://github.com/sysown/proxysql/releases

ProxySQL配置示例：

datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"  
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/mysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{
address = "172.18.64.7" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = "172.18.64.107"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = "172.18.64.106"
port = 3306
hostgroup = 1
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
}
)
mysql_users:
(
{
username = "heiye"
password = "centos"
default_hostgroup = 0
max_connections=1000
default_schema="mydb"
active = 1
}
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=0
reader_hostgroup=1
}
)

maxscale配置示例：

[maxscale]
threads=auto

[server1]
type=server
address=172.18.64.7
port=3306
protocol=MySQLBackend

[server2]
type=server
address=172.18.64.107
port=3306
protocol=MySQLBackend

[server3]
type=server
address=172.18.64.106
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=201221DC8FC5A49EA50F417A939A1302
monitor_interval=1000

[Read-Only Service]
type=service
router=readconnroute
servers=server2,server3
user=maxscale
passwd=201221DC8FC5A49EA50F417A939A1302
router_options=slave

[Read-Write Service]
type=service
router=readwritesplit
servers=server1
user=maxscale
passwd=201221DC8FC5A49EA50F417A939A1302
max_slave_connections=100%

[MaxAdmin Service]
type=service
router=cli

[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6602

mysql常用优化参数

InnoDB存储引擎相关的参数：
　　innodb_buffer_pool_size：索引、数据、插入数据时的缓冲区，一般为专用服务器70-80%；
　　如果数据集本身较小，可根据数据变化幅度及规划的时长也设定合理值，比预估的目标值略大；
　　innodb_buffer_pool_instances： buffer_pool的区段（实例）数量；

　　innodb_file_per_table：innodb的诸多高级特性都依赖于此参数；
　　innodb_read_io_threads: 
　　innodb_write_io_threads
　　文件读写的io线程数；可根据并发量和CPU核心数适当调整；
　　innodb_open_files：innodb可打开的文件数量上限；

　　innodb_flush_method:
　　innodb_thread_concurrency=
　　skip_name_resolve =  ON
　　max_connections 

事务日志： 　  
　　innodb_log_files_in_group：一组的日志文件数量，至少2个；
　　innodb_log_file_size：日志文件大小，默认为5M；建议调大此值；
　　innodb_flush_logs_at_trx_commit：
　　　　0：log buffer（内存）每秒一次同步到log file中，且同时会进行log file到data file的同步操作；
　　　　1：每次提交时，log buffer同步到log file，同时进行log file到data file的同步操作；
　　　　2：每次提交时，log buffer同步到log file，但不会同时进行log file到data file的同步操作；
　　建议：关闭autocommit，而后将此值设置为1或2；