ubuntu系统下mysql重置密码和修改密码操作

一、忘记密码后想重置密码

在介绍修改密码之前,先介绍一个文件/etc/mysql/debian.cnf.其主要内容如下图:

里面有一个debian-sys-maint用户,这个用户只有Debian或Ubuntu服务器才有,所以如果您的服务器是Debain或Ubuntu,debian-sys-maint是个Mysql安装之后自带的用户,具体作用是重启及运行mysql服务。所以如果忘了root密码,可以通过这个用户来重设密码。下面介绍具体操作:

操作步骤

1.进入/etc/mysql/目录,并用root权限打开debian.cnf文件

> cd /etc/mysql

> sudo vim debian.cnf

2.使用这个文件中的用户名和密码进入mysql

> mysql -u debian-sys-maint -p

然后输入文件中的password值,即可进入mysql。

3.选择mysql数据库(用户名和密码均存储在此数据库的user表中)

> use mysql;

4.显示user表中的列

>show fields from user;  或者(describe user;)

authentication_string这列就是密码(注:以前的版本这个字段是password,如果是password下面的操作将authentication_string替换成password即可)

 

5.修改密码(修改密码为:123456)

update mysql.user set authentication_string=password(‘123456′) where user=’root’

 

6. 退出

>exit

 

7. 重启mysql

service mysql restart

PHP的 mysql 驱动与api

MySQLli : 支持面向对象接口

prepared 语句支持,

多语句支持 mysqli_multi_query(),多个查询语句

pdo_mysql:都支持预编译,防注入只能作为最后一道防线

mysql_real_escape_string 本函数将 unescaped_string 中的特殊字符转义

myqli  prepare  参数绑定 bind_param(‘s’,$city); 绑定参数有MySQL api处理

原则:

1.不要相信任何第三方的数据,根据业务校验,验证数据类型

mysql分组取最大(最小、最新、前N条)条记录

mysql分组取最大(最小、最新、前N条)条记录

 

创建表并插入数据:

create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2')
insert into tb values('a',    1,   'a1')
insert into tb values('a',    3,   'a3')
insert into tb values('b',    1,   'b1')
insert into tb values('b',    3,   'b3')
insert into tb values('b',    2,   'b2')
insert into tb values('b',    4,   'b4')
insert into tb values('b',    5,   'b5')

数据表如下:

name val memo
a 2 a2
a 1 a1
a 3 a3
b 1 b1
b 3 b3
b 2 b2
b 4 b4
b 5 b5

 

按name分组取val最大的值所在行的数据

方法一:

select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name

方法二:

select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)

方法三:

select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

方法四:

select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by

方法五:

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

以上五种方法运行的结果均为如下所示:

name val memo
a 3 a3
b 5 b5

小编推荐使用第一、第三、第四钟方法,结果显示第1,3,4种方法效率相同,第2,5种方法效率差些。

按name分组取val最小的值所在行的数据

方法一:

select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name

方法二:

select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)

方法三:

select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

方法四:

select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

方法五:

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name

以上五种方法运行的结果均为如下所示:

name val memo
a 1 a1
b 1 b1

 

按name分组取第一次出现的行所在的数据

sql如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name

结果如下:

name val memo
a 2 a2
b 1 b1

 

按name分组随机取一条数据

sql如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name

结果如下:

name val memo
a 1 a1
b 3 b3

 

按name分组取最小的两个(N个)val

第一种方法:

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val

第二种方法:

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val

第三种方法:

select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name

结果如下:

name val memo
a 1 a1
a 2 a2
b 1 b1
b 2 b2

 

按name分组取最大的两个(N个)val

第一种方法:

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val

第二种方法:

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val

第三种方法:

select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name

结果如下:

name val memo
a 3 a3
a 2 a2
b 5 b5
b 4 b4

Mysql主从配置,实现读写分离

