mysqladmin-commands-for-database-administration-in-linux
mysqladmin-commands-for-database-administration-in-linux
mysqladmin-commands-for-database-administration-in-linux
mysqladmin

How to Set MySQL Root Password?
How to Change MySQL Root Password
How to Check Status of MySQL Server
How to Check Which MySQL Version I am Running
How to Find Out Current Status of MySQL Server
How to Check Which MySQL Version I am Running
How to see all MySQL server Variables and Value
How to Check Active Threads of MySQL Server
How to Create a Database in MySQL Server
How to Drop a Database in MySQL Server
How to Reload/Refresh MySQL Privileges
How to Shutdown MySQL Server Safely
Flush all information in the host cache.
Flush all tables.
XXXXXXXXXXXXXXXXXXX55

mysqladmin is a command-line database administration utility that comes with MySQL/MariaDB server, which is used by Database Administrators to perform some basic MySQL tasks such as setting the root password, changing the root password, monitoring mysql processes, reloading privileges, creating/dropping databases, checking server status, show usage statistic, kill running queries, etc.

The command to use mysqladmin and the general syntax is:

# mysqladmin [options] command [command-arg] [command [command-arg]] ...

1. How to Set MySQL Root Password If you have a fresh installation of MySQL/MariaDB server, then it doesn’t require any password to connect it as the root user. To set the MySQL password for the root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

Warning: Setting a new MYSQL password using mysqladmin should be considered vulnerable. On some systems, your password becomes visible to system status programs such as the ps command that may be executed by other users to know the status of active processes on a system.

1. How to Change MySQL Root Password If you would like to change or update the MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with a new password say xyz123.

# mysqladmin -u root -p123456 password 'xyz123' 

1. How to Check Status of MySQL Server To find out whether the MySQL server is up and running, use the following command.

# mysqladmin -u root -p ping

Enter password:
mysqld is alive 

1. How to Check Which MySQL Version I am Running The following command shows the MySQL version along with the current running status.

mysqladmin -u root -p version

Enter password:
mysqladmin Ver 9.1 Distrib 10.3.32-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Server version      10.3.32-MariaDB
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/lib/mysql/mysql.sock
Uptime:         18 min 6 sec

Threads: 6 Questions: 20 Slow queries: 0 Opens: 18 Flush tables: 1

Open tables: 11 Queries per second avg: 0.018

1. How to Find Out Current Status of MySQL Server To find out the current status of the MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

mysqladmin -u root -p status

Enter password:
Uptime: 1185 Threads: 6 Questions: 21 Slow queries: 0 Opens: 18 Flush tables: 1
Open tables: 11 Queries per second avg: 0.017
6.** How to Check MySQL Status Variables and Their Values**
To check all the running status of MySQL server variables and values, type the following command. The output would be similar to the one below.

mysqladmin -u root -p extended-status

Enter password:
+--------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------------------+
| Aborted_clients | 0 |
| Aborted_connects | 2 |
| Access_denied_errors | 2 |
| Acl_column_grants | 0 |
| Acl_database_grants | 0 |
| Acl_function_grants | 0 |
| Acl_procedure_grants | 0 |
| Acl_package_spec_grants | 0 |
| Acl_package_body_grants | 0 |
| Acl_proxy_users | 2 |
| Acl_role_grants | 0 |
| Acl_roles | 0 |
| Acl_table_grants | 0 |
| Acl_users | 4 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15706 |
| Aria_pagecache_blocks_used | 0 |
| Aria_pagecache_read_requests | 0 |
| Aria_pagecache_reads | 0 |
| Aria_pagecache_write_requests | 0 |

...

1. How to see all MySQL server Variables and Values? To see all the running variables and values of the 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           |
+---------------------------------------------------+----------------------+

...

1. How to Check Active Threads of MySQL Server The following command will display all the running processes of MySQL database queries.

mysqladmin -u root -p processlist

Enter password:

+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 2  | system user |           |    | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 1  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 4  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 3  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5  | system user |           |    | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 20 | root        | localhost |    | Query   | 0    | Init                     | show processlist | 0.000    |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+

1. How to Create a Database in MySQL Server To create a new database in the MySQL server, use the command shown below.

mysqladmin -u root -p create tecmint

Enter password:

mysql -u root -p

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.32-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tecmint |
+--------------------+

4 rows in set (0.001 sec)

1. How to Drop a Database in MySQL Server

