mysql之扩展

r囧r小猫 2022-05-24 09:48 140阅读 0赞

# 一，索引（开发） #

## MySQL索引 ##

========================================================  
创建索引  
创建表时创建索引  
CREATE在已存在的表上创建索引  
ALTER TABLE在已存在的表上创建索引  
查看并测试索引  
删除索引  
  
一、索引简介  
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，  
尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。  
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。  
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。  
  
二、索引的分类  
普通索引  
唯一索引  
全文索引  
**单列索引**  
多列索引  
空间索引  
  
  
三、准备实验环境

# 1. 准备表 #

![20180524221945757][]

# 2. 创建存储过程，实现批量插入记录 #

mysql> use school  
mysql> delimiter $$  
mysql> create procedure autoinsert1()  
\-> BEGIN  
\-> declare i int default 1;  
\->  while(i<200000) do  
\-> insert into school.t2 values(i,'ccc');  
\-> set i=i+1;  
\-> end while;  
\-> END$$  
mysql> delimiter ;  
  
查看存储过程的基本信息  
![20180524222341464][]  
  
查看存储过程的详细信息  
![20180524222507356][]  
  
3. 调用存储过程  
mysql> call autoinsert1();  
  
四、创建索引  
 ＝＝＝创建表时  
语法：  
CREATE TABLE 表名 (  
字段名1 数据类型 \[完整性约束条件…\],  
字段名2 数据类型 \[完整性约束条件…\],  
\[UNIQUE | FULLTEXT | SPATIAL \] INDEX | KEY  
\[索引名\] (字段名\[(长度)\] \[ASC |DESC\])   
);  
  
创建普通索引示例：

## CREATE TABLE department10 (   dept\_id INT,   dept\_name VARCHAR(30) ,   comment VARCHAR(50),   INDEX index\_dept\_name (dept\_name)   );    ##

创建唯一索引示例：  
CREATE TABLE department11 (  
dept\_id INT,  
dept\_name VARCHAR(30) ,  
comment VARCHAR(50),  
UNIQUE INDEX  index\_dept\_name (dept\_name)  
);  
  
  
创建全文索引示例：  
CREATE TABLE department12 (  
dept\_id INT,  
dept\_name VARCHAR(30) ,  
comment VARCHAR(50),  
log text,  
FULLTEXT INDEX index\_log (log)  
);  
  
  
创建多列索引示例：  
CREATE TABLE department13 (  
dept\_id INT,  
dept\_name VARCHAR(30) ,  
comment VARCHAR(50),  
INDEX index\_dept\_name\_comment (dept\_name, comment)  
);  
  
  
===CREATE在已存在的表上创建索引  
语法：  
CREATE  \[UNIQUE | FULLTEXT | SPATIAL \] INDEX  索引名  
ON 表名 (字段名\[(长度)\] \[ASC |DESC\]) ;

## 创建普通索引示例：   CREATE INDEX index\_dept\_name ON department (dept\_name); ##

创建唯一索引示例：  
CREATE UNIQUE INDEX  index\_dept\_name ON department (dept\_name);  
  
创建全文索引示例：  
CREATE FULLTEXT INDEX  index\_dept\_name ON department (dept\_name);  
  
创建多列索引示例：  
CREATE INDEX index\_dept\_name\_ comment ON department (dept\_name, comment);  
  
  
===ALTER TABLE在已存在的表上创建索引  
语法：  
ALTER TABLE 表名 ADD \[UNIQUE | FULLTEXT | SPATIAL \] INDEX  
索引名 (字段名\[(长度)\] \[ASC |DESC\]) ;  
  
创建普通索引示例：  
ALTER TABLE department ADD INDEX index\_dept\_name (dept\_name);  
  
创建唯一索引示例：  
ALTER TABLE department ADD UNIQUE INDEX index\_dept\_name (dept\_name);  
  
  
创建全文索引示例：  
ALTER TABLE department ADD FULLTEXT INDEX index\_dept\_name (dept\_name);  
  
创建多列索引示例：  
ALTER TABLE department ADD  INDEX index\_dept\_name\_comment (dept\_name, comment);  
  
  
  
四、管理索引  
查看索引  
SHOW CRETAE TABLE 表名\\G  
  
测试示例  
EXPLAIN SELECT \* FROM department WHERE dept\_name=‘hr’;

## 删除索引   show create table employee6;   DROP INDEX 索引名 ON 表名;    ##

## 索引测试实验： ##

mysql> create table school.t2(id int,name varchar(30));  
Query OK, 0 rows affected (1.33 sec)  
  
mysql> desc school.t2;  
\+-------+-------------+------+-----+---------+-------+  
| Field | Type | Null | Key | Default | Extra |  
\+-------+-------------+------+-----+---------+-------+  
| id | int(11) | YES | | NULL | |  
| name | varchar(30) | YES | | NULL | |  
\+-------+-------------+------+-----+---------+-------+  
2 rows in set (0.00 sec)  
  
mysql> delimiter $$  
mysql> create procedure autoinsert1()  
\-> BEGIN  
\-> declare i int default 1;  
\-> while(i<100000)do  
\-> insert into school.t2 values(i,'ccc');  
\-> set i=i+1;  
\-> end while;  
\-> END$$  
mysql> use school  
Database changed  
mysql> delimiter ;  
  
mysql> call autoinsert1();

## 未创建索引 ##

mysql> explain select \* from school.t2 where id=20000;  
\+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
\+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+  
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL |  44848 | Using where |  
\+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)  
  
mysql> create index index\_id on school.t2(id);  
Query OK, 0 rows affected (0.91 sec)  
Records: 0 Duplicates: 0 Warnings作用: 0  
  
mysql> explain select \* from school.t2 where id=20000;  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+  
| 1 | SIMPLE | t2 | ref | index\_id | index\_id | 5 | const | 1 | Using where |  
\+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+  
1 row in set (0.00 sec)  
  
SHOW CREATE TABLE 表名\\G  
EXPLAIN： 命令的作用是查看查询优化器如何决定执行查询  
  
花费时间比较：  
创建索引前  
mysql> select \* from school.t2 where id=20000;  
\+-------+------+  
| id | name |  
\+-------+------+  
| 20000 | ccc |  
\+-------+------+  
1 row in set (0.03 sec)  
  