大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库必然会崩溃,数据丢失的话,后果更是 不堪设想。这时候,我们会考虑如何减少数据库的联接,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据缓存技术如:memcached,如果资金丰厚的话,必然会想到假设服务器群,来分担主数据库的压力。Ok切入今天微博主题,利用MySQL主从配置,实现读写分离,减轻数据库压力。这种方式,在如今很多网站里都有使用,也不是什么新鲜事情,今天总结一下,方便大家学习参考一下。

概述:搭设一台Master服务器(win8.1系统,Ip:192.168.0.104),搭设两台Slave服务器(虚拟机——一台Ubuntu,一台 Windows Server 2003)

原理:主服务器(Master)负责网站NonQuery操作,从服务器负责Query操作,用户可以根据网站功能模特性块固定访问Slave服务器,或者自己写个池或队列,自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件,实现数据同步。二进制日志由主服务器产生,从服务器响应获取同步数据库。

具体实现:

1、在主从服务器上都装上MySQL数据库,windows系统鄙人安装的是mysql_5.5.25.msi版本,Ubuntu安装的是mysql-5.6.22-linux-glibc2.5-i686.tar

windows安装mysql就不谈了,一般地球人都应该会。鄙人稍微说一下Ubuntu的MySQL安装,我建议不要在线下载安装,还是离线安装的好。大家可以参考  http://www.linuxidc.com/Linux/2013-01/78716.htm 这位不知道大哥还是姐妹,写的挺好按照这个就能装上。在安装的时候可能会出现几种现象,大家可以参考解决一下:

(1)如果您不是使用root用户登录,建议 su – root 切换到Root用户安装,那就不用老是 sudo 了。

(2)存放解压的mysql 文件夹,文件夹名字最好改成mysql

(3)在./support-files/mysql.server start 启动MySQL的时候,可能会出现一个警告,中文意思是启动服务运行读文件时,忽略了my.cnf文件,那是因为my.cnf的文件权限有问题,mysql会认为该文件有危险不会执行。但是mysql还会启动成功,但如果下面配置从服务器参数修改my.cnf文件的时候,你会发现文件改过了,但是重启服务时,修改过后的配置没有执行,而且您 list一下mysql的文件夹下会发现很多.my.cnf.swp等中间文件。这都是因为MySQL启动时没有读取my.cnf的原因。这时只要将my.cnf的文件权限改成my_new.cnf的权限一样就Ok,命令:chmod 644 my.cnf就Ok

(4)Ubuntu中修改文档内容没有Vim,最好把Vim 装上,apt-get install vim,不然估计会抓狂。

这时候我相信MySQL应该安装上去了。

2、配置Master主服务器

(1)在Master MySQL上创建一个用户‘repl’,并允许其他Slave服务器可以通过远程访问Master,通过该用户读取二进制日志,实现数据同步。

 创建用于读取日志的数据库用户
1 mysql>create user repl; //创建新用户
2 //repl用户必须具有REPLICATION SLAVE权限,除此之外没有必要添加不必要的权限,密码为mysql。说明一下192.168.0.%,这个配置是指明repl用户所在服务器,这里%是通配符,表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。
3 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'mysql';

(2)找到MySQL安装文件夹修改my.Ini文件。mysql中有好几种日志方式,这不是今天的重点。我们只要启动二进制日志log-bin就ok。

在[mysqld]下面增加下面几行代码

 View Code
1 server-id=1   //给数据库服务的唯一标识,一般为大家设置服务器Ip的末尾号
2 log-bin=master-bin
3 log-bin-index=master-bin.index

重启MySQL服务

(3)查看日志

mysql> SHOW MASTER STATUS;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000001 | 1285 | | |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)

 

3、配置Slave从服务器(windows)

(1)找到MySQL安装文件夹修改my.ini文件,在[mysqld]下面增加下面几行代码

 my.cnf 配置
1 [mysqld]
2 server-id=2
3 relay-log-index=slave-relay-bin.index
4 relay-log=slave-relay-bin

重启MySQL服务

(2)连接Master

change master to master_host=’192.168.0.104′, //Master 服务器Ip
master_port=3306,
master_user=’repl’,
master_password=’mysql’,
master_log_file=’master-bin.000001′,//Master服务器产生的日志
master_log_pos=0;

