In the 15 examples of the use mysqladmin command below, using the password root Mysql tmppassword. Change it to your password
1. How to change the root password for Mysql?
# mysqladmin -u root -ptmppassword password 'newpassword'
# mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2. How to check whether the MySQL server is running using mysqladmin?
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
3. How do I see what version of MySQL is running using mysqladmin?
This command also displays the current status of the server.
# mysqladmin -u root -ptmppassword version
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 107 days 6 hours 11 min 44 sec
Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. How to see the status of the MySQL server using mysqladmin?
# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
Status command displays the following informatsiyucommand displays the following information:
- Uptime: uptime in seconds
- Threads: Total number of clients connected to the server.
- Questions: The total number of requests to the server since startup.
- Slow queries: Total number of queries, whose execution time was more than the value of long_query_time.
- Opens: Total number of tables opened by the server.
- Flush tables: How many times the tables were flushed.
- Open tables: Total number of open tables in the database.
5. How do I view MySQL status variables and their current value using mysqladmin?
# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_admin_commands | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
6. How to display all the MySQL server system variables and their values using mysqladmin?
# mysqladmin -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| auto_increment_increment | 1 |
| basedir | / |
| big_tables | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
skip.....
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmpdir | /tmp |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.25-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------+
7. How to display all running processes / queries in the database mysql using mysqladmin?
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
You can use this command to effectively debug any performance problems and determine the process that causes problems by running on autoupdate every second.
# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
8. How to create database a MySQL using mysqladmin?
# mysqladmin -u root -ptmppassword create testdb
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
Note: To display all the tables in the database, the total number of columns, rows, indexes and so on .... use the command mysqlshow.
9. How do I delete the existing database MySQL using mysqladmin?
# mysqladmin -u root -ptmppassword drop testdb
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 'testdb' database [y/N] y
Database “testdb” dropped
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| sugarcrm |
+——————–+
3 rows in set (0.00 sec)
10. How to overload all the privileges and rights to the table using mysqladmin?
# mysqladmin -u root -ptmppassword reload;
Refresh command resets the table and close / open the log files.
# mysqladmin -u root -ptmppassword refresh
11. How to perform a safe way to stop MySQL server using mysqladmin?
# mysqladmin -u root -ptmppassword shutdown
# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'
You can use the command "/ etc / rc.d / init.d / mysqld stop" to stop the server. To start the run "/ etc / rc.d / init.d / mysql start"
12. List of all mysqladmin flush commands.
# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads
- Flush-hosts: Reset all the information in the cache hosts.
- Flush-privileges: Reload right.
- Flush-status: Clear status variables.
- Flush-threads: Flush the thread cache.
13. How to kill hung client process to MySQL using mysqladmin?
First, define the hanging process using the command processlist.
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 64 | | |
| 24 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Then use the kill command and the desired process_id. To complete several processes separate process id commas.
# mysqladmin -u root -ptmppassword kill 20
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 26 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
14. How to start or stop the replication of MySQL on the slave-server using mysqladmin?
# mysqladmin -u root -ptmppassword stop-slave
Slave stopped
# mysqladmin -u root -ptmppassword start-slave
mysqladmin: Error starting slave: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO
15. How to combine several commands mysqladmin together ?
In the example below combined team process-list, status and version for the complete withdrawal of the status of the server.
# mysqladmin -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3135
Threads: 1 Questions: 80 Slow queries: 0 Opens: 15 Flush tables: 3
Open tables: 0 Queries per second avg: 0.25
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 52 min 15 sec
You can also use the short form of writing:
# mysqladmin -u root -ptmppassword pro stat ver
Use the-h option to connect to remote MySQL server and execute the command.
# mysqladmin -h 192.168.1.112 -u root -ptmppassword pro stat ver
Useful articles:
Mysqladmin is a client for performing administrative operations!
Leave a comment!
| < Prev | Next > |
|---|
Comments
RSS feed for comments to this post.