创建索引后  
mysql> create index index\_id on school.t2(id);  
Query OK, 0 rows affected (0.39 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
mysql> select \* from school.t2 where id=20000;  
\+-------+------+  
| id | name |  
\+-------+------+  
| 20000 | ccc |  
\+-------+------+  
1 row in set (0.00 sec)  
========================================================

# 二，视图（开发） #

## MySQL视图VIEW ##

========================================================  
视图简介  
创建视图  
查看视图  
修改视图  
通过视图操作基表  
删除视图  
  
一、视图简介  
MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行  
数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所  
引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，MySQL视图的作用类似于筛选。  
定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任  
何限制，通过它们进行数据修改时的限制也很少。  
视图是存储在数据库中的SQL查询语句，它主要出于两种原因： **安全原因**，视图可以隐藏一些数据，如：  
一些敏感的信息，另一原因是可以 **使复杂的查询易于理解和使用**。  
  
  
二、创建视图  
语法一：  
CREATE \[ALGORITHM = \{UNDEFINED | MERGE | TEMPTABLE\} \]  
VIEW 视图名 \[(字段1,字段2…)\]  
AS SELECT语句  
\[WITH \[CASCADED | LOCAL\] CHECK OPTION \];  
  
语法二：  
CREATE VIEW 视图名  
AS SELECT语句;  
  
示例1：创建视图案例 (单表)  
USE mysql  
  
  
示例2：创建视图案例 (多表)  
mysql> create database shop;  
Query OK, 1 row affected (0.21 sec)  
  
mysql> use shop  
Database changed  
  
mysql> create table product(  
\-> id int unsigned auto\_increment primary key not null,  
\->  name varchar(60) not null,  
\-> price double not null  
\-> );  
mysql> insert into product(name,price) values  
\-> ('pear',4.3),  
\-> ('orange',6.5),  
\-> ('apple',5.0)  
\-> ;  
  
mysql> create table purchase(  
\-> id int unsigned auto\_increment primary key not null,  
\-> name varchar(60) not null,  
\-> quantity int not null default 0,  
\-> gen\_time datetime not null  
\-> );  
mysql> insert into purchase(name,quantity,gen\_time) values  
\-> ('apple',7,now()),  
\-> ('pear',10,now())  
\-> ;  
  
  
mysql> create view purchase\_detail  
\-> as  select  
\-> product.name, product.price,   
\->  purchase.quantity,   
\->  product.price \* purchase.quantity as total\_value  
\-> from product,purchase  
\-> where product.name = purchase.name;  
  
  
mysql> select \* from purchase\_detail;  
\+-------+-------+----------+-------------+  
| name | price | quantity | total\_value |  
\+-------+-------+----------+-------------+  
| pear | 4.3 | 10 | 43 |  
| apple | 5 | 7 | 35 |  
\+-------+-------+----------+-------------+  
2 rows in set (0.04 sec)  
  
  
mysql> insert into purchase(name,quantity,gen\_time) values ('orange',20,now());  
Query OK, 1 row affected (0.01 sec)  
  
mysql> select \* from purchase\_detail;  
\+--------+-------+----------+-------------+  
| name | price | quantity | total\_value |  
\+--------+-------+----------+-------------+  
| apple | 5 | 7 | 35 |  
| pear | 4.3 | 10 | 43 |  
| orange | 6.5 | 20 | 130 |  
\+--------+-------+----------+-------------+  
3 rows in set (0.00 sec)  
  
  
三、查看视图  
1. SHOW TABLES 查看视图名  
SHOW TABLES;  
  
2. SHOW TABLE STATUS   
示例：查看数据库 mysql中视图及所有表详细信息  
SHOW TABLE STATUS FROM mysql \\G  
  
示例：查看数据库 mysql中视图名view\_user详细信息  
SHOW TABLE STATUS FROM mysql  
LIKE 'view\_user' \\G  
  
3. SHOW CREATE VIEW  
示例：查看视图定义信息  
SHOW CREATE VIEW view\_user\\G  
  
4. DESCRIBE  
示例：查看视图结构  
DESC view\_user ;  
  
  
四、修改视图  
方法一：删除后新创建  
DROP VIEW view\_user ;  
CREATE VIEW view\_user  
AS SELECT user,host FROM mysql.user;  
SELECT \* FROM view\_user;  
  
方法二：ALTER修改视图  
语法：  
ALTER VIEW 视图名  
AS SELECT语句;  
示例：  
ALTER VIEW view\_user  
AS SELECT user,password FROM mysql.user;  
  
  
五、通过视图操作基表  
查询数据SELECT  
SELECT \* FROM view\_user;  
  
更新数据UPDATE  
  
删除数据DELETE  
  
  
六、删除视图  
语法：  
DROP VIEW view\_name \[,view\_name\]…;  
  
示例：  
USE mysql;  
DROP VIEW view\_user ;  
  
========================================================

# 三，触发器（开发） #

## MySQL触发器Triggers ##

========================================================  
触发器简介  
创建触发器  
查看触发器  
删除触发器  
触发器案例  
  
一、触发器简介  
触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，  
比如当对一个表进行操作（insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整  
性约束和业务规则等。  
  
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发  
器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。  
  
  
二、创建Trigger  
语法：  
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件  
ON 表名 FOR EACH ROW  
BEGIN  
触发器程序体;  
END  
  
**<触发器名称>**  最多64个字符，它和MySQL中其他对象的命名方式一样  
**\{ BEFORE | AFTER \}** 触发器时机  
**\{ INSERT | UPDATE | DELETE \}** 触发的事件  
**ON <表名称>** 标识建立触发器的表名，即在哪张表上建立触发器  
**FOR EACH ROW** 触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行  
执行一次动作，而不是对整个表执行一次  
**<触发器程序体>**  要触发的SQL语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能  
  
example1  
1. 创建表  
mysql> create table student(  
\-> id int unsigned auto\_increment primary key not null,  
\-> name varchar(50)  
\-> );  
mysql> insert into student(name) values('jack');  
  
mysql> create table student\_total(total int);  
mysql> insert into student\_total values(1);  
  
2. 创建触发器student\_insert\_trigger  
mysql> delimiter $$  
mysql> create trigger student\_insert\_trigger after insert  
\-> on student for each row  
\-> BEGIN  
\-> update student\_total set total=total+1;  
\-> END$$  
mysql> delimiter ;  
  
3. 创建触发器student\_delete\_trigger  
mysql> delimiter $$  
mysql> create trigger student\_delete\_trigger after delete  
\-> on student for each row  
\-> BEGIN  
\-> update student\_total set total=total-1;  
\-> END$$  
mysql> delimiter ;  
  
  
三、查看触发器  
1. 通过SHOW TRIGGERS语句查看  
SHOW TRIGGERS\\G  
  
2. 通过系统表triggers查看  
USE information\_schema  
SELECT \* FROM triggers\\G  
SELECT \* FROM triggers WHERE TRIGGER\_NAME='触发器名称'\\G  
  
  
四、删除触发器  
1. 通过DROP TRIGGERS语句删除  
DROP TRIGGER 解发器名称  
  
五、触发器案例  
example2

# 创建表tab1 #

DROP TABLE IF EXISTS tab1;  
CREATE TABLE tab1(  
id int primary key auto\_increment,  
name varchar(50),  
sex enum('m','f'),  
age int  
);

# 创建表tab2 #

DROP TABLE IF EXISTS tab2;  
CREATE TABLE tab2(  
id int primary key auto\_increment,  
name varchar(50),  
salary double(10,2)  
);

## 触发器tab1\_after\_delete\_trigger ##

作用：tab1表删除记录后，自动将tab2表中对应记录删除  
mysql> \\d $$  
mysql> create trigger tab1\_after\_delete\_trigger  
after delete on tab1  
for each row  
BEGIN  
delete from tab2 where name= old .name;  
delete from tab2 where id= old.id; //删除必须通过primary key  
END$$

## 触发器tab1\_after\_update\_trigger ##

作用：当tab1更新后，自动更新tab2  
mysql> create trigger tab11\_after\_update\_trigger  
after update on tab1  
for each row  
BEGIN  
update tab111 set id=new.id,name= new.name,sex=new.sex,age=new.age //所有字段  
where id= old.id;  
END$$  
Query OK, 0 rows affected (0.19 sec)

## 触发器tab1\_after\_insert\_trigger ##

作用：当tab1增加记录后，自动增加到tab2  
mysql> create trigger tab1\_after\_insert\_trigger  
after insert on tab1  
for each row  
BEGIN  
insert into tab2(name,salary) values( new.name,5000);  
END$$  
Query OK, 0 rows affected (0.19 sec)  
  
  
example3  
mysql> create table t1(  
\-> id int primary key auto\_increment,  
\-> name varchar(50),  
\-> salary float(10,2)  
\-> );  
Query OK, 0 rows affected (0.63 sec)  
  
mysql> create table t2(  
\-> id int primary key auto\_increment,  
\-> total\_num int, //员工总人数  
\-> total\_salary float(10,2) //员工薪水总和  
\-> );  
Query OK, 0 rows affected (0.64 sec)  
  
mysql> \\d $$  
mysql> create trigger t1\_after\_insert\_trigger  
\-> after insert on t1  
\-> for each row  
\-> BEGIN  
\-> update t2 set total\_num=total\_num+1, total\_salary=total\_salary+ new.salary;  
\-> END$$  
Query OK, 0 rows affected (0.54 sec)  
  
mysql>  insert into t2 values(0,0); //初始值  
Query OK, 1 row affected (0.10 sec)  
  
mysql> select \* from t2;  
\+-----------+--------------+  
| total\_num | total\_salary |  
\+-----------+--------------+  
| 0 | 0.00 |  
\+-----------+--------------+  
1 row in set (0.00 sec)

### update   delete ###

# 四，存储过程与函数（开发）    #

## procedure and function ##

==================================================  
  
一、概述：  
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。  
存储过程和函数的区别：  
• 函数必须有返回值，而存储过程没有。  
• 存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN  
**优点：**  
• 存储过程只在创建时进行编译；  
而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。  
• 简化复杂操作，结合事务一起封装。  
• 复用性好  
• 安全性高，可指定存储过程的使用权。  
**说明：**  
并发量少的情况下，很少使用存储过程。  
并发量高的情况下，为了提高效率，用存储过程比较多。  
  
二、创建与调用  
创建存储过程语法 ：  
create procedure sp\_name(参数列表)  
\[特性...\]过程体  
  
存储过程的参数形式：\[IN | OUT | INOUT\]参数名 类型  
IN 输入参数  
OUT 输出参数  
INOUT 输入输出参数  
  
 delimiter $$  
create procedure 过程名(形式参数列表)  
begin  
SQL语句  
end $$  
delimiter ;  
  
调用：  
call 存储过程名(实参列表)

## 存储过程三种参数类型：IN, OUT, INOUT ##

=================== **NONE**========================  
mysql> \\d $$  
mysql> create procedure p1()  
\-> begin  
\-> select count(\*) from mysql.user;  
\-> end$$  
Query OK, 0 rows affected (0.51 sec)  
  
mysql> \\d ;  
mysql> call p1();  
  
mysql> create table school.t1(id int,cc varchar(100));  
mysql> delimiter $$  
mysql> create procedure autoinsert1()  
\-> BEGIN  
\-> declare i int default 1;  
\-> while(i<= **20000**) do  
\-> insert into school.t1 values(i,md5(i));  
\-> set i=i+1;  
\->  end while;  
\-> END$$  
mysql> delimiter ;  
  
==================== **IN**==========================  
mysql> create procedure autoinsert2( IN **a** int)  
\-> BEGIN  
\-> declare i int default 1;  
\-> while(i<= **a**)do  
\-> insert into school.t1 values(i,md5(i));  
\-> set i=i+1;  
\-> end while;  
\-> END$$  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> call autoinsert1( 10);  
Query OK, 1 row affected (1.10 sec)  
  
mysql> set @num=20;  
mysql> select @num;  
\+------+  
| @num |  
\+------+  
| 20 |  
\+------+  
1 row in set (0.00 sec)  
  
mysql> call autoinsert1( @num);  
mysql> select @a;  
\+------+  
| @a |  
\+------+  
| NULL |  
\+------+  
1 row in set (0.00 sec)  
  
  
==================== **OUT**=======================  
mysql> delimiter $$  
mysql> CREATE PROCEDURE p2 ( OUT param1 INT)  
\-> BEGIN  
\-> SELECT COUNT(\*) INTO  param1 FROM mysql.user;  
\-> END$$  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> delimiter ;  
  
mysql> select @a;  
\+------+  
| @a |  
\+------+  
| NULL |  
\+------+  
1 row in set (0.00 sec)  
  
mysql> CALL p2( @a);  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> SELECT @a;  
\+------+  
| @a |  
\+------+  
| 3 |  
\+------+  
  
=================== **IN 和 OUT**=====================

### 作用：统计指定部门的员工数 ###

mysql> create procedure count\_num( IN p1 varchar(50), OUT  p2 int)  
\-> BEGIN  
\-> select count(\*) into p2 from company.employee5  
\-> where post= p1;  
\-> END$$  
Query OK, 0 rows affected (0.00 sec)  
mysql> \\d ;  
  
  
mysql> call count\_num(' hr', @a);  
  
mysql>select @a;

### 作用：统计指定部门工资超过例如5000的总人数 ###

mysql> create procedure count\_num( IN p1 varchar(50), IN p2 float(10,2), OUT p3 int)  
\-> BEGIN  
\-> select count(\*) into  p3 from employee5  
\-> where post= p1 and salary=> p2;  
\-> END$$  
Query OK, 0 rows affected (0.00 sec)  
mysql> \\d ;  
  
mysql> call count\_num('hr',5000,@a);  
  
  
==================== **INOUT**======================  
mysql> create procedure proce\_param\_inout( inout p1 int)  
\-> begin  
\-> if ( p1 is not null) then  
\-> set p1= p1\+1;  
\-> else  
\-> select 100 into  p1; //set p1=100;  
\-> end if;  
\-> end$$  
Query OK, 0 rows affected (0.00 sec)  
  
  
mysql> select @h;  
\+------+  
| @h |  
\+------+  
| NULL |  
\+------+  
1 row in set (0.00 sec)  
  
mysql> call proce\_param\_inout( @h);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> select @h;  
\+------+  
| @h |  
\+------+  
| 100 |  
\+------+begin  
1 row in set (0.00 sec)  
  
mysql> call proce\_param\_inout(@h);  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select @h;  
\+------+  
| @h |  
\+------+  
| 101 |  
\+------+  
1 row in set (0.00 sec)

## FUNCTION函数 ##

=================================================  
mysql> CREATE FUNCTION hello ( s CHAR(20))  
\-> RETURNS CHAR(50)  RETURN CONCAT('Hello, ', s ,'!');  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select hello('tianyun');  
\+------------------+  
| hello('tianyun') |  
\+------------------+  
| Hello, tianyun! |  
\+------------------+  
  
root@(company)> select hello('tianyun') return1;  
\+-----------------+  
| return1 |  
\+-----------------+  
| Hello, tianyun! |  
\+-----------------+  
1 row in set (0.00 sec)  
  
  
mysql> create function name\_from\_employee( x int)  
\-> returns varchar(50)  
\-> BEGIN  
\-> return ( select emp\_name from employee  
\-> where emp\_id=x);  
\-> END$$  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select name\_from\_employee( 3);  
  
mysql> select \* from employee where emp\_name= name\_from\_employee(1 );  
\+--------+----------+------+------------+------------+-----------------+---------+--------+--------+  
| emp\_id | emp\_name | sex | hire\_date | post | job\_description | salary | office | dep\_id |  
\+--------+----------+------+------------+------------+-----------------+---------+--------+--------+  
| 1 | jack  | male | 2019-02-02 | instructor | teach | 5000.00 | 501 | 100 |  
\+--------+----------+------+------------+------------+-----------------+---------+--------+--------+  
1 row in set (0.00 sec)  
  
==============================================  
创建函数的语法：  
create function 函数名(参数列表) returns 返回值类型  
\[特性...\] 函数体  
函数的参数形式：参数名 类型  
  
delimiter $$  
create function 函数名(参数列表) returns 返回值类型  
begin  
有效的SQL语句  
end$$  
delimiter ;  
调用：  
select 函数名(实参列表)  
  
delimiter $$  
create function fun1(str char(20)) returns char(50)  
return concat("hello",str,"!");  
$$  
delimiter ;  
  
select fun1(' function');  
  
存储过程与函数的维护：  
show create procedure pr1 \\G;  
show create function pr1 \\G;  
  
show \{procedure|function\} status \{like 'pattern'\}  
  
drop \{procedure|function\} \{if exists\} sp\_name  
  
mysql变量的术语分类：  
1.用户变量：以"@"开始，形式为"@变量名"，由客户端定义的变量。  
用户变量跟mysql客户端是绑定的，设置的变量只对当前用户使用的客户端生效，当用户断开连接时，所有变量会自动释放。  
2.全局变量：定义时如下两种形式，set GLOBAL 变量名  或者  set @@global.变量名  
对所有客户端生效，但只有具有super权限才可以设置全局变量。  
3.会话变量：只对连接的客户端有效。  
4.局部变量：设置并作用于begin...end语句块之间的变量。  
declare语句专门用于定义局部变量。而set语句是设置不同类型的变量，包括会话变量和全局变量  
语法：declare 变量名\[...\] 变量类型 \[default 值\]  
declare定义的变量必须写在复合语句的开头，并且在任何其它语句的前面。  
  
变量的赋值：  
直接赋值： set 变量名=表达式值或常量值\[...\];  
  
用户变量的赋值：  
1、set 变量名=表达式或常量值;  
2、也可以将查询结果赋值给变量(要求查询返回的结果只能有一行)  
例：set 列名 into 变量名 from 表名 where 条件;  
3、select 值 into @变量名；  
客户端变量不能相互共享。  
  
delimiter $$  
create procedure pr2()  
begin  
declare xname varchar(50);  
declare xdesc varchar(100);  
set xname="caiwu";  
set xdesc="accouting";  
insert into dept(name,desc) values(xname,xdesc);  
end$$  
delimiter ;  
call pr2();  
  
delimiter $$  
create procedure pr3(in x int,in y int,out sum int)  
begin  
set sum=x+y;  
end$$  
delimiter ;  
call pr3(3,4,@sum);  
select @sum;  
  
delimiter //  
create function fun6(x int,y int) returns int  
begin  
declare sum int;  
set sum=x+y;  
return sum;  
end//  
delimiter ;  
select fun6(4,3);  
  
delimiter //  
create function fun\_add\_rand(in\_int int )  
RETURNS int  
BEGIN  
declare i\_rand int;  
declare i\_return int;  
set i\_rand=floor(rand()\*100);  
set i\_return = in\_int + i\_rand;  
return i\_return;  
END;  
//  
========================================================

# 五，ftp虚拟帐号（扩展） #

## 一，ftp+mysql虚拟机帐号 ##

虚拟账户(对系统来说是假账户)  
ftp本身实现：使用文本文件存储账户名和密码  
ftp+mysql实现: 利用数据库存储用户名和密码  
  
额外技术:pam插入式验证模块  
  
1、安装软件  
安装主程序：  
\#yum install mysql-server mysql-devel vsftpd pam pam-devel -y  
  
安装pam\_mysql模块：  
\# tar zxvf pam\_mysql-0.7RC1.tar.gz  
\# ./configure --with-openssl && make && make install  
  
2、拷贝pam\_mysql模块：  
\# cp /lib/security/pam\_mysql.so /lib64/security/  
\# service mysqld start  
  
3、在mysql里面创建数据库、表以及在表里面插入用户名和密码。  
mysql> create database wuhan;  
mysql> use wuhan;  
mysql> create table ftp (id int not null primary key auto\_increment, name nvarchar(30),  
passwd nvarchar(20));  
mysql> insert into ftp values ('','user1','123'),('','user2','123'),('','user3','123');  
  
4、授权virtual只能读wuhan数据库的ftp表  
mysql> grant select on wuhan.ftp to virtual@localhost identified by '123';  
  
5、建立虚拟用户所需的pam配置文件。  
\#vim /etc/pam.d/vsftpd.virt  
auth required pam\_mysql.so user=virtual passwd=123 host=localhost db=wuhan table=ftp usercolumn=name passwdcolumn=passwd crypt=0  
  
account required pam\_mysql.so user=virtual passwd=123 host=localhost db=wuhan table=ftp usercolumn=name passwdcolumn=passwd crypt=0  
  
pam\_mysql.so //用于linux连接mysql的库文件  
user=virtual //用于访问Mysql的用户。  
passwd=123 //对应的是用户的密码。  
host=localhost //代表mysql在本机。  
db=wuhan //指定在mysql上的数据库。  
table=ftp //指定mysql上存放用户的表格。  
usercolumn=name //指定存储用户名的列。  
passwdcolumn=passwd //指定存储密码的列。  
crypt=0  
crypt=0 //crypt=0: 明文密码  
//crypt=1: 使用crpyt()函数(对应SQL数据里的encrypt()，encrypt()随机产生salt)  
//crypt=2: 使用MYSQL中的password()函数加密  
//crypt=3: 表示使用md5的散列方式  
  
6、配置ftp服务器  
\#vim /etc/vsftpd/vsftpd.conf  
guest\_enable=YES  
guest\_username=virtual  
pam\_service\_name=vsftpd.virt  
user\_config\_dir=/etc/vsftpd/peruser  
  
7、配置用户单独的配置文件  
\# mkdir /etc/vsftpd/peruser //创建用户单独配置文件的目录  
\# touch user1 user2 user3  
\# tail user1 user2 user3  
==> user1 <==  
local\_root=/ftp/user1  
  
==> user2 <==  
local\_root=/ftp/user2  
  
==> user3 <==  
local\_root=/ftp/user3  
  
8、创建用户登录目录  
\# mkdir -p /ftp/user1 /ftp/user2 /ftp/user3  
\# useradd virtual  
\# chown -R virtual.virtual /ftp  
  
9、测试：  
\# lftp -u user1,123 192.168.1.250

## 二，ftp本身虚拟帐号 ##

ftp本身虚拟账户  
=======================  
建立存储账户的文件:  
\# cat /etc/vsftpd/ftpuser.txt  
test1 //用户名  
123 //密码  
test2  
123  
  
将存储账户的文本文件转换成pam需要的库文件  
\# rpm -qf $(which db\_load)  
db4-utils-4.7.25-17.el6.x86\_64  
\# db\_load -T -t hash -f /etc/vsftpd/ftpuser.txt /etc/vsftpd/vsftpd\_login.db  
  
配置pam模块  
\# cat /etc/pam.d/vsftpd.virt  
auth required /lib64/security/pam\_userdb.so db=/etc/vsftpd/vsftpd\_login  
account required /lib64/security/pam\_userdb.so db=/etc/vsftpd/vsftpd\_login  
  
建立所有用户使用的虚拟账户  
\# useradd virtual  
  
修改vsftpd配置文件  
\# vim /etc/vsftpd/vsftpd.conf  
guest\_enable=YES  
guest\_username=virtual  
pam\_service\_name=vsftpd.virt  
user\_config\_dir=/etc/vsftpd/peruser  
  
配置用户单独的配置文件  
\# mkdir /etc/vsftpd/peruser //创建用户单独配置文件的目录  
\# touch test1 test2  
\# tail test1 test2  
==> test1 <==  
local\_root=/ftp/test1  
  
==> test2 <==  
local\_root=/ftp/test2  
  
创建用户登录目录  
\# mkdir -p /ftp/test1 /ftp/test2  
\# chown -R virtual.virtual /ftp  
  
测试：  
\# lftp -u test1,123 192.168.1.250

# 六，lamp环境部署（扩展） #

LAMP  
Linux+Apache+Mysql+Php  
  
一.rpm  
\#yum install httpd mysql mysql-server php php-mysql -y  
  
二.编译安装  
1.安装apache  
下载软件httpd-2.2.25.tar.bz2  
\#tar xvjf /root/Desktop/httpd-2.2.25.tar.bz2 -C /usr/src/  
\#./configure --prefix=/usr/local/apache2 --enable-mods-shared=most --enable-so --enable-rewrite --enable-ssl && make && make install  
  
测试apache  
\#/usr/local/apache2/bin/apachectl start  
  
浏览器: http://172.16.70.50  
  
2.安装mysql  
\#userdel -r mysql  
\#useradd mysql  
\# rm -rf /etc/my.cnf  
\# rm -rf /var/lib/mysql/  
  
下载软件mysql-5.1.34  
安装  
\#./configure --with-mysqld-user=mysql --prefix=/usr/local/mysql --with-extra-charsets=all --exec-prefix=/usr/local/mysql && make && make install  
  
初始化数据库  
产生目录:/usr/local/mysql/var 存放数据库的目录  
\#cd /usr/loca/mysql/bin  
\#./mysql\_install\_db  
  
修改权限  
\#chown .mysql /usr/loca/mysql/ -R  
\#chown mysql /usr/local/mysql/var -R  
  
启动数据库：  
\#./mysqld\_safe --user=mysql &  
\# lsof -i:3306  
测试数据库：  
\#./mysql  
  
3. 安装Php  
  
\# ./configure --prefix=/usr/local/php --with-apxs2=/usr/local/apache2/bin/apxs --with-mysql=/usr/local/mysql --with-config-file-path=/usr/local/php && make && make install  
\# cp php.ini-dist /usr/local/php/php.ini  
\# /usr/local/php/bin/php -v //查看php版本  
  
4. 测试  
apache配置文件：  
<IfModule dir\_module>  
DirectoryIndex index.php index.html //增加index.php  
</IfModule>  
  
352 AddType application/x-compress .Z  
353 AddType application/x-gzip .gz .tgz  
354 AddType application/x-httpd-php .php //增加此行  
  
5.部署网站  
安装flash插件  
\# rpm -ivh flash-plugin-11.2.202.291-release.x86\_64.rpm  
\# unzip farm-ucenter1.5.zip  
\# cp -r upload/\* /usr/local/apache2/htdocs/  
\# chmod 777 /usr/local/apache2/htdocs/\* -R  
  
设置数据库root密码  
\# /usr/local/mysql/bin/mysqladmin -u root password uplooking  
  
创建数据库  
\# /usr/local/mysql/bin/mysqladmin -u root -puplooking create discuz  
  
导入农场数据库信息  
\# /usr/local/mysql/bin/mysql -u root -puplooking -D discuz < qqfarm.sql  
  
安装完以后提示信息：  
\-----------------------------  
UCenter的访问网址:  
[http://172.16.70.50/ucenter][http_172.16.70.50_ucenter]  
  
UCenter 创始人密码:uplooking  
  
DISCUZ!的访问网址:  
[http://172.16.70.50/bbs][http_172.16.70.50_bbs]  
  
管理员访问网址:  
[http://172.16.70.50/bbs/admincp.php][http_172.16.70.50_bbs_admincp.php]  
  
管理员帐号:admin 管理员密码:uplooking  
  
  
UCenter Home的访问网址:  
[http://172.16.70.50/home][http_172.16.70.50_home]  
  
管理员访问网址:  
[http://172.16.70.50/home/admincp.php][http_172.16.70.50_home_admincp.php]  
  
管理员帐号:admin 管理员密码:uplooking  
\-----------------------------  
=================================  
mysql  
编译安装开机启动  
1.把启动命令写到rc.local  
2. 使用启动脚本  
\# pwd  
/usr/src/mysql-5.1.34/support-files  
\#cp mysql.server /etc/init.d/  
\# chmod 777 /etc/init.d/mysql.server  
\# chkconfig --add mysql.server  
\# chkconfig --list | grep mysql  
mysql.server 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭  
  
默认情况下Mysql本身有一个root账户，默认没有密码  
  
配置文件位置：  
rpm安装方式: /etc/my.cnf  
模板文件: /usr/share/doc/mysql-...  
编译安装:/etc/my.cnf 安装目录下 var目录下  
模板文件:解压目录下的support-files目录  
  
sock文件的位置:mysql.sock  
  
rpm: /var/lib/mysql  
编译:/tmp  
  
存储数据库的默认目录  
rpm : /var/lib/mysql  
编译: var  
  
设置密码：  
\# mysqladmin -u root password 1  
登录数据库:  
\# mysql -u root -p1  
命令结束符：默认是;或者\\g  
中断当前操作: \\c  
竖向显示表内容: \\G

# 七，多实例安装和管理（扩展） #

多实例mysql的安装和管理  
mysql的多实例有两种方式可以实现，两种方式各有利弊。  
第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。  
第二种是通过官方自带的mysqld\_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方便，优点是管理起来很方便，集中管理。  
  
下面就分别来实战这两种多实例的安装和管理  
第一种：使用多个配置文件启动多个不同进程的情况：  
环境介绍：  
mysql 版本：5.1.50  
mysql实例数：3个  
实例占用端口分别为：3306、3307、3308  
创建mysql用户  
1. /usr/sbin/groupadd mysql  
2. /usr/sbin/useradd -g mysql mysql  
编译安装mysql  
1. tar xzvf mysql-5.1.50.tar.gz  
2. cd mysql-5.1.50  
3. ./configure '--prefix=/usr/local/mysql' '--with-charset=utf8' '--with-extra-charsets=complex' '--with-pthread' '--enable-thread-safe-client' '--with-ssl' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-plugins=partition,innobase,blackhole,myisam,innodb\_plugin,heap,archive' '--enable-shared' '--enable-assembler'  
4. make  
5. make install  
初始化数据库  
1. /usr/local/mysql/bin/mysql\_install\_db --basedir=/usr/local/mysql --datadir=/data/dbdata\_3306 --user=mysql  
2. /usr/local/mysql/bin/mysql\_install\_db --basedir=/usr/local/mysql --datadir=/data/dbdata\_3307 --user=mysql  
3. /usr/local/mysql/bin/mysql\_install\_db --basedir=/usr/local/mysql --datadir=/data/dbdata\_3308 --user=mysql  
创建配置文件  
vim /data/dbdata\_3306/my.cnf  
3306的配置文件如下：  
1. \[client\]  
2. port = 3306  
3. socket = /data/dbdata\_3306/mysql.sock  
4. \[mysqld\]  
5. datadir=/data/dbdata\_3306/  
6. skip-name-resolve  
7. lower\_case\_table\_names=1  
8. innodb\_file\_per\_table=1  
9. port = 3306  
10. socket = /data/dbdata\_3306/mysql.sock  
11. back\_log = 50  
12. max\_connections = 300  
13. max\_connect\_errors = 1000  
14. table\_open\_cache = 2048  
15. max\_allowed\_packet = 16M  
16. binlog\_cache\_size = 2M  
17. max\_heap\_table\_size = 64M  
18. sort\_buffer\_size = 2M  
19. join\_buffer\_size = 2M  
20. thread\_cache\_size = 64  
21. thread\_concurrency = 8  
22. query\_cache\_size = 64M  
23. query\_cache\_limit = 2M  
24. ft\_min\_word\_len = 4  
25. default-storage-engine = innodb  
26. thread\_stack = 192K  
27. transaction\_isolation = REPEATABLE-READ  
28. tmp\_table\_size = 64M  
29. log-bin=mysql-bin  
30. binlog\_format=mixed  
31. slow\_query\_log  
32. long\_query\_time = 1  
33. server-id = 1  
34. key\_buffer\_size = 8M  
35. read\_buffer\_size = 2M  
36. read\_rnd\_buffer\_size = 2M  
37. bulk\_insert\_buffer\_size = 64M  
38. myisam\_sort\_buffer\_size = 128M  
39. myisam\_max\_sort\_file\_size = 10G  
40. myisam\_repair\_threads = 1  
41. myisam\_recover  
42. innodb\_additional\_mem\_pool\_size = 16M  
43. innodb\_buffer\_pool\_size = 200M  
44. innodb\_data\_file\_path = ibdata1:10M:autoextend  
45. innodb\_file\_io\_threads = 8  
46. innodb\_thread\_concurrency = 16  
47. innodb\_flush\_log\_at\_trx\_commit = 1  
48. innodb\_log\_buffer\_size = 16M  
49. innodb\_log\_file\_size = 512M  
50. innodb\_log\_files\_in\_group = 3  
51. innodb\_max\_dirty\_pages\_pct = 60  
52. innodb\_lock\_wait\_timeout = 120  
53. \[mysqldump\]  
54. quick  
55. max\_allowed\_packet = 256M  
56. \[mysql\]  
57. no-auto-rehash  
58. prompt=\\\\u@\\\\d \\\\R:\\\\m>  
59. \[myisamchk\]  
60. key\_buffer\_size = 512M  
61. sort\_buffer\_size = 512M  
62. read\_buffer = 8M  
63. write\_buffer = 8M  
64. \[mysqlhotcopy\]  
65. interactive-timeout  
66. \[mysqld\_safe\]  
67. open-files-limit = 8192  
vim /data/dbdata\_3307/my.cnf  
3307的配置文件如下：  
1. \[client\]  
2. port = 3307  
3. socket = /data/dbdata\_3307/mysql.sock  
4. \[mysqld\]  
5. datadir=/data/dbdata\_3307/  
6. skip-name-resolve  
7. lower\_case\_table\_names=1  
8. innodb\_file\_per\_table=1  
9. port = 3307  
10. socket = /data/dbdata\_3307/mysql.sock  
11. back\_log = 50  
12. max\_connections = 300  
13. max\_connect\_errors = 1000  
14. table\_open\_cache = 2048  
15. max\_allowed\_packet = 16M  
16. binlog\_cache\_size = 2M  
17. max\_heap\_table\_size = 64M  
18. sort\_buffer\_size = 2M  
19. join\_buffer\_size = 2M  
20. thread\_cache\_size = 64  
21. thread\_concurrency = 8  
22. query\_cache\_size = 64M  
23. query\_cache\_limit = 2M  
24. ft\_min\_word\_len = 4  
25. default-storage-engine = innodb  
26. thread\_stack = 192K  
27. transaction\_isolation = REPEATABLE-READ  
28. tmp\_table\_size = 64M  
29. log-bin=mysql-bin  
30. binlog\_format=mixed  
31. slow\_query\_log  
32. long\_query\_time = 1  
33. server-id = 1  
34. key\_buffer\_size = 8M  
35. read\_buffer\_size = 2M  
36. read\_rnd\_buffer\_size = 2M  
37. bulk\_insert\_buffer\_size = 64M  
38. myisam\_sort\_buffer\_size = 128M  
39. myisam\_max\_sort\_file\_size = 10G  
40. myisam\_repair\_threads = 1  
41. myisam\_recover  
42. innodb\_additional\_mem\_pool\_size = 16M  
43. innodb\_buffer\_pool\_size = 200M  
44. innodb\_data\_file\_path = ibdata1:10M:autoextend  
45. innodb\_file\_io\_threads = 8  
46. innodb\_thread\_concurrency = 16  
47. innodb\_flush\_log\_at\_trx\_commit = 1  
48. innodb\_log\_buffer\_size = 16M  
49. innodb\_log\_file\_size = 512M  
50. innodb\_log\_files\_in\_group = 3  
51. innodb\_max\_dirty\_pages\_pct = 60  
52. innodb\_lock\_wait\_timeout = 120  
53. \[mysqldump\]  
54. quick  
55. max\_allowed\_packet = 256M  
56. \[mysql\]  
57. no-auto-rehash  
58. prompt=\\\\u@\\\\d \\\\R:\\\\m>  
59. \[myisamchk\]  
60. key\_buffer\_size = 512M  
61. sort\_buffer\_size = 512M  
62. read\_buffer = 8M  
63. write\_buffer = 8M  
64. \[mysqlhotcopy\]  
65. interactive-timeout  
66. \[mysqld\_safe\]  
67. open-files-limit = 8192  
vim /data/dbdata\_3308/my.cnf  
3308的配置文件如下：  
1. \[client\]  
2. port = 3308  
3. socket = /data/dbdata\_3308/mysql.sock  
4. \[mysqld\]  
5. datadir=/data/dbdata\_3308/  
6. skip-name-resolve  
7. lower\_case\_table\_names=1  
8. innodb\_file\_per\_table=1  
9. port = 3308  
10. socket = /data/dbdata\_3308/mysql.sock  
11. back\_log = 50  
12. max\_connections = 300  
13. max\_connect\_errors = 1000  
14. table\_open\_cache = 2048  
15. max\_allowed\_packet = 16M  
16. binlog\_cache\_size = 2M  
17. max\_heap\_table\_size = 64M  
18. sort\_buffer\_size = 2M  
19. join\_buffer\_size = 2M  
20. thread\_cache\_size = 64  
21. thread\_concurrency = 8  
22. query\_cache\_size = 64M  
23. query\_cache\_limit = 2M  
24. ft\_min\_word\_len = 4  
25. default-storage-engine = innodb  
26. thread\_stack = 192K  
27. transaction\_isolation = REPEATABLE-READ  
28. tmp\_table\_size = 64M  
29. log-bin=mysql-bin  
30. binlog\_format=mixed  
31. slow\_query\_log  
32. long\_query\_time = 1  
33. server-id = 1  
34. key\_buffer\_size = 8M  
35. read\_buffer\_size = 2M  
36. read\_rnd\_buffer\_size = 2M  
37. bulk\_insert\_buffer\_size = 64M  
38. myisam\_sort\_buffer\_size = 128M  
39. myisam\_max\_sort\_file\_size = 10G  
40. myisam\_repair\_threads = 1  
41. myisam\_recover  
42. innodb\_additional\_mem\_pool\_size = 16M  
43. innodb\_buffer\_pool\_size = 200M  
44. innodb\_data\_file\_path = ibdata1:10M:autoextend  
45. innodb\_file\_io\_threads = 8  
46. innodb\_thread\_concurrency = 16  
47. innodb\_flush\_log\_at\_trx\_commit = 1  
48. innodb\_log\_buffer\_size = 16M  
49. innodb\_log\_file\_size = 512M  
50. innodb\_log\_files\_in\_group = 3  
51. innodb\_max\_dirty\_pages\_pct = 60  
52. innodb\_lock\_wait\_timeout = 120  
53. \[mysqldump\]  
54. quick  
55. max\_allowed\_packet = 256M  
56. \[mysql\]  
57. no-auto-rehash  
58. prompt=\\\\u@\\\\d \\\\R:\\\\m>  
59. \[myisamchk\]  
60. key\_buffer\_size = 512M  
61. sort\_buffer\_size = 512M  
62. read\_buffer = 8M  
63. write\_buffer = 8M  
64. \[mysqlhotcopy\]  
65. interactive-timeout  
66. \[mysqld\_safe\]  
67. open-files-limit = 8192  
  
创建自动启动文件  
vim /data/dbdata\_3306/mysqld  
3306的启动文件如下：  
  
1. \#!/bin/bash  
2. mysql\_port=3306  
3. mysql\_username="admin"  
4. mysql\_password="password"  
5.  
6. function\_start\_mysql()  
7. \{  
8. printf "Starting MySQL...\\n"  
9. /bin/sh /usr/local/mysql/bin/mysqld\_safe --defaults-file=/data/dbdata\_$\{mysql\_port\}/my.cnf 2>&1 > /dev/null &  
10. \}  
11.  
12. function\_stop\_mysql()  
13. \{  
14. printf "Stoping MySQL...\\n"  
15. /usr/local/mysql/bin/mysqladmin -u $\{mysql\_username\} -p$\{mysql\_password\} -S /data/dbdata\_$\{mysql\_port\}/mysql.sock shutdown  
16. \}  
17.  
18. function\_restart\_mysql()  
19. \{  
20. printf "Restarting MySQL...\\n"  
21. function\_stop\_mysql  
22. function\_start\_mysql  
23. \}  
24.  
25. function\_kill\_mysql()  
26. \{  
27. kill -9 $(ps -ef | grep 'bin/mysqld\_safe' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
29. \}  
30.  
31. case $1 in  
32. start)  
33. function\_start\_mysql;;  
34. stop)  
35. function\_stop\_mysql;;  
36. kill)  
37. function\_kill\_mysql;;  
38. restart)  
39. function\_stop\_mysql  
40. function\_start\_mysql;;  
41. \*)  
42. echo "Usage: /data/dbdata\_$\{mysql\_port\}/mysqld \{start|stop|restart|kill\}";;  
43. esac  
  
vim /data/dbdata\_3307/mysqld  
3307的启动文件如下：  
  
  
1. \#!/bin/bash  
2. mysql\_port=3307  
3. mysql\_username="admin"  
4. mysql\_password="password"  
5.  
6. function\_start\_mysql()  
7. \{  
8. printf "Starting MySQL...\\n"  
9. /bin/sh /usr/local/mysql/bin/mysqld\_safe --defaults-file=/data/dbdata\_$\{mysql\_port\}/my.cnf 2>&1 > /dev/null &  
10. \}  
11.  
12. function\_stop\_mysql()  
13. \{  
14. printf "Stoping MySQL...\\n"  
15. /usr/local/mysql/bin/mysqladmin -u $\{mysql\_username\} -p$\{mysql\_password\} -S /data/dbdata\_$\{mysql\_port\}/mysql.sock shutdown  
16. \}  
17.  
18. function\_restart\_mysql()  
19. \{  
20. printf "Restarting MySQL...\\n"  
21. function\_stop\_mysql  
22. function\_start\_mysql  
23. \}  
24.  
25. function\_kill\_mysql()  
26. \{  
27. kill -9 $(ps -ef | grep 'bin/mysqld\_safe' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
29. \}  
30.  
31. case $1 in  
32. start)  
33. function\_start\_mysql;;  
34. stop)  
35. function\_stop\_mysql;;  
36. kill)  
37. function\_kill\_mysql;;  
38. restart)  
39. function\_stop\_mysql  
40. function\_start\_mysql;;  
41. \*)  
42. echo "Usage: /data/dbdata\_$\{mysql\_port\}/mysqld \{start|stop|restart|kill\}";;  
43. esac  
  
