十字螺丝钉 | 十字螺丝钉的DBA日记

2017年8月2日十字螺丝钉 troubleshooting Leave a comment

Atlas ERROR 1105 (07000): Proxy Warning – Syntax Forbidden

公司的一套老Atlas执行delete时报如下错误

mysql> delete from sdb_jos_order_js t ;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden

1 2	mysql> delete from sdb_jos_order_js t ; ERROR 1105 (07000): Proxy Warning - Syntax Forbidden

猜测atlas对删除全表的操作做了限制。尝试加上limit限制删除的条数，依然报错

mysql> delete from sdb_jos_order_js t limit 1;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden

1 2	mysql> delete from sdb_jos_order_js t limit 1; ERROR 1105 (07000): Proxy Warning - Syntax Forbidden

看来也可能是没有where条件造成的，添加伪where条件测试，删除成功。 [crayon-663bd58ba1a206163799

2017年6月21日十字螺丝钉备份还原 Leave a comment

利用MySQL全备份，如何只恢复一个库或者一个表？

在实际工作中，一个MySQL实例中可能有多个database。而我们备份时，通常采用完全备份，将所有database都备份到一个文件中。但是，偶尔会遇到只恢复一个database或者一个表的情况。怎么解决呢？一、利用全备恢复一个库（database）的数据案例：朋友在群里问， MySQL全库备份。如何只恢复一个库？ 1、采用–one-database 选项。不建议使

2017年3月29日十字螺丝钉工具插件 2 comments

SQLAdvisor：MySQL SQL优化工具

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。 SQLAdvisor项目地址 https:

2017年3月15日十字螺丝钉 MySQL, troubleshooting Leave a comment

xtrabackup恢复数据后，MySQL无法启动问题处理

应审计需要，需将MySQL数据恢复到14天前。公司MySQL采用xtrabackup方式备份。使用xtrabackup恢复MySQL后，无法启动MySQL，报错如下

Starting MySQL....The server quit without updating PID file[失败]a/mysql/data/SL010A-IVAMD1.pid).

1	Starting MySQL....The server quit without updating PID file[失败]a/mysql/data/SL010A-IVAMD1.pid).

查看MySQL错误日志

2017-03-15 09:08:01 23485 [Note] InnoDB: Memory barrier is not used
2017-03-15 09:08:01 23485 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-15 09:08:01 23485 [Note] InnoDB: Using Linux native AIO
2017-03-15 09:08:01 23485 [Note] InnoDB: Using CPU crc32 instructions
2017-03-15 09:08:01 23485 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2017-03-15 09:08:01 23485 [Note] InnoDB: Completed initialization of buffer pool
2017-03-15 09:08:01 23485 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2017-03-15 09:08:01 23485 [ERROR] InnoDB: The system tablespace must be writable!
2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' init function returned error.
2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
^G/data/mysql/bin/mysqld: File '/mysql/log/slow.log' not found (Errcode: 2 - No such file or directory)
2017-03-15 09:08:01 23485 [ERROR] Could not open /mysql/log/slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown t
he MySQL server and restart it.
2017-03-15 09:08:01 23485 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-03-15 09:08:01 23485 [ERROR] Aborting

2017-03-15 09:08:01 23485 [Note] InnoDB: Memory barrier is not used

2017-03-15 09:08:01 23485 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-03-15 09:08:01 23485 [Note] InnoDB: Using Linux native AIO

2017-03-15 09:08:01 23485 [Note] InnoDB: Using CPU crc32 instructions

2017-03-15 09:08:01 23485 [Note] InnoDB: Initializing buffer pool, size = 6.0G

2017-03-15 09:08:01 23485 [Note] InnoDB: Completed initialization of buffer pool

2017-03-15 09:08:01 23485 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode

2017-03-15 09:08:01 23485 [ERROR] InnoDB: The system tablespace must be writable!

2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' init function returned error.

2017-03-15 09:08:01 23485 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

^G/data/mysql/bin/mysqld: File '/mysql/log/slow.log' not found (Errcode: 2 - No such file or directory)

2017-03-15 09:08:01 23485 [ERROR] Could not open /mysql/log/slow.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown t

he MySQL server and restart it.

2017-03-15 09:08:01 23485 [ERROR] Unknown/unsupported storage engine: InnoDB

2017-03-15 09:08:01 23485 [ERROR] Aborting

解决办法：由于执行xtrabackup使用root用户，恢复

2017年2月21日十字螺丝钉监控 Leave a comment

zabbix邮件脚本路径错误，导致无法发送邮件的问题处理

公司zabbix主要用于监控MySQL数据库及操作系统的运行状态及性能状况。前两天，zabbix莫名其妙不发送邮件了，幸好及时查看zabbix界面，才避免了一次无空间可用的事故。排查思路：zabbix涉及到邮件配置、zabbix media types、以及action等环节，所以采取排除法的策略来确定问题。 zabbix发送邮件采用mutt+msmtp方式，直接调用mutt命令或者发送邮件的m