(3)启动Slave

start slave;

4、Slave从服务器(Ubuntu)

(1)找到MySQL安装文件夹修改my.cnf文件,vim my.cnf

 s

 

(2) ./support-files/myql.server restart 重启MySQL服务  ,  ./bin/mysql 进入MySQL命令窗口

(3)连接Master

change master to master_host=’192.168.0.104′, //Master 服务器Ip
master_port=3306,
master_user=’repl’,
master_password=’mysql’,
master_log_file=’master-bin.000001′,//Master服务器产生的日志
master_log_pos=0;

(4)启动Slave

start slave;

OK所有配置都完成了,这时候大家可以在Master Mysql 中进行测试了,因为我们监视的时Master mysql  所有操作日志,所以,你的任何改变主服务器数据库的操作,都会同步到从服务器上。创建个数据库,表试试吧。。。

 

查看MYSQL数据库中所有用户及拥有权限

查看MYSQL数据库中所有用户及拥有权限

查看MYSQL数据库中所有用户
mysql> SELECT DISTINCT CONCAT(‘User: ”’,user,”’@”’,host,”’;’) AS query FROM mysql.user;
+—————————————+
| query                                 |
+—————————————+
| User: ‘cactiuser’@’%’;                |
| User: ‘common_user’@’%’;              |
| User: ‘dba’@’%’;                      |
| User: ‘root’@’%’;                     |
| User: ‘slave’@’%’;                    |
| User: ‘wtgame’@’%’;                   |
| User: ‘root’@’127.0.0.1’;             |
| User: ‘webman’@’127.0.0.1’;           |
| User: ‘root’@’192.168.166.200’;       |
| User: ‘cactiuser’@’192.168.166.202’;  |
| User: ‘root’@’192.168.166.202’;       |
| User: ”@’localhost’;                 |
| User: ‘cacti’@’localhost’;            |
| User: ‘cactiuser’@’localhost’;        |
| User: ‘dba’@’localhost’;              |
| User: ‘extmail’@’localhost’;          |
| User: ‘javauser’@’localhost’;         |
| User: ‘root’@’localhost’;             |
| User: ‘webman’@’localhost’;           |
| User: ”@’localhost.localdomain’;     |
| User: ‘root’@’localhost.localdomain’; |
+—————————————+
21 rows in set (0.01 sec)
查看数据库中具体某个用户的权限
mysql> show grants for ‘cactiuser’@’%’;
+——————————————————————————————————————-+
| Grants for cactiuser@%                                                                                            |
+——————————————————————————————————————-+
| GRANT PROCESS, SUPER ON *.* TO ‘cactiuser’@’%’ IDENTIFIED BY PASSWORD ‘*DAFF917B80E3314B1ABECBA9DF8785AFD342CE89’ |
| GRANT ALL PRIVILEGES ON `cacti`.* TO ‘cactiuser’@’%’                                                              |
+——————————————————————————————————————-+
2 rows in set (0.00 sec)
mysql> select * from mysql.user where user=’cactiuser’ \G
*************************** 1. row ***************************
Host: %
User: cactiuser
Password: *DAFF917B80E3314B1ABECBA9DF8785AFD342CE89
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: Y
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
查看user表结构 需要具体的项可结合表结构来查询
mysql> desc mysql.user;
+———————–+———————————–+——+—–+———+——-+
| Field                 | Type                              | Null | Key | Default | Extra |
+———————–+———————————–+——+—–+———+——-+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum(‘N’,’Y’)                     | NO   |     | N       |       |
******************
******************
+———————–+———————————–+——+—–+———+——-+
39 rows in set (0.00 sec)

MySQL-负载很高排查思路

MySQL-负载很高排查思路

工欲善其事必先利其器,我说一下思路

思路:
1、确定高负载的类型 htop,dstat命令看负载高是CPU还是IO
2、监控具体的sql语句,是insert update 还是 delete导致高负载
3、检查mysql日志
4、检查硬件问题

dstat

