第 14 页 | zeven's blog

[Mysql] mysql show processlist详解

2018-09-09

0、show processlist查看正在运行的线程，如果你有process权限，能够看到所有的线程，如果没有权限只能看到自己的线程。

有管理员权限的用户，如下图所示：

没有管理权限的用户，如下图所示：：

如果不加full关键字，每条语句的info信息栏只能显示前100个字符。
如果看到“too many connections”的错误信息，想要看看什么线程正在运行，使用show processlist语句是很有用的。mysql数据库会额外保持一个连接，供具有connection_admin或者super权限用户的使用，用以确保管理员能随时连接进来检查系统状况（假设你没有将此权限授予所有用户）。
show processlist输出列详解：

1、id：连接标识符，与INFORMATION_SCHEMA PROCESSLIST表的id列显示的是同类型的值，如下图所示：

Performance_Schema threads表的processlist_id列也是相同值，由connection_id())函数返回值。

通过kill 语句可以杀掉查询到的线程，执行命令如下：

kill id号

2、user: 代表数据库用户，如果是system user代表非客户端线程，是服务器发起的处理内部事务的线程。这些线程可能是io线程、复制从库的sql线程、延迟行处理线程。对于system user，host列没有任何信息。 unauthenticated user是有客户端发起的，但是尚未对客户端用户进行验证的线程。event_scheduler 是监控调度事件的线程。

3、host：发起线程的客户的主机名（对于system user，host这一列为空）。tcp/ip连接的主机名是：hostname：client_port格式，用这种格式来更方面查看客户端在做什么操作。

4、db：如果指定了库名，会使用当前的库名；如果没有则为空。

5、command：线程的命令类型。下面列举几种常见的线程命令，如果想查看详细信息点击此处

connect：从复制已经连接到主库
connect out：从复制正在连接到主库
create db：正在执行一个创建库的操作。
execute：正在执行一个准备好的语句。
field list：提取表列信息线程
kill：当前线程被其他线程杀掉。
query：正在整型一条语句。
quit：线程被终止。
refresh：刷新表、日志、缓存、重置状态变量值或从服务信息线程。
shutdown：关闭服务线程
sleep：等待客户端发送一条新语句线程。
table dump：发送表内容到从服务器
statics：获取当前服务状态信息。
如果一个线程耗时比较久，需要重点关注造成该线程。

6、time：当前线程耗时。对于slave sql 线程，该值代表最后一次复制时间的时间戳和从服务器的真正时间的秒数。

7、state：行为，事件，状态指明当前线程在做什么。下面列举几种常见状态值，查看详细信息点击此处

after create：执行完create table函数之后，创建表的线程（包括内部临时表），即使因为某些错误创建表失败，该状态也会显示。
analyzing：统计myisam表键分布（例如执行analyze table）
checking permissions：检查是否有权限执行该语句。
check tables：执行表检查操作。
cleaning up：线程正在处理一条命名，准备释放内存重置某些状态变量。
closing tables:刷新更改的表到磁盘，关闭使用的表。该操作应该快速完成，如果没有，请确保是否有足够的磁盘空间或者磁盘io是否太高。
copy to tmp table:处理alter table语句。该状态发生在表新结构被创建之后，行数据被拷贝到表之前。
copying to group table：该执行语句中order by和group by有不同的关键字，行被组处理拷贝到临时表。
copying to tmp table：正在复制内存中的临时表。
altering table：正在执行alter table
Copying to tmp table on disk:正在拷贝临时表到磁盘。临时结果变得太大，因此，线程将临时表从内存转到磁盘来节省内存。
creating index：对myisam表进行alter table ... enable keys操作
creating sort index:使用内部临时表处理一个select语句。
creating table：正在创建表（包括创建临时表）
creating tmp table：在内存或磁盘创建一个临时表。如果一个表刚开始再内存创建，之后转到磁盘，该状态会变为：Copying to tmp table on disk。
committing alter table to storage engine：alter table已经执行完成，正在提交 结果。
executing：已经开始执行一个语句
freeing items：已经执行了一个命令，该状态通常在cleaning up之后。
query end：，在freeing items状态之前，处理一个查询之后
logging slow query：写语句到slow-query日志
sending data：正在读取和处理一个select语句的行，并发送数据到客户端，因为可能会发生物理读，该状态可能是给定生命周期时间最长的。
update：准备更新表
updating：正在搜索更新的行，并更新他们
system lock：调用了mysql_lock_tables()函数，线程状态一直没有更新。产生该问题原因很多：
比如：请求或等待一个表的内部或者外部系统锁。当innodb等待一个表锁等级为lock tables时会发生。如果这个问题是由请求外部锁，并且你没有用多个mysqld服务访问相同的myisam引擎表，你可以通过参数 --skip-external-locking禁用外部系统锁。但是外部锁默认情况下是禁用的，因此该参数可能没有影响。对于show profile命令，该状态代表正在请求锁（不是在等待锁）
对于系统表锁状态是Locking system tables。
waiting for commit lock：flush tables with read lock正在等待一个commit 锁
waiting for tables：线程收到通知，表的底层架构已经更改，需要重新打开表后去一个新的结构。但是重新打开一个表，需要等待其他正在访问该表的线程。
通常在其他线程使用flush tables或者执行下面语句: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE. 时会发生此通知。

