20 mysqladmin命令示例
1.如何设置MySQL Root密码?
如果你有 MySQL服务器的全新安装,那么它不要求任何密码进行连接root用户。 要为root用户设置密码 的MySQL,请使用以下命令。# mysqladmin -u root password YOURNEWPASSWORD
2.如何更改MySQL Root密码?
如果您想更改或更新 MySQL root密码,则需要键入以下命令。 例如,假设您的旧密码是 123456,你想用新的密码来改变它说 XYZ123。mysqladmin -u root -p123456 password 'xyz123'
3.如何检查MySQL Server是否正在运行?
要找出 MySQL服务器是否正常运行,请使用以下命令。# mysqladmin -u root -p ping Enter password: mysqld is alive
4.如何检查我运行的MySQL版本?
以下命令显示当前运行状态以及 MySQL版本。# mysqladmin -u root -p version Enter password: mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.28 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 days 14 min 45 sec Threads: 2 Questions: 36002 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
5.如何找出MySQL服务器的当前状态?
要找出 MySQL服务器的当前状态,请使用以下命令。 mysqladmin命令显示 的正常运行时间与正在运行的 线程和 查询的状态。# mysqladmin -u root -ptmppassword status Enter password: Uptime: 606704 Threads: 2 Questions: 36003 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
6.如何检查所有MySQL Server变量的值和值的状态?
要检查 MySQL服务器变量和值的所有运行状态,请键入以下命令。输出将类似于下面。# mysqladmin -u root -p extended-status Enter password: +------------------------------------------+-------------+ | 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 | +------------------------------------------+-------------+
7.如何查看所有MySQL服务器变量和值?
要查看所有正在运行的变量和 MySQL服务器的值,使用如下命令。# mysqladmin -u root -p variables Enter password: +---------------------------------------------------+----------------------------------------------+ | 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 | +---------------------------------------------------+----------------------------------------------+
8.如何检查MySQL服务器的所有正在运行的进程?
以下命令将显示 MySQL数据库查询的所有正在运行的进程。# mysqladmin -u root -p processlist Enter password: +-------+---------+-----------------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+---------+-----------------+---------+---------+------+-------+------------------+ | 18001 | rsyslog | localhost:38307 | rsyslog | Sleep | 5590 | | | | 18020 | root | localhost | | Query | 0 | | show processlist | +-------+---------+-----------------+---------+---------+------+-------+------------------+
9.如何在MySQL服务器中创建数据库?
要在 MySQL服务器一个新的数据库,使用命令如下所示。# mysqladmin -u root -p create databasename Enter password:
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18027 Server version: 5.5.28 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | databasename | | mysql | | test | +--------------------+ 8 rows in set (0.01 sec) mysql>
10.如何删除MySQL服务器中的数据库?
要滴在 MySQL服务器数据库,请使用以下命令。 你会被要求确认按 “y”。# mysqladmin -u root -p drop databasename Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'databasename' database [y/N] y Database "databasename" dropped
11.如何重新加载/刷新MySQL特权?
reload命令告诉服务器重装授权表。 刷新命令刷新所有表并重新打开日志文件。# mysqladmin -u root -p reload; # mysqladmin -u root -p refresh
12.如何安全关闭MySQL服务器?
关机 MySQL服务器安全,请键入以下命令。mysqladmin -u root -p shutdown Enter password:您还可以使用以下命令启动/停止MySQL服务器。
# /etc/init.d/mysqld stop # /etc/init.d/mysqld start
13.一些有用的MySQL Flush命令
以下是一些有用的flush命令及其描述。- 嵌入式主机 :刷新从主机缓存中的所有主机信息。
- 嵌入式表 :刷新所有表。
- 平齐线 :刷新所有线程缓存。
- 平齐日志 :刷新所有信息记录。
- 冲洗权限 :刷新授权表(同重装)。
- 冲洗状态 :清除状态变量。
# mysqladmin -u root -p flush-hosts # mysqladmin -u root -p flush-tables # mysqladmin -u root -p flush-threads # mysqladmin -u root -p flush-logs # mysqladmin -u root -p flush-privileges # mysqladmin -u root -p flush-status
14.如何杀死睡眠的MySQL客户端进程?
使用以下命令来确定睡眠 MySQL客户端程序。# mysqladmin -u root -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 5 | root | localhost | | Sleep | 14 | | | | 8 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+现在, 杀灭和 进程ID运行以下命令,如下图所示。
# mysqladmin -u root -p kill 5 Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 12 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+如果你喜欢 杀多的过程,然后用逗号,如下图所示分开传递 进程ID的。
# mysqladmin -u root -p kill 5,10
15.如何一起运行多个mysqladmin命令?
如果你想执行多个 “mysqladmin”命令组合在一起,则该命令会是这样。# mysqladmin -u root -p processlist status version Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 8 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ Uptime: 3801 Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003 mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.28 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 hour 3 min 21 sec Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003
16.如何连接远程mysql服务器
连接远程 MySQL服务器,使用 -h( 主机 )与远程计算机的 IP地址 。# mysqladmin -h 172.16.25.126 -u root -p
17.如何在远程MySQL服务器上执行命令
比方说,你想看到远程 MySQL服务器的 状态 ,则该命令会。# mysqladmin -h 172.16.25.126 -u root -p status
18.如何在从服务器上启动/停止MySQL复制?
要启动/停止 MySQL复制药膏服务器上,使用以下命令。# mysqladmin -u root -p start-slave
# mysqladmin -u root -p stop-slave
19.如何存储MySQL服务器调试信息到日志?
它告诉服务器写写正在使用的锁,使用的内存和查询使用情况,包括有关事件调度信息 MySQL日志文件调试信息。# mysqladmin -u root -p debug Enter password:
20.如何查看mysqladmin选项和用法
要了解更多的选择和 myslqadmin命令的用法使用help命令,如下图所示。它将显示可用选项的列表。# mysqladmin --help与本文中的示例,我们已经尽了最大努力,包括几乎所有的 “mysqladmin”命令,如果还在,我们已经错过了什么,请不要让我们通过注释知道,不要忘记与你的朋友分享。