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
+--------------------------------+---------------------------------+
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!
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
+--------------------------------+---------------------------------+
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!
No comments:
Post a Comment