mysql数据库命令

//ob 数据库登录

mysql -hobbvzbn1044-1044-vpc.cn-shanghai.oceanbase.aliyuncs.com -umediumManage -P3306 -p123456

//创建数据库

CREATE DATABASE mediumManage DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

//ob 新建用户并授权

CREATE USER 'mediumManage' IDENTIFIED BY '123456';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON `mediumManage`.* TO 'mediumManage';

GRANT GRANT OPTION ON `mediumManage`.* TO 'mediumManage';

//mysql 查看当前连接信息

select concat('KILL ',id,';') from information_schema.processlist where user='root' and db='75_workflow-activiti' and host like '172.16.100.70%';

show variables like '%max_connections%'; //查看最大连接数

set global max_connections=1000 重新设置

mysql> show status like 'Threads%';

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

| Variable_name | Value |

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

| Threads_cached | 58 |

| Threads_connected | 57 | ###这个数值指的是打开的连接数

| Threads_created | 3676 |

| Threads_running | 4 | ###这个数值指的是激活的连接数，这个数值一般远低于connected数值

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

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

这是是查询数据库当前设置的最大连接数

mysql> show variables like '%max_connections%';

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

| Variable_name | Value |

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

| max_connections | 1000 |

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

可以在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]

max_connections = 1000

mysql查看锁表情况

show status like 'Table%';

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

| Variable_name | Value |

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

| Table_locks_immediate | 105 |

| Table_locks_waited | 3 |

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

Table_locks_immediate 指的是能够立即获得表级锁的次数

Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数

mysql 查看锁表解锁

-- 查看那些表锁到了

show OPEN TABLES where In_use > 0;

-- 查看进程号

show processlist;

--删除进程

kill 1085850；