To drop a Database in the MySQL server, use the following command. You will be asked to confirm press ‘y‘.

mysqladmin -u root -p drop tecmint

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 'tecmint' database [y/N] y
Database "tecmint" dropped

1. How to Reload/Refresh MySQL Privileges? The reload command tells the server to reload the grant tables and the refresh command flushes all tables and reopens the log files.

mysqladmin -u root -p reload

mysqladmin -u root -p refresh

1. How to Shutdown MySQL Server Safely To shutdown the MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:
You can also use the following commands to start/stop the MySQL server.

systemctl stop mysqld

systemctl start mysqld

Or

systemctl stop mariadb

systemctl start mariadb

1. Some useful MySQL Flush Commands Following are some useful flush commands with their description.

flush-hosts: Flush all host information from the host cache.
flush-tables: Flush all tables.
flush-threads: Flush all threads cache.
flush-logs: Flush all information logs.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.

Let’s check out these commands.

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

1. How to kill Sleeping MySQL Client Process? Use the following command to identify the sleeping MySQL client process.

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 |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

mysqladmin -u root -p kill 5

Enter password:

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple processes, then pass the process ids with commas separated as shown below.

mysqladmin -u root -p kill 5,10

1. How to Run Multiple mysqladmin Commands Together If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

mysqladmin -u root -p processlist status version

Enter password:

+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 1  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 2  | system user |           |    | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 4  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 3  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5  | system user |           |    | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 9  | root        | localhost |    | Query   | 0    | Init                     | show processlist | 0.000    |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+

Uptime: 173 Threads: 6 Questions: 4 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.023
mysqladmin Ver 9.1 Distrib 10.3.32-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Server version      10.3.32-MariaDB
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/lib/mysql/mysql.sock
Uptime:         2 min 53 sec

Threads: 6  Questions: 4  Slow queries: 0  Opens: 18  Flush tables: 1  Open tables: 11  Queries per second avg: 0.023

1. How to Connect Remote Mysql Server To connect to the remote MySQL server, use the -h (host) with the IP Address of the remote machine.

mysqladmin -h 172.16.25.126 -u root -p

1. How to Execute Command on Remote MySQL Server Let’s say you would like to see the status of the remote MySQL server, then the command would be.

mysqladmin -h 172.16.25.126 -u root -p status

1. How to Start/Stop MySQL Replication on a Slave Server To start/stop MySQL replication on the slave server, use the following commands.

mysqladmin -u root -p start-slave

mysqladmin -u root -p stop-slave

1. 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 the event scheduler.

mysqladmin -u root -p debug

Enter password:

1. How to View mysqladmin Options and Usage To find out more options and usage of the myslqadmin command use the help command as shown below. It will display a list of available options.

mysqladmin --help

Working with mysqladmin Made Easy

mysqladmin

MySQL is one of the most popular Relational Database Management Systems used for managing Relational Databases. MySQL uses the Structured Query Language (SQL) to define, update, and query the Database. SQL is the most widely used language for accessing and managing records in any Database. Supported by Oracle, MySQL is an open-source and free Database software under the GNU license.

MySQL is faster, highly scalable, and an easy-to-use Database Management System when compared to Microsoft SQL Server and Oracle Database. It is based on the Client-Server model, which means that the Database typically runs on a server and the data is accessed over the network part clients and workstations. The server returns the desired output to the Graphical User Interface (GUI) requests sent by the clients. MySQL supports different types of Operating Systems with many languages like PHP, PERL, JAVA, C++, C, etc.

Key Features of MySQL
Below are the reasons mentioned for the immense popularity of MySQL.

1. MySQL is an open-source Relational Database, which means it is completely free to use.
2. MySQL is based on a well-known and most widely used SQL language. It lets you execute queries on Tables, Rows, Columns, and Indexes.
3. MySQL stores data in a collection of Rows and Columns called Tables, also known as Relations.
4. MySQL works well even with a large data set and can support up to 50 million rows or more in a table.
5. MySQL is highly customizable and the open-source GPL license allows the SQL software to be modified easily as per the needs of programmers . Simplify MySQL Data Analysis with Hevo’s No-code Data Pipeline Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up data integration from MySQL and 100+ Data Sources (including 30+ Free Data Sources)and will let you directly load data to a Data Warehouse or the destination of your choice. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.

Let’s look at some of the salient features of Hevo:

Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
Scalable Infrastructure: Hevo has in-built integrations for 100’s of sources that can help you scale your data infrastructure as required.
Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

