在数据库运维过程中，我们时常会关注数据库的链接情况，比如总共有多少链接、有多少活跃链接、有没有执行时间过长的链接等。数据库的各种异常也能通过链接情况间接反应出来，特别是数据库出现死锁或严重卡顿的时候，我们首先应该查看数据库是否有异常链接，并杀掉这些异常链接。本篇文章将主要介绍如何查看数据库链接及如何杀掉异常链接的方法。

1.查看数据库链接

查看数据库链接最常用的语句就是 show processlist 了，这条语句可以查看数据库中存在的线程状态。普通用户只可以查看当前用户发起的链接，具有 PROCESS 全局权限的用户则可以查看所有用户的链接。

show processlist 结果中的 Info 字段仅显示每个语句的前 100 个字符，如果需要显示更多信息，可以使用 show full processlist 。同样的，查看 information_schema.processlist 表也可以看到数据库链接状态信息。

普通用户只能看到当前用户发起的链接

mysql> select user();

| user() |

| testuser@localhost |

1 row in set (0.00 sec)

mysql> show grants;

| Grants for testuser@% |

| GRANT USAGE ON . TO 'testuser'@'%' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'%' |

2 rows in set (0.00 sec)

mysql> show processlist;

| Id | User | Host | db | Command | Time | State | Info |

| 769386 | testuser | localhost | NULL | Sleep | 201 | | NULL |

| 769390 | testuser | localhost | testdb | Query | 0 | starting | show processlist |

2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist;

| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

| 769386 | testuser | localhost | NULL | Sleep | 210 | | NULL |

| 769390 | testuser | localhost | testdb | Query | 0 | executing | select * from information_schema.processlist |

2 rows in set (0.00 sec)

授予了PROCESS权限后，可以看到所有用户的链接

mysql> grant process on . to 'testuser'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants;

| Grants for testuser@% |

| GRANT PROCESS ON . TO 'testuser'@'%' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'%' |

2 rows in set (0.00 sec)

mysql> show processlist;

| Id | User | Host | db | Command | Time | State | Info |

| 769347 | root | localhost | testdb | Sleep | 53 | | NULL |

| 769357 | root | 192.168.85.0:61709 | NULL | Sleep | 521 | | NULL |

| 769386 | testuser | localhost | NULL | Sleep | 406 | | NULL |

| 769473 | testuser | localhost | testdb | Query | 0 | starting | show processlist |

4 rows in set (0.00 sec)

通过 show processlist 所得结果，我们可以清晰了解各线程链接的详细信息。具体字段含义还是比较容易理解的，下面具体来解释下各个字段代表的意思：

Id：就是这个链接的唯一标识，可通过 kill 命令，加上这个Id值将此链接杀掉。

User：就是指发起这个链接的用户名。

Host：记录了发送请求的客户端的 IP 和 端口号，可以定位到是哪个客户端的哪个进程发送的请求。

db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。

Command：是指此刻该线程链接正在执行的命令。

Time：表示该线程链接处于当前状态的时间。

State：线程的状态，和 Command 对应。

Info：记录的是线程执行的具体语句。

当数据库链接数过多时，筛选有用信息又成了一件麻烦事，比如我们只想查某个用户或某个状态的链接。这个时候用 show processlist 则会查找出一些我们不需要的信息，此时使用 information_schema.processlist 进行筛选会变得容易许多，下面展示几个常见筛选需求：

只查看某个ID的链接信息

select * from information_schema.processlist where id = 705207;

筛选出某个用户的链接

select * from information_schema.processlist where user = 'testuser';

筛选出所有非空闲的链接

select * from information_schema.processlist where command != 'Sleep';

筛选出空闲时间在600秒以上的链接

select * from information_schema.processlist where command = 'Sleep' and time > 600;

筛选出处于某个状态的链接

select * from information_schema.processlist where state = 'Sending data';

筛选某个客户端IP的链接

select * from information_schema.processlist where host like '192.168.85.0%';

2.杀掉数据库链接

如果某个数据库链接异常，我们可以通过 kill 语句来杀掉该链接，kill 标准语法是：KILL [CONNECTION | QUERY] processlist_id;

KILL 允许使用可选的 CONNECTION 或 QUERY 修饰符：

KILL CONNECTION 与不含修改符的 KILL 一样，它会终止该 process 相关链接。

KILL QUERY 终止链接当前正在执行的语句，但保持链接本身不变。

杀掉链接的能力取决于 SUPER 权限：

如果没有 SUPER 权限，则只能杀掉当前用户发起的链接。

具有 SUPER 权限的用户，可以杀掉所有链接。

