mysql 相关2-白红宇

mysql 相关2

阅读量：7030 次

发布时间：2019-06-28

本文共 9700 字，大约阅读时间需要 32 分钟。

1、查看当前所有连接的详细资料:

mysqladmin -uroot -proot processlist

D:\MySQL\bin>mysqladmin -uroot -proot processlist

+-----+------+----------------+---------+---------+------+-------+------------------+

+-----+------+----------------+---------+---------+------+-------+------------------+

| 591 | root | localhost:3544 | bbs | Sleep | 25 | | |

+-----+------+----------------+---------+---------+------+-------+------------------+

2、只查看当前连接数(Threads就是连接数.):

mysqladmin -uroot -proot status

D:\MySQL\bin>mysqladmin -uroot -proot status

Uptime: 2102 Threads: 3 Questions: 15531 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 61 Queries per second avg: 7.389

3、修改mysql最大连接数：

打开my.ini，修改max_connections=100(默认为100)。

命令： show processlist;

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。

show processlist;只列出前100条，如果想全列出请使用show full processlist;

缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。

　　查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。

　　下面sql查询缓存认为是不同的：

　　SELECT * FROM tbl_name

　　Select * from tbl_name

　　查询缓存相关参数

mysql> SHOW VARIABLES LIKE ’%query_cache%’;

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES | --查询缓存是否可用

| query_cache_limit | 1048576 | --可缓存具体查询结果的最大值

| query_cache_min_res_unit | 4096 |

| query_cache_size | 599040 | --查询缓存的大小

| query_cache_type | ON | --阻止或是支持查询缓存

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

　　下面是一个简单的例子：

[mysql@csdba1850 ~]$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 3

Server version: 5.0.45-community MySQL Community Edition (GPL)

Type ’help;’ or ’\\h’ for help. Type ’\\c’ to clear the buffer.

mysql> set global query_cache_size = 600000; --设置缓存内存

Query OK, 0 rows affected (0.00 sec)

mysql> set session query_cache_type = ON; --开启查询缓存

Query OK, 0 rows affected (0.00 sec)

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| animals |

| person |

+----------------+

5 rows in set (0.00 sec)

mysql> select count(*) from animals;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

--Qcache_hits表示sql查询在缓存中命中的累计次数，是累加值。

mysql> SHOW STATUS LIKE ’Qcache_hits’;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Qcache_hits | 0 | --0次 [Page]

+---------------+-------+

8 rows in set (0.00 sec)

mysql> select count(*) from animals;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE ’Qcache%’;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Qcache_hits | 1 | --表示sql在缓存中直接得到结果，不需要再去解析

+---------------+-------+

8 rows in set (0.00 sec)

mysql> select count(*) from animals;

+----------+ | count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

mysql> select count(*) from animals;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE ’Qcache_hits’;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Qcache_hits | 3 | --上面的sql也是是从缓存中直接取到结果

1rowinset(0.00sec) mysqlinsertintoanimalsselect9,testsds;--插入数据后，跟这个表所有相关的sql缓存就会被清空掉 QueryOK,1rowaffected(0.00sec) Records:1Duplicates:0Warnings:0 mysqlselectcount(*)fromanima

1 row in set (0.00 sec)

mysql> insert into animals select 9,’testsds’ ; --插入数据后，跟这个表所有相关的sql缓存就会被清空掉

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select count(*) from animals;

+----------+