What is mysqladmin

mysqladmin is an administration program that is used by Database Administrators to easily perform basic tasks in MySQL. mysqladmin comes with many valuable tools which can be used for Server Maintenance, Server Configuration, Server Monitoring, Database Backup, Resource Management, starting and stopping of MySQL Server, managing User Roles, performing SQL CRUD Operations, etc.

mysqladmin has a controlled set of procedures and workflow. You can perform Database operations and queries with the help of standard and easy-to-use Structured Query Language (SQL). It assigns users permissions to work on the Database Server Management and Maintenance activities. Here are some of the important tasks that can be performed with mysqladmin.

1. Monitor MySQL Server processes
2. Create and drop Databases in MySQL Server
3. Flush information logs, statistics, status variables, and tables
4. Reload/reset MySQL privileges
5. Kill running queries
6. Start and stop the server with backups
7. Start and stop replicas
8. Check server configuration and status

Benefits of mysqladmin

Below are some of the advantages of mysqladmin.

1. mysqladmin follows a defined and improved structure of settings vital for the performance of the MySQL Server. It helps us to visualize the flow by displaying a graphical representation, making it easy for users to read, interpret, and fine-tune the settings of the MySQL Server.
2. It takes care of Security Risk Management, hence you can feel super safe working around your data.
3. With mysqladmin, you can easily import and export data files from the MySQL Server depending on the limited file size.
4. It maintains User Accounts, their Passwords, and is also capable of locking or unlocking users whenever needed.
5. It provides open-source flexibility and secure transactional support with high scalability and continuous uptime.
6. Thus, mysqladmin is very essential in handling all the tasks executed on a server and ensuring Data Integrity, Data Security, and Data Management.

Using mysqladmin

mysqladmin is easy to use, and you can easily perform basic MySQL tasks using mysqladmin commands. The general syntax and command to use mysqladmin are:

mysqladmin [options] command [command-arg] [command [command-arg]] ...
Let’s discuss the various aspects of mysqladmin that will come in handy when working with a huge amount of data.

mysqladmin Commands

1. Options
2. Option Files
3. Option Groups
4. mysqladmin Variables
5. The shutdown Command
6. mysqladmin Commands Here are some useful ‘mysqladmin’ commands that can help you work around the MySQL server.

mysqladmin commands
Image Source: www.2daygeek.com
Create a new Database.

create db_name

This command will create a new Database with the name “db_name”.

Delete a Database.

drop db_name

This command will delete the Database with the name “db_name” and all its tables.

Check the status of all MySQL Server variables.

extended-status

This command will display the status of server variables and their values.

Flush all information in the host cache.

flush-hosts

Similarly, you can use the “flush-” command with appropriate extension to reset tables, status, privileges, etc.

Flush all tables.

refresh

This command will flush all tables, and also close and open log files.

Set a new password.
password new_password
This command changes the password to “new_password” for your MySQL Account.

Stop the server.
shutdown
This command will stop or shutdown the server safely.

Display the server variables.
variables
This command will display the server system variables and their values.

How to Use MySQLAdmin to Change the MySQL Root Password

mysqladmin -u root password [New_Password]

Using the MySQLAdmin Command, Change the MySQL Root Password

mysqladmin -u root -pOld_Password password 'New_Password'

Using the MySQLAdmin Command, see if your MySQL server is up and running.

mysqladmin -u root -pPassword ping

Using the MySQLAdmin Command to Check MySQL Server Uptime

mysqladmin -u root -pPassword status

Using the MySQLAdmin Command, you can check the version of the MySQL server you’ve installed.

mysqladmin -u root -pPassword version

How to Use MySQLAdmin to Check the Status of a MySQL Server

mysqladmin -u root -pPassword status

How to Use MySQLAdmin to Check the Extended Status of a MySQL Server

mysqladmin -u root -pPassword extended-status

Using the MySQLAdmin Command to Check MySQL Server Variables

mysqladmin -u root -pPassword variables

Using the MySQLAdmin Command to Check the MySQL Process List

mysqladmin -u root -pPassword processlist

Using the MySQLAdmin command, kill the MySQL client process

mysqladmin -u root -pPassword kill 195003

mysqladmin can also read options from command-line and Option Files. Let’s discuss this in detail in the next few sections.

Options
mysqladmin supports the following options.

Option Description

–character-sets-dir=name  The character set files are located in this directory.
-C, –compress Compress all information exchanged between the Client and the Server if both support compression.
–connect_timeout=val  Maximum time (in seconds) before connection timeout.
-c val, –count=val    A number of iterations to make. This works with -i (–sleep) only.
–debug[=debug_options], -# [debug_options]    Write a debugging log.
–debug-check  Check memory and open file usage at exit.
–debug-info   Print debugging information, memory, and CPU usage statistics at the exit.
–default-auth=plugin  Default authentication client-side plugin to use.
–default-character-set=name   Set the default character set.
-f, –force    Don’t ask for confirmation on the drop Database.
-?, –help Display this help and exit.
-h name, –host=name   Hostname to connect to.
-l, –local    Suppress the SQL command(s) from being written to the binary log by enabling sql_log_bin=0 for the session.
-b, –no-beep  Turn off beep on error.
-p[password], –password[=password]    Password to use when connecting to the server.
–pipe, -W On Windows, connect to the server via a named pipe.
-P portnum, –port=portnum Port Number to use for connection (0 as default).
–protocol=name    The protocol to be used for a connection.
-r, –relative Show difference between current and previous values when used with -i.
-O value, –set-variable=vaue  Change the value of a variable. You can also directly set variables with –variable-name=value.
–shutdown_timeout=val Maximum time (in seconds) to wait for server shutdown.
-s, –silent   Silently exit if one is unable to connect to the server.
-i delay, –sleep=delay    Execute commands repeatedly, sleeping for delay seconds in between.
-S name, –socket=name When connecting to localhost, the Unix socket file to use, or, the name of the named pipe to use on Windows.
–ssl  Enables TLS. Even without setting this option, TLS is enabled when certain other TLS options are set.
–ssl-ca=name  Defines a path to a PEM file that should contain 1 or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS.
–ssl-capath=name  Defines a path to a directory that should contain 1 or more PEM files that should each contain 1 X509 certificate for a trusted Certificate Authority (CA) to use for TLS.
–ssl-cert=name    Defines a path to the X509 certificate file to use for TLS.
–ssl-cipher=name  List of permitted ciphers or cipher suites to use for TLS.
–ssl-crl=name Defines a path to a PEM file that should contain 1 or more revoked X509 certificates to use for TLS.
–ssl-crlpath=name Defines a path to a directory that contains 1 or more PEM files that should each contain 1 revoked X509 certificate to use for TLS.
–ssl-key=name Defines a path to a private key file to use for TLS.
–ssl-verify-server-cert   Enables server certificate verification.
–tls-version=name This option accepts a comma-separated list of TLS protocol versions.
-u, –user=name    User for login if not the current user.
-v, –verbose  Write more information.
-V, –version  Output version information and exit.
-E, –vertical Print output vertically.
-w[count], –wait[=count]  Wait and retry instead of aborting, if one can’t establish the connection.
–wait-for-all-slaves  Wait for the last binlog event to be sent to all connected replicas before shutting down.

Source: www.mariadb.com
Option Files
This description shows how mysqladmin handles Option Files. They must be given as the first argument on the command-line.

Option Description
–print-defaults Prints the program argument list and exit.
–no-defaults Default options aren’t read from any option file.
–defaults-file=# Reads only default options from the given file.
–defaults-extra-file=# Reads a file after the global files are read.
–defaults-group-suffix=# Reads option groups as well, in addition to the default option groups.
Source: www.mariadb.com
Option Groups
mysqladmin reads options from the following Option Groups from Option Files.

Group Description
[mysqladmin] Options read by mysqladmin.
[mariadb-admin] Options read by mysqladmin.
[client] Options read by all client programs.
Source: www.mariadb.com
mysqladmin Variables
The following table presents you with the list of mysqladmin variables. Variables can be set with –variable-name=value.

Variables and boolean options Value
count 0
debug-check FALSE
debug-info FALSE
force FALSE
compress FALSE
character-sets-dir (No default value)
default-character-set (No default value)
host (No default value)
no-beep FALSE
port 3306
relative FALSE
socket /var/run/mysqld/mysqld.sock
sleep 0
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
user (No default value)
verbose FALSE
vertical FALSE
connect_timeout 43200
shutdown_timeout 3600
Source: www.mariadb.com
The shutdown Command
You can shut down the server by executing the shutdown command in mysqladmin utility.

mysqladmin shutdown