8、info：正在执行的语句，如果没有语句则为空。语句可能是发送的服务端的，或一个内部语句（如果一个语句执行其他语句）例如：call调用一个（select语句）存储过程，info值会显示select语句。

9、另外使用mysqladmin processlist也可以查看进程信息，如下图所示：

10、如果想通过sql命令去获取自己设定的行，可按以下方法设置：

1 2	select * from information_schema.processlist where state !=' '; ![](https://i.imgur.com/pGGuMER.png)

可根据自己需求添加条件。

mysql

点击阅读

[Mysql] mysql update一条语句过程遇到的问题

2018-09-09

1、因为业务已停掉不担心会出现大量的锁等待事件，因此也没有考虑批量去更新（数据量有1700万左右），直接尝试去跑这一条update语句，第一次跑出现以下问题：

1
2

UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL';
error：Multi-statement TRANSACTION required more THAN 'max_binlog_cache_size' bytes of STORAGE; increase this mysqld variable AND try again

2、查看binlog_cache_size，最大值为1G：

mysql> show variables like '%binlog_cache_size%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| binlog_cache_size     | 4194304    |
| max_binlog_cache_size | 1073741824 |
+-----------------------+------------+
2 rows in set (0.00 sec)

3、尝试去调高max_binlog_cache_size，在此执行update语句：

mysql> set global max_binlog_cache_size=4073741824;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%binlog_cache_size%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| binlog_cache_size     | 4194304    |
| max_binlog_cache_size | 4073738240 |
+-----------------------+------------+
2 rows in set (0.00 sec)

4、将至调到4G、8G去执行该语句，均报以上的错误，考虑用批量更新的方法去处理该update问题，尝试每次更新500万数据，binlog_cache_size仍然不满足，每次更新100万，执行正常：

mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id >=1 and id <= 1000000;
Query OK, 974190 rows affected (2 min 3.48 sec)
Rows matched: 974190  Changed: 974190  Warnings: 0
...
...
mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id >=16000000 and id <= 17000000;
Query OK, 982238 rows affected (2 min 37.44 sec)
Rows matched: 982238  Changed: 982238  Warnings: 0

每100万更新耗时为2min 40s左右

5、上述方法适用于当前表有主键id且递增的情况。如果该id值从其他关联表获取，可考虑一下方法：

1	mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from trade_order ORDER BY id ASC LIMIT 1,1000000) as tmp);

该方法涉及到一个子查询，总体执行速度会下降，根据取值范围和当前实际更新的行数，耗时在4min左右：

1
2
3

UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from trade_order ORDER BY id ASC LIMIT 1,1000000) as tmp);
Query OK, 981631 rows affected (3 min 49.27 sec)
Rows matched: 981631  Changed: 981631  Warnings: 0

mysql

点击阅读

[Mysql] mysql报错：[ERROR] InnoDB: Unable to lock ./ibdata1

2018-09-08

1、登录mysql报错：

1
2
3

mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/tmp/mysql.sock' (2)

2、netstat -tunlp|grep 3306发现mysql进程不存在，尝试去启动mysql：

/etc/init.d/mysql restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL....................................................................................^C
启动失败，按ctrl+c退出。

3、查看log日志，一直在刷错误日志

1 2	[ERROR] InnoDB: Unable to lock ./ibdata1 error: 11 [Note] InnoDB: Check that you do not already have

4、查看磁盘使用情况

df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdc1       1.1T   59G  967G   6% /data
数据目录还有很多空间。

5、查看mysql进程

shell>ps -ef|grep mysql
mysql      1486 130771  2 05:16 pts/0    00:00:01 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysql/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --open-files-limit=65535 --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root      77736      1  0 Jul25 ?        00:00:00 /bin/sh /data/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysql.pid
mysql    128601  77736 85 05:13 ?        00:03:49 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysql/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --open-files-limit=65535 --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root     130771      1  0 05:16 pts/0    00:00:00 /bin/sh /data/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysql.pid

6、top查看进程运行情况

Tasks: 187 total,   1 running, 186 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.4 us,  0.3 sy,  0.0 ni, 99.2 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 32930580 total, 21548588 free,  7218592 used,  4163400 buff/cache
KiB Swap:        0 total,        0 free,        0 used. 24748960 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                             
128601 mysql     20   0 21.711g 4.678g  12784 S   2.6 14.9   3:50.26 mysqld

7、杀掉进程128601

1
2

kill -9 128601
/data/mysql/bin/mysqld_safe: line 198:  1486 Killed                  nohup /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysql/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --open-files-limit=65535 --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306 < /dev/null > /dev/null 2>&1

因为mysql的守护进程存在，会自动启动mysql进程，再次登录数据库
mysql -uroot -p,登录正常。

mysql

点击阅读

[Mysql] mysql批量建库、修改多库中同一命名个表

2018-09-07

1、批量删除数据库

