MySQL数据库优化
1 SQL语句优化
2 表损的优化，表引擎读锁写锁
3 数据库优化
4 服务器优化
LAMP环境准备：
Linux+Apache+Mysql/MariaDB+Perl/PHP/Python
一组常用来搭建动态网站或者服务器的开源软件，
本身都是各自独立的程序，但是因为常被放在一起使用，
拥有了越来越高的兼容度，共同组成了一个强大的Web应用程序平台。
从网站的流量上来说，70%以上的访问流量是LAMP来提供的，
LAMP是最强大的网站解决方案．
内存调配：
物理机的1/4
1 Mysql基础操作
查看是否安装
ps -ef|grep mysql
usr/bin/mysql 是指：mysql的运行路径
var/lib/mysql 是指：mysql数据库文件的存放路径
usr/lib/mysql 是指：mysql的安装路径
先用rpm -qa \*mysql\*看看你安装了哪些包，
然后用 rpm -ql xxxxxxx 去查看具体的包安装位置
yum安装的mysql的话
mysql的命令可以在/usr/bin里面找到
登陆：mysql -u root -p openview
\s 查看当前数据库信息，比如4大字符集
表复制
复制表结构+复制表数据
create table t1(id int unsigned not null auto_increment primary key, name varchar(30));
insert into t1(name) values("user1");
insert into t1(name) values("user2");
create table t3 like t1;
使用like可以把表结构也复制过去
insert into t3 select * from t1;
t1,t3结构完全一样才可以这么做
最好是指明需要复制哪些字段
索引
1 alter方法，最通用，推荐
alter table用来创建普通索引，unique索引或primary key索引
当表已经存在主键时，无法再创建主键索引
alter table table_name add index index_name (column_list)
alter table table_name add unique (column_list)
alter table table_name add primary key (column_list)
2 create index 不推荐 不可以创建主键索引
create index index_name on table_name(column_list)
创建唯一索引，要保证这列没有重复值
create unique index index_name on table_name(column_list)
3 drop index 不推荐 不可以删除主键索引
drop index index_name on table_name
4 alter table table drop
alter table table_name drop index index_name
alter table table_name drop primary key
5 show index from table_name
视图
用于作为大量查询的中间表
随主表的变化而变化
可以让视图包含主表的一部分
对这一部分的频繁查询可以用视图来做
如果主表损坏，视图也损坏
使用MySql帮助： ? view 查看视图语法
alter view
create view
drop view
show tables 也可以看到视图
create view v_t1 as select * from t1 where id>4 and id<11;
drop view v_t1
内置函数，字符串函数，数学函数，日期函数
字符串函数
concat(str[,str,str...]) 连接字符串
lcase(str) 转小写
ucase(str) 转大写
length(str) 长度
ltrim(str) 去前空格
rtrim(str) 去后空格
repeat(str, count) 统计字符重复次数
replace(str, searchStr,replaceStr) 替换
substring(str,position,[length]) 截取
space(count) 生成空格
数学函数
bin(decimal_number) 十进制转二进制
ceiling(num) 小数向上取整, 12.2423->13
floor(num) 小数向下取整, 12.2423->12
max(col) 取最大值 聚合时使用
min(col) 取最小值 聚合时使用
sqrt(num) 开平方
rand() 返回0-1内随机值
比如， select bin(255);
返回11111111
日期函数
curdate() 当前日期
curtime() 当前时间
now() 当前日期时间
unix_timestamp([date]) 当前date的unit时间戳
from_unixtime() unit时间戳的日期值
week(date) 一年中的第几周
year(date) 年份
datediff(d1,d2) 起始时间和结束时间的天数
比如
select now();
select unix_timestamp(date);
预处理语句 Prepare
类似动态语言的预处理
? prepare查看语法
设置一个预处理stmt1
prepare stmt1 from 'select * from t1 where id>?';
设置一个变量
set @i=1;
执行stmt1预处理
execute stmt1 using @i;
设置i为5
set @i=5;
再次执行
execute stmt1 using @i;
删除预处理
drop prepare stmt1;
事务处理
关闭自动提交功能
set autocommit=0;
从表中删除一条记录
delete from t1 where id=11;
此时作一个p1还原点
savepoint p1;
再次从表t1种删除一条记录
delete from t1 where id=10;
再作一个p2还原点
savepoint p2;
此时恢复到p1还原点，后面p2还原点自动失效
rollback to p1;
退回到最原始的还原点
rollback;
存储过程 procedure
代码段，函数
? precedure查看帮助
例如，为t1表插入100条数据
存储里面很多用到分号，我们首先要修改语句结束标记
使用//作为结束标记
\d //
创建存储p2()
create procedure p2()
begin
set @i=3;
while @i<=100 do
insert into t2(name) values(concat("user", @i));
set @i=@i+1;
end while;
end//
改回结束符号为分号
\d ;
查看p2状态
show procedure status;
查看p2里面的语句
show create procedure p2;
执行存储
call p2;
查看结果
select * from t2 where id>50 limit 5;
触发器
修改delimiter为//
\d //
例1 创建一个名字tg1的触发器，当t1表插入数据时，t2表插入一条数据
new.name表示新的name数据，
create trigger tg1 before insert on t1 for each row
begin
insert into t2(name) values(new.name);
end //
准备两个空表t1,t2
select * from t1;
select * from t2;
向t1种插入多条数据，
insert into t1 values(1),(2),(3),(4);
查看t2结果
例2 删除表t1后t2表的记录也删除
需要用到old.name
create trigger tg2 before delete on t1 for each row
begin
delete from t2 where name=old.name;
end //
例2 更改表t1后t2表记录跟着修改
create trigger tg3 before update on t1 for each row
begin
update t2 set name=new.name where name=old.name;
end //
查看触发器
show triggers;
删除触发器
drop trigger t2;
查询时，只要使用union，联合表就可以了
select * from t1 union select * from t2;
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。
列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同。
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
UNION ALL 命令和 UNION 命令几乎是等效的，
不过 UNION ALL 命令会列出所有的值。即重复值
重排auto_increment值，自增
自动增长id如何恢复
清空表的时候不能用delete from
要用truncate 或者truncate table
这样auto_increment恢复成1
或者清空内容后直接用Alter修改表
alter table t1 auto_increment=1;
删除数据技巧：
一般我们用delete from t1;
当数据量很多时，比如上亿，这样写很慢
要使用：
truncate t1; 瞬间清空整表数据
并且自增字段归1，delete的话无法归1
2 常用SQL技巧
正则表达式
regexp 关键字
? regexp 查看帮助
select "linux is very good!" regexp ".*"; 返回1行数据
select "linux is very good!" regexp "^linux"; 返回1行数据
select "linux is very good!" regexp "^very"; 返回0行数据
select name,email from t where email regexp "@163[.,]com$"
相当于select name,email from t where email like "%@163.com" or email like "%@163,com"
但比like更消耗资源
^ 开头
$ 结尾
. 任意单个
[] 括号内任意字符
[^] 括号内不出现的人一字符
a* 0个或多个a，包括空串
a+ 1个或多个a，不包括空串
a? 1个或0个a
a1|a2 a1或者a2
a(m) m个a
a(m,) 至少m个a
a(m,n) m到n个a
a(,n) 0到n个a
() 将模式元素组成单一元素
rand()提取随机行
获取0-1之间的数，利用它和order by能够把数据随机排序
select * from stu order by rand();
select * from stu order by rand() limit 3;
group by的with rollup子句统计
可以检索出更多的分组聚合信息, 比如合计信息，不太好把空
不可以和order by一起使用
select cname,pname,count(pname) from demo group by cname,pname with rollup;
可以得到分组后的pname数量以及pname的合计信息
bit group functions做统计
使用group by 时可以同时使用bit_and, bit_or来统计，
数值间的逻辑位运算
把分组查询后的数值列kind的值做二进制逻辑运算
select id, bit_or(kind) from order_rab group by id;
select id, bit_and(kind) from order_rab group by id;
使用外键需要注意的问题
create table temp(id int, name char(20),foreign key(id) references outTable(id) on delete cascade on update cascade);
其中cascade表示级联
Innodb类型的表支持外键，
myisam类型的表，虽然创建外键可以成功，但是不起作用，不支持外键
mysql中和help使用
? create
? reg% 模糊查询
? opti% 模糊查询优化optimize的全称
? contents 基本上有mysql所有的帮助信息， 重要
单独输入?
mysql> ?
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
\G 行列垂直显示 列比较多时用
show plugins\G 垂直方式显示mysql中的插件信息
相对于show plugins; 普通行方式显示数据
清屏命令
system clear
3 Sql语句优化
一般步骤
1 通过慢查询日志，slow.log
找到select语句，查看问题在哪里，要否加索引
是否需要加索引，使用explain解析下该语句
或者desc，解析该语句运行行数
行数特别长，字段就需要加索引
2 通过show status命令了解各种SQL执行频率
show [session|global] status;
session表示当前的连接，默认
global表示自数据库启动至今
show status like 'Com_%';
show global status like 'Com_select%';
可以看到select, insert, repair ...命令用了多少次，
Com开头表示每个增删改查等语句的运行次数
一次查询只累计1次
批量插入支算一次
只针对InnoDB存储引擎的增删改查影响行数，不是运行次数，
比如对100条记录查询出来，就是100
InnoDB_rows_read
InnoDB_rows_updated
InnoDB_rows_inserted
InnoDB_rows_deleted
我们可以用下列语句把一个表改为innoDB类型
alter table demo engine=innodb;
数据执行大量的INSERT或UPDATE应该使用InnoDB表
执行大量的SELECT，MyISAM是更好的选择
其他：
connections 连接进mysql的次数，包括成功和失败
uptime 服务器已经工作的秒数
slow_queries，慢查询的次数
查看慢查询是否开启
show variables like "%slow%";
如果log_slow_queries是OFF，就是关闭状态
slow_query_log_file 文件路径：
/var/lib/mysql/localhost-slow.log
慢查询时间：
show variables like "%long%";
long_query_time 默认10秒
如果一条语句执行了超过10秒，就会记载进慢查询日志
所以我们查看慢查询日志，发现有记录，说明执行比较慢，需要调优
3 定位执行效率低的SQL语句
首先可以通过慢查询日志查询
然后使用下面语句解析
explain select * from demo where id = 1000;
desc select * from demo where id = 1000;
如果列太多，可以使用\G
比如desc select * from demo where id = 1000\G
显示结果：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
Extra: NULL
1 row in set (0.00 sec)
可以看到，查询行数，时间，没有用到索引，单表查询，影响行数9行等信息，
重点看扫描到的行数，rows
然后是单表查询还是多表查询，select_type
simple 单表查询
primary 主查询，
union 多表连接查询
subquery 子查询
是否用到索引，possible_keys，key，key_len
possible_keys 可能使用的索引
key实际使用的索引
key_len索引字段长度
查询类型，type
all 通过全表扫描得到的数据 要尽量避免
查询特别慢的语句往往是where,order by,group by, having后面的字段没有做索引造成
range 表单中的范围查询，大于，小于等
index 通过查询索引得到的数据
unique_subquery 主键子查询
index_subquery 非主键子查询
index_merge 索引合并优化，符合索引
system 表仅一行
const 只匹配到一行
eq_ref 对于前面的每一行使用主键和唯一
ref 没有用主键索引
索引问题
是数据库优化中最常见也是最重要的手段之一
可以帮助解决大多数的SQL性能问题
索引的存储分类
MyISAM存储引擎（用的多）的表的数据和索引是分开存储的，
各自是独一的一个文件
InnoDB是存储在同一个表空间里面，但可以有多个文件组成
利用独立空间做表分区，要把数据文件索引文件分离开存储
目前不支持函数索引
可以对列的某一部分进行索引，
比如name字段，只取它值得前4个字符进行索引
这样可以缩小索引文件的大小
一般在where，order by ， group by 后面的having 建立索引
create index ind_company2_name on company2(name(4));
索引用于快速找出在某个列中有一特定值得行
是提高查询的最佳途径
减少影响行数
使用索引
1 对于创建的多列索引，只要查询的条件中用到最左边的列
索引一般就会被使用，如下创建符合索引
create index ind_sales2_com_mon onsales2(company_id,moneys);
然后按company_id进行查询，发现使用到了复合索引
explain select * from sales2 where company_id=2006\G
使用下面的查询就没有使用复合索引
explain select * from sales2 where moneys=1\G
2 使用like查询，后面如果是常量并且%号不在第一个字符
索引才会被使用
explain select * from company2 where name like "%3"\G
可以看到这时候没有索引被使用，影响行数增多
当%在后面，就可以用索引，影响行数马上降低
所以记得尽量%写在后面
3 如果对大文本进行搜索，使用全文索引而不使用like"%...%"
4 如果列名是索引，使用<column_name> is null将使用索引
explain select * from company2 where name is null\G
显示查询中使用到了索引
存在索引但不使用索引的情况
1 MySQL判断使用索引比全表扫描更慢的情况，就不使用索引
例如如果列key_part1均分在1到100间，查询时使用索引就不是很好
还比如时间段
select * from table_name where key_part1>1 and key_part<90;
2 如果使用memory/heap表并且where条件中不使用=号进行索引列，
那么不会用到索引。heap表只有在=的条件下会使用索引
3 用or分割开的条件，如果or前的条件中的列有索引，
而后面的列中没有索引，那么涉及的索引都不会被用到
要加索引都加索引，否则没有意义
4 如果不是索引列的第一部分，这个索引不会被采用
参见上面使用索引例子
5 如果列类型是字符串， 但查询时把一个数值型常量赋值给了一个字符型的列名name
那么虽然在name列上有索引，但是也没有用到
explain select * from company2 where name =294\G
查看索引的使用情况
如果索引正在工作，Handler_read_key的值将很高
这个值代表一个行被索引值读的次数
Handle_read_rnd_next的值高表示查询运行低效，
并且应该建立索引补救
show status like 'Handler_read%';
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 15 |
| Handler_read_key | 77 |
| Handler_read_last | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 21 |
| Handler_read_rnd_next | 343 |
+-----------------------+-------+
7 rows in set (0.01 sec)
两个简单使用的表优化方法
1 定期分析表和检查表
analyze [local|no_write_to_binlog] table <table_name1>[,table_name2...]
用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息
使SQL能够生成正确的执行计划
analyze table t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.06 sec)
分析表的语法如下：
检查一个或多个表是否有错误
check table tbl_name[,tbl_name2...] [option...] option={quick|fast|medium|extended|changed}
比如，
create view v1 as select * from t1 where id>2 and id<4;
create table t2 like t1;
insert into t2 select * from t1;
drop table t1;
check table v1\G
*************************** 1. row ***************************
Table: test.v1
Op: check
Msg_type: Error
Msg_text: Table 'test.t1' doesn't exist
*************************** 2. row ***************************
Table: test.v1
Op: check
Msg_type: Error
Msg_text: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
Table: test.v1
Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)
然后执行把表t2改名为t1,还原t1表
rename table t2 to t1;
check table v1\G
*************************** 1. row ***************************
Table: test.v1
Op: check
Msg_type: status
Msg_text: OK
1 row in set (0.00 sec)
2 定期优化表
optimize [local|no_write_to_binlog] table tbl_name[tbl_name2...]
如果已经删除了表的一大部分，或者已经对含有可变长度行的表进行了很多改动
就需要做定期优化。这个命令可以将表中的空间碎片进行合并，
但此命令只对MyISAM，BDB和InnoDB表起作用
optimize table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status | OK |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.16 sec)
不要随便对一个大表用optimize，会很慢
常用SQL优化
1 整个数据库的导入导出
备份t1表，做导入导出
备份test数据库到test.sql文件
/usr/bin/mysqldump -uroot -popenview test > /tmp/test.sql
然后登陆mysql，删除t1
导入数据
/usr/bin/mysql -uroot -popenview test < /tmp/test.sql
登陆mysql，查看t1
对于大量数据的数据库，比可以这么导入导出
2 load data, infile和outfile命令
在mysql命令行中使用
导出数据到文件，只导出表的数据，不包括结构：
select name from t1 into outfile "/tmp/test.txt";
导入文件到数据库
trancate t1; 清空先
load data infile "/tmp/test.txt" into table t1(name);
当用load命令导入数据时，暂时关闭普通索引可以提高导入速度
对于MyISAM存储引擎的表，可以通过下面方式快速导入大量数据
alter table t1 disable keys
。。。loading the data。。。
alter table t1 enable keys
disable keys和enable keys用来打开和关闭MyISAM表非唯一索引的更新
可以提高速度，对InnoDB无效
不要关闭唯一索引
3 针对于InnoDB类型表数据导入的优化
InnoDB表按照主键顺序保存的，所以将导入的数据主键的顺序排列
可以有效地提高导入数据的效率
比如使用test1.txt内的文本数据按照主键顺序保存的
耗时22.92秒
load data infile '/tmp/test1.txt' into table t1;
如果不是按照主键排列的test2.txt
耗时33.16秒，慢了1.12倍
load data infile '/tmp/test2.txt' into table t1;
如果不得不关闭唯一索引
确保数据没有冲突
在导入数据前执行
set unique_checks=0
结束后执行
set unique_checks=1
可以提高导入效率
4 关闭自动提交可以提高导入效率
导入前先执行
set autocommit=0
结束后执行
set autocommit=1
优化insert语句
尽量使用多个值表的insert语句，大大缩短损耗
列入，连接一次，连续插入，关闭一次
insert into t1(name) values("user1"),("user2"),("user3");
可以使用insert delayed (马上执行)语句中的列的顺序必须相同。
将索引文件和数据文件分别存放不同的磁盘上
可以增加bulk_insert_buffer_size变量值的方法对MyISAM表提速
从一个文件中装载一个表时，使用Load data infile
这比insert语句快20倍
优化group by语句
查询包含group by ，如果想避免排序结果的损耗，
可以使用order by null来禁止排序
优化嵌套查询
嵌套查询要少用，可以使用表链接join替代
desc select * from t1 where id in(select uid from t2)\G
显示t1没有用到主键索引，只有t2用到了
所以嵌套查询少用
可以使用
select t1.* from t1,t2 where t1.id=t2.uid;
select t1.* from t1 right join t2 on t1.id=t2.uid ;
这样两张表的索引都用到了
4 MySql数据库优化
优化表的字段类型
认为判断字段适合什么类型
比如所在地区，适合枚举类型enum
字段固定，查询快
对于名字，长度使用30差不多了，不要太长
也可以使用函数procedure analyse()对当前应用的表进行分析，
不过不推荐
mysql> select * from t2 procedure analyse()\G
*************************** 1. row ***************************
Field_name: test.t2.id
Min_value: 1
Max_value: 9
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 5.0000
Std: 2.5820
Optimal_fieldtype: ENUM('1','2','3','4','5','6','7','8','9') NOT NULL
*************************** 2. row ***************************
Field_name: test.t2.name
Min_value: user10
Max_value: user9
Min_length: 5
Max_length: 6
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 5.2222
Std: NULL
Optimal_fieldtype: ENUM('user10','user11','user3','user4','user5','user6','user7','user8','user9') NOT NULL
2 rows in set (0.00 sec)
大存储量问题通过拆分提高表的访问效率
分库（主从数据库）
分表
分区
减少表的记录数
减小对操作系统的负担
使用中间表提高统计查询速度
中间表可以使用普通表，或者试图
例如，当desc select * from t1 where id<4;
可以发现影响行数大于4行，表示中间有其他处理过程
比如主键的影响,这时候先去掉主键
去掉自增属性
alter table t1 modify id int;
去掉主键索引
alter table t1 drop primary key;
然后要把这些频繁被查询的数据放到中间表里面
create table t4 like t1;
insert into t4 select * from t1 where id<4;
但是这样子做不灵活，适用试图更合适
create view v_t1 select * from t1 where id<4;
5 Myisam表读写锁
读锁
lock table t1 read;
开启另一个mysql连接终端，尝试可以select * from t1;
但无法insert,update, delete
做大量数据的备份时需要用到
mysqldump -uroot -popenview test > test.sql
写锁
lock table t1 write;
另起一个终端操作，select, insert, update, delete都不能执行
解锁
unlock tables;
6 MySql服务器优化
查看字符集
show variables like 'character%'\g
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
四种字符集问题
更好的兼容java，php
现在开发网站4种字符集都需要统一用UTF8
1 服务器字符集
2 数据库字符集
3 客户端字符集
4 连接字符集
在mysql的配置文件里面配置
一般是/etc/my.cnf, windows下是my.ini文件，来自my-default.ini
Linux下用rpm包安装的MySQL是不会安装/etc/my.cnf文件的
my.cnf只是MySQL启动时的一个参数文件，可以没有它，
这时MySQL会用内置的默认参数启动
MySQL在启动时自动使用/usr/share/mysql目录下的my-medium.cnf文件，
这种说法仅限于rpm包安装的MySQL
只需要复制一个/usr/share/mysql目录下的my-default.cnf文件到/etc目录，
并改名为my.cnf即可
cp /usr/share/mysql/my-default.cnf /etc/my.cnf
vi /etc/my.cnf
[client]区域找到或者添加，客户端字符集
default-character-set=utf8
[mysqld]区域找到或者添加,服务器字符集，数据库字符集，表字符集
character-set-server=utf8
collation-server=utf8_general_ci 针对数据库校验字符集,比如order by的字典排序
重起服务
pkill mysqld
pstree |grep mysqld
/usr/bin/mysqld_safe --user=mysql & （后台启动）
或者service mysql restart
查看：
mysql> show variables like 'character%'\g
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
查看所有字符集
show character set;
show create table t1;
可以查看t1表结构，属于哪种引擎，MyISAM还是InnoDB，什么字符编码
binary log 日志问题
show variables like "%bin%";
目前是OFF状态
log_bin | OFF
linux默认是开启的
默认在/usr/local/mysql/var/下有mysql-bin.00000x文件
我们系统是/var/lib/mysql/
开启方式：
vi /etc/my.cnf
log_bin=mysql-bin log文件用mysql-bin开头，并且开启日志
可以看到
log_bin | ON
log_bin_basename | /var/lib/mysql/mysql-bin
log_bin_index | /var/lib/mysql/mysql-bin.index
在/var/lib/mysql/下就可以看到mysql-bin.000001文件
slow log慢查询日志问题
show variables like "%slow%";
默认off，
路径是/var/lib/mysql/localhost-slow.log
+---------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
show variables like "%long%";
默认写慢日志时间是10秒
| long_query_time | 10.000000 |
vi /etc/my.cnf
[mysqld]
slow_query_log
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=5
重起服务后
!ps
查看进程，表示运行刚才运行过的ps命令
相当于ps -ef | grep mysql
结果
root 33422 3522 0 03:20 pts/0 00:00:00 grep mysql
查看端口占用情况
netstat -tunpl | grep :3306
tcp 0 0 :::3306 :::* LISTEN 34126/mysqld
再使用!net
效果同netstat -tunpl | grep :3306
表示运行刚才运行过的net命令
在/var/lib/mysql/下可以看到slow.log
之后超过5秒的sql语句都会记录进去
socket问题
登陆时需要用socket程序
默认保存在tmp下
我们环境里它在/var/lib/mysql/mysql.sock
如果删除这个文件，就无法登陆了
有时登陆提示不能使用socket登陆，
可以换成tcp方式登陆
可以手动命令行设置临时登陆方式
mysql -uroot -popenview --protocol tcp -hlocalhost
这样就可以不使用mysql.sock来登陆
永久设置，网站还是需要mysql.sock的
mysql重新启动，就会生成mysql.sock
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
skip-locking
root密码丢失
service mysql stop
//跳过授权表mysql.user和mysql.db这些表
//用户信息都在mysql.user表里
//select user,host,password from mysql.user;
mysqld_safe --skip-grant-tables --user=mysql &
//登陆时不再需要密码
mysql -uroot
//最通用的修改密码语句如下
//用password()技术来加密，比md5更严格
update user set password = password("openview") where user='root' and host='localhost';
//也可以用
set password for root@localhost=password("openview");
//使用下面语句报错，因为加了--skip-grant-tables
set password=password("openview");
509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
