15 MySQL administration commands

15 MySQL administration commands

In all the 15 mysqladmin command-line examples below, tmppassword is used as the MySQL Root User Password. please change this to your MySQL Root Password.
1. How to change the MySQL Root User Password?
# 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 MySQL server is up and running?
# Mysqladmin-u root-P Ping
Enter password:
Mysqld is alive3. how do I find out what version of MySQL I am running?
Apart from giving the 'server version', this command also displays the current status of the MySQL 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. Sock
Uptime: 107 days 6 hours 11 min 44 Sec

Threads: 1 questions: 231976 slow queries: 0 open S: 17067
Flush tables: 1 open tables: 64 queries per second AVG: 0.254. What is the current status of MySQL server?
# Mysqladmin-u root-ptmppassword status
Uptime: 9267148
Threads: 1 questions: 231977 slow queries: 0 open S: 17067
Flush tables: 1 open tables: 64 queries per second AVG: 0.25the status command displays the following information:

Uptime: uptime of the MySQL server in seconds
Threads: Total number of clients connected to the server.
Questions: Total number of queries the server has executed since the startup.
Slow queries: Total number of queries whose execution time WAAS more than long_query_time variable's value.
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 to view all the MySQL server status variable and it's current value?
# Mysqladmin-u root-ptmppassword extended-status
+ ----------------------------------- + ----------- +
| Variable_name | value |
+ ----------------------------------- + ----------- +
| Aborted_client | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_administrator | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 | 6. How to display all MySQL Server System variables and the values?
# 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 the running process/queries in the MySQL database?
# 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 debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# 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 a MySQL database?
# 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 tables in a database, total number of columns, row, column types, indexes Etc., use the mysqlshow command that we discussed in our previous articles.

9. How to delete/drop an existing MySQL database?
# 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 reload/refresh the privilege or the grants tables?
# Mysqladmin-u root-ptmppassword reload; refresh command will flush all the tables and close/open log files.

# Mysqladmin-u root-ptmppassword refresh11. what is the safe method to shutdown the MySQL server?
# Mysqladmin-u root-ptmppassword Shutdown

# Mysql-u root-ptmppassword
Error 2002 (hy000): Can't connect to local MySQL Server
Through socket '/var/lib/MySQL. sock 'note: You can also use "/etc/rc. d/init. d/mysqld stop "to shutdown the server. to start the server, execute "/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-threadsflush-hosts: flush all information in the host C Ache.
flush-privileges: Reload the grant tables (same as RELOAD ).
flush-status: clear status variables.
flush-threads: flush the thread cache.
13. how to kill a hanging MYSQL client process?
first identify the hanging MYSQL client process using the processlist command.

# 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 |
+ ---- + ------ + ----------- + ---- + --------- + ------ + ------- + ------------------ + now, use the kill command and pass the process_id as shown below. to kill multiple process you can pass comma separated process ID's.

# 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 and stop MySQL replication on a slave server?
# 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 to15. how to combine multiple mysqladmin commands together?
In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# 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. Sock
Uptime: 52 min 15 secyou can also use the short form as shown below:

# Mysqladmin-u root-ptmppassword pro stat veruse the option-h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# Mysqladmin-H 192.168.1.112-u root-ptmppassword pro stat ver