Linux中19个MySQL数据库管理命令

当前位置：首页 > 行业要闻 > 正文

Linux中19个MySQL数据库管理命令

来源 : 面包芯语时间：2023-07-04 12:24:50

>mysqladmin-uroot-p123456passwordrumenz123

2. 如何检查 MySQL Server是否运行？

查看MySQL服务器运行状态

>mysqladmin-uroot-ppingEnterpassword:mysqldisalive

3. 查看MySQL版本？

显示MySQL版本以及当前运行状态。

>mysqladmin-uroot-pversionEnterpassword:mysqladminVer8.0.27formacos10.14onx86_64(Homebrew)Copyright(c)2000,2021,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Serverversion8.0.27Protocolversion10ConnectionLocalhostviaUNIXsocketUNIXsocket/tmp/mysql.sockUptime:5days5hours46min40secThreads:2Questions:140408Slowqueries:0Opens:727Flushtables:3Opentables:624Queriespersecondavg:0.310

4. 查看 MySQL服务器的当前状态？

查看当前MySQL服务器状态。mysqladmin命令显示状态uptime，threads和queries.

(资料图片)

>mysqladmin-uroot-pstatusUptime:452778Threads:2Questions:140406Slowqueries:0Opens:727Flushtables:3Opentables:624Queriespersecondavg:0.310

5. 查看所有 MySQL服务器变量和值的状态？

检查MySQL服务器变量和值

>mysqladmin-uroot-pextended-statusEnterpassword:+------------------------------------------+-------------+|Variable_name|Value|+------------------------------------------+-------------+|Aborted_clients|3||Aborted_connects|3||Binlog_cache_disk_use|0||Binlog_cache_use|0||Binlog_stmt_cache_disk_use|0||Binlog_stmt_cache_use|0||Bytes_received|6400357||Bytes_sent|2610105||Com_admin_commands|3||Com_assign_to_keycache|0||Com_alter_db|0||Com_alter_db_upgrade|0||Com_alter_event|0||Com_alter_function|0||Com_alter_procedure|0||Com_alter_server|0||Com_alter_table|0||Com_alter_tablespace|0|+------------------------------------------+-------------+

6. 查看所有 MySQL服务器的变量和值？

查看MySQL变量和值

>mysqladmin-uroot-pvariablesEnterpassword:+---------------------------------------------------+----------------------------------------------+|Variable_name|Value|+---------------------------------------------------+----------------------------------------------+|auto_increment_increment|1||auto_increment_offset|1||autocommit|ON||automatic_sp_privileges|ON||back_log|50||basedir|/usr||big_tables|OFF||binlog_cache_size|32768||binlog_direct_non_transactional_updates|OFF||binlog_format|STATEMENT||binlog_stmt_cache_size|32768||bulk_insert_buffer_size|8388608||character_set_client|latin1||character_set_connection|latin1||character_set_database|latin1||character_set_filesystem|binary||character_set_results|latin1||character_set_server|latin1||character_set_system|utf8||character_sets_dir|/usr/share/mysql/charsets/||collation_connection|latin1_swedish_ci|+---------------------------------------------------+----------------------------------------------+

7.查看MySQL服务器的所有运行进程？

>mysqladmin-uroot-pprocesslistEnterpassword:+-------+---------+-----------------+---------+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+-------+---------+-----------------+---------+---------+------+-------+------------------+|18001|rsyslog|localhost:38307|rsyslog|Sleep|5590||||18020|root|localhost||Query|0||showprocesslist|+-------+---------+-----------------+---------+---------+------+-------+------------------+

8. 在 MySQL 服务器中创建数据库？

创建数据库。

>mysqladmin-uroot-pcreaterumenz_testEnterpassword:

9. 在 MySQL 服务器中删除数据库？

通过mysqladmin创建数据库

>mysqladmin-uroot-pdroprumenz_testDroppingthedatabaseispotentiallyaverybadthingtodo.Anydatastoredinthedatabasewillbedestroyed.Doyoureallywanttodropthe"rumenz_test"database[y/N]yDatabase"rumenz_test"dropped

10. 重新加载/刷新 MySQL 权限？

reload命令告诉服务器重新加载授权表。refresh命令刷新所有表并重新打开日志文件。

>mysqladmin-uroot-preload>mysqladmin-uroot-prefresh

11. 安全关闭 MySQL 服务器？

>mysqladmin-uroot-pshutdownEnterpassword:

12. MySQL Flush 常用命令

#mysqladmin-uroot-pflush-hosts#mysqladmin-uroot-pflush-tables#mysqladmin-uroot-pflush-threads#mysqladmin-uroot-pflush-logs#mysqladmin-uroot-pflush-privileges#mysqladmin-uroot-pflush-status

13. 杀死正在休眠的 MySQL 客户端进程？

找出休眠的进程

>mysqladmin-uroot-pprocesslistEnterpassword:+----+------+-----------+----+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+----+---------+------+-------+------------------+|5|root|localhost||Sleep|14||||8|root|localhost||Query|0||showprocesslist|+----+------+-----------+----+---------+------+-------+------------------+

用kill和process ID杀死

>mysqladmin-uroot-pkill5Enterpassword:+----+------+-----------+----+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+----+---------+------+-------+------------------+|12|root|localhost||Query|0||showprocesslist|+----+------+-----------+----+---------+------+-------+------------------+

如果需要kill多个进程，用逗号分隔多个process ID

>mysqladmin-uroot-pkill5,10

14、同时运行多个mysqladmin命令？

同时执行多个 mysqladmin命令

>mysqladmin-uroot-pprocessliststatusversionEnterpassword:+----+------+-----------+----+---------+------+-------+------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+----+---------+------+-------+------------------+|8|root|localhost||Query|0||showprocesslist|+----+------+-----------+----+---------+------+-------+------------------+Uptime:3801Threads:1Questions:15Slowqueries:0Opens:15Flushtables:1Opentables:8Queriespersecondavg:003mysqladminVer42Distrib28,forLinuxoni686Copyright(c)2000,2012,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Serverversion28Protocolversion10ConnectionLocalhostviaUNIXsocketUNIXsocket/var/lib/mysql/mysql.sockUptime:1hour3min21secThreads:1Questions:15Slowqueries:0Opens:15Flushtables:1Opentables:8Queriespersecondavg:003