1、查询当前连接数:show status like ‘Threads%’
[apache@bkcase ~]$ mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 34385
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 11 |
| Threads_connected | 23 |
| Threads_created | 34 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
2、远程连接数据库:mysql -h ip uroot -p
[apache@ai_bk ~]$ mysql -h 172.21.16.12 -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 16327509
Server version: 5.7.18-txsql-log 20190203
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> exit
Bye
3、导入、导出
导出整个数据库结构和数据
mysqldump -h localhost -uroot -p*** ai_bk > ai_bk_20190713.sql
导出整个数据库结构(不包含数据)
mysqldump -h localhost -uroot -p*** -d ai_bk > ai_bk_20191008.sql
导入:
create database ai_bk;
mysql -h localhost -uroot -p*** ai_bk< ai_bk_20190713.sql
4、查看最大连接数:show variables like ‘character%’;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
4、查看当前操作:show processlist’;
MySQL [(none)]> show processlist;
+----------+------+--------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+------+--------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 16796147 | root | 172.21.16.3:39038 | NULL | Query | 0 | starting | show processlist |
| 16796150 | root | 172.21.16.11:50932 | NULL | Sleep | 1 | | NULL |
| 16796208 | root | 172.21.16.11:51002 | NULL | Sleep | 186 | | NULL |
| 16796359 | root | 172.21.16.11:51176 | NULL | Query | 59 | Sending data | select * from ai_bk.dev_status a where pack_id like '%%' and id = (select max(id) from ai_bk.dev_st |
+----------+------+--------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
可以用:kill id;杀死