遇到突发情况，需要批量杀链接时，可以通过拼接 SQL 得到 kill 语句，然后再执行，这样会方便很多，分享几个可能用到的杀链接的 SQL ：

杀掉空闲时间在600秒以上的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

where command = 'Sleep' and time > 600;

杀掉处于某个状态的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.processlist

where state = 'Waiting for table metadata lock';

杀掉某个用户发起的链接，拼接得到kill语句

select concat('KILL ',id,';') from information_schema.processlist

user = 'testuser';

这里提醒下，kill 语句一定要慎用！特别是此链接执行的是更新语句或表结构变动语句时，杀掉链接可能需要比较长时间的回滚操作。

总结：

本篇文章讲解了查看及杀掉数据库链接的方法，以后怀疑数据库有问题，可以第一时间看下数据库链接情况。
————————————————
版权声明：本文为CSDN博主「y咯p秒杀软」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_35990581/article/details/113952377

2 rows in set (0.00 sec)

周末参加了一个数据库的维护培训，感觉颇有收获，突然想记录一下，其实工作中会有各种技能培训，参加了也不少，也组织过一些，回想一下哪些培训是真正有意义的，有收获的？
首先一般一次培训2个小时左右，即使活力非常密集，全部是干货，到头来真正能留在头脑里面的也是少数，因为人在一个时刻的记忆是有限，理解力也是有限的，培训的价值就在于启发思维，打破定式，指明方向。
授人以鱼不如授人以渔，重要的是思维方式的传递。
如何组织一次有效的培训呢？
第一、对于参与的听众需求也明确的了解，能够有针对性的培训。
第二、对于材料的充分准备，能够引用多个不同的方面对观点进行论证。
第三、有层次的展现培训的内容，讲解逻辑合理，循序渐进。
第四、预演准备。

1、环境清理，删除测试数据，用于测试的环境配置
2、权限回收，检查不合理的用户权限，不需要的dba权限，非必要的数据库连接
3、压力测试总结
4、参数优化报告
5、基础信息统计
6、备份恢复测试报告
7、性能基线报告

1. 查看mysql版本
   输入如下命令，回车，再输入mysql密码，即可查看mysql的版本。

   mysql -uroot -p

mysql的版本为：5.7.20-log

2. 查看服务器版本
   通过如下命令：

   cat /etc/redhat-release

服务器版本为：CentOS Linux release 7.4.1708 (Core)

3. 下载percona-xtrabackup
   通过如下命令下载：

官网路径：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/\
binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

注意：
percona-xtrabackup在官网最新版本是8.0，8.0版本只支持mysql8.0和percona8.0
早于mysql8.0的版本需要使用xtrabackup 2.4版本备份和恢复，因此我们上次选择的是2.4.4版本。
2.4版本的官网文档地址：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

通过如下命令安

yum localinstall -y percona-xtrabackup-80-8.0.5-1.el7.x86_64.rpm

4. 全量备份

   innobackupex --defaults-file=${my.cnf} --user=${user} --password=${password} /opt

其中my.cnf为配置文件地址，user和password变量为数据库账号和密码，/opt为备份存储目录。

5. 预处理

   innobackupex --user=${user} --password=${password} --apply-log /opt/2019-07-16_16-38-13

其中user和password变量为，数据库账号和密码，/opt/2019-07-16_16-38-13为备份存储目录。

6. scp到从库

   scp -r /opt/2019-07-16_16-38-13 root@192.192.2.26:/opt

7. 关闭从库并清空从库数据和日志信息

   systemctl stop mysqld

rm -rf ${dataPath}

dataPath为数据库数据所在目录的文件

8. 在从库上恢复数据

   innobackupex --user=${user} --password=${password} --copy-back /opt/2019-07-16_16-38-13

其中user和password变量为，数据库账号和密码，/opt/2019-07-16_16-38-13为备份存储目录。

9. 修改权限

   chmod -R 777 ${dataPath}

dataPath为数据库数据所在目录的文件

10. 查看master位置

    cat /opt/2019-07-16_16-38-13/xtrabackup_binlog_info

结果如下：

mysql-bin.000468 742632435

11. 从库执行同步
    打开mysql命令客户端，执行如下sql语句

    change master to master_host=${host}, master_user=${user}, master_password=${password}, master_log_file='mysql-bin.000468',master_log_pos=742632435

其中host为主库ip，user和password为主库账号密码，master_log_file和master_log_pos取第10步，获取到的值。

启动从库复制，执行如下sql语句

start slave;

经过以上步骤，即恢复mysql主从同步
————————————————
版权声明：本文为CSDN博主「chuxiongzouzhihui」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013294608/article/details/96114174