Mysql

Profile

profile 用于查看在服务器上执行所有语句的耗时和其他一些查询执行状态变更的数据.

mysql> set profiling = 1;  #设置变量
mysql> select * from mytable;
mysql> show profiles;   # 查看结果
mysql> show profile for query 1;  #查看指定ID的sql执行详细信息
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=26; #查看指定ID的sql执行详细信息

每秒刷新当前连接数和sql数

$ mysqladmin ext -i1 -uroot -proot | awk '
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

Mysql 操作技巧

$ mysql -e "sql"  命令行执行sql
mysql> show processlist; 查看当前的连接客户端

Mac开始漫查询

$sudo vim /private/etc/my.cnf

[mysqld]
long_query_time = 0  #查询时间大于设置的,0表示所有
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log

$mysql sudo mkdir /var/log/mysql                                                                                                                                                                                                                                                                                                                    $mysql sudo touch /var/log/mysql/slow-query.log
$mysql sudo chown -R _mysql /var/log/mysql/
$awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' /var/log/mysql/slow-query.log #查看每秒查询数
$tail -f /var/log/mysql/slow-query.log #时时打印sql

web集成测试工具:
1. ab
2. http_load
3. Jmeter

Mysql 测试工具:
1. Mysqlslap
2. mysql benchmark suite (sql-bench) 内置, mysql 基准测试套件
3. super smack
4. database test suite
5. percona’s TPCC-MySQL tool
6. sysbench 测试IO
7. Mysql benchmar可以某些特定操作的执行速度: SELECT BENCHMARK(100001100, MD5(@input));

Mysql 工具包 percona-toolkit

Install percona-toolkit on Mac OSX


pt-mysql-summary 和 pt-summary 打印状态和配置信息. 以及操作系统硬件信息
pt-sift 导航样本数据

Mysql 开启漫查询,记录sql

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)
long_query_time=2 (记录超过的时间,默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
log-long-format (如果设置了,所有没有使用索引的查询也将被记录)

Centos 编译PHP-fpm mysql nginx

前言
我的编译目录分别为

/app/mysql
/app/php
/app/nginx

MYSQL

下载必要的软件

yum -y install make gcc-c++ cmake bison-devel  ncurses-devel

下载mysql, 这里现在最新的是5.6.26, 要下载其它的把URL后面的版本号改成你需要的版本号就可以了

wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.26.tar.gz
tar -zxvf mysql-5.6.26.tar.gz
cd mysql-5.6.26

开始编译

cmake \
-DCMAKE_INSTALL_PREFIX=/app/mysql/ \
-DMYSQL_DATADIR=/app/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

make && make install

创建用户组

groupadd mysql
useradd -g mysql mysql

修改mysql目录权限

chown -R mysql:mysql /app/mysql

执行初始化配置

/app/mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data --user=mysql
#移除默认的my.cnf文件
mv /etc/my.cnf /etc/myback.cnf
cp /app/mysql/support-files/mysql.server /etc/init.d/mysql
chkconfig mysql on
#启动mysql
/etc/init.d/mysql start

编辑/etc/profile 增加

PATH=/app/mysql/bin:$PATH
export PATH
source /etc/profile

设置mysql密码

mysql_secure_installation

mysqlconfig

mysql基本配置

vim /app/mysql/my.cnf

[mysqld]
socket=/tmp/mysql.sock

建议sock文件

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

Nginx

安装必要软件

yum -y install gcc gcc-c++ make zlib-devel pcre-devel openssl-devel

下载nginx
http://nginx.org/en/download.html 选择最新的

wget http://nginx.org/download/nginx-1.9.5.tar.gz
tar -zxvf nginx-1.9.5.tar.gz
cd nginx-1.9.5

创建用户

useradd -r nginx

开始编译

./configure \
--user=nginx                          \
--group=nginx                         \
--prefix=/app/nginx                   \
--sbin-path=/usr/sbin/nginx           \
--conf-path=/app/nginx/nginx.conf     \
--pid-path=/var/run/nginx.pid         \
--lock-path=/var/run/nginx.lock       \
--error-log-path=/var/log/nginx/error.log \
--http-log-path=/var/log/nginx/access.log \
--with-http_gzip_static_module        \
--with-http_stub_status_module        \
--with-http_ssl_module                \
--with-pcre                           \
--with-file-aio                       \
--with-http_realip_module             \
--without-http_scgi_module            \
--without-http_uwsgi_module

make && make install

创建启动文件/etc/init.d/nginx

#!/bin/sh
#
# nginx - this script starts and stops the nginx daemin
#
# chkconfig:   - 85 15
# description:  Nginx is an HTTP(S) server, HTTP(S) reverse \
#               proxy and IMAP/POP3 proxy server
# processname: nginx
# config:      /etc/nginx/nginx.conf
# pidfile:     /var/run/nginx.pid
# user:        nginx

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ "$NETWORKING" = "no" ] && exit 0

nginx="/usr/sbin/nginx"
prog=$(basename $nginx)

NGINX_CONF_FILE="/etc/nginx/nginx.conf"

lockfile=/var/run/nginx.lock

start() {
    [ -x $nginx ] || exit 5
    [ -f $NGINX_CONF_FILE ] || exit 6
    echo -n $"Starting $prog: "
    daemon $nginx -c $NGINX_CONF_FILE
    retval=$?
    echo
    [ $retval -eq 0 ] && touch $lockfile
    return $retval
}

stop() {
    echo -n $"Stopping $prog: "
    killproc $prog -QUIT
    retval=$?
    echo
    [ $retval -eq 0 ] && rm -f $lockfile
    return $retval
}

restart() {
    configtest || return $?
    stop
    start
}

reload() {
    configtest || return $?
    echo -n $"Reloading $prog: "
    killproc $nginx -HUP
    RETVAL=$?
    echo
}

force_reload() {
    restart
}

configtest() {
  $nginx -t -c $NGINX_CONF_FILE
}

rh_status() {
    status $prog
}

rh_status_q() {
    rh_status >/dev/null 2>&1
}

case "$1" in
    start)
        rh_status_q && exit 0
        $1
        ;;
    stop)
        rh_status_q || exit 0
        $1
        ;;
    restart|configtest)
        $1
        ;;
    reload)
        rh_status_q || exit 7
        $1
        ;;
    force-reload)
        force_reload
        ;;
    status)
        rh_status
        ;;
    condrestart|try-restart)
        rh_status_q || exit 0
            ;;
    *)
        echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload|configtest}"
        exit 2
esac

增加可执行文件并启动nginx

chmod +x /etc/init.d/nginx
/etc/init.d/nginx start

chkconfig --add nginx
chkconfig --level 345 nginx on

nginx_welcome

开启fastcgi, 编辑/etc/nginx/nginx.conf

location ~ \.php$ {
            root           html;
            fastcgi_pass   127.0.0.1:9000;
            fastcgi_index  index.php;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            include        fastcgi_params;
}

PHP-fpm

安装必要软件

yum -y install gcc automake autoconf libtool make gcc-c++ glibc install libmcrypt-devel mhash-devel libxslt-devel libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel

下载php http://php.net/get/php-5.5.29.tar.bz2/from/a/mirror

wget http://cn2.php.net/get/php-5.5.29.tar.bz2/from/this/mirror
mv mirror php-5.5.29.tar.bz2
bunzip2 php-5.5.29.tar.bz2 
tar -xvf php-5.5.29.tar
cd php-5.5.29

开始编译,其中 –with-mysql=/app/mysql 是我的Mysql安装目录

./configure --prefix=/app/php  --enable-fpm --with-mcrypt --enable-mbstring --enable-pdo --with-curl --disable-debug  --disable-rpath --enable-inline-optimization --with-bz2  --with-zlib --enable-sockets --enable-sysvsem --enable-sysvshm --enable-pcntl --enable-mbregex --with-mhash --enable-zip --with-pcre-regex --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-gd --with-jpeg-dir --enable-opcache --with-pdo-mysql

make && make install

如果出现错误:configure: error: mcrypt.h not found. Please reinstall libmcrypt.
需要安装mcrypt 2.5.8,一定是2.5.8

wget http://nchc.dl.sourceforge.net/project/mcrypt/Libmcrypt/2.5.8/libmcrypt-2.5.8.tar.gz
tar -zxvf libmcrypt-2.5.8.tar.gz 
cd libmcrypt-2.5.8
./configure
make && make install

opcache 出错?
configure: error: Don’t know how to define struct flock on this system, set –enable-opcache=no

vim /etc/ld.so.conf.d/local.conf     # 编辑库文件
/usr/local/lib                       # 添加该行,64位使用:/usr/local/lib64
                                     # 保存退出
