Working with mysqladmin Made Easy

on Data Integration, Database Management Systems, MySQL, Relational Database, SQL, SQL Server, Tutorials • January 7th, 2022 • Write for Hevo

mysqladmin Cover Image

MySQL is one of the most widely used open-source Database Management Systems in the world. MySQL is an open-source Relational Database Management System (RDMS) and is popularly used with PHP programming and other web applications. It works on a server as it is based on a Client-Server model. MySQL Server is capable of storing a huge amount of data in the form of MySQL tables. And, MySQL uses the Structured Query Language (SQL) to fetch and manage this data. SQL is used by organizations all over the world to manage and maintain Databases. This article will take you through a very important MySQL command-line utility called mysqladmin.

mysqladmin is a MySQL Client used by Database Administrators for performing basic administrative tasks. mysqladmin is defined as a program that can be used to check the server’s configuration and status, manage users and roles, monitor MySQL processes, and other such admin procedures. It also guides you on how to start and stop a MySQL Server with backups on Windows and Linux. Before getting into details, let’s discuss MySQL in brief.

Table of Contents

What is MySQL?

mysqladmin: Logo
Image Source: www.mg.wikipedia.org

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.

  • MySQL is an open-source Relational Database, which means it is completely free to use.
  • MySQL is based on a well-known and most widely used SQL language. It lets you execute queries on Tables, Rows, Columns, and Indexes.
  • MySQL stores data in a collection of Rows and Columns called Tables, also known as Relations.
  • MySQL works well even with a large data set and can support up to 50 million rows or more in a table.
  • 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.

Get started with hevo for free

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.
Sign up here for a 14-day free trial!

What is mysqladmin?

mysqladmin
Image Source: www.howtoforge.com

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.

  • Monitor MySQL Server processes
  • Create and drop Databases in MySQL Server
  • Flush information logs, statistics, status variables, and tables
  • Reload/reset MySQL privileges
  • Kill running queries
  • Start and stop the server with backups
  • Start and stop replicas
  • Check server configuration and status

Benefits of mysqladmin

Below are some of the advantages of mysqladmin.

  • 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.
  • It takes care of Security Risk Management, hence you can feel super safe working around your data.
  • With mysqladmin, you can easily import and export data files from the MySQL Server depending on the limited file size.
  • It maintains User Accounts, their Passwords, and is also capable of locking or unlocking users whenever needed.
  • It provides open-source flexibility and secure transactional support with high scalability and continuous uptime.

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

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.

OptionDescription
–character-sets-dir=nameThe character set files are located in this directory.
-C, –compressCompress all information exchanged between the Client and the Server if both support compression.
–connect_timeout=valMaximum time (in seconds) before connection timeout.
-c val, –count=valA number of iterations to make. This works with -i (–sleep) only.
–debug[=debug_options], -# [debug_options]Write a debugging log.
–debug-checkCheck memory and open file usage at exit.
–debug-infoPrint debugging information, memory, and CPU usage statistics at the exit.
–default-auth=pluginDefault authentication client-side plugin to use.
–default-character-set=nameSet the default character set.
-f, –forceDon’t ask for confirmation on the drop Database.
-?, –helpDisplay this help and exit.
-h name, –host=nameHostname to connect to.
-l, –localSuppress the SQL command(s) from being written to the binary log by enabling sql_log_bin=0 for the session.
-b, –no-beepTurn off beep on error.
-p[password], –password[=password]Password to use when connecting to the server.
–pipe, -WOn Windows, connect to the server via a named pipe.
-P portnum, –port=portnumPort Number to use for connection (0 as default).
–protocol=nameThe protocol to be used for a connection.
-r, –relativeShow difference between current and previous values when used with -i.
-O value, –set-variable=vaueChange the value of a variable. You can also directly set variables with –variable-name=value.
–shutdown_timeout=valMaximum time (in seconds) to wait for server shutdown.
-s, –silentSilently exit if one is unable to connect to the server.
-i delay, –sleep=delayExecute commands repeatedly, sleeping for delay seconds in between.
-S name, –socket=nameWhen connecting to localhost, the Unix socket file to use, or, the name of the named pipe to use on Windows.
–sslEnables TLS. Even without setting this option, TLS is enabled when certain other TLS options are set.
–ssl-ca=nameDefines 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=nameDefines 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=nameDefines a path to the X509 certificate file to use for TLS.
–ssl-cipher=nameList of permitted ciphers or cipher suites to use for TLS.
–ssl-crl=nameDefines a path to a PEM file that should contain 1 or more revoked X509 certificates to use for TLS.
–ssl-crlpath=nameDefines 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=nameDefines a path to a private key file to use for TLS.
–ssl-verify-server-certEnables server certificate verification.
–tls-version=nameThis option accepts a comma-separated list of TLS protocol versions.
-u, –user=nameUser for login if not the current user.
-v, –verboseWrite more information.
-V, –versionOutput version information and exit.
-E, –verticalPrint output vertically.
-w[count], –wait[=count]Wait and retry instead of aborting, if one can’t establish the connection.
–wait-for-all-slavesWait 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.

OptionDescription
–print-defaultsPrints the program argument list and exit.
–no-defaultsDefault 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.

GroupDescription
[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 optionsValue
count0
debug-checkFALSE
debug-infoFALSE
forceFALSE
compressFALSE
character-sets-dir(No default value)
default-character-set(No default value)
host(No default value)
no-beepFALSE
port3306
relativeFALSE
socket/var/run/mysqld/mysqld.sock
sleep0
sslFALSE
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-certFALSE
user(No default value)
verboseFALSE
verticalFALSE
connect_timeout43200
shutdown_timeout3600
Source: www.mariadb.com

The shutdown Command

You can shut down the server by executing the shutdown command in mysqladmin utility.

mysqladmin shutdown

However, when a Master Server is shutdown, the Master kills Client Threads in random order which is ambiguous in nature. The Master also considers its Binary Log Dump Threads as regular Client Threads by default. This means that there is a possibility of binary log dump threads being killed before Client Threads. This leads to the data written on the Master during normal shutdown not being replicated.

This is where –wait-for-all-slaves option comes into the picture. You can provide the –wait-for-all-slaves option to the mysqladmin utility to kill the Binary Log Dump Threads only after all Client Threads have been killed. The Binary Logs are sent to all connected replicas before shutdown.

mysqladmin --wait-for-all-slaves shutdown

Conclusion

MySQL Server is a Relational Database that stores data in a combination of rows and columns in a table. mysqladmin command-line utility is useful for retrieving this data or for performing operations/queries on this data using the standard Structured Query Language (SQL). mysqladmin comes in handy when dealing with massive amounts of data in various industries such as IT, Healthcare, Finance, Government, Education, etc. It is pretty user-friendly and makes it easy to interact with other web-based administrative tools like phpMyAdmin.

However, managing growing Databases and the number of associated processes like Business Analytics isn’t an easy job. Migrating massive amounts of data from MySQL to a Cloud-based Data Warehouse is a tedious and time taking process but using a Data Integration tool like Hevo can perform this process with no effort and no time.

visit our website to explore hevo

Hevo Data with its strong integration with 100+ Sources & BI tools such as MySQL, PostgreSQL, MS SQL Server, etc., allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with mysqladmin in the comments section below.

No-code Data Pipeline for MySQL