[Mysql] mysql lock table && unlock tables实验


0、mysql版本

1
2
3
4
5
6
7
root@db 04:12:  [aaaa]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)

1、创建实验表，内容如下：

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
root@db 15:11:  [aaaa]> show tables;
+----------------+
| Tables_in_aaaa |
+----------------+
| aaa            |
| bbb            |
+----------------+
2 rows in set (0.00 sec)
root@db 15:15:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:15:  [aaaa]> select * from bbb;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
+----+------+-------------+
3 rows in set (0.00 sec)

2、开启两个会话，session1、session2,对表aaa进行read表锁

session1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
root@db 15:16:  [aaaa]> lock table aaa read;
Query OK, 0 rows affected (0.00 sec)
root@db 15:17:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:17:  [aaaa]> select * from bbb;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES
root@db 15:17:  [aaaa]> 
root@db 15:18:  [aaaa]> update aaa set name='e' where id=1;
ERROR 1099 (HY000): Table 'aaa' was locked with a READ lock and can't be updated

session 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 15:18:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:18:  [aaaa]> update aaa set name='e' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 15:20:  [aaaa]> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| aaaa     | aaa   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

session 1

1
2
root@db 15:21:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：在session1对表aaa进行read锁表，session1只能对表aaa进行读操作，对其他表没有任何操作权限，session2对表aaa有读权限，没有写权限。

3、开启两个会话，session1、session2,对表aaa进行write表锁

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@db 15:21:  [aaaa]> lock table aaa write;
Query OK, 0 rows affected (0.00 sec)
root@db 15:26:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)
root@db 15:26:  [aaaa]> select * from bbb;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES
root@db 15:27:  [aaaa]>  update aaa set name='e' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@db 15:29:  [aaaa]> update bbb set name='e' where id=1;
ERROR 1100 (HY000): Table 'bbb' was not locked with LOCK TABLES

session 2

1
2
3
4
root@db 15:20:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 15:28:  [aaaa]>  update aaa set name='e' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session 1

1
2
root@db 15:31:  [aaaa]> unlock  tables;
Query OK, 0 rows affected (0.00 sec)

结论：在session1对表aaa进行write锁表，session1对表aaa有读写权限，对其他表没有任何操作权限，session2对表aaa即没有读权限，又没有写权限。

4、开启两个会话，session1、session2,对表aaa进行write&read表锁

session1

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
32
33
34
root@db 15:32:  [aaaa]> lock table aaa write , aaa as t1 read;
Query OK, 0 rows affected (0.00 sec)
root@db 15:32root@db 04:01:  [aaaa]> select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)

root@db 04:01:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
+----+------+-------------+
4 rows in set (0.00 sec)

root@db 04:02:  [aaaa]> update aaa as t1 set name='e' where id =1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
root@db 04:04:  [aaaa]> update aaa  set name='e' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@db 04:04:  [aaaa]> insert into aaa select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 04:04:  [aaaa]> insert into aaa select * from aaa as t1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

session2

1
2
3
4
root@db 04:05:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@db 04:06:  [aaaa]> update aaa set name='e' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1

1
2
root@db 04:07:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session1对表aaa同时进行read，write锁，需要使用别名。对表进行select,update操作正常，如果使用insert into aaa select * from aaa as t1;需要加上别名。session 2对表aaa即没有读权限，又没有写权限。

5、对表aaa进行read表锁，并使用别名

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
root@db 03:47:  [aaaa]>  LOCK TABLE aaa AS t1 READ;
Query OK, 0 rows affected (0.00 sec)

root@db 03:47:  [aaaa]> select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:47:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

session 2

1
2
3
4
5
6
7
8
9
10
11
 select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

session 1

1
2
root@db 03:48:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session 1对表aaa加别名read表锁，session1查询需要使用别名，直接查询无效，session2对表aaa有读权限，无写权限

6、对表aaa使用write表锁，并添加别名

session 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
root@db 03:51:  [aaaa]> LOCK TABLE aaa AS t1 write;
Query OK, 0 rows affected (0.00 sec)

root@db 03:53:  [aaaa]> 
root@db 03:53:  [aaaa]> select * from aaa;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:53:  [aaaa]> select * from aaa as t1;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | e    | 11111111111 |
|  2 | e    | 22222222222 |
|  3 | c    | 33333333333 |
|  4 | d    | 44444444    |
|  5 | f    | 5555555     |
+----+------+-------------+
5 rows in set (0.00 sec)

root@db 03:53:  [aaaa]> update aaa set name='e' where id =1;
ERROR 1100 (HY000): Table 'aaa' was not locked with LOCK TABLES
root@db 03:54:  [aaaa]> update aaa as t1 set name='e' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session 2

1
2
root@db 03:55:  [aaaa]> select * from aaa;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session 1

1
2
root@db 03:55:  [aaaa]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

结论：session 1对表aaa加别名write表锁，session1查询和更改需要使用别名，直接查询和更改无效，session2对表aaa无读权限，无写权限

##7、额外提示：

1
LOCK TABLES或者UNLOCK TABLES，当应用于分区表时，始终锁定或解锁整个表; 这些语句不支持分区锁定修剪
mysql

点击阅读

[Zabbix] zabbix监控mongodb数据库


1、创建监控脚本，用于连接mongodb数据库，可根据自身数据库配置修改该脚本

mkdir -p /usr/local/zabbix/script
vim /usr/local/zabbix/script/zabbix_monitor_mongodb.sh

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash
#mongodb管理员用户
authuser=admin1
#mongodb管理员密码
authpass=admin123
#Mongodb指定验证数据库
authdb=admin
#mongodb指定端口
dbport=30000
#mongodb安装路径
dbpath=/data/mongodb/bin
${dbpath}/mongo --port ${dbport} -u ${authuser} -p ${authpass} --authenticationDatabase ${authdb}