ldconfig -v                          # 重新加载

复制php.ini和php-fpm.conf

cp php.ini-production /app/php/lib/php.ini
cp /app/php/etc/php-fpm.conf.default /app/php/etc/php-fpm.conf

配置php.ini, 编辑/app/php/lib/php.ini

display_errors = On
memory_limit = 1024M #根据个人情况来
date.timezone = Asia/Shanghai
post_max_size = 1000M
upload_max_filesize = 1000M
max_execution_time = 1000

[opcache]
opcache.enable=1
opcache.enable_cli=1
opcache.memory_consumption=128
opcache.interned_strings_buffer=4
opcache.max_accelerated_files=4000

配置php-fpm.conf, 编辑/app/php/etc/php-fpm.conf

user = nginx   #这里和上面nginx的用户对应起来
group = nginx

启动php-fpm

/app/php/sbin/php-fpm

#重启, 不支持重启,唯一的办法就是结束进程再启动
killall php-fpm
/app/php/sbin/php-fpm

编辑/etc/profile

export PATH=/app/php/bin:$PATH

测试

vim /app/nginx/html/index.php

phpinfo();

phptest

Mysql 常用

# 查看mysql状态
mysql> show global status;

#查看指定的变量,查看当前的连接数
mysql> show global status LIKE "%threads_connected%";

#查看当前的连接创建数
mysql> show global status LIKE "%threads_created%";

#查看变量信息
mysql> show variables;

#查看指定变量的信息
mysql> show variables LIKE "%xxxx%";

# 每秒刷新一次mysql变量信息
$mysqladmin -uroot -proot extended-status -ri1



MYSQL 性能优化

1. 关闭不必要的二进制日志和慢查询日志,仅在内存足够或开发调试时打开它们

show variables like '%slow%';

2. 适度使用 Query Cache
3. 增加MySQL允许的最大连接数。可用下面的语句査看MySQL允许的最大连接数

show variables like 'max_connections';

4. 从表中删除大量行后,可运行OPTIMIZE TABLE TableName进行碎片整理。

MYSQl 数据存储引擎选择

( 1 ) 采用MylSAM引擎
R/W > 100: 1 且 update 相对较少;
并发不卨,不需要事务;
表数据量小;
硬件资源有限。

( 2 ) 采用I _ D B 引擎
R /W 比较小,频繁更新大字段;
表数据量超过1000万,并发高;
安全性和可用性要求高。

( 3 ) 采用Memory引擎
有足够的内存;
对数据一致性要求不高,如在线人数和Session等应用;

CentOS 安装LAMP

1. 基本安装

#yum install mysql-server httpd php php-mysql php-pdo php-gd php-pear php-dom php-mbstring

2. 开机启动mysql和Apache

#chkconfig httpd on
#chkconfig mysqld on

3. 配置mysql

#service mysqld start
#mysql_secure_installation
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y                                            
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

4. 测试PHP, 创建/var/www/html/info.php

phpinfo()

5. 配置虚拟主机
\
ServerAdmin zhouitpro@gmail.com
DocumentRoot /var/www/html/baifi.com
ServerName www.baifi.com
ServerAlias baifi.com
\

Mysql Case when 查询排序

这种算法也叫做分值查询,将查询的匹配度赋值一个数字.最后这个值进行排序
我的数据表里面有两个字段title, short_name
这里有一个查询排序需求:
1. 当查询字符串完全等于short_name, 那么此条记录排最前面.
2 其次等于title
3 其次 %short_name.
4 其次 %title
5 其次 %short_name%
6 其次 %title%

以上面的6条需求对查询结果进行排序

$string = "我的查询字符串";

// 将查询的值给一个叫orderme的值.然后使用这个值来进行排序.  Then 后的结果为此字段的值
$result = db_query("
       SELECT
       *, CASE
        WHEN short_name='{$string}' THEN 1
        WHEN title='{$string}' THEN 2
        WHEN short_name  LIKE '{$string}%' THEN 3
        WHEN title  LIKE '{$string}%' THEN 4
        WHEN short_name  LIKE '%{$string}%' THEN 5
        WHEN title  LIKE '%{$string}%' THEN 6
       END AS orderme
       FROM mytable 
         WHERE short_name LIKE '%{$string}%' 
         OR
         title LIKE '%{$string}%'
       ORDER BY orderme ASC LIMIT 20");