for i in test_ETH_BTC test_BTC_USDT test_ETH_USDT test_BCH_BTC test_BCH_ETH test_BCH_USDT test_ETC_BTC test_ETC_ETH test_ETC_USDT test_LTC_BTC test_LTC_ETH test_LTC_USDT test_OMG_BTC test_OMG_ETH test_OMG_USDT test_ACT_BTC test_ACT_ETH test_ACT_USDT ;
do
mysql -u root -ptest -e "drop database $i";
done

2、批量创建数据库

for i in test_ETH_BTC test_BTC_USDT test_ETH_USDT test_BCH_BTC test_BCH_ETH test_BCH_USDT test_ETC_BTC test_ETC_ETH test_ETC_USDT test_LTC_BTC test_LTC_ETH test_LTC_USDT test_OMG_BTC test_OMG_ETH test_OMG_USDT test_ACT_BTC test_ACT_ETH test_ACT_USDT ;
do
mysql -u root -ptest -e "create database $i";
done

3、批量在多个库中创建多个表

for i in test_ETH_BTC test_BTC_USDT test_ETH_USDT test_BCH_BTC test_BCH_ETH test_BCH_USDT test_ETC_BTC test_ETC_ETH test_ETC_USDT test_LTC_BTC test_LTC_ETH test_LTC_USDT test_OMG_BTC test_OMG_ETH test_OMG_USDT test_ACT_BTC test_ACT_ETH test_ACT_USDT ;
do
for j in {0..19}
do 
sql="CREATE TABLE \`match_record_${j}\` (
  \`id\` bigint(20) NOT NULL AUTO_INCREMENT ,
  \`symbol\` varchar(30) NOT NULL COMMENT '交易对',
  \`price\` decimal(30,15) NOT NULL COMMENT '成交价格',
  \`quantity\` decimal(30,15) NOT NULL COMMENT '成交量',
  \`buy_match_seq\` int(11) NOT NULL COMMENT '买入成交序号',
  \`buy_tx_no\` varchar(32) NOT NULL COMMENT '买入交易单号',
  \`sell_match_seq\` int(11) NOT NULL COMMENT '卖出成交序号',
  \`sell_tx_no\` varchar(32) NOT NULL COMMENT '卖出交易单号',
  \`is_maker\` tinyint(1) NOT NULL COMMENT '买入方是否为maker',
  \`create_time\` bigint(20) NOT NULL COMMENT '成交时间戳',
  PRIMARY KEY (\`id\`),
  UNIQUE KEY \`uniq_idsymbol\` (\`id\`,\`symbol\`),
  UNIQUE KEY \`uniq_txno\` (\`buy_tx_no\`,\`sell_tx_no\`),
  KEY \`idx_create_time\` (\`create_time\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成交记录表';
"
mysql -uroot -ptest -D $i -e"${sql}";
done
done

4、批量修改多个库中的一个同名表做ddl语句操作

对表增加列，添加唯一索引：

for i in test_ETH_BTC test_BTC_USDT test_ETH_USDT test_BCH_BTC test_BCH_ETH test_BCH_USDT test_ETC_BTC test_ETC_ETH test_ETC_USDT test_LTC_BTC test_LTC_ETH test_LTC_USDT test_OMG_BTC test_OMG_ETH test_OMG_USDT test_ACT_BTC test_ACT_ETH test_ACT_USDT ;
do
symbol=`echo "$i"| awk -F 'test_' '{print $2}'`
sql="alter table match_record_0 add column symbol varchar(30) NOT NULL COMMENT '交易对' default '"${symbol}"'"
alter1="alter table match_record_0 add UNIQUE KEY \`uniq_idsymbol\` (\`id\`,\`symbol\`)"
alter2="alter table match_record_0 add UNIQUE KEY \`uniq_txno\` (\`buy_tx_no\`,\`sell_tx_no\`)"
mysql -u root -ptest -D $i -e"${sql};${alter1};${alter2}";
done

对表删除列，删除唯一索引：

for i in test_ETH_BTC test_BTC_USDT test_ETH_USDT test_BCH_BTC test_BCH_ETH test_BCH_USDT test_ETC_BTC test_ETC_ETH test_ETC_USDT test_LTC_BTC test_LTC_ETH test_LTC_USDT test_OMG_BTC test_OMG_ETH test_OMG_USDT test_ACT_BTC test_ACT_ETH test_ACT_USDT ;
do
symbol=`echo "$i"| awk -F 'test_' '{print $2}'`
sql="alter table match_record_0 drop column symbol"
alter1="alter table match_record_0 drop index \`uniq_idsymbol\`"
alter2="alter table match_record_0 drop index \`uniq_txno\`"
mysql -u root -ptest -D $i -e"${sql};${alter1};${alter2}";
done

mysql

点击阅读

[Mysql] mysql-binary排序规则与_bin排序规则对比

2018-09-06

1、binary排序规则与_bin排序规则对比

二进制字符串（像用binary，barbinary，blob存储的数据类型）有一个字符集和以binary命名的排序规则。二进制字符串是序列字节，这些字节的数字值决定了比较和排序的顺序。
非二进制字符串（像用char，varchar，text存储的数据类型）有一个字符集和排序规则（名称不是binary）。一个给定的非二进制字符集有几个排序规则，每一种排序规则对集合中的字符串定义一个特定的比较和排序的顺序。这些非二进制字符集的命名规则是在二进制字符集排序规则的名称后面添加_bin后缀。例如二进制排序规则latin1和utf8分别被命名latin1_bin,utf8_bin。

2、在某些方面，二进制排序规则与_bin排序规则不同。

2.1、比较和排序单元：

二进制字符串是字节序列。对于二进制排序规则，比较和排序是基于数字字节值。
非二进制字符串是（可能是多字节）字符的序列。非二进制排序规则定义了比较和排序字符值的顺序。对于_bin排序规则，这个顺序基于数字字符串编码值，类似于二进制字符串顺序（多字节字符串编制值除外）

2.2、字符转换：

一个非二进制字符串有一个字符集，在很多情况下（即使字符串有_bin排序规则）也可以自动转换为另一个字符集。

当从另一个有不同字符集的列分配列值：
UPDATE t1 SET utf8_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;
当使用字符串文字为insert或update分配值时：
SET NAMES latin1;
INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');
当从服务端给客户端返回结果：
SET NAMES latin1;
SELECT utf8_bin_column FROM t2;

对于二进制字符串列，没有转换发生。对于前面的情况，字符串值是按字节复制的。

2.3、大小写转换：

非二进制字符集排序规则提供关于字符字母大小写的信息，因此非二进制字符可以被转换从一个字母到另一个，即使_bin排序规则忽略字母大小写顺序：

mysql> SET NAMES latin1 COLLATE latin1_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+

字母大小写的概念对二进制字符串的字节不适用，执行字母大小写转换，字符串必须转换为非二进制字符串

mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING latin1));
+-------------+-----------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING latin1)) |
+-------------+-----------------------------------+
| aA          | aa                                |
+-------------+-----------------------------------+

2.4、比较过程的尾随空间处理：非二进制字符串为所有的排序规则进行填充空间，包括_bin排序规则，尾随空间在比较过程是无关紧要的：

mysql> SET NAMES utf8 COLLATE utf8_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+

对于二进制字符串，比较过程所有的字符都是重要的，包括尾随空间:

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

2.5、在插入和检索中的尾随空间处理：

char(n)列存储非二进制字符串，当insert操作是，值小于n字符会使用空格扩展。在检索时尾随空间会被移除。
二进制列存储二进制字符串,插入过程中值小于n字节，会使用ox00扩展，在检索过程中不会被移除；总是返回声明长度的值。

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('a','a');
mysql> SELECT HEX(a), HEX(b) FROM t1;
+--------+----------------------+
| HEX(a) | HEX(b)               |
+--------+----------------------+
| 61     | 61000000000000000000 |
+--------+----------------------+

mysql

点击阅读

[Mysql] mysql唯一性索引详解

2018-09-05

1、唯一性索引创建条件：

唯一性索引创建一个约束条件要求索引列上的所有值必须是不同的，如果你尝试添加一行新数据，但该行的键值与已经存在的其他行的键值相同将会报错。如果为唯一性索引指定前缀值，前缀长度的列值要保证唯一。唯一性索引允许null值。

2、唯一非空索引_rowid的使用：

如果一个表有主键索引或者唯一非空索引，且索引由一个单独整型列类型组成，你可以在select语句使用_rowid来引用索引列，具体如下：
_rowid引用主键列如果主键列由单个整型列组成，如果存在主键列但是他不是由一个单独整型列组成，_rowid不能被使用。
另外，_rowid引用第一个有单个整型列组成的唯一非空索引列，如果不是，_rowid不能被使用。

3、创建测试表，并将列修改为唯一非空进行试验：

create table unique_t(id int,name varchar(10));
alter table unique_t add unique index id_unique_ind(id);
alter table unique_t modify id int not null;
show create table unique_t;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                      |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  UNIQUE KEY `id_unique_ind` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

插入测试数据：

insert into unique_t values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(6,'e');
select *,_rowid from unique_t;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
|  1 | a    |      1 |
|  2 | b    |      2 |
|  3 | c    |      3 |
|  4 | d    |      4 |
|  6 | e    |      6 |
+----+------+--------+

4、删除唯一性索引，修改id默认列可以为空：

alter table unique_t drop index id_unique_ind;
alter table unique_t modify id int null;
show create table unique_t;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+----------------------------------------------------------------------------------------------------------------------------------+

为id列创建主键索引

alter table unique_t add primary key(id);
show create table unique_t;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

select *,_rowid from unique_t;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
|  1 | a    |      1 |
|  2 | b    |      2 |
|  3 | c    |      3 |
|  4 | d    |      4 |
|  6 | e    |      6 |
+----+------+--------+
5 rows in set (0.00 sec)

给id添加主键约束和唯一非空约束，_rowid都可以引用对应列。

5、删除id主键，将name列设置为主键测试

alter table unique_t drop primary key;
alter table unique_t add primary key(name);
show index from unique_t;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| unique_t |          0 | PRIMARY  |            1 | name        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 show create table unique_t;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                     |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| unique_t | CREATE TABLE `unique_t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+