授权脚本执行权限

1
chmod +x /usr/local/zabbix/script/zabbix_monitor_mongodb.sh

2、修改zabbix监控项脚本，用于获取mongodb参数

vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/zabbix_mongodb.conf

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#当前连接数，包括当前的shell会话，副本集成员连接，mongos实例连接
#(4.0version)connections.current[*],echo "db.serverStatus().connections.current"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=connections.current[*],echo "db.serverStatus().connections.current"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#当前可用的连接数，数据库上的连接负载的值
#(4.0version)connections.available[*],echo "db.serverStatus().connections.available"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=connections.available[*],echo "db.serverStatus().connections.available"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#服务器所有的连接，包括已经关闭的连接
#(4.0version)connections.totalCreated[*],echo "db.serverStatus().connections.totalCreated"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=connections.totalCreated[*],echo "db.serverStatus().connections.totalCreated"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p|cut -d '(' -f2|cut -d ')' -f1

#因锁而造成排队等待的总数
#(4.0version)UserParameter=globalLock.currentQueue.total[*],echo "db.serverStatus().globalLock.currentQueue.total"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=globalLock.currentQueue.total[*],echo "db.serverStatus().globalLock.currentQueue.total"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p
#因读锁而造成排队等待的数量
#(4.0version)UserParameter=globalLock.currentQueue.readers[*],echo "db.serverStatus().globalLock.currentQueue.readers"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=globalLock.currentQueue.readers[*],echo "db.serverStatus().globalLock.currentQueue.readers"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p
#因写锁而造成排队等待的数量
#(4.0version)UserParameter=globalLock.currentQueue.writers[*],echo "db.serverStatus().globalLock.currentQueue.writers"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p
#3.0version
UserParameter=globalLock.currentQueue.writers[*],echo "db.serverStatus().globalLock.currentQueue.writers"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#当前数据库进程占用内存情况
#(4.0version)mem.resident[*],echo "db.serverStatus().mem.resident"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=mem.resident[*],echo "db.serverStatus().mem.resident"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#当前数据库进程占用虚拟内存的大小
#(4.0version)mem.virtual[*],echo "db.serverStatus().mem.virtual"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=mem.virtual[*],echo "db.serverStatus().mem.virtual"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#流入mongodb数据库的总量
#(4.0version)network.bytesIn[*],echo "db.serverStatus().network.bytesIn"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh| grep NumberLong |cut -d '(' -f2|cut -d ')' -f1
#3.0version
UserParameter=network.bytesIn[*],echo "db.serverStatus().network.bytesIn"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#数据库流出总量
#(4.0version)network.bytesOut[*],echo "db.serverStatus().network.bytesOut"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh| grep NumberLong |cut -d '(' -f2|cut -d ')' -f1|cut -d '"' -f2|cut -d '"' -f1
#3.0version
UserParameter=network.bytesOut[*],echo "db.serverStatus().network.bytesOut"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#数据库总请求数
#(4.0version)network.numRequests[*],echo "db.serverStatus().network.numRequests"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh| grep NumberLong |cut -d '(' -f2|cut -d ')' -f1
#3.0version
UserParameter=network.numRequests[*],echo "db.serverStatus().network.numRequests"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#当前副本集状态，为1代表为主节点，为2代表为从节点
#(4.0version)rs.status.myState[*],echo "rs.status().myState"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=rs.status.myState[*],echo "rs.status().myState"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

#页错误总数，当数据库性能不佳、内存限制、或者数据库较大会导致该值增加
#(4.0version)extra_info.page_faults[*],echo "db.serverStatus().extra_info.page_faults"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 4p
#3.0version
UserParameter=extra_info.page_faults[*],echo "db.serverStatus().extra_info.page_faults"|sh /usr/local/zabbix/script/zabbix_monitor_mongodb.sh|sed -n 3p

3、修改zabbix-agent配置文件，添加zabbix_agentd.conf.d目录，用于加载该目录下文件

vim /usr/local/zabbix/etc/zabbix_agentd.conf

1
Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

4、重新启动agent客户端

1
/etc/init.d/zabbix_agentd restart

5、通过zabbix-web端，添加配置模板，参考模板，点击下载

zabbixmongodb

点击阅读

[Zabbix] zabbix监控mysql数据库


1、编写定期收集mysql信息脚本，并指定到对应目录

cat /usr/local/zabbix/script/mysql_monitor.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/bash
dbpath='/data/mysql/bin/mysql'
#mysql的用户
dbuser='root'
#mysql的密码
dbpass='123456'
#mysql的端口
dbport='3306'
#mysql的socket文件
dbsocket='/data/mysql/tmp/mysql.sock'
#mysql-status日志路径
dbstatuspath=/tmp/mysql_status_monitor.log
#mysql-engine-status日志路径
dbenginestatuspath=/tmp/mysql_engine_innodb_status.log
#self-define-script
dbselfpath=/tmp/mysql_self_status.log

#查询mysql-status信息
${dbpath} -u${dbuser} -p${dbpass} -S${dbsocket} -P${dbport} -BNe "show global status;" > ${dbstatuspath}
#查询mysql-engine-innodb信息
${dbpath} -u${dbuser} -p${dbpass} -S${dbsocket} -P${dbport} -BNe "show engine innodb status\G" > ${dbenginestatuspath}
#检查集群节点
nodecount=`export MYSQL_PWD=${dbpass};${dbpath} -u${dbuser} --socket=/data/mysql/data/mysql.sock -P${dbport} -BNe "select count(*) from performance_schema.replication_group_members where MEMBER_STATE != 'ONLINE'"`
echo -e "nodecount ${nodecount}" > ${dbselfpath}

