当前位置：Gxlcms > 数据库问题 > mysql日常问题

mysql日常问题

时间：2021-07-01 10:21:17 帮助过：1人阅读


发生的原因是：
1 ) 进程  a 中有涉及到 tb_1 的执行很长时间的 sql 没有执行完成，一直在执行
2 ) 进程 b 中有涉及到 tb_1 的 ddl 操作(如 create table if not exists) ，该操作由于进程 a 中的 sql 没有执行完成，所以该 ddl 操作转入【 Waiting for table metadata lock】这样的锁等待
3 ) 其他进程有任何涉及到 tb_1 的操作都会由于进程 b 中的【 Waiting for table metadata lock】这个锁等待而不能执行，不论是 dml 还是 ddl 操作都会被阻塞

  • 备份时锁表
    对于 mysqldump 时，如不指定 --skip-opt 或者 --lock-tables=false 时，该表在导出时将只能读取，不能写入

  • 查询慢

    1. 数据缓存被清出
      如果发现查询比较慢，但索引已经使用了的，但是就是慢，这时如果使用 orzdba --hit --innodb_rows 发现输出结果中，缓存命中率在 99% 以下，或者每秒读出的行数在 20000 以下，这时应该
      是该表的数据缓存被从 innodb buffer pool 中清除出去了。

    2. 索引不正确

    3. IO 出现瓶颈

    •  

    4. 锁等待问题


      1. 查找锁等待

        select b.trx_state, d.state, a.requesting_trx_id, a.requested_lock_id, b.trx_query,  b.trx_mysql_thread_id, a.blocking_trx_id, a.blocking_lock_id, c.trx_query, c.trx_mysql_thread_id

                from information_schema.INNODB_LOCK_WAITS a 

                left join information_schema.INNODB_TRX b on a.requesting_trx_id = b.trx_id 

                left join information_schema.INNODB_TRX c on a.blocking_trx_id = c.trx_id 

                left join information_schema.processlist d on c.trx_mysql_thread_id=d.id;

        如果有 dml 操作的等待时，会在查询结果中看到哪些 sql 在等哪些 sql，查询结果中的前面部分是被阻塞部分，后面部分是阻塞的 sql
        注意：对于 Waiting for metadata lock 不会在这里查询到

      2. 查询已经执行了，但没有提交的锁

        select a.trx_id, id, trx_state, b.STATE, b.COMMAND, info, trx_query ,  trx_started

                     from information_schema.INNODB_TRX a, information_schema.processlist b 

                     where a.trx_mysql_thread_id = b.id and b.COMMAND != ‘Query‘;

        如果有事务已经执行但还没有提交，这时会查询出来相应的进程和事务号，但由于事务已经没有 sql 执行，所以看不到相关的 sql 

      3. 对于 Waiting for metadata lock  这样的锁会将该表上的后面所有的操作都会阻塞住，要注意这样的锁等待

    2. 死锁
      查询死锁的方法就是 show engine innodb status ，查看其中的 【LATEST DETECTED DEADLOCK】部分，这里会记录下最后发生的那个死锁，对于支持行锁的数据库，死锁的情况是不可能避免的，只能减少
      死锁发生的概率，方法有( 参考自网络 )：
      1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，加快写数据的速度，减少写数据时的锁定时间。
      2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
      3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
      4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
      5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
      6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。


    5.数据库备份


    1. mysqldump
      逻辑级的备份，通过备份出 sql 语句或者数据记录文件来备份，如（这个备份是不保证数据一致性和不锁表的方式）：

      mysqldump -q  -e --routines --triggers  --lock-tables=false --allow-keywords --default-character-set=utf8 --net_buffer_length=1048576  --max_allowed_packet=134217728  dbname > /data/dbname.sql 

      优点：a ) 最小能针对到单表，灵活，简单

      缺点：a ) 速度慢

    2. xtrabackup
      文件级备份，通过 percona 的工具 xtrabackup 进行文件级的备份
      详见【http://www.mike.org.cn/articles/xtrabackup-guide/】
      备份：

      innobackupex --user=root --defaults-file=/etc/my.cnf --database=ccms /home/databack

      注：database：是指要备份的表结构文件的数据库，对于数据文件， innodb 引擎的是全部备份，恢复时也是全部恢复，所以建议这里不指定，备份全部数据库的数据文件和表结构文件

      恢复：

      1) 应用备份中的日志

      innobackupex --user=root --password=pwd --apply-log /home/databack/2012-10-10_00-28-26/

      2) 关闭数据库，并删除除系统数据库文件【mysql、performance_schema、test、错误日志文件、binlog 文件】以外的文件

      3) 将应用完日志后的备份文件 cp 到数据库数据目录中，并修改这些文件的所有者为 mysql

      4) 启动数据库

      优点：a ) 文件级的备份，速度比较快
                b ) 支持热备
      缺点：a ) 备份全部的 innodb 引擎的数据文件，就算是指定了备份的库，也还是会备份其他数据库的 innodb 引擎的数据文件

    3. 文件备份
      即冷备，拷贝整个数据库目录
      优点：a ) 简单，速度较快
      缺点：a ) 数据库要停掉，影响业务


    6.并行 mysqldump


    对于自带的 mysqldump 是单线程的，在导入时速度很慢。

    可以使用 Maatkit 工具集中的 mk-parallel-dump 工具进行备份出多个文件，即一个表按固定行数备份出多个文件。

    对于多个文件，这时就可以使用并行导入。

    1 ) 安装

        a ) 下载 【http://www.maatkit.org/doc/maatkit.html】

        b )  安装：

               直接 rpm -ivh 

    2 ) 使用

    导出：

    mk-parallel-dump -uroot -pccms -hlocalhost --databases dbtest --chunk-size 1000000 --threads 8 --base-dir /tmp/dbtest --charset binary --no-gzip -S /tmp/mysql1.sock

    注：chunk-size：分隔单表的固定行数：使用该参数时要注意，由于该工具记录行数是使用 主键 的最大最小值进行计算的，所以如果 主键 不是数字类型，会有问题，导不出该表数据。

    导入：

    mk-parallel-restore -uroot -pccms -hlocalhost --databases dbtest --fast-index  --threads 8   /tmp/dbtest


    7. 记一次数据库公共表空间文件损坏恢复过程

    1. 环境
      mysql 5.5.25

    2. 现场
      由于意外机器宕机，造成数据库的公共表空间数据文件损坏，启动时，报如下错误：

      InnoDB: Error: trying to access page number 4294935295 in space 0,
      InnoDB: space name /data/mysql/data/ibdata1,
      InnoDB: which is outside the tablespace bounds.
      InnoDB: Byte offset 0, len 16384, i/o type 10.
      InnoDB: If you get this error at mysqld startup, please check that
      InnoDB: your my.cnf matches the ibdata files that you have in the
      InnoDB: MySQL server.
      121221 10:01:15 InnoDB: Assertion failure in thread 140248534529824 in file fil0fil.c line 5297

      通过恢复模式【innodb_force_recovery=1|2|3|4|5|6】仍然报错不能启动

    3. 方法
      由于该数据库的数据文件使用的是单独表空间，公共表空间的中没有表的数据部分，从而判断单独表空间中的数据应该还是可用的。
      所以这时的问题就是如果将 copy 出来的表的数据文件( ibd 文件 ) 放到一个正常的数据库的中，从而使得该数据库还可以读取出来。
      查阅资料知道，对于表，在公共表空间中会记录一下标识ID（同样该ID也会记录在表的数据文件 ibd 中），该ID会在对表进行 ddl 操作（除了 drop table 以外）时加 1 ，所以这时的问题就是如果将正常库中的该表的ID处理成原来要恢复的数据文件中的ID，
      使得可以读取出表数据文件中的数据。
      参考资料：1. http://www.mysqlperformanceblog.com/2011/06/03/a-recovery-trivia-or-how-to-recover-from-a-lost-ibdata1-file/
                      2. http://www.prg-cn.com/article-12975-1.html

    4. 过程

      1. 备份要恢复的数据库的数据目录

      2. 在一个新建的数据库创建要恢复的数据库的数据库结构

      3. 使用脚本全部修改要恢复的数据文件的标识ID值与新建立的表的标识ID值相同

      4. 停止数据库，将修改过了标识ID值的要恢复的数据文件替换新建的数据库的数据文件（即 ibd 文件）

      5. 修改 my.cnf 中的恢复模式参数为6

        innodb_force_recovery=6

        注意：对于 mysql 5.5.25 中有一个bug，当 innodb_purge_threads =1， innodb_force_recovery>=2 时，会有问题，将 innodb_purge_threads = 0 或者 打一个补丁，详见【修订 mysql 5.5.25 版本中当 innodb_purge_threads=1 , innodb_force_recovery >= 2 时，启动时进入死循环，不能启动问题】

      6. 启动数据库，导出此时已经可以读取出来数据库的文件中的数据

      7. 使用导出来的数据恢复数据库

      1. 通过命令查看要恢复的数据文件的标识ID的值
        hexdump tb_campaign.ibd -C | head -4
        技术分享
        其中圆圈中的部分就是16进制的标识ID值，我们要将把处理成公共表空间文件和数据文件中的值一样的

      2. 由于该标识ID字段在表变动一次就会增加一个，并且该值是全局唯一的。这样该标识ID的值会非常的大，所以如果要将新建的表的ID通过表的 ddl 操作处理成原来要恢复的数据文件的ID一样，过程将会非常漫长
        所以为了加快速度，就直接修改要恢复的数据文件中的标识ID值 全部 改成与新建的表的标识ID一样（当然这里有可能会将只是与标识ID值相同的值也会被替换掉，造成数据错误，当然这种概率会比较小）



    8.zabbix 监控 mysql 配置


    zabbix 中使用模板  appaloosa-zabbix-templates 监控 mysql

    一、客户机配置

    1. 准备

        1.1 ) php-mysql： yum install php-mysql

        1.2 ) yum install expat-devel

               perl -MCPAN -e"install XML::Simple"

       1.3 )  数据库连接的 sock 的默认值为注意

    2. 配置

        2.1 ) 在 zabbix 的安装目录【如：/usr/local/zabbix】中创建目录 【agent.d】

                mkdir agent.d

        2.2 ) 将收集脚本文件放到 zabbix 的插件目录 plugins 中，【如：/usr/local/zabbix/plugins/ss_get_mysql_stats.php】

                这个文件中有配置数据库的用户名密码配置，修改一个实际的用户名密码

    $mysql_user = ‘zabbix‘;
    $mysql_pass = ‘zabbix‘;
    $mysql_port = 3306;

     

        2.3 ) 将配置文件放到创建的目录 agent.d 中

                注意配置文件 mysql.conf 中的收集脚本的文件目录是要实际的目录【如: /usr/local/zabbix/plugins/ss_get_mysql_stats.php】

        2.4 ) 修改配置文件 zabbix_agentd.conf，如【/usr/local/zabbix/etc/zabbix_agentd.conf】

                在配置文件  zabbix_agentd.conf 的最下面加上 【 Include=/usr/local/zabbix/agent.d/ 】，即收集脚本的配置文件的目录

        2.5 ) 注意，这个收集脚本连接数据库的 socket 文件的默认位置是 /var/lib/mysql/mysql.sock ， 如果出现提示说找不到 socket 文件，就在这个位置创建一个软连接


    二、服务端配置

     1. 导入模板文件

    2. 修改主机配置，添加上导入的模板文件 


    9.innodb 压缩表测试


    对于 innodb 的压缩表有 5 种数据页大小的压缩方式，分别为 1K, 2K, 4K, 8K, 16K

    其中 4K 的压缩比最大

    创建压缩表时，要修改数据库的文件模式参数

    innodb_file_format=Barracuda

    才能再创建压缩表：

    CREATE TABLE `plt_taobao_order_cmp_1` (
    `tid` varchar(50) COLLATE utf8_bin NOT NULL ,
    `dp_id` varchar(50) COLLATE utf8_bin NOT NULL ,
    `customerno` varchar(50) COLLATE utf8_bin NOT NULL,
    `created` datetime DEFAULT NULL ,
    `endtime` datetime DEFAULT NULL ,
    `status` varchar(10) COLLATE utf8_bin NOT NULL ,
    `payment` decimal(12,2) NOT NULL,
    PRIMARY KEY (`tid`),
    KEY `idx_plt_taobao_order_customerno` (`customerno`,`payment`,`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1

    以上 sql 中

    • ROW_FORMAT=COMPRESSED
      表示使用压缩表

    • KEY_BLOCK_SIZE=1
      表示数据页大小为 1K，这里可以用的值为： 1 、2、4、8、16

    压缩表测试结果【rds 压缩表测试.xlsx】

    淘宝数据库相关文档【开放平台数据中心数据库优化总结_玄惭.pdf】


    mysql日常问题

    标签：data

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！