vim /data/dbdata\_3308/mysqld  
3308的启动文件如下：  
  
  
1. \#!/bin/bash  
2. mysql\_port=3308  
3. mysql\_username="admin"  
4. mysql\_password="password"  
5.  
6. function\_start\_mysql()  
7. \{  
8. printf "Starting MySQL...\\n"  
9. /bin/sh /usr/local/mysql/bin/mysqld\_safe --defaults-file=/data/dbdata\_$\{mysql\_port\}/my.cnf 2>&1 > /dev/null &  
10. \}  
11.  
12. function\_stop\_mysql()  
13. \{  
14. printf "Stoping MySQL...\\n"  
15. /usr/local/mysql/bin/mysqladmin -u $\{mysql\_username\} -p$\{mysql\_password\} -S /data/dbdata\_$\{mysql\_port\}/mysql.sock shutdown  
16. \}  
17.  
18. function\_restart\_mysql()  
19. \{  
20. printf "Restarting MySQL...\\n"  
21. function\_stop\_mysql  
22. function\_start\_mysql  
23. \}  
24.  
25. function\_kill\_mysql()  
26. \{  
27. kill -9 $(ps -ef | grep 'bin/mysqld\_safe' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
28. kill -9 $(ps -ef | grep 'libexec/mysqld' | grep $\{mysql\_port\} | awk '\{printf $2\}')  
29. \}  
30.  
31. case $1 in  
32. start)  
33. function\_start\_mysql;;  
34. stop)  
35. function\_stop\_mysql;;  
36. kill)  
37. function\_kill\_mysql;;  
38. restart)  
39. function\_stop\_mysql  
40. function\_start\_mysql;;  
41. \*)  
42. echo "Usage: /data/dbdata\_$\{mysql\_port\}/mysqld \{start|stop|restart|kill\}";;  
43. esac  
启动3306、3307、3308的mysql  
  
  
1. /data/dbdata\_3306/mysqld start  
2. /data/dbdata\_3307/mysqld start  
3. /data/dbdata\_3308/mysqld start  
  
更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：  
  
  
1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3306/mysql.sock  
2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3307/mysql.sock  
3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3308/mysql.sock  
登录测试并创建关闭mysql的帐号权限，mysqld脚本要用到！  
  
  
1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
2. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
3. flush privileges;  
4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
5. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
6. flush privileges;  
7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
8. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
9. flush privileges;  
创建了admin帐号以后脚本的stop功能和restart功能就正常了！  
更改环境变量  
  
  
1. vim /etc/profile 添加下面一行内容  
2. PATH=$\{PATH\}:/usr/local/mysql/bin/  
3. source /etc/profile  
添加到自动启动  
  
  
1. vim /etc/init.d/boot.local  
2. /data/dbdata\_3306/mysqld start  
3. /data/dbdata\_3307/mysqld start  
4. /data/dbdata\_3308/mysqld start  
如果是rhel或者centos系统的话自启动文件/etc/rc.local  
管理的话，在本地都是采用 -S /data/dbdata\_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！  
再来看第二种通过官方自带的mysqld\_multi来实现多实例实战：  
这里的mysql安装以及数据库的初始化和前面的步骤一样，就不再赘述。  
  
mysqld\_multi的配置  
vim /etc/my.cnf  
  
1. \[mysqld\_multi\]  
2. mysqld = /usr/local/mysql/bin/mysqld\_safe  
3. mysqladmin = /usr/local/mysql/bin/mysqladmin  
4. user = admin  
5. password = password  
6.  
7. \[mysqld1\]  
8. socket = /data/dbdata\_3306/mysql.sock  
9. port = 3306  
10. pid-file = /data/dbdata\_3306/3306.pid  
11. datadir = /data/dbdata\_3306  
12. user = mysql  
13. skip-name-resolve  
14. lower\_case\_table\_names=1  
15. innodb\_file\_per\_table=1  
16. back\_log = 50  
17. max\_connections = 300  
18. max\_connect\_errors = 1000  
19. table\_open\_cache = 2048  
20. max\_allowed\_packet = 16M  
21. binlog\_cache\_size = 2M  
22. max\_heap\_table\_size = 64M  
23. sort\_buffer\_size = 2M  
24. join\_buffer\_size = 2M  
25. thread\_cache\_size = 64  
26. thread\_concurrency = 8  
27. query\_cache\_size = 64M  
28. query\_cache\_limit = 2M  
29. ft\_min\_word\_len = 4  
30. default-storage-engine = innodb  
31. thread\_stack = 192K  
32. transaction\_isolation = REPEATABLE-READ  
33. tmp\_table\_size = 64M  
34. log-bin=mysql-bin  
35. binlog\_format=mixed  
36. slow\_query\_log  
37. long\_query\_time = 1  
38. server-id = 1  
39. key\_buffer\_size = 8M  
40. read\_buffer\_size = 2M  
41. read\_rnd\_buffer\_size = 2M  
42. bulk\_insert\_buffer\_size = 64M  
43. myisam\_sort\_buffer\_size = 128M  
44. myisam\_max\_sort\_file\_size = 10G  
45. myisam\_repair\_threads = 1  
46. myisam\_recover  
47. innodb\_additional\_mem\_pool\_size = 16M  
48. innodb\_buffer\_pool\_size = 200M  
49. innodb\_data\_file\_path = ibdata1:10M:autoextend  
50. innodb\_file\_io\_threads = 8  
51. innodb\_thread\_concurrency = 16  
52. innodb\_flush\_log\_at\_trx\_commit = 1  
53. innodb\_log\_buffer\_size = 16M  
54. innodb\_log\_file\_size = 512M  
55. innodb\_log\_files\_in\_group = 3  
56. innodb\_max\_dirty\_pages\_pct = 60  
57. innodb\_lock\_wait\_timeout = 120  
58.  
59.  
60. \[mysqld2\]  
61. socket = /data/dbdata\_3307/mysql.sock  
62. port = 3307  
63. pid-file = /data/dbdata\_3307/3307.pid  
64. datadir = /data/dbdata\_3307  
65. user = mysql  
66. skip-name-resolve  
67. lower\_case\_table\_names=1  
68. innodb\_file\_per\_table=1  
69. back\_log = 50  
70. max\_connections = 300  
71. max\_connect\_errors = 1000  
72. table\_open\_cache = 2048  
73. max\_allowed\_packet = 16M  
74. binlog\_cache\_size = 2M  
75. max\_heap\_table\_size = 64M  
76. sort\_buffer\_size = 2M  
77. join\_buffer\_size = 2M  
78. thread\_cache\_size = 64  
79. thread\_concurrency = 8  
80. query\_cache\_size = 64M  
81. query\_cache\_limit = 2M  
82. ft\_min\_word\_len = 4  
83. default-storage-engine = innodb  
84. thread\_stack = 192K  
85. transaction\_isolation = REPEATABLE-READ  
86. tmp\_table\_size = 64M  
87. log-bin=mysql-bin  
88. binlog\_format=mixed  
89. slow\_query\_log  
90. long\_query\_time = 1  
91. server-id = 1  
92. key\_buffer\_size = 8M  
93. read\_buffer\_size = 2M  
94. read\_rnd\_buffer\_size = 2M  
95. bulk\_insert\_buffer\_size = 64M  
96. myisam\_sort\_buffer\_size = 128M  
97. myisam\_max\_sort\_file\_size = 10G  
98. myisam\_repair\_threads = 1  
99. myisam\_recover  
100. innodb\_additional\_mem\_pool\_size = 16M  
101. innodb\_buffer\_pool\_size = 200M  
102. innodb\_data\_file\_path = ibdata1:10M:autoextend  
103. innodb\_file\_io\_threads = 8  
104. innodb\_thread\_concurrency = 16  
105. innodb\_flush\_log\_at\_trx\_commit = 1  
106. innodb\_log\_buffer\_size = 16M  
107. innodb\_log\_file\_size = 512M  
108. innodb\_log\_files\_in\_group = 3  
109. innodb\_max\_dirty\_pages\_pct = 60  
110. innodb\_lock\_wait\_timeout = 120  
111.  
112.  
113. \[mysqld3\]  
114. socket = /data/dbdata\_3308/mysql.sock  
115. port = 3308  
116. pid-file = /data/dbdata\_3308/3308.pid  
117. datadir = /data/dbdata\_3308  
118. user = mysql  
119. skip-name-resolve  
120. lower\_case\_table\_names=1  
121. innodb\_file\_per\_table=1  
122. back\_log = 50  
123. max\_connections = 300  
124. max\_connect\_errors = 1000  
125. table\_open\_cache = 2048  
126. max\_allowed\_packet = 16M  
127. binlog\_cache\_size = 2M  
128. max\_heap\_table\_size = 64M  
129. sort\_buffer\_size = 2M  
130. join\_buffer\_size = 2M  
131. thread\_cache\_size = 64  
132. thread\_concurrency = 8  
133. query\_cache\_size = 64M  
134. query\_cache\_limit = 2M  
135. ft\_min\_word\_len = 4  
136. default-storage-engine = innodb  
137. thread\_stack = 192K  
138. transaction\_isolation = REPEATABLE-READ  
139. tmp\_table\_size = 64M  
140. log-bin=mysql-bin  
141. binlog\_format=mixed  
142. slow\_query\_log  
143. long\_query\_time = 1  
144. server-id = 1  
145. key\_buffer\_size = 8M  
146. read\_buffer\_size = 2M  
147. read\_rnd\_buffer\_size = 2M  
148. bulk\_insert\_buffer\_size = 64M  
149. myisam\_sort\_buffer\_size = 128M  
150. myisam\_max\_sort\_file\_size = 10G  
151. myisam\_repair\_threads = 1  
152. myisam\_recover  
153. innodb\_additional\_mem\_pool\_size = 16M  
154. innodb\_buffer\_pool\_size = 200M  
155. innodb\_data\_file\_path = ibdata1:10M:autoextend  
156. innodb\_file\_io\_threads = 8  
157. innodb\_thread\_concurrency = 16  
158. innodb\_flush\_log\_at\_trx\_commit = 1  
159. innodb\_log\_buffer\_size = 16M  
160. innodb\_log\_file\_size = 512M  
161. innodb\_log\_files\_in\_group = 3  
162. innodb\_max\_dirty\_pages\_pct = 60  
163. innodb\_lock\_wait\_timeout = 120  
164.  
165.  
166. \[mysqldump\]  
167. quick  
168. max\_allowed\_packet = 256M  
169. \[mysql\]  
170. no-auto-rehash  
171. prompt=\\\\u@\\\\d \\\\R:\\\\m>  
172. \[myisamchk\]  
173. key\_buffer\_size = 512M  
174. sort\_buffer\_size = 512M  
175. read\_buffer = 8M  
176. write\_buffer = 8M  
177. \[mysqlhotcopy\]  
178. interactive-timeout  
179. \[mysqld\_safe\]  
180. open-files-limit = 8192  
mysqld\_multi启动  
  
1. /usr/local/mysql/bin/mysqld\_multi start 1  
2. /usr/local/mysql/bin/mysqld\_multi start 2  
3. /usr/local/mysql/bin/mysqld\_multi start 3  
或者采用一条命令的形式：  
  
1. /usr/local/mysql/bin/mysqld\_multi start 1-3  
更改原来密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：  
  
1. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3306/mysql.sock  
2. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3307/mysql.sock  
3. /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata\_3308/mysql.sock  
登录测试并创建admin密码(停止mysql的时候需要使用到)  
  
1. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
2. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
3. flush privileges;  
4. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
5. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
6. flush privileges;  
7. /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata\_3308/mysql.sock  
8. GRANT SHUTDOWN ON \*.\* TO 'admin'@'localhost' IDENTIFIED BY 'password';  
9. flush privileges;  
更改环境变量  
  
1. vim /etc/profile  
2. PATH=$\{PATH\}:/usr/local/mysql/bin/  
3. source /etc/profile  
添加到自动启动  
  
1. vim /etc/init.d/boot.local  
2. /usr/local/mysql/bin/mysqld\_multi start 1-3  
如果是rhel或者centos系统的话自启动文件/etc/rc.local  
管理的话，在本地都是采用 -S /data/dbdata\_3308/mysql.sock，如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别！  
大家在管理多实例的话可以使用mysqlmanager实例管理器，管理器来会比较方面，限于篇幅，这里就不在多做介绍！

### client ###

本地客户端不用输入密码登录的配置  
\[client\]  
user = root  
password = 123

### 事务 ###

=========================================  
事务：  
在创建表的时候create table xxxx ( ..........) engine innoDB; 后一句表示创建引擎类型为innoDB，它支持事务，  
  
开启一个事务： start transaction;  
然后你写你的sql语句，无论你写多少，只要没提交事务，这个事务就存在，有commit显式提交，还有隐式提交，你觉得你写的sql语句没有问题时就，你就commit; 提交这个事务；如果前面你写的sql语句出了问题，比如有条sql语句是批量改金币什么的，改多了。 Rollback;回滚，意思是回到你开启事务时的状态，就是说你开启事务后的所有sql操作当作没有发生，你重新来过。  
  
注意：当一个事务commit,或者rollback就结束了

### mysql错误中文参考列表 ###

1005：创建表失败  
1006：创建数据库失败  
1007：数据库已存在，创建数据库失败  
1008：数据库不存在，删除数据库失败  
1009：不能删除数据库文件导致删除数据库失败  
1010：不能删除数据目录导致删除数据库失败  
1011：删除数据库文件失败  
1012：不能读取系统表中的记录  
1020：记录已被其他用户修改  
1021：硬盘剩余空间不足，请加大硬盘可用空间  
1022：关键字重复，更改记录失败  
1023：关闭时发生错误  
1024：读文件错误  
1025：更改名字时发生错误  
1026：写文件错误  
1032：记录不存在  
1036：数据表是只读的，不能对它进行修改  
1037：系统内存不足，请重启数据库或重启服务器  
1038：用于排序的内存不足，请增大排序缓冲区  
1040：已到达数据库的最大连接数，请加大数据库可用连接数  
1041：系统内存不足  
1042：无效的主机名  
1043：无效连接  
1044：当前用户没有访问数据库的权限  
1045：不能连接数据库，用户名或密码错误  
1048：字段不能为空  
1049：数据库不存在  
1050：数据表已存在  
1051：数据表不存在  
1054：字段不存在  
1065：无效的SQL语句，SQL语句为空  
1081：不能建立Socket连接  
1114：数据表已满，不能容纳任何记录  
1116：打开的数据表太多  
1129：数据库出现异常，请重启数据库  
1130：连接数据库失败，没有连接数据库的权限  
1133：数据库用户不存在  
1141：当前用户无权访问数据库  
1142：当前用户无权访问数据表  
1143：当前用户无权访问数据表中的字段  
1146：数据表不存在  
1147：未定义用户对数据表的访问权限  
1149：SQL语句语法错误  
1158：网络错误，出现读错误，请检查网络连接状况  
1159：网络错误，读超时，请检查网络连接状况  
1160：网络错误，出现写错误，请检查网络连接状况  
1161：网络错误，写超时，请检查网络连接状况  
1062：字段值重复，入库失败  
1169：字段值重复，更新记录失败  
1177：打开数据表失败  
1180：提交事务失败  
1181：回滚事务失败  
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库  
1205：加锁超时  
1211：当前用户没有创建用户的权限  
1216：外键约束检查失败，更新子表记录失败  
1217：外键约束检查失败，删除或修改主表记录失败  
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器  
1227：权限不足，您无权进行此操作  
1235：MySQL版本过低，不具有本功能

# 八，mysql优化 #

## 一，引擎 ##

**存储引擎**  
![20180525091221295][]  
  
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为**表类型**（即存储和操作此表的类型）  
  
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。  
用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。  
  
**查看引擎**：  
mysql> show engines;  
mysql> SHOW VARIABLES LIKE '%storage\_engine%';  
mysql> show create table t1;  
mysql> show table status like 't1';  
  
**关闭不必要的引擎：**  
不想用哪个，加上跳过哪个就可以,重启mysql，这样也可以优化数据库。  
默认如果不想他们启动的话，修改配置文件  
\#vim /etc/my.cnf  
\[mysqld\]  
skip-mrg\_myisam  
skip-csv  
skip-memory  
  
**临时指定引擎**：  
mysql> create table innodb1(id int)engine=innodb;  
  
**修改默认引擎**：  
/etc/my.cnf  
\[mysqld\]  
default-storage-engine=INNODB  
  
**修改已经存在的表的引擎**：  
mysql> alter table t2 engine=myisam;  
  
**MySQL常用存储引擎**:  
**MyISAM存储引擎**  
由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。  
.myi index 存储索引  
.myd data 存储数据  
.frm 存储表结构  
  
**InnoDB存储引擎**  
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。  
因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。  
大型数据库用innodb  
\[root@www var\]\# pwd  
/usr/local/mysql/var  
  
\[root@www var\]\# ls ib\*  
ibdata1  ib\_logfile0  ib\_logfile1  
innodb类型的表的数据存在ibdata1里面，不像myisam生成３个文件， ib\_logfile0  ib\_logfile1存放日志  
  
初始是10M,每次增加8M ，初始大小可以指定，要修改配置文件  
\#vim /etc/my.cnf  
    innodb\_data\_file\_path=ibdata1:20M:autoextend：max:1000M  
    设定初始大小是20M，增幅也是8M  设定峰值是1000M,就是指定最大能增加到1000M  
     
    innodb\_data\_home\_dir=/data   
    指定他的存储数据的位置，也就是ibdata1的位置  
  
**数据分开存储:**  
比放在一块硬盘里访问速度快  
默认的数据都保存在var下面，可以人为的改变他们的存储位置  
只适用于innodb类型，.frm文件必须得放在var下，不能指定放到别的地方  
\#mkdir data  最好是不同的硬盘上    
\#chown mysql data  
mysql> create table tb1(name char(20)) data directory='/data';  
data directory=指定数据文件的位置  
  
**MEMORY**  
速度快，比myisam快30％，当数据库重启之后，数据就会丢失，因为他是存在内存里的.适合于需要快速的访问或临时表。  
mysql> create table t20(id int,name char(10)) type=memory; 创建一个memory类型的表  
  
**BLACKHOLE**  
黑洞存储引擎，可以应用于主备复制中的分发主库。  
mysql> desc blackhole1;  
\+-------+---------+------+-----+---------+-------+  
| Field | Type | Null | Key | Default | Extra |  
\+-------+---------+------+-----+---------+-------+  
| id | int(11) | YES | | NULL | |  
\+-------+---------+------+-----+---------+-------+  
1 row in set (0.00 sec)  
  
mysql> insert into blackhole1 values(1);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> select \* from blackhole1;  
Empty set (0.00 sec)

## 二，字符集设置 ##

字符集设置  
临时：  
mysql> create database db1 CHARACTER SET = utf8;  
mysql> create table t1(id int(10)) CHARACTER SET = utf8;  
  
永久：  
5.1版本设置：  
\#vim /etc/my.cnf  
\[mysqld\]  
default-character-set = utf8  
character\_set\_server = utf8  
  
\[mysql\]  
default-character-set = utf8  
  
5.5版本设置：  
\[mysqld\]  
character\_set\_server = utf8

## 三，慢查询配置    ##

5.1版本  
\#vim /etc/my.cnf  
\[mysqld\]  
log-slow-queries=/var/lib/mysql/sql\_row.log  
long\_query\_time=3  
  
查看是否设置成功：  
mysql> show variables like '%query%';  
  
5.5版本  
\[mysqld\]  
slow-query-log=on  
slow\_query\_log\_file=/var/lib/mysql/slowquery.log  
long\_query\_time=1  
\#log-queries-not-using-indexes=on //列出没有使用索引的查询语句  
  
5.7版本  
\[mysqld\]  
slow\_query\_log=1  
slow\_query\_log\_file=/var/log/mysql-slow/slow.log  
long\_query\_time=3  
  
\# mkdir /var/log/mysql-slow/  
\# chown mysql.mysql /var/log/mysql-slow/  
\# systemctl restart mysqld  
  
查看慢查询日志  
测试:BENCHMARK(count,expr)  
mysql> SELECT BENCHMARK(500000000,2\*3);

## 四，解决client连接mysql慢的问题 ##

客户端连接MySQL数据库速度慢的问题  
修改my.cnf配置，关闭DNS的反向解析参数  
\[mysqld\]  
skip-name-resolve

## 五，其他了解 ##

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的，它是随着用户量的增加，基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案，暂且抛开前端应用不说，大致分为以下五个阶段：  
1、数据库表设计  
  项目立项后，开发部根据产品部需求开发项目，开发工程师工作其中一部分就是对表结构设计。对于数据库来说，这点很重要，如果设计不当，会直接影响访问速度 和用户体验。影响的因素很多，比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞（死锁）等。当然，有测试工程师的团队，会做压力测试，找 bug。对于没有测试工程师的团队来说，大多数开发工程师初期不会太多考虑数据库设计是否合理，而是尽快完成功能实现和转交付，等项目有一定访问量后，隐 藏的问题就会暴露，这时再去修改就不是这么容易的事了。  
2、数据库部署  
  该运维工程师出场了，项目初期访问量不会很大，所以单台部署足以应对在1500左右的QPS（每秒查询率）。考虑到高可用性，可采用MySQL主从复制+Keepalived做双击热备，常见集群软件有Keepalived、Heartbeat。  
双机热备博文：http://lizhenliang.blog.51cto.com/7876557/1362313  
3、数据库性能优化  
  如果将MySQL部署到普通的X86服务器上，在不经过任何优化情况下，MySQL理论值正常可以处理2000左右QPS，经过优化后，有可能 会提升到2500左右QPS，否则，访问量当达到1500左右并发连接时，数据库处理性能就会变慢，而且硬件资源还很富裕，这时就该考虑软件问题了。那么 怎样让数据库最大化发挥性能呢？一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化，另一方面是对数据库进行优化，往往操作系统和数据库默认 配置都比较保守，会对数据库发挥有一定限制，可对这些配置进行适当的调整，尽可能的处理更多连接数。  
具体优化有以下三个层面：  
  3.1 数据库配置优化  
  MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。  
  表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。  
  行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。  
  为什么会出现表锁和行锁呢？主要是为了保证数据的完整性，举个例子，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。  
  根据以上看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多，以下列出主要影响数据库性能的参数。  
  公共参数默认值：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>12345678910</td> 
   <td>max_connections&nbsp;=&nbsp;151#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右&nbsp;&nbsp;&nbsp;sort_buffer_size&nbsp;=&nbsp;2M#查询排序时缓冲区大小，只对order&nbsp;by和group&nbsp;by起作用，可增大此值为16Mquery_cache_limit&nbsp;=&nbsp;1M&nbsp;&nbsp;#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖query_cache_size&nbsp;=&nbsp;16M&nbsp;&nbsp;#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值open_files_limit&nbsp;=&nbsp;1024&nbsp;#打开文件数限制，如果show&nbsp;global&nbsp;status&nbsp;like&nbsp;'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死</td> 
  </tr> 
 </tbody> 
</table>

MyISAM参数默认值：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>1234</td> 
   <td>key_buffer_size&nbsp;=&nbsp;16M#索引缓存区大小，一般设置物理内存的30-40%read_buffer_size&nbsp;=&nbsp;128K&nbsp;&nbsp;#读操作缓冲区大小，推荐设置16M或32M</td> 
  </tr> 
 </tbody> 
</table>

InnoDB参数默认值：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>12345678910</td> 
   <td>innodb_buffer_pool_size&nbsp;=&nbsp;128M#索引和数据缓冲区大小，一般设置物理内存的60%-70%innodb_buffer_pool_instances&nbsp;=&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;#缓冲池实例个数，推荐设置4个或8个innodb_flush_log_at_trx_commit&nbsp;=&nbsp;1&nbsp;&nbsp;#关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。innodb_file_per_table&nbsp;=&nbsp;OFF&nbsp;&nbsp;#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。innodb_log_buffer_size&nbsp;=&nbsp;8M&nbsp;&nbsp;#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M</td> 
  </tr> 
 </tbody> 
</table>

3.2 系统内核优化  
  大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化。

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>12345678910</td> 
   <td>net.ipv4.tcp_fin_timeout&nbsp;=&nbsp;30#TIME_WAIT超时时间，默认是60snet.ipv4.tcp_tw_reuse&nbsp;=&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;#1表示开启复用，允许TIME_WAIT&nbsp;socket重新用于新的TCP连接，0表示关闭net.ipv4.tcp_tw_recycle&nbsp;=&nbsp;1&nbsp;&nbsp;#1表示开启TIME_WAIT&nbsp;socket快速回收，0表示关闭net.ipv4.tcp_max_tw_buckets&nbsp;=&nbsp;4096&nbsp;&nbsp;&nbsp;#系统保持TIME_WAIT&nbsp;socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息net.ipv4.tcp_max_syn_backlog&nbsp;=&nbsp;4096#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接</td> 
  </tr> 
 </tbody> 
</table>

在linux系统中，如果进程打开的文件句柄数量超过系统默认值1024，就会提示“too many files open”信息，所以要调整打开文件句柄限制。

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>1234</td> 
   <td>#&nbsp;vi&nbsp;/etc/security/limits.conf&nbsp;&nbsp;#加入以下配置，*代表所有用户，也可以指定用户，重启系统生效*&nbsp;soft&nbsp;nofile&nbsp;65535*&nbsp;hard&nbsp;nofile&nbsp;65535#&nbsp;ulimit&nbsp;-SHn&nbsp;65535&nbsp;&nbsp;&nbsp;#立刻生效</td> 
  </tr> 
 </tbody> 
</table>

3.3 硬件配置  
  加大物理内存，提高文件系统性能。linux内核会从内存中分配出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时 （如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。也就是说物理内存越大，分配缓存区越大，缓存数据越多。当然，服务器故障会丢失一定 的缓存数据。  
  SSD硬盘代替SAS硬盘，将RAID级别调整为RAID1+0，相对于RAID1和RAID5有更好的读写性能（IOPS），毕竟数据库的压力主要来自磁盘I/O方面。  
4、数据库架构扩展  
  随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑加机器了，该做集群了~~~。主要思想是分解单台数据库负载，突破磁盘I/O性能，热数据存放缓存中，降低磁盘I/O访问频率。  
  4.1 主从复制与读写分离  
  因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作，主流的负载均衡器有LVS、HAProxy、Nginx。  
  怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率比较高。另一个种方式通过代理程序实现读写分离，企业中应用较少，常见代理程序有 MySQL Proxy、Amoeba。在这样数据库集群架构中，大大增加数据库高并发能力，解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS，那么5台就能处理1w QPS，数据库横向扩展性也很容易。  
  有时，面对大量写操作的应用时，单台写性能达不到业务需求。如果做双主，就会遇到数据库数据不一致现象，产生这个原因是在应用程序不同的用户会有可能操作 两台数据库，同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性，怎样在多台主 库时解决这个问题呢？有一套基于perl语言开发的主从复制管理工具，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主复制管理器），这个工具最大的优点是在同一时间只提供一台数据库写操作，有效保证数据一致性。  
  主从复制博文：http://lizhenliang.blog.51cto.com/7876557/1290431  
  读写分离博文：http://lizhenliang.blog.51cto.com/7876557/1305083  
 MySQL-MMM博文：http://lizhenliang.blog.51cto.com/7876557/1354576  
  4.2 增加缓存  
  给数据库增加缓存系统，把热数据缓存到内存中，如果缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。缓存实现有本地缓存和分布式缓 存，本地缓存是将数据缓存到本地服务器内存中或者文件中。分布式缓存可以缓存海量数据，扩展性好，主流的分布式缓存系统有memcached、 redis，memcached性能稳定，数据缓存在内存中，速度很快，QPS可达8w左右。如果想数据持久化就选择用redis，性能不低于 memcached。  
  工作过程：  
    
  4.3 分库  
  分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。如果业务量很大，还可将切分后的库做主从架构，进一步避免单个库压力过大。  
  4.4 分表  
  数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。  
  分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表 进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O 性能。  
  分表分为垂直拆分和水平拆分：  
  垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。  
  水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。  
  4.5 分区  
  分区就是把一张表的数据根据表结构中的字段（如range、list、hash等）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区 后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。  
注：增加缓存、分库、分表和分区主要由程序猿来实现。  
5、数据库维护  
  数据库维护是运维工程师或者DBA主要工作，包括性能监控、性能分析、性能调优、数据库备份和恢复等。  
  5.1 性能状态关键指标  
  QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数  
  TPS，Transactions Per Second：每秒处理事务数  
  通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：  
  Uptime：服务器已经运行的实际，单位秒  
  Questions：已经发送给数据库查询数  
  Com\_select：查询次数，实际操作数据库的  
  Com\_insert：插入次数  
  Com\_delete：删除次数  
  Com\_update：更新次数  
  Com\_commit：事务次数  
  Com\_rollback：回滚次数  
  那么，计算方法来了，基于Questions计算出QPS：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td>&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;like&nbsp;'Questions';&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;like&nbsp;'Uptime';</td> 
  </tr> 
 </tbody> 
</table>

QPS = Questions / Uptime  
  基于Com\_commit和Com\_rollback计算出TPS：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>123</td> 
   <td>&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;like&nbsp;'Com_commit';&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;like&nbsp;'Com_rollback';&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;like&nbsp;'Uptime';</td> 
  </tr> 
 </tbody> 
</table>

TPS = (Com\_commit + Com\_rollback) / Uptime  
  另一计算方式：基于Com\_select、Com\_insert、Com\_delete、Com\_update计算出QPS

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;where&nbsp;Variable_name&nbsp;in('com_select','com_insert','com_delete','com_update');</td> 
  </tr> 
 </tbody> 