根据自己的数据库配置，修改相应的参数，修改完成之后授权脚本执行权限：

1
chmod +x /usr/local/zabbix/script/mysql_monitor.sh

2、将脚本添加到定时任务，每分钟执行一次

crontab -l

1
* * * * * sh /usr/local/zabbix/script/mysql_monitor.sh

3、编写获取数据脚本，对定时生成的log文件进行信息筛选

cat /usr/local/zabbix/script/mysql_get_data.sh

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
case $1 in
	nodecount)
       #查询集群节点存活状态
       cat /tmp/mysql_self_status.log |awk '/nodecount/ {print $2}' |head -1 
       ;;
	uptime)
       cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1
       ;;
    com_select)
       cat /tmp/mysql_status_monitor.log | awk '/Com_select/ {print $2}'
       ;;
    com_insert)
       cat /tmp/mysql_status_monitor.log | awk '/Com_insert/ {print $2}'|head -1
       ;;
    com_update)
       cat /tmp/mysql_status_monitor.log | awk '/Com_update/ {print $2}'|head -1
       ;;
    com_delete)
       cat /tmp/mysql_status_monitor.log | awk '/Com_delete/ {print $2}'|head -1
       ;;
    connections)
       cat /tmp/mysql_status_monitor.log | awk '/Connections/ {print $2}'|head -1
       ;;
    thread_cached)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_cached/ {print $2}'
       ;;
    threads_connected)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_connected/ {print $2}'
       ;;
    thread_created)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_created/ {print $2}'            
       ;;
    Threads_running)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_running/ {print $2}'
       ;;
    table_locks_immediate)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_immediate/ {print $2}'
       ;;
    table_locks_waited)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_waited/ {print $2}'
       ;;
    slow_launch_threads)
       cat /tmp/mysql_status_monitor.log | awk '/Slow_launch_threads/ {print $2}'
       ;;
    slow_queries)
       cat /tmp/mysql_status_monitor.log | awk '/Slow_queries/ {print $2}'
       ;;
    qps)
       uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
       questions=`cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'`
       echo $(printf "%.2f" `echo "scale=2;${questions}/${uptime}"|bc`)
       ;;
    tps)
       uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
       com_commit=`cat /tmp/mysql_status_monitor.log | awk '/Com_commit/ {print $2}'`
       com_rollback=`cat /tmp/mysql_status_monitor.log | awk '/Com_rollback/ {print $2}'|head -1`
       com_sum=$((${com_commit}+${com_rollback}))
       echo $(printf "%.2f" `echo "scale=2;${com_sum}/${uptime}"|bc`)
       ;;
    innodb_buffer_read_hits)
       innodb_buffer_pool_reads=`cat /tmp/mysql_status_monitor.log | awk '/Innodb_buffer_pool_reads/ {print $2}'|head -1`
       innodb_buffer_pool_read_requests=`cat /tmp/mysql_status_monitor.log | awk '/Innodb_buffer_pool_read_requests/ {print $2}'|head -1`
       innodb_buffer_read_diff=$((${innodb_buffer_pool_read_requests}-${innodb_buffer_pool_reads}))
       echo $(printf "%.2f" `echo "scale=2;${innodb_buffer_read_diff}/${innodb_buffer_pool_read_requests}"|bc`)
       ;;
    table_cache_hit)
       cat /tmp/mysql_status_monitor.log | awk '/Opened_tables/ {print $2}'
       ;;
    thread_cache_hits)
       thread_created=`cat /tmp/mysql_status_monitor.log | awk '/Threads_created/ {print $2}'`
       connections=`cat /tmp/mysql_status_monitor.log | awk '/Connections/ {print $2}'|head -1`
       thread_cache_diff=$((${connections}-${thread_created}))
       echo $(printf "%.2f" `echo "scale=2;${thread_cache_diff}/${connections}"|bc`)
       ;;
    create_tmp_tables_hits)
       created_tmp_tables=`cat /tmp/mysql_status_monitor.log | awk '/Created_tmp_tables/ {print $2}'`
       created_tmp_disk_tables=`cat /tmp/mysql_status_monitor.log | awk '/Created_tmp_disk_tables/ {print $2}'`
       echo $(printf "%.2f" `echo "scale=2;${created_tmp_disk_tables}/${created_tmp_tables}"|bc`)
       ;;
    binlog_cache_disk_use)
       cat /tmp/mysql_status_monitor.log | awk '/Binlog_cache_disk_use/ {print $2}'
       ;;
    table_locks_immediate)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_immediate/ {print $2}'
       ;;
    table_locks_waited)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_waited/ {print $2}'
       ;;
    innodb_row_lock_waits)
       cat /tmp/mysql_status_monitor.log | awk '/Innodb_row_lock_waits/ {print $2}'
       ;;               
    *)
       ;;
esac

4、编写zabbix监控项文件,通过/usr/local/zabbix/script/mysql_get_data.sh脚本去获取对应监控的数值

