Mysql innodb 文件

Innodb数据存储

Innodb存储引擎将存储的数据按表空间进行存放,默认会一个10MB, 名字为ibdata1的文件

innodb_data_file_path = ibdata1:2000MB; ibdata2:2000MB:autoextend;

设置了innodb_data_file_path 参数以后所有innodb引擎的数据都会保存在设置的文件里面
当参数innodb_file_per_table设置了以后, 每个innodb表的将生成一个单独.idb的文件来存储数据, 这个单独的.idb表空间文件只存储该表的数据,索引和插入缓冲等信息. 其它的信息还是放在默认的表空间里面, 表空间又由段(segment),区(extent),页(page)组成.

Innodb 日志文件

在mysql文件下面有两个文件ib_logfile0和ib_logfile1, 这两个是innodb的日志文件,记录了innodb引擎的事务日志,也叫做innodb重做日志
重做日志文件的主要目的是,万一事务失败或者事务执行时断电,这时可以通过重做日志文件来恢复
设置参数:

  • innodb_log_file_size
  • innodb_log_file_in_group
  • innodb_mirrored_log_groups
  • Mysql 二进制文件

    二进制文件记录了对数据库执行的所有操作,不包括select和show. 开启二进制文件会使mysql性能下降百分之1
    作用:
    1. 恢复(recovery): 用户恢复数据
    2. 复制(replication): 可以使备用mysql服务器(slave)进行实时同步

    我的 datadir是/usr/local/var/mysql/.
    先确定log-bin是否则开启了

    mysql> show variables LIKE "log_bin"
    

    如果没有开启, 修改my.cnf

    log_bin=bin-log  #bin-log是名字
    

    此时再显示datadir目录会看到两个文件

    -rw-rw----     1 _mysql  _mysql   326B  6 14 17:52 bin-log.000001
    -rw-rw----     1 _mysql  _mysql    17B  6 14 17:52 bin-log.index
    

    bin-log.000001 二进制日志文件
    bin-log.index 二进制日志索引文件

    配置

    max_binlog_size=1024M
    

    查看日志, mysql自带了一个mysqlbinlog的工具

    sudo mysqlbinlog --start-position=1 bin-log.000001 | more
    

    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 位置

    查看my.cnf位置

    $ mysql --help | grep my.cnf
    

    查看mysql data_dir位置

    $ ps -ef | grep mysqld
    

    或者

    $ show variables like 'datadir';
    

    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中增加

    #MySQL 5.1.29 之前使用log_slow_queries
    log_slow_queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)
    long_query_time=2 (记录超过的时间,默认为10s)
    log_queries_not_using_indexes=ON (此开关会记录没有索引的查询语句)
    log_long_format (如果设置了,所有没有使用索引的查询也将被记录)
    
    #MySQL 5.1.29 之后使用slow_query_log
    log_queries_not_using_indexes = ON
    slow_query_log = ON
    slow_query_log_file = /var/log/mysql/mysql-slow.log
    long_query_time=0
    

    工作mysqldumpslow 可以更好的显示漫查询日志

    $ mysqldumpslow /var/log/mysql/mysql-slow.log
    $ mysqldumpslow -s al -n 10 /var/log/mysql/mysql-slow.log
    

    将漫查询日志放到数据库. 修改my.cnf

    log_output=TABLE
    
    mysql> SELECT * FROM mysql.slow_log
    

    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进行碎片整理。