</table>

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS  
  TPS计算方法：

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>&nbsp;&nbsp;mysql&gt;&nbsp;show&nbsp;global&nbsp;status&nbsp;where&nbsp;Variable_name&nbsp;in('com_insert','com_delete','com_update');</td> 
  </tr> 
 </tbody> 
</table>

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。  
  经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com\_\*计算比较准确。  
  5.2 开启慢查询日志  
  MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

<table>     
 <tbody> 
  <tr> 
   <th>点击这里</th> 
   <th>点击这里</th> 
  </tr> 
  <tr> 
   <td>1234</td> 
   <td>mysql&gt;&nbsp;set&nbsp;global&nbsp;slow-query-log=on&nbsp;&nbsp;#开启慢查询功能mysql&gt;&nbsp;set&nbsp;global&nbsp;slow_query_log_file='/var/log/mysql/mysql-slow.log';&nbsp;&nbsp;#指定慢查询日志文件位置mysql&gt;&nbsp;set&nbsp;global&nbsp;log_queries_not_using_indexes=on;&nbsp;&nbsp;&nbsp;#记录没有使用索引的查询mysql&gt;&nbsp;set&nbsp;global&nbsp;long_query_time=1;&nbsp;&nbsp;&nbsp;#只记录处理时间1s以上的慢查询</td> 
  </tr> 
 </tbody> 
</table>

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。  
  \# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    \#查看最慢的前三个查询  
  也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。  
  分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log  
  分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql   
  pt-query-digest --type=binlog mysql-bin.000001.sql   
  分析普通日志：pt-query-digest --type=genlog localhost.log  
  5.3 数据库备份  
  备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策 略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup 等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备 份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。  
  Xtrabackup备份工具使用博文：http://lizhenliang.blog.51cto.com/7876557/1612800  
  5.4 数据库修复  
  有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。  
  myisamchk：只能修复myisam表，需要停止数据库  
  常用参数：  
  -f --force    强制修复，覆盖老的临时文件，一般不使用  
  -r --recover  恢复模式  
  -q --quik     快速恢复  
  -a --analyze  分析表  
  -o --safe-recover 老的恢复模式，如果-r无法修复，可以使用此参数试试  
  -F --fast     只检查没有正常关闭的表  
  快速修复weibo数据库:  
  \# cd /var/lib/mysql/weibo   
  \# myisamchk -r -q \*.MYI  
  mysqlcheck：myisam和innodb表都可以用，不需要停止数据库，如修复单个表，可在数据库后面添加表名，以空格分割  
  常用参数：  
  -a  --all-databases  检查所有的库  
  -r  --repair   修复表  
  -c  --check    检查表，默认选项  
  -a  --analyze  分析表  
  -o  --optimize 优化表  
  -q  --quik   最快检查或修复表  
  -F  --fast   只检查没有正常关闭的表  
  快速修复weibo数据库:  
  mysqlcheck -r -q -uroot -p123 weibo   
  5.5 另外，查看CPU和I/O性能方法  
  \#查看CPU性能  
  
  \#参数-P是显示CPU数，ALL为所有，也可以只显示第几颗CPU  
  \#查看I/O性能  
  
   
  \#参数-m是以M单位显示，默认K  
  \#%util：当达到100%时，说明I/O很忙。  
  \#await：请求在队列中等待时间，直接影响read时间。  
  I/O极限：IOPS（r/s+w/s）,一般RAID0/10在1200左右。（IOPS，每秒进行读写（I/O）操作次数）  
  I/O带宽：在顺序读写模式下SAS硬盘理论值在300M/s左右，SSD硬盘理论值在600M/s左右。

# \---------------------------------------------------------------------------------------------------- #

作为MySQL调优的一部分，很多人都推荐开启skip\_name\_resolve。这个参数是禁止域名解析的（当然，也包括  
主机名）。很多童鞋会好奇，这背后的原理是什么，什么情况下开启这个参数比较合适。  
   
基于以下原因，MySQL服务端会在内存中维护着一份host信息， 包括三部分：IP，主机名和错误信息。主要用于  
非本地TCP连接。  
1. 通过在第一次建立连接时缓存IP和host name的映射关系，同一主机的后续连接将直接查看host cache，而不用再次进行DNS解析。  
2. host cache中同样会包含IP登录失败的错误信息。可根据这些信息，对这些IP进行相应的限制。后面将会具体提到。  
host cache的信息可通过performance\_schema中host\_cache表查看。  
   
那么，IP和host name的映射关系是如何建立的呢？  
1. 当有一个新的客户端连接进来时，MySQL Server会为这个IP在host cache中建立一个新的记录，包括IP，主机名和client lookup validation flag，分别对应host\_cache表中的IP，HOST和HOST\_VALIDATED这三列。第一次建立连接因为只有IP，没有主机名，所以 HOST将设置为NULL，HOST\_VALIDATED将设置为FALSE。  
2. MySQL Server检测HOST\_VALIDATED的值，如果为FALSE，它会试图进行DNS解析，如果解析成功，它将更新HOST的值为主机名，并将 HOST\_VALIDATED值设为TRUE。如果没有解析成功，判断失败的原因是永久的还是临时的，如果是永久的，则HOST的值依旧为NULL，且将 HOST\_VALIDATED的值设置为TRUE，后续连接不再进行解析，如果该原因是临时的，则HOST\_VALIDATED依旧为FALSE，后续连 接会再次进行DNS解析。  
   
另，解析成功的标志并不只是通过IP，获取到主机名即可，这只是其中一步，还有一步是通过解析后的主机名来反向解析为IP，判断该IP是否与原IP相同，如果相同，才判断为解析成功，才能更新host cache中的信息。  
   
基于上面的总结，下面谈谈 host cache的优缺点：  
缺点：当有一个新的客户端连接进来时，MySQL Server都要建立一个新的记录，如果DNS解析很慢，无疑会影响性能。如果被允许访问的主机很多，也会影响性能，这个与 host\_cache\_size有关，这个参数是5.6.5引入的。5.6.8之前默认是128,5.6.8之后默认是-1,基于 max\_connections的值动态调整。所以如果被允许访问的主机很多，基于LRU算法，先前建立的连接可能会被挤掉，这些主机重新进来时，会再次 进行DNS查询。  
优点：通常情况下，主机名是不变的，而IP是多变的。如果一个客户端的IP经常变化，那基于IP的授权将是一个繁琐的过程。因为你很难确定IP什么 时候变化。而基于主机名，只需一次授权。而且，基于host cache中的失败信息，可在一定程度上阻止外界的暴力破解攻击。  
   
关于阻止外界的暴力破解攻击，涉及到max\_connect\_errors参数，默认为100，官方的解释如下：  
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.如果某个客户端的连接达到了max\_connect\_errors的限制，将被禁止访问，并提示以下错误：  
Host 'host\_name' is blocked because of many connection errors.  
Unblock with 'mysqladmin flush-hosts'   
下面来模拟一下  
首先，设置max\_connect\_errors的值  
mysql> show variables like 'max\_connect\_errors';  
\+\--------------------+-------+  
| Variable\_name | Value |  
\+\--------------------+-------+  
| max\_connect\_errors | **100**|  
\+\--------------------+-------+  
**1** row in set (**0.00** sec)  
  
mysql> set global max\_connect\_errors=**2**;  
Query OK, **0** rows affected (**0.00** sec)  
  
mysql> show variables like 'max\_connect\_errors';  
\+\--------------------+-------+  
| Variable\_name | Value |  
\+\--------------------+-------+  
| max\_connect\_errors | **2**|  
\+\--------------------+-------+  
**1** row in set (**0.00** sec)通过telnet模拟interrupted without a successful connection。  
\[root@mysql-slave1 ~\]\# telnet 192.168.244.145 3306  
Trying 192.168.244.145...  
Connected to 192.168.244.145.  
Escape character is '^\]'.  
N  
5.6.26\-log  
K\]qA1nYT!w|+ZhxF1c\#|kmysql\_native\_password  
^\]  
!\#08S01Got packets out of orderConnection closed by foreign host.  
\[root@mysql\-slave1 ~\]\# telnet 192.168.244.145 3306  
Trying 192.168.244.145...  
Connected to 192.168.244.145.  
Escape character is '^\]'.  
N  
Y\#>PVB(>!Bl\}NKnjIj\]sMmysql\_native\_password  
^\]  
!\#08S01Got packets out of orderConnection closed by foreign host.  
\[root@mysql\-slave1 ~\]\# mysql -h192.168.244.145 \-uroot -p123456  
Warning: Using a password on the command line interface can be insecure.  
ERROR 1129 (HY000): Host '192.168.244.144' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'即便后来使用了正确的账号和密码登录，依旧会被阻止。  
再来看看host\_cache表中的信息，sum\_connect\_errors为2了。  
mysql> select ip,host,host\_validated,sum\_connect\_errors,count\_authentication\_errors from performance\_schema.host\_cache;  
\+\-----------------+------+----------------+--------------------+-----------------------------+  
| ip | host | host\_validated | sum\_connect\_errors | count\_authentication\_errors |  
\+\-----------------+------+----------------+--------------------+-----------------------------+  
| **192.168**.**244.144**| NULL | YES | **2**| **0**|  
\+\-----------------+------+----------------+--------------------+-----------------------------+  
**1** row in set (**0.00** sec)  
该阻止会一直生效，直到采取以下操作：  
1. mysql> flush hosts;  
2. \# mysqladmin flush-hosts  
3. truncate table performance\_schema.host\_cache;  
4. 或者等待该记录从host cache中被挤掉。  
   
如果要禁止DNS解析，可设置skip\_name\_resolve参数，这样，mysql.user表中基于主机名的授权将无法使用，且错误日志中会提示：  
\[Warning\] 'user' entry 'root@mysql-slave1' ignored in \--skip-name-resolve mode.这里，通过mysql-slave1访问，将会拒绝访问  
\[root@mysql-slave1 ~\]\# mysql \-h192.**168.244**.**145**\-uroot \-p123  
Warning: Using a password on the command line interface can be insecure.  
ERROR **1045** (**28000**): Access denied for user 'root'@'192.168.244.144' (using password: YES)   
host cache是默认开启的，如果要禁掉，可将host\_cache\_size设置为0，该参数是个动态参数，可在线修改。  
   
如果要完全禁掉TCP/IP连接,可在MySQL启动时，设置skip-networking参数。  
   
总结：  
1. 从原理上看，DNS解析一般只针对客户端的第一次连接，客户端数据量比较小的情况下，开销其实不大，完全不必禁掉skip\_name\_resolve参数，带来的好处就是，为客户端和多变的IP直接解耦，只需对主机名进行一次授权。  
可通过\\s查看当前连接使用的是socket还是TCP。  
2. 奇怪的是，对于skip\_name\_resolve参数，虽然官方文档说的是布尔值，  
但如果在配置文件中指定了，无论是skip\_name\_resolve=off或者skip\_name\_resolve=0。  
最后，通过show variables like '%skip\_name\_resolve%'查看均显示ON。将该参数设置为OFF的唯一办法是不写该参数（因为它默认值即为OFF）。  
3. 在skip\_name\_resolve=ON的情况下，在本地通过-h127.0.0.1没有问题。  
\[root@localhost ~\]\# mysql -uroot -h127.0.0.1 \-p123456  
Warning: Using a password on the command line interface can be insecure.  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 4  
Server version: 5.6.31\-log MySQL Community Server (GPL)  
  
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.  
  
Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
  
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
  
root@(none) 09:02:15> \\s  
\--------------  
mysql Ver 14.14 Distrib 5.6.31, for Linux (x86\_64) using EditLine wrapper  
  
Connection id: 4  
Current database:  
Current user: root@127.0.0.1  
SSL: Not in use  
Current pager: stdout  
Using outfile: ''  
Using delimiter: ;  
Server version: 5.6.31\-log MySQL Community Server (GPL)  
Protocol version: 10  
Connection: 127.0.0.1 via TCP/IP  
Server characterset: utf8  
Db characterset: utf8  
Client characterset: utf8  
Conn. characterset: utf8  
TCP port: 3306  
Uptime: 11 min 10 sec  
  
Threads: 1 Questions: 20 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.029  
\--------------  
  
root@(none) 09:02:18> show variables like '%skip\_name\_resolve%';  
\+-------------------+-------+  
| Variable\_name | Value |  
\+-------------------+-------+  
| skip\_name\_resolve | ON |  
\+-------------------+-------+  
1 row in set (0.06 sec)  
如果该参数设置为OFF，则上述方式就会报错，通过报错信息可以看出，它直接将127.0.0.1转化为localhost了。  
\[root@localhost ~\]\# mysql -uroot -h127.0.0.1 \-p123456 -P3306  
Warning: Using a password on the command line interface can be insecure.  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)注意： 't1'@'%'中包含't1'@'127.0.0.1'，如果开启skip\_name\_resolve参数，则't1'@'%'中定义的密码可用于 't1'@'127.0.0.1'的登录，如果没有开启该参数，则't1'@'127.0.0.1'会转化为't1'@'localhost'登录，此时 't1'@'%'定义的密码并不适用。