cat /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_monitor_parameter.conf

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
#集群节点数量
UserParameter=nodecount[*],sh /usr/local/zabbix/script/mysql_get_data.sh nodecount
#系统运行时间
UserParameter=uptime[*],sh /usr/local/zabbix/script/mysql_get_data.sh uptime
#查看select语句的执行数
UserParameter=com_select[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_select
#查看insert语句的执行数
UserParameter=com_insert[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_insert
#查看update语句的执行数
UserParameter=com_update[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_update
#查看delete语句的执行数
UserParameter=com_delete[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_delete
#查看试图连接到MySQL(不管是否连接成功)的连接数
UserParameter=connections[*],sh /usr/local/zabbix/script/mysql_get_data.sh connections
#查看线程缓存内的线程的数量
UserParameter=thread_cached[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_cached
#当线程打开连接数
UserParameter=threads_connected[*],sh /usr/local/zabbix/script/mysql_get_data.sh threads_connected
#查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值
UserParameter=thread_created[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_created
#查看激活的(非睡眠状态)线程数
UserParameter=threads_running[*],sh /usr/local/zabbix/script/mysql_get_data.sh threads_running
#查看创建时间超过slow_launch_time秒的线程数。
UserParameter=slow_launch_threads[*],sh /usr/local/zabbix/script/mysql_get_data.sh slow_launch_threads
#查看查询时间超过long_query_time秒的查询的个数。
UserParameter=slow_queries[*],sh /usr/local/zabbix/script/mysql_get_data.sh slow_queries
#在服务器上执行语句的数量，只包括客户端发送给服务器的语句，并不包括存储项目执行的语句
UserParameter=qps[*],sh /usr/local/zabbix/script/mysql_get_data.sh qps
#事物提交数量com_commit
#事物回滚数量com_rollback
UserParameter=tps[*],sh /usr/local/zabbix/script/mysql_get_data.sh tps
#在buffer pool满足不了逻辑读的请求，而必须直接从硬盘读取的数量innodb_buffer_pool_reads
#逻辑读的请求数innodb_buffer_pool_read_requests
#buffer_read命中率
UserParameter=innodb_buffer_read_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh innodb_buffer_read_hits
#打开表的数量open_tables
#已经打开表的数量opened_tables
#table_cache命中率
UserParameter=table_cache_hit[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_cache_hit
#thread_cache命中率
UserParameter=thread_cache_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_cache_hits
#执行语句过程中创建的临时表created_tmp_tables
#当创建的内部临时表过大，mysql会自动将表从内存中转换为磁盘上的表created_tmp_disk_tables
#在磁盘上创建临时表的比例
UserParameter=create_tmp_tables_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh create_tmp_tables_hits
#事物使用binlog但是临时二进制日志超过binlog_cache_size，使用临时文件存储事物的语句，如果该值不为0，需要调整binlog_cache_size
UserParameter=binlog_cache_disk_use[*],sh /usr/local/zabbix/script/mysql_get_data.sh binlog_cache_disk_use
#可以立即授予表锁请求的次数
UserParameter=table_locks_immediate[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_locks_immediate
#一个表锁的请求不能立即被授予，需要等待的次数,如果该值过高，说明数据库有性能问题，应该优化sql语句，表分区或者读写分离
UserParameter=table_locks_waited[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_locks_waited
#操作innodb表等待行数的次数
UserParameter=innodb_row_lock_waits[*],sh /usr/local/zabbix/script/mysql_get_data.sh innodb_row_lock_waits

5、修改/usr/local/zabbix/etc/zabbix_agentd.conf配置文件,添加该行参数：

1
Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

添加完成之后，需要重启zabbix_agent客户端

6、通过zabbix-web控制台，自定义一个模板，新建监控项、触发器、图形等内容，下面链接是我创建的比较简单的mysql模板，上传到百度云，仅供参考，点击下载

7、上面模式中tps和qps的算法是按照自数据库启动获取的参数值与uptime相除获取的每秒内执行数，下面计算另一个取值方法：取单位时间内前后值得差值，得到的差值除以时间间隔，获取单位时间内的qps和tps值，修改配置文件/usr/local/zabbix/script/mysql_get_data.sh：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
qps)
   #uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
   #questions=`cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'`
   #echo $(printf "%.2f" `echo "scale=2;${questions}/${uptime}"|bc`)
   cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'
   ;;
tps)
   #uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
   com_commit=`cat /tmp/mysql_status_monitor.log | awk '/Com_commit/ {print $2}'`
   com_rollback=`cat /tmp/mysql_status_monitor.log | awk '/Com_rollback/ {print $2}'|head -1`
   com_sum=$((${com_commit}+${com_rollback}))
   echo ${com_sum}
   #echo $(printf "%.2f" `echo "scale=2;${com_sum}/${uptime}"|bc`)
   ;;

修改zabbix模板监控项，将tps、qps的监控项下添加如下图所示进程：

亦可点击此处下载，获取该模板，并导入zabbix。

zabbixmysql

点击阅读

[Mysql] 去掉查询结果中带的mysql: [Warning] Using a password on the command line interface can be insecure


1、一般情况为了方便，在使用mysql命令查询的时候会在-p后面直接加上密码,输出结果如下：

1
mysql -u root -p12345678 -BNe "select count(*) from performance_schema.replication_group_members where MEMBER_STATE='ONLINE'"

结果如下：

1
2
mysql: [Warning] Using a password on the command line interface can be insecure.
3

2、为了消除结果中的[Warning]信息，只显示查询结果，可使用环境变量存储密码的方法：

1
export MYSQL_PWD=12345678;mysql -u root -BNe "select count(*) from performance_schema.replication_group_members where MEMBER_STATE='ONLINE'"

结果如下：

1
3
mysql

点击阅读

[Mysql] mysql-innodb buffer pool size调整


1、查看当前数据库innodb_buffer_pool参数

1
show global variables like 'innodb_buffer_pool_size';

##2、查看page_size大小

1
show variables like 'innodb_page_size';

官方文档参数详解

1
2
3
Innodb_page_size
InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes

3、查看当前内存innodb页的总数量和包含数据的页的数量

1
2
show global status like 'Innodb_buffer_pool_pages_data';
show global status like 'Innodb_buffer_pool_pages_total';

官方文档参数详解：

1
2
3
4
5
6
Innodb_buffer_pool_pages_data
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.

Innodb_buffer_pool_pages_total
The total size of the InnoDB buffer pool, in pages.

4、调优参考计算方法：

1
2
3
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

5、设置innodb_buffer_pool_siz大小

设置命令：

1
set global innodb_buffer_pool_size = 17179869184;

缓冲池字节大小，单位kb，如果不设置，默认为128M
5.7版本以后可以动态修改参数，但是也要修改配置文件参数，防止重启之后，参数又变成配置文件内的参数,5.7以下的版本为静态参数，需要修改配置文件，并重新启动mysql

1
2
3
4
cat /etc/my.cnf
---------
innodb_buffer_pool_size = 17179869184  #设置16G
---------

6、配置参数也可使用M、G等参数，内容如下：

1
2
3
4
5
/etc/my.cnf：
-------------------
innodb_buffer_pool_size = 16G  #设置16G
innodb_buffer_pool_size = 500M  #设置500M
--------------------------
mysql

点击阅读

[Mongodb] mongodb4.0定时备份并邮件告知备份情况


1、mongodb全量备份脚本，内容如下：

cat /data/soft/mongodb_backup.sh

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#!/bin/bash
#认证用户名
authuser=admin1
#认证用户对应密码
authpass=admin123
#备份的服务器地址
servername=testrepl/127.0.0.1:30000
#备份的数据库实例名称
instancename=test
#认证数据库实例名称
authdbname=admin
#备份脚本运行获取到的时间戳
stamp=`date +"%Y-%m-%d"`
#项目名称
programname=bulingbuling
#备份文件目录
backuppath=/data/backup
#备份文件绝对路径名称
backname=${backuppath}/${instancename}
#需要删除的备份文件绝对路径名称
oldstamp=`date +"%Y-%m-%d" -d "-5 day"`
oldbackname=${backname}-${oldstamp}.dump
#数据库安装路径
dbpath=/data/mongodb/bin
#备份操作log目录
backuplogname=/tmp/mongodb_bakcup_${stamp}.log
#当前服务器主机名
localname=`hostname`
#当前服务器ip地址
localip=`ip a |grep global| head -1| awk '{print $2}'|awk -F'/' '{print $1}'`
#localip='65.52.165.104'
#目标邮箱
dest_mail='test@qq.com'

#判断指定log文件是否存在，如果存在将其删除
if [ -e ${backuplogname} ];then
   sudo rm -rf ${backuplogname}
else
   :
fi

#备份执行失败发送错误邮件
send_fail_mail(){
	echo "${programname}备份失败,登录服务器${localname}-${localip}的日志${backuplogname}下查看报错！！！" |mail -s ${programname}数据库备份情况 ${dest_mail}
}

#备份执行成功发送成功邮件 
send_success_mail(){
	echo "${programname}备份成功,可登录服务器${localname}-${localip}的日志${backuplogname}下查看备份信息。" |mail -s ${programname}数据库备份情况 ${dest_mail}
}

#创建备份时间函数
date_func(){
   dateresult=$((${afterstamp}-${beforstamp}))
   hour=$((${dateresult}/3600))
   min=$(((${dateresult}-${hour}*3600)/60))
   sec=$((${dateresult}-${hour}*3600-${min}*60))
   echo "6、本次备份运行时间为:"${hour}时${min}分${sec}秒 >> ${backuplogname}
}

#创建一个删除五天前的备份数据的函数
del_old_bakdata(){
   if [ -e ${oldbackname} ];then
   	 sudo echo "7、五天前的备份数据${oldbackname}存在，进行删除"  >> ${backuplogname}
   	 rm -rf ${oldbackname}
   	 if [ $? == 0 ];then
   	 	sudo echo "7.1、备份数据删除成功" >> ${backuplogname}
   	 else
   	 	sudo echo "7.1、备份数据删除失败，注意查看失败原因" >> ${backuplogname}
   	 fi
   else
     sudo echo "7、五天前的备份数据${oldbackname}不存在，不需要再删除。" >> ${backuplogname}
   fi 
}

#因为mongodbdump只能指定路径，不能自定义备份文件名称，因此需要对备份完成的目录重命名
rename_backname_func(){
	if [ -e ${backname}-${stamp} ];then
	  mv ${backname}-${stamp} ${backname}-${stamp}-old
	  mv ${backname} ${backname}-${stamp}
	  if [ $? == 0 ];then
	    echo "5、备份目录重命名成功" >> ${backuplogname}
	  else
	    echo "5、备份目录重命名失败,查看命名失败原因！" >> ${backuplogname}
	  fi
	else
	  mv ${backname} ${backname}-${stamp}
	  if [ $? == 0 ];then
	    echo "5、备份目录重命名成功" >> ${backuplogname}
	  else
	    echo "5、备份目录重命名失败,查看命名失败原因！" >> ${backuplogname}
	  fi
	fi
}

#备份之前判断备份目录是否存在
if [ -d ${backuppath} ];then
	sudo echo "1、备份目录存在,可执行备份操作" >> ${backuplogname}
else
	sudo echo "1、备份目录不存在,手动创建" >> ${backuplogname}
	sudo mkdir -p ${backuppath}
	if [ $? == 0 ];then
	   sudo echo "1.1、备份目录创建成功，可进行下面操作" >> ${backuplogname}
	else
	   sudo echo "1.1、备份目录不存在，且创建失败，无法进行下面操作，退出备份" >> ${backuplogname}
	   exit
	fi
fi

#备份之前判断备份文件是否已经存在
if [ -d ${backname} ];then
	sudo echo "2、指定的备份实例目录${backname}已存在，对其进行重命名" >> ${backuplogname}
    mv ${backname} ${backuppath}/bak-${fullbackupname}-${stamp}.dump
    if [ $? == 0 ];then
    	sudo echo "2.1、文件已重新命名为${backuppath}/bak-${fullbackupname}-${stamp}.dump,可继续备份" >> ${backuplogname}
    else
    	sudo echo "2.1、文件重命名失败，退出备份操作" >> ${backuplogname}
    	exit
    fi
else
	sudo echo "2、指定的备份文件不存在，开始进行备份" >> ${backuplogname}
fi

#备份之前时间戳
beforstamp=`date +%s`
#开始全量备份数据库
sudo echo "3、开始全量备份数据库" >> ${backuplogname}
${dbpath}/mongodump -h ${servername} -u ${authuser} -p ${authpass} -d ${instancename} -o ${backuppath} --authenticationDatabase ${authdbname}
backupresult=$?
afterstamp=`date +%s`
if [ ${backupresult} == 0 ];then
    	sudo echo "4、${programname}-${instancename}数据库备份成功,文件名称为${backname}，重命名备份文件" >> ${backuplogname}
    	rename_backname_func
    	date_func
        del_old_bakdata
        send_success_mail
else
    	sudo echo "4、${programname}-${instancename}数据库备份失败,注意查看备份失败原因并重新备份" >> ${backuplogname}
    	send_fail_mail
    	exit
fi

使用该脚本需要修改自己数据库对应的用户名authuser,密码authpass,邮箱dest_mail,项目名称programname,服务器名称servername,数据库实例名称instancename,mongodb数据库安装路径dbpath。其他参数可使用脚本内默认的，也可自行修改

2、配置邮件发送功能

2.1、安装mailx软件

1
yum install -y mailx

2.2、修改配置文件/etc/mail.rc,在行尾添加一下信息：

1
2
set from=test@qq.com smtp=smtp.qq.com
set smtp-auth-user=test@qq.com smtp-auth-password=testpassword smtp-auth=login

参数详解：

1
2
3
4
from:指定发送邮件的发件人
smtp:指定smtp服务器信息
smtp-auth-user:允许第三方登录的用户名
smtp-auth-password：允许第三方登录的密码

2.3、发送测试邮件：

1
echo 'test email' |mail -s 'title' test@qq.com

title:指定发送文件的标题，可自行定义

3、修改备份脚本权限

1
chmod +x /data/soft/mongodb_backup.sh

4、将脚本添加到定时任务

如：每天凌晨一点备份

1
shell > crontab -e
1
0 1 * * * sh /data/soft/mongodb_backup.sh
mongodb

点击阅读

[Mongodb] mongodb4.0备份与恢复


1、mongodb备份命令

1.1、备份之前查看备份实例的相关数据：

1
2
3
4
5
6
7
8
MongoDB Enterprise testrepl:PRIMARY> use admin
switched to db admin
MongoDB Enterprise testrepl:PRIMARY> db.auth('admin1','admin123');
1
MongoDB Enterprise testrepl:PRIMARY> use test;
switched to db test
MongoDB Enterprise testrepl:PRIMARY> db.test_collection.find().count();
844703

1.2、对于replica set集群模式命令如下：

1
/data/mongodb/bin/mongodump -h "testrepl/127.0.0.1:30000" -u admin1 -p admin123 -d test -o /data/backup/ --authenticationDatabase admin

结果如下：

1
2
2018-08-12T03:40:01.489+0000    writing test.test_collection to 
2018-08-12T03:40:03.823+0000    done dumping test.test_collection (844703 documents)

会在指定的备份目录下，生成备份实例名对应的文件夹，文件夹下有以下文件：

1
test_collection.bson  test_collection.metadata.json

命令参数详解：

1
2
3
4
5
6
7
-h:指定当前备份主机ip
--port:指定当前mongodb的启动端口
-u:指定验证的用户名
-d:需要备份的数据库实例
-p:指定用户名对应的密码
-o:指定备份的路径
--authenticationDatabase:认证数据库

备份过程没有加上指定认证数据库“–authenticationDatabase admin”,会报一下错误

1
Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.

1.3、对于单节点备份命令如下：

1
/data/mongodb/bin/mongodump -h 127.0.0.1 --port 30000 -u admin1 -p admin123 -d test -o /data/backup --authenticationDatabase admin

2、拷贝备份出来的文件，新建一台新节点(10.0.7.36)mongodb,并做恢复测试,恢复命令如下(因为测试环境节点有限，该恢复操作是将一个集群模式的备份，恢复到一个单点上的操作)：

1
/data/mongodb/bin/mongorestore -h 10.0.7.36 --port 30000  -u admin1 -p admin123 -d test  /data/backup/test --authenticationDatabase admin

恢复输出结果如下：

1
2
3
4
5
6
7
8
9
2018-08-11T14:19:48.860+0000    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2018-08-11T14:19:48.860+0000    building a list of collections to restore from /data/backup/test dir
2018-08-11T14:19:48.885+0000    reading metadata for test.test_collection from /data/backup/test/test_collection.metadata.json
2018-08-11T14:19:48.972+0000    restoring test.test_collection from /data/backup/test/test_collection.bson
2018-08-11T14:19:51.781+0000    [###########.............]  test.test_collection  28.4MB/57.1MB  (49.8%)
2018-08-11T14:19:54.490+0000    [########################]  test.test_collection  57.1MB/57.1MB  (100.0%)
2018-08-11T14:19:54.490+0000    restoring indexes for collection test.test_collection from metadata
2018-08-11T14:19:57.737+0000    finished restoring test.test_collection (844703 documents)
2018-08-11T14:19:57.737+0000    done

控制台登录，查看数据是否一致：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MongoDB Enterprise > use admin
switched to db admin
MongoDB Enterprise > db.auth('admin1','admin123');
1
MongoDB Enterprise > show dbs;
admin   0.000GB
config  0.000GB
local   0.000GB
test    0.031GB
MongoDB Enterprise > use test
switched to db test
MongoDB Enterprise > show tables;
test_collection
MongoDB Enterprise > db.test_collection.find().count();
844703

恢复完成

mongodb

点击阅读

[Mysql] 使用innodb trx和innodb lock信息表查看锁事物


1、插入表测试数据

1
2
3
4
5
6
7
8
select * from aaa;
+----+------+-------------+
| id | name | telephone   |
+----+------+-------------+
|  1 | a    | 11111111111 |
|  2 | b    | 22222222222 |
|  3 | c    | 33333333333 |
+----+------+-------------+

2、创建三个会话，造成锁事物

sessin 1:

1
2
3
BEGIN;
SELECT id FROM aaa FOR UPDATE;
SELECT SLEEP(60);

session 2:

1
SELECT name FROM aaa FOR UPDATE;

sesion 3:

1
SELECT telephone FROM aaa FOR UPDATE;

3、使用以下查询来查看正在等待的事务以及阻止它们的事务：

session 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;
查询结果如下：
+----------------+----------------+--------------------------------------+-----------------+-----------------+---------------------------------+
| waiting_trx_id | waiting_thread | waiting_query                        | blocking_trx_id | blocking_thread | blocking_query                  |
+----------------+----------------+--------------------------------------+-----------------+-----------------+---------------------------------+
| 2488           |             58 | SELECT telephone FROM aaa FOR UPDATE | 2487            |              57 | SELECT name FROM aaa FOR UPDATE |
| 2488           |             58 | SELECT telephone FROM aaa FOR UPDATE | 2486            |              53 | SELECT SLEEP(100)               |
| 2487           |             57 | SELECT name FROM aaa FOR UPDATE      | 2486            |              53 | SELECT SLEEP(100)               |
+----------------+----------------+--------------------------------------+-----------------+-----------------+---------------------------------+

上述sql语句可能太繁琐，也可使用下面语句查询

1
2
3
4
5
6
7
8
SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

4、如果查询造成锁事物的会话已经变成空闲，上面查询出来的数据会变为空，可以通过process_id,来查询琐事物

1
2
show full processlist;
| 57 | root        | localhost | aaaa | Sleep   |    566 |                                                        | NULL                  |

查询出process_id为57

5、通过processlist_id查询出thread_id

1
2
3
4
5
6
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 57;
+-----------+
| THREAD_ID |
+-----------+
|        79 |
+-----------+

6、根据thread_id查询出来造成锁事物的sql语句

1
2
3
4
5
6
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current 
WHERE THREAD_ID = 79\G
*************************** 1. row ***************************
THREAD_ID: 79
 SQL_TEXT: SELECT name FROM aaa FOR UPDATE
1 row in set (0.00 sec)

7、如果线程执行的最后一个查询不足以确定锁定的原因，则可以查询Performance Schema events_statements_history 表以查看该线程执行的最后10个语句。

1
2
3
4
5
6
7
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history 
WHERE THREAD_ID = 79 ORDER BY EVENT_ID;
+-----------+---------------------------------+
| THREAD_ID | SQL_TEXT                        |
+-----------+---------------------------------+
|        79 | SELECT name FROM aaa FOR UPDATE |
+-----------+---------------------------------+
mysql

点击阅读

[Mysql] ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


1、对一个表进行ddl操作

1
2
3
mysql>ALTER TABLE deposit_coin_order_user add `txid`  varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '区块链id' ;
提示报错：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查看该表数据只有39行数据：

1
2
3
4
5
6
select count(*) from deposit_coin_order_user;
+----------+
| count(*) |
+----------+
|       39 |
+----------+

2、查看事物表Innodb_trx是否记录相关事物，如果有找到该事物的‘trx_mysql_thread_id’

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
SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 421462261342800
                 trx_state: RUNNING
               trx_started: 2018-08-10 08:24:58
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 12989053
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

使用show full processlist查看是否有‘trx_mysql_thread_id’对应的进程，如果有就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。没有的话看看有没有正在执行的很慢SQL记录线程。

1
2
mysql > show full processlist;
| 12989053 | cwreadonly                        | 10.1.10.9:51650  | exchange_market | Sleep            |   1189 |                                                               | NULL

3、发现有id为12989053的sql，需要手动kill掉

mysql > KILL 12989053;

4、再次执行ddl语句，正常执行

ALTER TABLE deposit_coin_order_user add txid varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘区块链id’ ;

mysql

点击阅读

[Mysql] mysql定时备份及邮件告知备份情况


1、mysql全量备份脚本，内容如下：

cat /data/soft/mysql_backup.sh

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#!/bin/bash
#数据库用户名
dbuser=test
#数据库密码
dbpass=test
#备份脚本运行获取到的时间戳
stamp=`date +"%Y-%m-%d"`
#项目名称
programname=mw
#备份文件名称前缀
fullbackupname=${programname}-dbfullbak
#备份文件目录
backuppath=/data/backup
#备份文件绝对路径名称
backname=$backuppath/${fullbackupname}-${stamp}.dump
#需要删除的备份文件绝对路径名称
oldstamp=`date +"%Y-%m-%d" -d "-5 day"`
oldbackname=$backuppath/${fullbackupname}-${oldstamp}.dump
#数据库安装路径
dbpath=/data/mysql/bin
#备份操作log目录
backuplogname=/tmp/backup_${stamp}.log
#当前服务器主机名
localname=`hostname`
#当前服务器ip地址
localip=`ip a |grep global| head -1| awk '{print $2}'|awk -F'/' '{print $1}'`
#localip='65.52.165.104'
#目标邮箱
dest_mail='test@qq.com'


#判断指定log文件是否存在，如果存在将其删除
if [ -e ${backuplogname} ];then
   sudo rm -rf ${backuplogname}
else
   :
fi

#备份执行失败发送错误邮件
send_fail_mail(){
	echo "${programname}备份失败,登录服务器${localname}-${localip}的日志${backuplogname}下查看报错！！！" |mail -s ${programname}数据库备份情况 ${dest_mail}
}

#备份执行成功发送成功邮件 
send_success_mail(){
	echo "${programname}备份成功,可登录服务器${localname}-${localip}的日志${backuplogname}下查看备份信息。" |mail -s ${programname}数据库备份情况 ${dest_mail}
}

#创建备份时间函数
date_func(){
   dateresult=$((${afterstamp}-${beforstamp}))
   hour=$((${dateresult}/3600))
   min=$(((${dateresult}-${hour}*3600)/60))
   sec=$((${dateresult}-${hour}*3600-${min}*60))
   echo "5、本次备份运行时间为:"${hour}时${min}分${sec}秒 >> ${backuplogname}
}


#创建一个删除五天前的备份数据的函数
del_old_bakdata(){
   if [ -e ${oldbackname} ];then
   	 sudo echo "6、五天前的备份数据${oldbackname}存在，进行删除"  >> ${backuplogname}
   	 rm -rf ${oldbackname}
   	 if [ $? == 0 ];then
   	 	sudo echo "6.1、备份数据删除成功" >> ${backuplogname}
   	 else
   	 	sudo echo "6.1、备份数据删除失败，注意查看失败原因" >> ${backuplogname}
   	 fi
   else
     sudo echo "6、五天前的备份数据${oldbackname}不存在，不需要再删除。" >> ${backuplogname}
   fi 
}

#备份之前判断备份目录是否存在
if [ -d ${backuppath} ];then
	sudo echo "1、备份目录存在,可执行备份操作" >> ${backuplogname}
else
	sudo echo "1、备份目录不存在,手动创建" >> ${backuplogname}
	sudo mkdir -p ${backuppath}
	if [ $? == 0 ];then
	   sudo echo "1.1、备份目录创建成功，可进行下面操作" >> ${backuplogname}
	else
	   sudo echo "1.1、备份目录不存在，且创建失败，无法进行下面操作，退出备份" >> ${backuplogname}
	   exit
	fi
fi

#备份之前判断备份文件是否已经存在
if [ -e ${backname} ];then
	sudo echo "2、指定的备份文件${backname}已存在，对其进行重命名" >> ${backuplogname}
    mv ${backname} ${backuppath}/bak-${fullbackupname}-${stamp}.dump
    if [ $? == 0 ];then
    	sudo echo "2.1、文件已重新命名为${backuppath}/bak-${fullbackupname}-${stamp}.dump,可继续备份" >> ${backuplogname}
    else
    	sudo echo "2.1、文件重命名失败，退出备份操作" >> ${backuplogname}
    	exit
    fi
else
	sudo echo "2、指定的备份文件不存在，开始进行备份" >> ${backuplogname}
fi

#备份之前时间戳
beforstamp=`date +%s`
#开始全量备份数据库
sudo echo "3、开始全量备份数据库" >> ${backuplogname}
${dbpath}/mysqldump -u${dbuser} -p${dbpass} --all-databases  --single-transaction --master-data=2 --quick --set-gtid-purged=OFF > ${backname}
backupresult=$?
afterstamp=`date +%s`
if [ ${backupresult} == 0 ];then
    	sudo echo "4、${programname}数据库备份成功,文件名称为${backname}，准备删除五天前的备份数据" >> ${backuplogname}
    	date_func
        del_old_bakdata
        send_success_mail
else
    	sudo echo "4、${programname}数据库备份失败,注意查看备份失败原因并重新备份" >> ${backuplogname}
    	send_fail_mail
    	exit
fi

使用该脚本需要修改自己数据库对应的用户名dbuser,密码dbpass,邮箱dest_mail,项目名称programname,mysql数据库安装路径dbpath。其他参数可使用脚本内默认的，也可自行修改
其中–single-transaction：该参数在备份前将隔离模式设为repeatable read并启动start transaction语句。该参数只对innodb事物表有作用，当启动start transaction时开始备份数据库一致性状态，并不阻塞其他的应用。对于myisam和memory数据引擎使用这个参数备份的时候状态是实时改变的。另外该参数与–lock-tables参数是相互排斥的，lock-tables会是隐式吧提交的事物产生等待。
–master-data=2：使用该参数指备份执行时binglog开始的坐标，在输出结果中属于change master to语句，如下所示：

1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000036', MASTER_LOG_POS=79549;

如果使用该参数不指定值，默认值为1，语句不会以注释的形式写入，当dump文件被加载时该行参数会影响加载过程：

1
CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000036', MASTER_LOG_POS=79549;

如果指定参数为2，会以注释的形式写入输出文件，dump文件被加载也不会受该行语句影响。

–quick：对于备份大表比较有用，该参数强制mysqldump命令一次性从数据库中获取表的行，而不是先获取行集合，在写出之前把他们缓存到内存。

2、配置邮件发送功能

2.1、安装mailx软件

1
yum install -y mailx

2.2、修改配置文件/etc/mail.rc,在行尾添加一下信息：

1
2
set from=test@qq.com smtp=smtp.qq.com
set smtp-auth-user=test@qq.com smtp-auth-password=testpassword smtp-auth=login

参数详解：

1
2
3
4
from:指定发送邮件的发件人
smtp:指定smtp服务器信息
smtp-auth-user:允许第三方登录的用户名
smtp-auth-password：允许第三方登录的密码

2.3、发送测试邮件：

1
echo 'test email' |mail -s 'title' test@qq.com

title:指定发送文件的标题，可自行定义

3、修改备份脚本权限

1
chmod +x /data/soft/mysql_backup.sh

4、将脚本添加到定时任务

如：每天凌晨一点备份

1
shell > crontab -e
1
0 1 * * * sh /data/soft/mysql_backup.sh
mysql

点击阅读

归档:

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog