MySQL Commands For Database Administration

MySQL-DB-Commands-Admins

In this article we’ve compiled some very useful ‘mysqladmin’ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

How to change MySQL root password:

If you want to change MySQL password, then you need to type the following command. For example suppose your old password is ‘oldpass’ and you want to change it with new password ‘newpass’
# mysqladmin -u root -oldpass password 'newpass'

How to check MySQL Server is running:

To find out whether MySQL server is up and running or not, use the following command.
# mysqladmin -u root -p ping

Enter password:
mysqld is alive

How to Check which MySQL version I am running:

The following command shows MySQL version along with the current running status.

# mysqladmin -u root -p version

How to Find out current Status of MySQL server:

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.
# mysqladmin -u root -ptmppassword status

Enter password:
Uptime: 426705 Threads: 2 Questions: 44003 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059

How to check status of all MySQL Server Variables and values:

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.
# 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 |
+------------------------------------------+-------------+

How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.
# 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 |
+—————————————————+———————————————-+

How to check all the running Process of MySQL server:

The following command will display all the running process of MySQL database queries.
# 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 |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

How to create a Database in MySQL server:

To create a new database in MySQL server, use the command as shown below.
# mysqladmin -u root -p create databasename

Enter password:

How to List all MySQL Databases:

To find all the databses on the MySQL database server, use the command as show below
# 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>

How to drop a Database in MySQL server:

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘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

How to reload/refresh MySQL Privileges:

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.
# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

How to shutdown MySQL server Safely:

To shutdown MySQL server safely, type the following command.
mysqladmin -u root -p shutdown

Enter password:

How to Connect remote mysql server:

To connect remote MySQL server, use the -h (host)  with IP Address of remote machine.
# mysqladmin -h 172.16.25.126 -u root -p

How to execute command on remote MySQL server:

Let’s say you would like to see the status of remote MySQL server, then the command would be.
# mysqladmin -h 172.16.25.126 -u root -p status

How to store MySQL server Debug Information to logs

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.
# mysqladmin -u root -p debug

Enter password:

How to view mysqladmin options and usage?

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.
# mysqladmin --help

Leave a Reply

Your email address will not be published. Required fields are marked *