root@db 07:11:  [exchange_market]> select *,_rowid from unique_t;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'

非单个整型列的主键，_rowid无法使用。

mysql

点击阅读

[Mysql] mysql前缀索引详解

2018-09-05

1、使用前缀索引注意事项：

1)、列类型为char，varchar，binary，varbinary可以创建前缀索引；
2)、列类型为blob，text必须创建前缀索引，而且只有innodb，myisam，blackhole的存储引擎前缀索引才生效；
3)、前缀限制是以字节为单位的，但是在create table，alter table，create index语句中索引指定的前缀长度的值为：非二进制类型的（cahr varchar，text）的字符串数据量、二进制类型的（binary，varbinary，blob）的字节数量。当用多字节字符串集为非二进制字符串列创建前缀索引长度时，需要考虑这一点。
是否支持前缀索引和前缀的长度跟存储引擎有关系。例如，innodb引擎表前缀索引支持767字节长度，如果开启innodb_large_prefix参数，支持3072字节长度。myisam存储引擎表，前缀长度为1000字节。NDB引擎不支持前缀索引。

show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+

4)、以mysql5.7.17为例，如果一个指定的索引前缀长度超过最大列数据类型大小，create index时会如下处理索引：
对于非唯一索引，或者一个错误发生(严格sqlmode开启)，或者索引长度降低到最大列数据类型大小并产生一个警告信息(如果strcit sql mode没有开启)

show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+

严格模式是指将SQL_MODE变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。
对于唯一索引，不管sqlmode模式是何种，都会直接报错，因为降低索引长度会插入非唯一的条目不满足唯一性需求。

5)、创建前缀索引语句：

1	CREATE INDEX part_of_name ON customer (name(10));

如果列中的名字在前10个字节中通常是不同的，查询性能不应该慢于创建整个name列索引的查询。另外使用列前缀索引使索引文件更小，能够节省更多的磁盘空间并且增加插入速度。

2、下面是根据网上提供的方法做的测试，参考链接

1)、插入测试数据

create table music(
name varchar(30)
);

insert into music values('BITE'),('There for you'),('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

insert into music values ('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

2)、查看完整列索引选择性

root@db 03:31:  [exchange_market]> select count(distinct name) / count(*) from music;
+---------------------------------+
| count(distinct name) / count(*) |
+---------------------------------+
|                          0.5556 |
+---------------------------------+

3)、查找合适的前缀索引长度

select count(distinct left(name,1))/count(*) as sel1, count(distinct left(name,2))/count(*) as sel2, count(distinct left(name,3))/count(*) as sel3, count(distinct left(name,4))/count(*) as sel4 from music;
+--------+--------+--------+--------+
| sel1   | sel2   | sel3   | sel4   |
+--------+--------+--------+--------+
| 0.3889 | 0.5000 | 0.5000 | 0.5000 |
+--------+--------+--------+--------+

4)、可以看到当前缀长度为2是已经接近完整列索引选择性,添加前缀索引

alter table music add index music_index(name(2));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

show index from music;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| music |          1 | music_index |            1 | name        | A         |           9 |        2 | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

5)、查询索引是否被正常应用：

select * from music where name like 's%';
+------------------+
| name             |
+------------------+
| Scarborough Fair |
| Scarborough Fair |
| Shape of You     |
| Shape of You     |
+------------------+
4 rows in set (0.07 sec)

explain select * from music where name like 's%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | music | NULL       | range | music_index   | music_index | 11      | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql

点击阅读

[Mysql] 使用mysqladmin查看innodb_buffer_pool缓存命中率及缓存使用情况

2018-09-04

1、查看缓存命中率

计算缓存命中率相关参数：

1 2	Innodb_buffer_pool_reads：innodb缓存无法满足逻辑读，不得不从磁盘获取数据的逻辑读数量 Innodb_buffer_pool_read_requests：逻辑读的请求数量

如果查看当前某一时刻的值：

1
2
3

 mysqladmin -uroot -p12345678  ext | grep Innodb_buffer_pool_reads
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_reads                      | 31763701                                         |

1
2
3

 mysqladmin -uroot -p12345678 ext | grep Innodb_buffer_pool_read_requests 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_read_requests              | 866018530                                        |

如果想要查看多次的取值，并计算间隔的差值(-i 1间隔为1s)(-r 获取前后两个值之间的差值)

 mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_reads
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_reads                      | 31763701                                         |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |

mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_read_requests 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_read_requests              | 866018530                                        |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |

也可指定-c(value)参数，指定获取几次的值：

1	mysqladmin -uroot -p12345678 -ri1 -c5 ext \| grep Innodb_buffer_pool_read_requests

根据获取到的值计算缓存命中率：