2017年2月17日十字螺丝钉优化 Leave a comment

SQL优化一例：懂得去除累赘，还你一身轻松

在生产库发现了一条奇葩SQL，下面进行分析。

select count(*)
  from ORD_MDA_CALL_INFO_D T, PRD_PRD_M P
 WHERE T.PRD_ID = P.PRD_ID(+)
   and (T.EMP_NO = :1 OR T.EMP_NO = :2)
   and T.MDA_CNVR_PTR_CD = :3
   and T.INST_DTM > to_date(:4, 'yyyy-mm-dd hh24:mi:ss')
   and T.INST_DTM < to_date(:5, 'yyyy-mm-dd hh24:mi:ss')
   AND (T.BIZ_UNT_CD = :6 or T.BIZ_UNT_CD = 'TV')
 order by T.INST_DTM desc;

select count(*)

from ORD_MDA_CALL_INFO_D T, PRD_PRD_M P

WHERE T.PRD_ID = P.PRD_ID(+)

and (T.EMP_NO = :1 OR T.EMP_NO = :2)

and T.MDA_CNVR_PTR_CD = :3

and T.INST_DTM > to_date(:4, 'yyyy-mm-dd hh24:mi:ss')

and T.INST_DTM < to_date(:5, 'yyyy-mm-dd hh24:mi:ss')

AND (T.BIZ_UNT_CD = :6 or T.BIZ_UNT_CD = 'TV')

order by T.INST_DTM desc;

问题1：count()统计行数的时候，排序操作是多余的。问题2：left join连接中，以左表为主表，和右表通过关联条件连接，A表满足条件的数据都会被返回，而B表中没有的数据则以NULL表示。上面的SQL中T表left join P表，但是SQL中P表并没有任何过滤条件，所有过滤都是

2017年2月13日十字螺丝钉备份还原 Leave a comment

MySQL将A库的表及数据导入到B库

在工作中，经常有开发提这样的需求：将A库中的表和数据导入到B库中。这里个记录，方便以后查找。 1、创建新数据库及用户

CREATE DATABASE db_B ;

GRANT ALL PRIVILEGES ON db_B.* TO user_B@'10.%.%.%' identified by "xxxx";

flush privileges;

CREATE DATABASE db_B ;

GRANT ALL PRIVILEGES ON db_B.* TO user_B@'10.%.%.%' identified by "xxxx";

flush privileges;

2、测试用户登录

mysql -uuser_B -p'xxx' -h 10.0.97.121

1	mysql -uuser_B -p'xxx' -h 10.0.97.121

3、导出源库数据

mysqldump -uuser_A -p -S /export1/data/mysql/mysql.sock -B db_A --single-transaction --no-create-db>/tmp/db_A.dmp

1	mysqldump -uuser_A -p -S /export1/data/mysql/mysql.sock -B db_A --single-transaction --no-create-db>/tmp/db_A.dmp

参数说明： –single-tran

2017年2月7日十字螺丝钉 MySQL Leave a comment

MySQL RANGE分区表

一、开启分区表功能 MySQL想要使用分区表，必须安装partition插件。查看是否安装了partition插件。

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
...........
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
...........
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.00 sec)


SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';
	
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| InnoDB             | 5.6     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| ARCHIVE            | 3.0     | ACTIVE   |
| partition          | 1.0     | ACTIVE   |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

mysql> show plugins;

+----------------------------+----------+--------------------+---------+---------+

+----------------------------+----------+--------------------+---------+---------+

...........

...........

+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00 sec)

SELECT

PLUGIN_NAME as Name,

PLUGIN_VERSION as Version,

PLUGIN_STATUS as Status

FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_TYPE='STORAGE ENGINE';

+--------------------+---------+----------+

| Name | Version | Status |

+--------------------+---------+----------+

| binlog | 1.0 | ACTIVE |

| MRG_MYISAM | 1.0 | ACTIVE |

| MEMORY | 1.0 | ACTIVE |

| CSV | 1.0 | ACTIVE |

| MyISAM | 1.0 | ACTIVE |

| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |

| InnoDB | 5.6 | ACTIVE |

| BLACKHOLE | 1.0 | ACTIVE |

| FEDERATED | 1.0 | DISABLED |

| ARCHIVE | 3.0 | ACTIVE |

| partition | 1.0 | ACTIVE |

+--------------------+---------+----------+

11 rows in set (0.00 sec)

MySQL 5.6二进制版本默认提供分区功能；如果是编译安装，那么编译时请加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。如果不想使用分区，启动MySQL服务请加上–ski

2024年5月
一	二	三	四	五	六	日
« 9月
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31