| count(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE ’Qcache_hits’;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Qcache_hits | 3 | --还是等于3，说明上一条sql是没有直接从缓存中直接得到的

+---------------+-------+

1 row in set (0.00 sec)

mysql> select count(*) from animals;

+----------+

| count(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE ’Qcache_hits’;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Qcache_hits | 4 |

+---------------+-------+

1 row in set (0.00 sec)

当查询接收到一个和之前同样的查询，

服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。

这样就大大提高了性能，节省时间，非常有用。

打开查询缓存，是通过几个步骤来设置的，例如：虽然你设置Mysql允许查询缓存，但

是如果你设置的查询缓存大小为了0，这和没有允许没什么区别。

所以必须是几个步骤的设置才能真正打开查询缓存这个功能。

下面我用 mysql6.0 最为演示最常用的设置查询缓存

第一： query_cache_type 使用查询缓存的方式

一般，我们会把 query_cache_type 设置为 ON,默认情况下应该是ON

mysql> select @@query_cache_type;

+--------------------+

| @@query_cache_type |

+--------------------+

| ON |

+--------------------+

这样当我们执行 select id,name from tableName; 这样就会用到查询缓存。

在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明

select sql_no_cache id,name from tableName;

当然也可以禁用查询缓存： mysql> set session query_cache_type=off;

这里我们不讨论这个，我们演示常用的设置。

第二：系统变量 have_query_cache 设置查询缓存是否可用

mysql> show variables like 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

+------------------+-------+

上面的显示，表示设置查询缓存是可用的。

第三：系统变量 query_cache_size

表示查询缓存大小，也就是分配内存大小给查询缓存，如果你分配大小为0，

那么第一步和第二步起不到作用，还是没有任何效果。

mysql> select @@global.query_cache_size;

+---------------------------+

| @@global.query_cache_size |

+---------------------------+

| 16777216 |

+---------------------------+

上面是 mysql6.0设置默认的，之前的版本好像默认是0的，那么就要自己设置下。

设置 set @@global.query_cache_size=1000000; 这里是设置1M左右，900多K。

再次查看下 select @@global.query_cache_size;

+---------------------------+

| @@global.query_cache_size |

+---------------------------+

| 999424 |

+---------------------------+

显示我们设置新的大小，表示设置成功。

第四： query_cache_limit 控制缓存查询结果的最大值

例如：如果查询结果很大，也缓存？？？？这个明显是不可能的。

MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会

进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。

mysql> select @@global.query_cache_limit;

+----------------------------+

| @@global.query_cache_limit |

+----------------------------+

| 1048576 |

+----------------------------+

这个是默认的数值，如果需要修改，就像设置缓存大小一样设置，使用set

重新指定大小。

MySQL查询缓存

注意：查询缓存绝不返回过期数据。

如果一个表发生的改变（insert,update,delect,truncate,alter或drop table|database）,那么所有这张表使用的缓冲的查询将被得失效，并从缓存中移除。

如果一个查询包括下面的函数，它将不能被缓存。

user-defined functions connection_id found_rows get_lock release_lock

load_file master_pos_wait now sysdate current_timestamp curdate

current_date curtime current_time database encrypt last_insert_id

rand unix_timestamp user benchmark

查询缓存选项 select @@query_cache_type;

select sql_cache[选项on/off]

select sql_no_cache id,name from customer;

系统变量have_query_cache表示查询缓存是否可用。

show variables like ‘have_query_cache’;

系统查询query_cache_size表示查询缓存的大小。如果设置它为0，查询缓存将被禁止（缺省值为0）。

select @@global.query_cache_size;

若启用查询缓存，则系统变量query_cache_type表示查询缓存工作方式，他可以设置成如下值：

0（off,不缓存结果）

1（on,缓存所有的结果，除了select sql_no_cache… 查询）

2（demand,仅缓存select sql_cache… 查询）

用户可以禁止查询缓存；

set session query_cache_type=off;

若查询结果很大，缓存吗？

系统变量query_cache_limit控制可以缓存的查询结果的最大值，不缓存大于这个值的结果（缺省为1M）。

碎片整理

使用flush query cache命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。

清除cache

seset query cache命令从查询缓存中移除所有的查询结果。

flush tables也会清除查询缓存。

我们可以使用show status来监视查询缓存性能。

show status like ‘qcache%’;

变量含义

Qcache_queries_in_cache 搜索缓存中结果集的查询数目

Qcache_inserts 被加入到缓存中的查询数目

Qcache_hits 缓存命令数目

Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目

Qcache_not_cached 没有被缓存的查询结果数目（不能被缓存的，或由于

Query_cache_type）

Qcache_free_memory 查询缓存的空闲内存总数

Qcache_free_blocks 查询缓存的空闲内存块的数目

Qcache_total_blocks 查询缓存中的块的总数目

--------------------------------------------

MySQL 数据库管理

MySQL数据库管理

MySQL启动/停止/测试

添加用户

增加一个用户test_user密码为123，让他可以在任何主机上登陆，并对所有数据库有查询、插入、修改、删除的权限。

grant select,insert,update,delete on *.* to ‘test_user’@ ‘%’ identified by

‘123’;

增加一个用户test_user2密码123，让他只可以在localhost上登陆，并可以对数据库test进行查询、插入、修改、删除的操作（localhost指本地主机，即MySQL数据库所在的那台主机），这样用户即使知道test_user2的密码，他也无法从Internet上直接访问数据库，只能通过MySQL主机上的web页来访问了。

grant select,insert,update,delete on test.* to ‘test_user2’@ ‘localhost’ identfied by ‘123’;

创建一个本地的完全的超级用户admin，口令 ‘123’

grant all privileges on *.* to admin@localhost identified by ‘123’ with grant option ;

删除用户

revoke all on *.* from ‘test_user’@localhost;

delete from user where user= ‘test_user’;

flush privileges;

删除匿名用户

mysql mysql

delete from user where host= ‘localhost’ and user= ‘’;

flush privileges;#刷新内存授权表

修改密码