1	(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests

如果太低，建议增大innodb_buffer_pool

2、查看缓存池使用情况

缓存池使用情况参数：

1
2
3

Innodb_buffer_pool_pages_free:innodb buffer pool空闲页；
Innodb_buffer_pool_pages_data：innodb buffer pool包含数据的页数量：
Innodb_buffer_pool_pages_total：innodb buffer pool内存页总数量(每页16k)；

mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_free
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_free                 | 31974                                            |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |

mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_data
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_data                 | 96923                                            |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |

mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_total
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_total                | 131056                                           |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |

如果Innodb_buffer_pool_pages_free偏大的话，证明有很多缓存没有被利用到，这时可以考虑减小缓存，相反Innodb_buffer_pool_pages_data过大就考虑增大缓存。

3、另附使用mysqladmin查询qps的方法，参考链接

mysqladmin -P3306 -uroot -p12345678  -ri1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
  print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
  print "---Time---|---QPS---|select insert update delete| read inserted updated deleted|  logical  physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
 printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
 printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
 printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
 printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'

mysql

点击阅读

[Mysql] mysql blackhole-engine详解

2018-09-04

1、mysql 黑洞引擎就像黑洞一样，能接受数据，但把接受的数据扔掉，不存储数据，检查结果返回为空。

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test;
Empty set (0.00 sec)

2、创建黑洞引擎表，数据目录下回创建表格式文件，以表明开头后缀为.frm，没有与该表关联的其他文件

黑洞引擎支持所有类型的索引，定义表时，可以声明索引。

1 2	ll test.* -rw-r----- 1 mysql mysql 8578 Sep 4 06:22 test.frm

3、可以通过show engines查看是否支持黑洞引擎：

show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

4、黑洞引擎表不存储任何数据，但是如果启用了二进制日志，sql语句会被记录并复制到从库，这对于中继机制和过滤机制很有用。

假设您的应用程序需要从属端过滤规则，但是传输所有的二进制数据到从库会造成很大的网络流量，在这种情况下，在主库设置存储引擎为黑洞的‘dummy’从进程，如下图所示：

1
2
3

主库写入二级制日志，‘dummy’mysqld 进程作为从进程执行，合并replicate-do-* 和replicate-ignore-*规则，自己写入一个新的，过滤好的二进制日志，该过滤日志传递给从库。
虚拟的进程不存储任何数据，因此外的mysqld进程复制主库信息产生少量的开销。
黑洞引擎表，insert触发器可正常使用，但是因为不存储数据，update和delete引擎没有激活，因为没有行，for each row触发器定义不适用。

5、黑洞引擎的用途：

1
2
3

1、验证转储文件语法
2、启用或禁用二级制日志，来测量二进制日志带来的开销
3、黑洞引擎本质上是一个无操作的引擎，因此可以用于查找与引擎本身无关的性能瓶颈。

黑洞引擎是交易感知的，提交的事物写入二进制日志，回滚事物不写入。

6、黑洞引擎和自动增量列的问题：

主从架构数据库：1、主库黑洞表有一个自动增量字段是主键；2、从服务器有相同表引擎为myisam，3、主库执行insert，并隐式设置增量值。
在这种情况下，主从复制将失败，
如果主站有许多从站，则在发送到从站之前进行过滤可能会减少网络流量。
在基于行的复制中，引擎为行返回的值对于每个插入始终是相同的。这将导致从服务器尝试使用主键列的相同值重播两个插入日志条目，因此复制将失败。

7、黑洞引擎和列过滤问题：

基于行的复制，从库支持表中缺少最后一列。
从库端执行过滤，在执行过滤之前，先将数据传到从库，最少有两种情况不希望将列拷贝到从库：
1、数据是机密的，从库没有权限访问它；
2、主库有多个从库，在发送到从库之前执行过滤用于减少网络带宽

官方文档提供的案例如下,对于受信任和不受信任从库的语句未找到合理解释，没看懂：

使用BLACKHOLE引擎和 --replicate-do-table或 --replicate-ignore-table选项，可以实现主列过滤，
主库配置：
CREATE TABLE t1 (public_col_1, ..., public_col_N,
                 secret_col_1, ..., secret_col_M) ENGINE=MyISAM;
受信任的从库配置：
CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=BLACKHOLE;
不受信任的从库配置：
CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=MyISAM;

mysql

点击阅读

[Grafana] grafana监控redis、mongodb、mysql数据库

2018-08-29

0、参考链接

https://mp.weixin.qq.com/s/Qz87UE1aXFZbAdFlx7Zeow

1、grafana下载安装

1 2	wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-5.2.2-1.x86_64.rpm sudo yum localinstall grafana-5.2.2-1.x86_64.rpm

启动grafana

1 2	service grafana-server start service grafana-server status

加入开机启动

1	chkconfig --add grafana-server

查看grafana端口

1 2	netstat -plntu \| grep grafana-serv tcp6 0 0 :::3000 :::* LISTEN 2539/grafana-server

登录web界面，localhost:3000
用户：admin
密码：admin（默认）
登录之后提示修改密码,登录界面如下图所示

2、安装Prometheus

下载Prometheus

1	wget https://github.com/prometheus/prometheus/releases/download/v2.3.2/prometheus-2.3.2.linux-amd64.tar.gz

解压安装

1	tar -zxvf prometheus-2.3.2.linux-amd64.tar.gz

配置 Prometheus

cd prometheus-2.3.2.linux-amd64/
cat prometheus.yml 
# my global config
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).

# Alertmanager configuration
alerting:
  alertmanagers:
  - static_configs:
    - targets:
      # - alertmanager:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['0.0.0.0:9090']

启动prometheus