可以看到具体是哪个用户哪个进程占用了相关系统资源,当前CPU、内存谁在使用

[root@cc ~]# dstat -l -m -r -c  --top-io --top-mem --top-cpu

--io/total- ------memory-usage----- --most-expensive- ----most-expensive---- -most-expensive-
 read  writ| used  buff  cach  free|  memory process |     i/o process      |  cpu process   
1.90   267 |3399M  178M 3892M  400M|php-fpm: poo 372M|init       1682k  647k|flush-202:0  0.1
   0  72.0 |3399M  178M 3892M  400M|php-fpm: poo 372M|php-fpm: po  10k  143k|php-fpm: pool2.0
   0  8.00 |3399M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 228k  229k|php-fpm: pool0.5
   0  88.0 |3399M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 102k  166k|php-fpm: pool 11
   0  38.0 |3399M  178M 3892M  399M|php-fpm: poo 372M|php-fpm: po 787k  650B|php-fpm: pool4.8
   0     0 |3399M  178M 3892M  399M|php-fpm: poo 372M|php-fpm: po 788k  723B|php-fpm: pool1.8
   0   140 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work  38k  154k|php-fpm: pool1.2
   0  12.0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 178k  364k|php-fpm: pool1.5
   0     0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 758k  639k|php-fpm: pool1.5
   0  12.0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 773k  616k|php-fpm: pool2.0
6.00     0 |3401M  178M 3892M  398M|php-fpm: poo 372M|nginx: work 994k  688k|nginx: worker1.5
   0   272 |3401M  178M 3892M  398M|php-fpm: poo 372M|nginx: work 388k  422k|php-fpm: pool1.5
   0     0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 483k  548k|php-fpm: pool1.8
   0  4.00 |3400M  178M 3893M  398M|php-fpm: poo 372M|php-fpm: po 787k  650B|php-fpm: pool1.5
   0  12.0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 223k  323k|php-fpm: pool1.5
   0     0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 371k  474k|php-fpm: pool7.8

htop

htop是top的增强版,更直观

[root@cc ~]# htop

  1  [|||||||||||                                                                    12.4%]
  2  [|||||||||                                                                       9.5%]
  3  [|                                                                               1.0%]
  4  [||                                                                              1.9%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||3394/7869MB]
  Swp[||||||||||||||                                                              75/478MB]
        
Tasks: 71, 12 thr; 2 running
Load average: 0.39 0.39 0.31 
Uptime: 526 days(!), 17:36:38

  PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command
    1 root       20   0 19232   396   248 S  0.0  0.0  0:01.86 /sbin/init
30752 root       20   0 52532    72    56 S  0.0  0.0  0:00.16 ├─ /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf
24301 root       20   0  193M  3268  1600 S  0.0  0.0  1:41.43 ├─ /usr/sbin/snmpd -LS0-6d -Lf /dev/null -p /var/run/snmpd.pid
21361 root       20   0  902M  6500  1308 S  0.0  0.1  0:07.16 ├─ php-fpm: master process (/etc/php-fpm.conf)
28627 www        20   0  962M  202M  138M S  0.0  2.6  0:34.46 │  ├─ php-fpm: pool www-c
27537 www        20   0  965M  236M  171M R  1.4  3.0  1:19.64 │  ├─ php-fpm: pool www-c
27449 www        20   0  961M  251M  189M S  0.0  3.2  1:35.54 │  ├─ php-fpm: pool www-a
26442 www        20   0  962M  280M  217M S  0.0  3.6  2:29.71 │  ├─ php-fpm: pool www-a
26310 www        20   0  917M  251M  234M S  1.9  3.2  2:46.45 │  ├─ php-fpm: pool www-a
26162 www        20   0  962M  297M  233M S  0.0  3.8  2:37.50 │  ├─ php-fpm: pool www-b
26147 www        20   0  924M  258M  233M S  0.0  3.3  2:38.37 │  ├─ php-fpm: pool www-c
25717 www        20   0  965M  302M  238M S  0.0  3.8  2:54.50 │  ├─ php-fpm: pool www-c
24585 www        20   0  964M  324M  260M S  0.0  4.1  4:15.20 │  ├─ php-fpm: pool www-b

