mysql日常问题

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql日常问题

科技小能手 2017-11-12 17:17:00 浏览956
展开阅读全文
  1. 数据库启动不了

    1. 参数不正确
      如果公共表空间文件或 innodb 日志文件的大小与配置文件中的大小不一致,会导致启动不了,即
      1 ) innodb_log_file_size 参数的大小与 实际文件 ib_logfile0 的大小不一致
      2 ) innodb_data_file_path 参数的大小与实际文件 ibdata1 的大小不一致
      处理方法:1 ) 修改参数与文件大小一致

    2. 内存不足
      如果在启动时提示内存不足,一般就是参数 innodb_buffer_pool_size 设置过大,超过了机器的实际内存

    3. 磁盘满了

  2. 数据库进程被阻塞

    1. 锁等待
      一般是由于 【 Waiting for table metadata lock】将后面的进程阻塞
      发生的原因是:
      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 操作都会被阻塞

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

  3. 查询慢

    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  这样的锁会将该表上的后面所有的操作都会阻塞住,要注意这样的锁等待

  1. 死锁
    查询死锁的方法就是 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
      image2012-12-25%209%3A17%3A38.png?versio
      其中圆圈中的部分就是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




本文转自 Bruceweien 51CTO博客,原文链接:http://blog.51cto.com/bruceweien/1932466

网友评论

登录后评论
0/500
评论