Working with mysqladmin Made Easy

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?
  • Key Features of MySQL
• What is mysqladmin?
  • Benefits of mysqladmin
• Using mysqladmin
  • mysqladmin Commands
  • Options
  • Option Files
  • Option Groups
  • mysqladmin Variables
  • The shutdown Command
• Conclusion

What is MySQL?

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.

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.

• 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
• Options
• Option Files
• Option Groups
• mysqladmin Variables
• The shutdown Command

mysqladmin Commands

Here are some useful ‘mysqladmin’ commands that can help you work around the MySQL server.

• 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.

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.

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.

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

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.