tcpdump

抓取mysql包分析,一般抓3306端口的数据

[root@cc ~]# tcpdump -i eth0 -A -s 3000 port 3306 > ~/sql.log

然后使用awk,sort,wc 等命令进行分析

或者

[root@cc ~]# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$qn"; }
    $q=$_;
    } else {
    $_ =~ s/^[ t]+//; $q.=" $_";
    }
}'

就可以看出最繁忙的sql语句了

strace

查看系统调用是否有问题,进程是否堵塞,是否有Broken pipe

[root@cc ~]# strace -p 26578

pt-query-digest

分析mysql慢日志,查看哪些sql语句最耗时

[root@cc ~]#  pt-query-digest slow.logs

# 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz  
# CURRENT DATE: Thu DEC 29 13:22:42 2014  
# Hostname: test   
# Files: slow.log  
# Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________  
# TIME range: 2011-09-10 04:03:19 TO 2011-12-29 05:02:51  
# Attribute          total     MIN     MAX     avg     95%  stddev  median  
# ============     ======= ======= ======= ======= ======= ======= =======  
# EXEC TIME          5657s      2s     33s      7s     23s      6s      5s  
# LOCK TIME            33s       0     19s    43ms    98us   715ms    38us  
# ROWS sent        323.38k       0 107.36k  426.73    0.99   6.35k       0  
# ROWS examine     323.39k       0 107.36k  426.74       0   6.35k       0  
# Query SIZE       217.95k      38     562  287.61  420.77   81.78  284.79  

show processlist

查看系统到底在干什么


mysql> show full processlist;
+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
| Id        | User          | Host                | db                  | Command | Time | State         | Info                      |
+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
| 184498848 | testdb_rr1356 | 10.11.211.120:61343 | testdb_rr1356_db121 | Sleep   | 1384 |               | NULL                      |
| 184508740 | testdb_rr1356 | 10.11.211.120:11809 | testdb_rr1356_db121 | Sleep   |   87 |               | NULL                      |
| 184509415 | testdb_rr1356 | 10.11.211.120:12760 | testdb_rr1356_db121 | Query   |    0 | NULL          | show full processlist     |
| 184509451 | testdb_rr1356 | 10.11.211.120:12804 | testdb_rr1356_db121 | Sleep   |   10 |               | NULL                      |
| 184509528 | testdb_rr1356 | 10.11.211.120:12919 | testdb_rr1356_db121 | Query   |    0 | freeing items | DESCRIBE test_channel     |

检查mysql配置参数是否有问题,引起大量的IO或者高CPU操作

innodb_flush_log_at_trx_commit 、innodb_buffer_pool_size 、key_buffer_size 等重要参数

mysql> show variables like '%innodb%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| have_innodb                     | YES                        |
| ignore_builtin_innodb           | ON                         |
| innodb_adaptive_flushing        | ON                         |
| innodb_adaptive_hash_index      | ON                         |
| innodb_additional_mem_pool_size | 2097152                    |
| innodb_autoextend_increment     | 8                          |
| innodb_autoinc_lock_mode        | 1                          |
| innodb_buffer_pool_size         | 2013265920                 |
| innodb_change_buffering         | inserts                    |
| innodb_checksums                | ON                         |

通过show engine innodb status查看当前事务,内存使用

mysql> show engine innodb status  \G

LATEST DETECTED DEADLOCK
------------------------
150731 10:36:50
*** (1) TRANSACTION:
TRANSACTION EBFBBEC, ACTIVE 0 sec, process no 20691, OS thread id 47345217033984 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2
MySQL thread id 143249904, query id 1286731854 10.135.21.120 tybuser2014 update

#此处具体sql省略

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2058485760; in additional pool allocated 0
Dictionary memory allocated 819282
Buffer pool size   122879
Free buffers       97599
Database pages     24313
Old database pages 8954
Modified db pages  7
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1049, created 41853, written 30401604
0.00 reads/s, 0.00 creates/s, 1.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24313, unzip_LRU len: 0
I/O sum[45]:cur[0], unzip sum[0]:cur[0]

最后通过zabbix或者cacti等监控来查看IO、CPU、MEMORY、磁盘等是否有异常

这样基本上就可以把问题找出来了

仅供参考,当然,不同的DBA肯定还有其他更好的方式

分类: MySQL

mysql百万数据分页优化

背景:

自己的一个网站,由于单表的数据记录高达了一百万条,造成数据访问很慢,Google分析的后台经常报告超时,尤其是页码大的页面更是慢的不行。

测试环境:

先让我们熟悉下基本的sql语句,来查看下我们将要测试表的基本信息

use infomation_schema
SELECT * FROM TABLES WHERE TABLE_SCHEMA = ‘dbname’ AND TABLE_NAME = ‘product’

查询结果:

从上图中我们可以看到表的基本信息:

表行数:866633
平均每行的数据长度:5133字节
单表大小:4448700632字节

关于行和表大小的单位都是字节,我们经过计算可以知道
平均行长度:大约5k
单表总大小:4.1g
表中字段各种类型都有varchar、datetime、text等,id字段为主键

测试实验

1.   直接用limit start, count分页语句, 也是我程序中用的方法:

select * from product limit start, count
当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下(也就是记录的一般左右)                                    select * from product limit 400000, 20   3.229秒

再看我们取最后一页记录的时间
select * from product limit 866613, 20   37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时,像这种分页最大的页码页显然这种时
间是无法忍受的。

从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

2.   对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈

另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
查询时间也很短,赞!

其实两者用的都是一个原理嘛,所以效果也差不多

mysql中的where和having子句的区别

having的用法

having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

SQL实例:

一、显示每个地区的总人口数和总面积.
SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

先以region把返回记录分成多个组,这就是GROUP BY的字面含义。分完组后,然后用聚合函数对每组中
的不同字段(一或多条记录)作运算。

二、 显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

在这里,我们不能用where来筛选超过1000000的地区,因为表中不存在这样一条记录。
相反,having子句可以让我们筛选成组后的各组数据

mysql判断某个字段的长度:

select home_page from aaa表 where char_length(trim(home_page))<10 and char_length(trim(home_page))>1;

mysql中的where和having子句的区别

mysql中的where和having子句都可以实现过滤记录的功能,但他们的用法还是有一些区别的,看一例子:
用group by和having子句联合来查出不重复的记录,sql如下:
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email
然后看这个,就容易理解了
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email HAVING ct > 1
先用group by 对email进行分组,在用having来过滤大于1的,这样查找出来的就是重复的记录了.

以下是having和where的区别:
Select city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
作用的对象不同。WHERE 子句作用于表和视图,HAVING 子句作用于组。
WHERE 在分组和聚集计算之前选取输入行(因此,它控制哪些行进入聚集计算), 而 HAVING 在分组和聚集之后选取分组的行。因此,WHERE 子句不能包含聚集函数; 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反,HAVING 子句总是包含聚集函数。(严格说来,你可以写不使用聚集的 HAVING 子句, 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。)
在前面的例子里,我们可以在 WHERE 里应用城市名称限制,因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效,因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算
综上所述:
having一般跟在group by之后,执行记录组选择的一部分来工作的。
where则是执行所有数据来工作的。
再者having可以用聚合函数,如having sum(qty)>1000

mysql 创建数据库以及常用语句

基本语法

创建数据库

CREATE DATABASE 数据库名字;

CREATE DATABASE db2 CHARACTER SET UTF8; //字符集utf8

CREATE DATABASE db3 CHARACTER SET utf8 COLLATE utf8_general_ci; //带校对规则的(校对规则可理解为排序的规则,ci代表大小写不敏感)

操作数据的三个指令

显示所有数据库

show databases;

显示当前创建数据库的指令

show create database 数据库名;

删除数据库

drop database 数据库名;

查看数据表结构

desc 表名

控制台发送编码设置

MySQL>set names gbk

mysql> \s