./prometheus --config.file=prometheus.yml
----
level=info ts=2018-08-28T03:42:08.966212621Z caller=main.go:222 msg="Starting Prometheus" version="(version=2.3.2, branch=HEAD, revision=71af5e29e815795e9dd14742ee7725682fa14b7b)"
level=info ts=2018-08-28T03:42:08.966876421Z caller=main.go:223 build_context="(go=go1.10.3, user=root@5258e0bd9cc1, date=20180712-14:02:52)"
level=info ts=2018-08-28T03:42:08.966953021Z caller=main.go:224 host_details="(Linux 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64 dax-mysql-mha (none))"
level=info ts=2018-08-28T03:42:08.966990021Z caller=main.go:225 fd_limits="(soft=1024, hard=4096)"
level=info ts=2018-08-28T03:42:08.968101421Z caller=main.go:533 msg="Starting TSDB ..."
level=info ts=2018-08-28T03:42:08.970314221Z caller=web.go:415 component=web msg="Start listening for connections" address=0.0.0.0:9090
level=info ts=2018-08-28T03:42:08.977918922Z caller=main.go:543 msg="TSDB started"
level=info ts=2018-08-28T03:42:08.977966022Z caller=main.go:603 msg="Loading configuration file" filename=prometheus.yml
level=info ts=2018-08-28T03:42:08.979126022Z caller=main.go:629 msg="Completed loading of configuration file" filename=prometheus.yml
level=info ts=2018-08-28T03:42:08.979171222Z caller=main.go:502 msg="Server is ready to receive web requests."
level=info ts=2018-08-28T05:00:04.558075427Z caller=compact.go:398 component=tsdb msg="write block" mint=1535421600000 maxt=1535428800000 ulid=01CNZEEBGJ237RVH965D3VWT57
level=info ts=2018-08-28T05:00:04.566214242Z caller=head.go:348 component=tsdb msg="head GC completed" duration=1.539902ms
level=info ts=2018-08-28T05:00:04.575381958Z caller=head.go:357 component=tsdb msg="WAL truncation completed" duration=9.095816ms
-----

登录控制台 localhost:9090,登录界面如下：

3、mysql_exporter部署

1
2
3

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.11.0/mysqld_exporter-0.11.0.linux-amd64.tar.gz
tar -zxvf mysqld_exporter-0.11.0.linux-amd64.tar.gz 
cd mysqld_exporter-0.11.0.linux-amd64/

配置连接数据库文件

cat .my.cnf
[client]
host = 127.0.0.1
user=root
password=12345678

启动mysqld_exporter

./mysqld_exporter --config.my-cnf="/data/soft/mysqld_exporter-0.11.0.linux-amd64/.my.cnf"  
----
INFO[0000] Starting mysqld_exporter (version=0.11.0, branch=HEAD, revision=5d7179615695a61ecc3b5bf90a2a7c76a9592cdd)  source="mysqld_exporter.go:206"
INFO[0000] Build context (go=go1.10.3, user=root@3d3ff666b0e4, date=20180629-15:00:35)  source="mysqld_exporter.go:207"
INFO[0000] Enabled scrapers:                             source="mysqld_exporter.go:218"
INFO[0000]  --collect.global_status                      source="mysqld_exporter.go:222"
INFO[0000]  --collect.global_variables                   source="mysqld_exporter.go:222"
INFO[0000]  --collect.slave_status                       source="mysqld_exporter.go:222"
INFO[0000]  --collect.info_schema.tables                 source="mysqld_exporter.go:222"
INFO[0000] Listening on :9104                            source="mysqld_exporter.go:232"
----

查看进程是否启动正常：

1	netstat -tunlp\|grep 9104

登录web界面,http://localhost:9104/

修改vim prometheus.yml配置文件

- job_name: mysql
    static_configs:
      - targets: ['127.0.0.1:9104']
        labels:
          instance: test

备注:
job_name,当前执行job的名字
targets，连接的主机
instance，数据库实例的标签明

重新启动prometheus，进入prometheus控制台，点击status->target

4、添加数据源

保存测试

5、下载仪表盘

https://github.com/percona/grafana-dashboards/tree/master/dashboards
选择仪表盘类型，导入到grafana

点击仪表盘查看

6、mongodb_exporter部署用于监控mongodb

prometheus监控mongodb数据库，安装之前需要配置go环境下载配置go环境

1 2	wget https://dl.google.com/go/go1.11.linux-amd64.tar.gz tar -C /usr/local -xzf go1.11.linux-amd64.tar.gz

添加环境变量

1
2
3

echo "
GOPATH=$HOME/go
export PATH=$PATH:$GOPATH/bin" >> ~/.bash_profile

使配置的环境变量生效

1	source ~/.bash_profile

创建配置mognodb所需要的目录

1	mkdir -p $HOME/go/{dir,src,pkg}

安装glide

1	curl https://glide.sh/get \| sh

安装mongodb-exporter

1
2
3

git clone git@github.com:dcu/mongodb_exporter.git $GOPATH/src/github.com/dcu/mongodb_exporter
cd $GOPATH/src/github.com/dcu/mongodb_exporter
make build

查看mongdob_exporter使用的参数

1	./mongodb_exporter -h

启动mongodb_exporter,获取数据库的数据

1	./mongodb_exporter -mongodb.uri mongodb://localhost:30000

启动端口默认为9001,登录http://localhost:9001，查看是否正常

修改prometheus.yml，添加mongodb参数

1
2
3

- job_name: mongodb
  static_configs:
    - targets: ['127.0.0.1:9001']

