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命令显示状态uptimethreadsqueries.


(资料图片)

>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|+----+------+-----------+----+---------+------+-------+------------------+

killprocess 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

15.连接远程mysql服务器

连接远程MySQL服务器,使用-h(host) 和IP Address参数

>mysqladmin-hrumenz.com-uroot-p

16.在远程MySQL服务器上执行命令

如果想查看远程的MySQL服务器的status

>mysqladmin-hrumenz.com-uroot-pstatus

17. 在从服务器上启动/停止 MySQL 主从复制?

要在从服务器上启动/停止MySQL 复制,使用以下命令。

>mysqladmin-uroot-pstart-slave
>mysqladmin-uroot-pstop-slave

18. 将 MySQL 服务器的 Debug Information 存储到日志中?

将有关正在使用的锁、已用内存和查询使用情况的调试信息写入MySQL日志文件(包括有关事件调度程序的信息)。

>mysqladmin-uroot-pdebugEnterpassword:

19.查看mysqladmin帮助

了解更多myslqadmin命令选项和用法。

>mysqladmin--help

标签:

推荐文章

X 关闭

最新资讯

X 关闭