Mysql 配置篇

所有配置都可以通过show variables获取, 所有的配置和默认的一些设置都可以称为Mysql变量. 只是作用域的范围不一样而已, 配置分为动态和静态两种,动态意味着你可以在Mysql运行中修改,静态表示在Mysql整个生命周期都是内都是不能修改的. 可以使用set对动态参数进行设置.

动态参数
@@session 表示当前回话
@@global 表示全局的

设置用户变量

set @test=1,@test2=2;

设置系统变量, 当针对当前mysql的生命周期生效

set @@session.read_buffer_size=51288;
set @@global.read_buffer_size=51288;

global 的修改会对全局生命,但不会覆盖mysql的配置文件,如果配置文件中有该配置那么该配置的优先级在一起global的设置,每个生命周期都是以配置中为的优先级最大.

静态参数
比如datadir这个参数就是只读的.

查询

select @@session.read_buffer_size  #当前生命周期的read_buffer_size
select @@global.read_buffer_size   #全局的read_buffer_size

内存配置
InnoDB存储引擎内存由有几个组成部分:

缓冲池 buffer pool(innodb_buffer_pool_size)

缓冲池是占最大块内存的部分,用来存放各种数据的缓存。因为InnoDB的存储引擎的工作方式总是将数据库文件按页(每页16K)读取到缓冲池,然后按最近最少使用(LRU)的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改,总是首先修改在缓存池中的页(发生修改后,该页即为脏页),然后再按照一定的频率将缓冲池的脏页刷新(flush)到文件。可以通过命令SHOW ENGINE INNODB STATUS来查看innodb_buffer_ pool的具体使用情况

innodb_buffer_pool_size 一般设置多大?
查看innodb过去时间范围内的的状态,查看Buffer pool size大小, Buffer pool size * 16 / 1024 = 要设置的大小

mysql > show engine innodb status\G; 

查看配置大小

show variables like 'innodb_buffer_pool_size';

重做日志缓冲池 redo log buffer(innodb_log_pool_size)

日志缓冲将重做日志信息先放入这个缓冲区,然后按一定频率将其刷新到重做日志文件。该值一般不需要设置为很大,因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件,因此我们只需要保证每秒产生的事务量在这个缓冲大小之内即可。

额外的内存池 additional memory pool (innodb_additional_mem_pool_size)

额外的内存池通常被DBA忽略,认为该值并不是十分重要,但恰恰相反的是,该值其实同样十分重要。在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆(heap)的方式进行的。在对一些数据结构本身分配内存时,需要从额外的内存池中申请,当该区域的内存不够时,会从缓冲池中申请。InnoDB实例会申请缓冲池(innodb_buffer_pool)的空间,但是每个缓冲池中的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block),而且这些对象记录了诸如LRU、锁、等待等方面的信息,而这个对象的内存需要从额外内存池中申请。因此,当你申请了很大的InnoDB缓冲池时,这个值也应该相应增加。

自动哈希索引

Innodb_adaptive_hash_index 用于启用或者禁用自适应hash索引

innodb_fast_shutdown

Innodb_fast_shutdown 参数告诉innodb在它关闭的时候该做什么工作,innodb_fast_shutdown影响着innodb表的行为,该参数有0,1,2三个值可以选择:
0表示在innodb关闭的时候,需要purge all, merge insert buffer,flush dirty pages。这是最慢的一种关闭方式,但是restart的时候也是最快的。
1表示在innodb关闭的时候,它不需要purge all,merge insert buffer,只需要flush dirty page,在缓冲池中的一些数据脏页会刷新到磁盘。(默认参数)
2表示在innodb关闭的时候,它不需要purge all,merge insert buffer,也不进行flush dirty page,只将log buffer里面的日志刷新到日志文件log files,MySQL下次启动时,会执行恢复操作。

innodb_force_recovery

innodb_force_recovery影响整个InnoDB存储引擎的恢复状况。默认为0(设置范围1-6),表示当需要恢复时执行所有的
恢复操作。当不能进行有效的恢复操作时,mysql有可能无法启动,并记录下错误日志。
1. 1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2. 2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
3. 3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4. 4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5. 5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
6. 6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

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

# 查询表大小. 并按照大小排序, 大小为MB.
mysql> SELECT TABLE_NAME AS "Table Name",  table_rows AS "Quant of Rows", ROUND( ( data_length + index_length )/1024/1024, 2 ) AS total_size FROM information_schema.TABLES WHERE `TABLE_SCHEMA` LIKE 'mydb' ORDER BY total_size DESC LIMIT 0, 50;

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
\