- Kali Linux Tutorial
- Kali Linux - Home
- Installation & Configuration
- Information Gathering Tools
- Vulnerability Analyses Tools
- Kali Linux - Wireless Attacks
- Website Penetration Testing
- Kali Linux - Exploitation Tools
- Kali Linux - Forensics Tools
- Kali Linux - Social Engineering
- Kali Linux - Stressing Tools
- Kali Linux - Sniffing & Spoofing
- Kali Linux - Password Cracking Tools
- Kali Linux - Maintaining Access
- Kali Linux - Reverse Engineering
- Kali Linux - Reporting Tools
- Kali Linux Useful Resources
- Kali Linux - Quick Guide
- Kali Linux - Useful Resources
- Kali Linux - Discussion
How to Use the mysql Client and Related Utilities in Linux
MySQL is an open source relational database management system (RDBMS) that has been popular for over twenty years. It is widely used on Linux systems for both web server solutions and standalone applications. This article will provide an overview of the most commonly used MySQL utilities, including mysql and mysqladmin, along with some examples on how to use them. It's worth noting that the explanations in this article also apply to MariaDB, a popular fork of MySQL created by the original developers, due to concerns that MySQL may not remain open source and intend to maintain high compatibility with MySQL.
Install and use MySQL
When installing MySQL, two packages are provided: mysql-server, which contains the server and all the utilities to connect to the server, and mysql-client, which contains only the utilities to connect to a server located elsewhere. Regardless of which package you choose, there will be several commands that start with "mysql".
Connecting to a MySQL Server
The mysql command is the command line client and main binary for connecting to a MySQL server. It provides a shell where we can interact with MySQL or MariaDB server. Most Linux distributions require you to run these utilities as root.
$ sudo mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>
With this command we access the shell in interactive mode. We can enter SQL statements in succession to interact with our databases, using the same connection.
Create and Manage Databases and Tables
For example, we can query the system database to get all the users in the system −
mysql> select host, user from mysql.user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | root | ... +-----------+------------------+ 6 rows in set (0.00 sec)
We can also use MySQL-specific statements like USE and SHOW TABLES −
mysql> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | ... +---------------------------+ 33 rows in set (0.01 sec)
mysqladmin is a tool to perform administrative tasks on the server. It's a non-interactive client that prompts for the command with the action we want to perform. For example, we can use mysqladmin to create and drop a database −
$ sudo mysqladmin create the_database $ $ sudo mysqladmin drop the_database ... Do you really want to drop the 'the_database' database [y/N] y Database "the_database" dropped $
In this section, we will explore some additional utilities that can be used to manage and maintain MySQL databases. These utilities include “mysqldump”, “mysqlrepair” and “mysqlimport”. mysqldump is a powerful database backup tool and can be used to dump a single database or a collection of databases for backup or transfer to another SQL server. “mysqlrepair” is a utility for repairing damaged tables in a MySQL database and can be used to repair MyISAM and InnoDB tables. Finally, “mysqlimport” is a tool for importing data from text files into a MySQL table, where each row represents a new record and each field is separated by a tab.
mysqldump is a utility for creating database backups. It can be used to download a database or a collection of databases for backup or transfer to another SQL server.
$ mysqldump -u root -p db_name > db_name.sql
mysqlrepair is used to repair damaged tables in a MySQL database. It can be used to repair MyISAM and InnoDB tables.
$ mysqlrepair -u root -p db_name table_name
mysqlimport is used to import data from text files into a MySQL table. The text file must have a specific format, with each line representing a new record and each field separated by a tab.
$ mysqlimport -u root -p db_name table_name.txt
In conclusion, MySQL is a powerful and widely used relational database management system that is perfect for small and large applications. The MySQL client and related utilities on Linux allow you to connect to a MySQL server, create and manage databases and tables, and manipulate data. This article has provided an overview of how to use the MySQL client and related utilities on Linux, including example commands for connecting to a MySQL server, creating and managing databases and tables, and manipulating data, as well as additional utilities for security backup and repair. Understanding the differences between these utilities will help you use them more effectively and efficiently.
- Related Articles
- How to Setup SSL for MySQL Server and Client on Linux
- How to Install and Configure an NTP Client and Server on Linux?
- In MySQL, why a client cannot use a user-defined variable defined by another client?
- 20 Useful Commands of ‘Sysstat’ Utilities for Linux Performance Monitoring
- How to use waiter functionality for bucket_not_exists using Boto3 and AWS Client?
- What is MySQL event and how it is related to trigger?
- How to Import and Export MySQL Databases in Linux
- The MySQL Command-Line Client
- MySQL Client Programs
- MySQL Client Options
- MySQL Client Commands
- MySQL Client Logging
- MySQL Client Tips