# 六，性能测试 #

mysql几种性能测试的工具使用  
1、mysqlslap  
安装：简单，装了mysql就有了  
  
作用：模拟并发测试数据库性能。  
  
优点：简单，容易使用。  
  
不足：不能指定生成的数据规模，测试过程不清楚针对十万级还是百万级数据做的测试，感觉不太适合做综合测试，比较适合针对既有数据库，对单个sql进行优化的测试。  
  
使用方法：  
可以使用mysqlslap --help来显示使用方法：  
  
Default options are read from the following files in the given order:  
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf  
  
\--concurrency代表并发数量，多个可以用逗号隔开，concurrency=10,50,100, 并发连接线程数分别是10、50、100个并发。  
  
\--engines代表要测试的引擎，可以有多个，用分隔符隔开。  
\--iterations代表要运行这些测试多少次。  
\--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。  
\--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）  
\--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。  
\--debug-info 代表要额外输出CPU以及内存的相关信息。  
\--number-int-cols ：创建测试表的 int 型字段数量  
\--auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto\_increment列，从5.1.18版本开始  
\--number-char-cols 创建测试表的 char 型字段数量。  
\--create-schema 测试的schema，MySQL中schema也就是database。  
\--query 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。  
\--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。  
  
mysqlslap -umysql -p123 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10 --debug-info  
  
或：  
  
指定数据库和sql语句:  
  
mysqlslap -h192.168.3.18 -P4040 --concurrency=100 --iterations=1 --create-schema='test' --query='select \* from test;' --number-of-queries=10 --debug-info -umysql -p123  
  
要是看到底做了什么可以加上：--only-print  
  
Benchmark  
Average number of seconds to run all queries: 25.225 seconds  
Minimum number of seconds to run all queries: 25.225 seconds  
Maximum number of seconds to run all queries: 25.225 seconds  
Number of clients running queries: 100  
Average number of queries per client: 0  
  
以上表明100个客户端同时运行要25秒  
  
2、sysbench  
安装：  
可以从http://sourceforge.net/projects/sysbench/ 下载  
tar zxf sysbench-0.4.12.tar.gz  
cd sysbench-0.4.12  
./autogen.sh  
./configure && make && make install  
strip /usr/local/bin/sysbench  
  
  
安装时候可能会报错，后来baidu发现个好文 http://blog.csdn.net/icelemon1314/article/details/7004955 怕以后找不到，也贴过来吧  
  
1.如果mysql不是默认路径安装，那么需要通过指定--with-mysql-includes和--with-mysql-libs参数来加载mysql安装路径  
2.如果报错：  
../libtool: line 838: X--tag=CC: command not found  
../libtool: line 871: libtool: ignoring unknown tag : command not found  
../libtool: line 838: X--mode=link: command not found  
../libtool: line 1004: \*\*\* Warning: inferring the mode of operation is deprecated.: command not found  
../libtool: line 1005: \*\*\* Future versions of Libtool will require --mode=MODE be specified.: command not found  
../libtool: line 2231: X-g: command not found  
../libtool: line 2231: X-O2: command not found  
那么执行下根目录的：autogen.sh文件，然后重新configure && make && make install  
3.如果报错：  
sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory  
那么执行下：  
n -s /usr/local/mysql5.5/mysql/lib/libmysqlclient.so.18 /usr/lib64/  
4.如果执行autogen.sh时，报如下错误：  
./autogen.sh: line 3: aclocal: command not found  
那么需要安装一个软件：  
yum install automake  
然后需要增加一个参数：查找： AC\_PROG\_LIBTOOL 将其注释，然后增加AC\_PROG\_RANLIB  
  
  
作用：模拟并发,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL  
  
优点：可以指定测试数据的规模，可以单独测试读、写的性能，也可以测试读写混合的性能。  
  
不足：测试的时候，由于网络原因，测试的非常慢，但是最终给的结果却很好，并发支持很高，所以给我的感觉是并不太准确。当然也可能我没搞明白原理  
  
使用方法：  
  
准备数据  
sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=400000 --mysql-db=dbtest2 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd prepare  
执行测试  
sysbench --num-threads=100 --max-requests=4000 --test=oltp --mysql-table-engine=innodb --oltp-table-size=400000 --mysql-db=dbtest1 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd run  
  
sysbench 0.4.12: multi-threaded system evaluation benchmark  
  
No DB drivers specified, using mysql  
Running the test with following options:  
Number of threads: 100  
  
Doing OLTP test.  
Running mixed OLTP test  
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)  
Using "BEGIN" for starting transactions  
Using auto\_inc on the id column  
Maximum number of requests for OLTP test is limited to 4000  
Threads started!  
Done.  
  
OLTP test statistics:  
queries performed:  
read: 56014  
write: 20005  
other: 8002  
total: 84021  
transactions: 4001 (259.14 per sec.)  
deadlocks: 0 (0.00 per sec.)  
read/write requests: 76019 (4923.75 per sec.)  
other operations: 8002 (518.29 per sec.)  
  
Test execution summary:  
total time: 15.4393s  
total number of events: 4001  
total time taken by event execution: 1504.7744  
per-request statistics:  
min: 33.45ms  
avg: 376.10ms  
max: 861.53ms  
approx. 95 percentile: 505.65ms  
  
Threads fairness:  
events (avg/stddev): 40.0100/0.67  
execution time (avg/stddev): 15.0477/0.22  
3、tpcc-mysql  
安装：  
如果从原网站上下载源码比较麻烦，需要工具、注册、生成证书等。这里提供一个下载包http://blog.chinaunix.net/blog/downLoad/fileid/8532.html  
export C\_INCLUDE\_PATH=/usr/include/mysql  
export PATH=/usr/bin:$PATH  
export LD\_LIBRARY\_PATH=/usr/lib/mysql  
cd /tmp/tpcc/src  
make  
然后就会在 /tmp/tpcc-mysql 下生成 tpcc 命令行工具 tpcc\_load 、 tpcc\_start  
  
作用：测试mysql数据库的整体性能  
  
优点：符合tpcc标准，有标准的方法，模拟真实的交易活动，结果比较可靠。  
  
不足：不能单独测试读或者写的性能，对于一些以查询为主或者只写的应用，就没有这么大的意义了。  
  
使用方法：  
  
加载数据  
创建库  
mysql>create database tpcc10;  
创建表：  
shell>mysql tpcc10 < create\_table.sql  
添加外键：  
shell>mysql tpcc10 < add\_fkey\_idx.sql  
  
加载数据:  
1、单进程加载：  
shell>./tpcc\_load 192.168.11.172 tpcc10 root pwd 300  
|主机||数据库||用户||密码||warehouse|  
2、并发加载：（推荐，但需要修改一下）  
shell>./load.sh tpcc300 300  
|数据库||warehouse|  
3、测试  
./tpcc\_start -h192.168.11.172 -d tpcc -u root -p 'pwd' -w 10 -c 10 -r 10 -l 60 -i 10 -f /mnt/hgfs/mysql/tpcc100\_2013522.txt  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\*\*\* \#\#\#easy\#\#\# TPC-C Load Generator \*\*\*  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
option h with value '192.168.11.172'  
option d with value 'tpcc'  
option u with value 'root'  
option p with value 'pwd'  
option w with value '1'  
option c with value '100'  
option r with value '120'  
option l with value '60'  
option i with value '10'  
option f with value '/mnt/hgfs/mysql/tpcc100\_2013522.txt'  
<Parameters>  
\[server\]: 192.168.11.172  
\[port\]: 3306  
\[DBname\]: tpcc  
\[user\]: root  
\[pass\]: pwd  
\[warehouse\]: 1  
\[connection\]: 100  
\[rampup\]: 120 (sec.)  
\[measure\]: 60 (sec.)  
  
RAMP-UP TIME.(120 sec.)  
  
MEASURING START.  
  
10, 245(77):10.923|28.902, 242(0):3.677|10.796, 25(0):1.579|2.198, 24(0):17.451|21.047, 25(4):19.999|33.776  
20, 262(75):9.070|11.917, 263(0):3.407|4.716, 26(0):1.608|1.776, 27(0):11.347|16.408, 26(1):19.166|21.018  
30, 247(90):11.130|14.131, 241(0):2.367|2.654, 24(0):0.960|1.095, 24(0):9.308|16.538, 25(3):19.999|24.874  
40, 237(69):11.840|13.009, 239(1):3.638|7.245, 24(0):0.692|0.773, 23(0):8.756|10.456, 23(1):19.527|20.495  
50, 252(69):10.548|17.925, 256(0):2.652|2.893, 26(0):1.177|3.579, 27(0):14.648|15.018, 25(4):19.999|26.398  
60, 256(78):9.323|11.328, 251(1):3.895|5.380, 25(0):0.785|1.542, 25(0):11.382|15.829, 26(0):18.481|18.855  
  
STOPPING THREADS....................................................................................................  
  
<Raw Results>  
\[0\] sc:1041 lt:458 rt:0 fl:0  
\[1\] sc:1490 lt:2 rt:0 fl:0  
\[2\] sc:150 lt:0 rt:0 fl:0  
\[3\] sc:150 lt:0 rt:0 fl:0  
\[4\] sc:137 lt:13 rt:0 fl:0  
in 60 sec.  
  
<Raw Results2(sum ver.)>  
\[0\] sc:1041 lt:458 rt:0 fl:0  
\[1\] sc:1490 lt:2 rt:0 fl:0  
\[2\] sc:150 lt:0 rt:0 fl:0  
\[3\] sc:150 lt:0 rt:0 fl:0  
\[4\] sc:137 lt:13 rt:0 fl:0  
  
<Constraint Check> (all must be \[OK\])  
\[transaction percentage\]  
Payment: 43.36% (>=43.0%) \[OK\]  
Order-Status: 4.36% (>= 4.0%) \[OK\]  
Delivery: 4.36% (>= 4.0%) \[OK\]  
Stock-Level: 4.36% (>= 4.0%) \[OK\]  
\[response time (at least 90% passed)\]  
New-Order: 69.45% \[NG\] \*  
Payment: 99.87% \[OK\]  
Order-Status: 100.00% \[OK\]  
Delivery: 100.00% \[OK\]  
Stock-Level: 91.33% \[OK\]  
  
<TpmC>  
1499.000 TpmC  
  
关于Tpcc的概念请参见http://baike.baidu.com/view/2776305.htm  
这里把测试用例介绍贴一下  
TPC-C测试用到的模型是一个大型的商品批发销售公司，它拥有若干个分布在不同区域的商品仓库。当业务扩展的时候，公司将添加新的仓库。  
每个仓库负责为10个销售点供货，其中每个销售点为3000个客户提供服务，每个客户提交的订单中，平均每个订单有10项产品，  
所有订单中约1%的产品在其直接所属的仓库中没有存货，必须由其他区域的仓库来供货。同时，每个仓库都要维护公司销售的100000种商品的库存记录。  
4. The MySQL Benchmark Suite  
这个测试工具是随着MySQL绑定发行的，基于Perl语言和其中的两个模块：DBI和Benchmark。如果有需要，它支持所有支持DBI驱动的数据库。可以通过修改bench-init.pl的选项以符合需要。另外提醒的是，它不支持多CPU。  
进行测试时，执行run-all-tests脚本，具体的命令选项请看README。  
5. MySQL super-smack  
这是一个强大的广受赞誉的压力测试工具，支持MySQL和PostgreSQL。  
[http://jeremy.zawodny.com/mysql/super-smack/][http_jeremy.zawodny.com_mysql_super-smack]  
安装很简单，请先仔细阅读目录里的指导文件。  
Preparing test data  
做测试时，最好用自己的数据。因为使用真实的数据，使测试变得接近现实和客观。  
Configuration  
smack的文件设置，看起来很简单。  
  
6. MyBench: A Home-Grown Solution  
MyBench一种基于Perl语言易于扩展的测试工具。  
[http://jeremy.zawodny.com/mysql/mybench/][http_jeremy.zawodny.com_mysql_mybench]

[20180524221945757]: /images/20220524/08bd58065c0f43fa88999227f62ced6f.png
[20180524222341464]: /images/20220524/8a4d422c58e94502aac7342f4b7abd39.png
[20180524222507356]: /images/20220524/8a2df56f17f745759ce7216fe7ff39fe.png
[http_172.16.70.50_ucenter]: http://172.16.70.50/ucenter
[http_172.16.70.50_bbs]: http://172.16.70.50/bbs
[http_172.16.70.50_bbs_admincp.php]: http://172.16.70.50/bbs/admincp.php
[http_172.16.70.50_home]: http://172.16.70.50/home
[http_172.16.70.50_home_admincp.php]: http://172.16.70.50/home/admincp.php
[20180525091221295]: /images/20220524/f9ea3f2d85a84f7d946746860940b1d1.png
[http_jeremy.zawodny.com_mysql_super-smack]: http://jeremy.zawodny.com/mysql/super-smack/
[http_jeremy.zawodny.com_mysql_mybench]: http://jeremy.zawodny.com/mysql/mybench/