重启premetheus,登录premetheus控制台查看获取mognodb是否正常

登录grafana添加模板，可以添加模板id或者导入代码

点击链接，跳转到mongodb_exporter模板
添加完成模板如下图所示：

7、redis_exporter部署用于监控redis,安装redis-exporter模板

1
2
3

go get github.com/oliver006/redis_exporter
cd $GOPATH/src/github.com/oliver006/redis_exporter
go build

启动redis_exporter进程获取redis数据(默认进程端口号位9121)

1	./redis_exporter -redis.addr redis://127.0.0.1:6379 -redis.password test

启动端口默认为9001,登录http://localhost:9121，查看是否正常

修改prometheus.yml，添加redis参数

1
2
3

- job_name: redis
  static_configs:
     - targets: ['127.0.0.1:9121']

重启premetheus,登录premetheus控制台查看获取redis是否正常

登录grafana添加模板，可以添加模板id或者导入代码,点击此处跳转到redis_exporter链接
，导入完成之后界面如下图所示：

redis mongodb mysql grafana prometheus

0、show processlist查看正在运行的线程，如果你有process权限，能够看到所有的线程，如果没有权限只能看到自己的线程。

1、id：连接标识符，与INFORMATION_SCHEMA PROCESSLIST表的id列显示的是同类型的值，如下图所示：

3、host：发起线程的客户的主机名（对于system user，host这一列为空）。tcp/ip连接的主机名是：hostname：client_port格式，用这种格式来更方面查看客户端在做什么操作。

4、db：如果指定了库名，会使用当前的库名；如果没有则为空。

5、command：线程的命令类型。下面列举几种常见的线程命令，如果想查看详细信息点击此处

6、time：当前线程耗时。对于slave sql 线程，该值代表最后一次复制时间的时间戳和从服务器的真正时间的秒数。

7、state：行为，事件，状态指明当前线程在做什么。下面列举几种常见状态值，查看详细信息点击此处

8、info：正在执行的语句，如果没有语句则为空。语句可能是发送的服务端的，或一个内部语句（如果一个语句执行其他语句）例如：call调用一个（select语句）存储过程，info值会显示select语句。

9、另外使用mysqladmin processlist也可以查看进程信息，如下图所示：

10、如果想通过sql命令去获取自己设定的行，可按以下方法设置：

1、因为业务已停掉不担心会出现大量的锁等待事件，因此也没有考虑批量去更新（数据量有1700万左右），直接尝试去跑这一条update语句，第一次跑出现以下问题：

2、查看binlog_cache_size，最大值为1G：

3、尝试去调高max_binlog_cache_size，在此执行update语句：

4、将至调到4G、8G去执行该语句，均报以上的错误，考虑用批量更新的方法去处理该update问题，尝试每次更新500万数据，binlog_cache_size仍然不满足，每次更新100万，执行正常：

5、上述方法适用于当前表有主键id且递增的情况。如果该id值从其他关联表获取，可考虑一下方法：

1、 登录mysql报错：

2、netstat -tunlp|grep 3306发现mysql进程不存在，尝试去启动mysql：

3、查看log日志，一直在刷错误日志

4、查看磁盘使用情况

5、查看mysql进程

6、top查看进程运行情况

7、杀掉进程128601

1、批量删除数据库

2、批量创建数据库

3、批量在多个库中创建多个表

4、批量修改多个库中的一个同名表做ddl语句操作

1、binary排序规则与_bin排序规则对比

2、在某些方面，二进制排序规则与_bin排序规则不同。

2.1、比较和排序单元：

2.2、字符转换：

2.3、大小写转换：

2.4、比较过程的尾随空间处理：非二进制字符串为所有的排序规则进行填充空间，包括_bin排序规则，尾随空间在比较过程是无关紧要的：

2.5、在插入和检索中的尾随空间处理：

1、唯一性索引创建条件：

2、唯一非空索引_rowid的使用：

3、创建测试表，并将列修改为唯一非空进行试验：

4、删除唯一性索引，修改id默认列可以为空：

5、删除id主键，将name列设置为主键测试

1、使用前缀索引注意事项：

2、下面是根据网上提供的方法做的测试，参考链接

1、查看缓存命中率

2、查看缓存池使用情况

3、另附使用mysqladmin查询qps的方法，参考链接

1、mysql 黑洞引擎就像黑洞一样，能接受数据，但把接受的数据扔掉，不存储数据，检查结果返回为空。

2、创建黑洞引擎表，数据目录下回创建表格式文件，以表明开头后缀为.frm，没有与该表关联的其他文件

3、可以通过show engines查看是否支持黑洞引擎：

4、黑洞引擎表不存储任何数据，但是如果启用了二进制日志，sql语句会被记录并复制到从库，这对于中继机制和过滤机制很有用。

5、黑洞引擎的用途：

6、黑洞引擎和自动增量列的问题：

7、黑洞引擎和列过滤问题：

0、参考链接

1、grafana下载安装

2、安装Prometheus

3、mysql_exporter部署

4、添加数据源

5、下载仪表盘

6、mongodb_exporter部署用于监控mongodb

7、redis_exporter部署用于监控redis,安装redis-exporter模板

1、登录mysql报错：