A command line primer with syntax, options, and a few examples.
The “mysql commands cheat sheet pdf” is a document that contains a list of all MySQL Database Commands. This document will help you learn more about the command line interface for MySQL.
Open-source relational database management systems MySQL and MariaDB are both referred to be open-source relational database management systems (RDBMS). Since MySQL is divided into two versions: community and enterprise.
MariaDB evolved into a drop-in substitute for MySQL, displaying all of MySQL’s structured query language (SQL) capabilities at a lower cost.
This page is for anybody who uses MySQL Enterprise Edition, MySQL Community Edition, or MariaDB. By the conclusion of this article, you should be able to employ the strong structured query language that these RDBMS mimic.
MySQL Server should be installed on Linux.
The official repository contains MySQL or MariaDB, which may be installed using the package manager as described.
—————- MySQL —————- [On Debian, Ubuntu, and Mint] $ sudo apt install mysql-server [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux] $ sudo apt install mysql-server —————- MariaDB —————- [On Debian, Ubuntu, and Mint] $ sudo apt install mariadb-server [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux] $ sudo dnf install mariadb-server
Setup of MySQL Pre-Configuration
The first step, assuming you have a MariaDB or MySQL installation, is to start, enable, and verify the status of your MySQL. Enabling MySQL guarantees that it remains operational even after your Linux system has been successfully restarted.
Run the following command to start MySQL:
$ sudo systemctl start mariadb OR $ sudo systemctl start mysql/mysqld
Run the following command to activate MySQL:
$ sudo systemctl enable mariadb OR $ sudo systemctl enable mysql/mysqld
Run the following command to verify MySQL’s status:
$ sudo systemctl status mysql/mysqld OR $ sudo systemctl status mariadb 
Check MySQL Running Status
It’s time to set up a few things now that we know MySQL is up and running. You may wish to execute the following script to protect your MySQL/MariaDB installation if you’re starting from scratch:
$ sudo mysql_secure_installation 
Secure MySQL Installation
After that, you should follow the prompts to setup your MySQL installation properly.
MySQL Commands for Database Management
Follow the syntax rules below to connect to your MySQL database.
-p mysql -u [username] $ mysql -u [username] $ mysql -u [username
To begin, the root user is used to access the time database, as seen below.
$ mysql -u root -p 
Connect MySQL Database
We can run multiple SQL queries now that we’re inside the MySQL database shell.
Make a brand-new MySQL user.
To Make a brand-new MySQL user., run the following SQL command.
MariaDB [(none)]> CREATE USER ‘your_user’@’localhost’ IDENTIFIED BY ‘your_user_password’;
We’ll run the following commands to give this user the same capabilities as the root user:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘your_user’@’localhost’; MariaDB [(none)]> EXIT;
To login to the DB console as this user, exit MySQL and re-enter the new user credentials.
$ mysql -u your_user -p 
Connect MySQL Database as User
Using MySQL for Databases
Run the following command to see all current MySQL databases:
MariaDB [(none)]> SHOW DATABASES;
Run the following commands to create a new MySQL database:
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS database_name;
Run the following command to remove a MySQL database that you are no longer using:
MariaDB [(none)]> DROP DATABASE IF EXISTS database_name; 
Using MySQL for Databases
Run the following command to switch to a particular MySQL database:
MariaDB [(none)]> USE database_name;
Tables in MySQL Databases (Working with MySQL Database Tables)
If you’re connected to a particular database, you can use the following command to get a list of the MySQL tables associated with that database:
MariaDB [(none)]> USE database_name; MariaDB [(none)]> SHOW TABLES; 
Show MySQL Database Tables
You must additionally specify column definitions when creating a new database table, as seen below.
CREATE TABLE IF NOT EXISTS table_name( column_list ); 
Create New MySQL Database Table
Refer to the following command syntax to remove an existing database table:
MariaDB [(none)]> DROP TABLE IF EXISTS table_name;
Use the following command syntax to add a new column to an existing database table:
MariaDB [(none)]> ALTER TABLE table_name ADD COLUMN column_name;
To remove a column from a database table, follow these steps:
MariaDB [(none)]> ALTER TABLE table_name DROP COLUMN column_name;
If your database table requires a primary key, use the following command:
MariaDB [(none)]> ALTER TABLE table_name ADD PRIMARY KEY (column_name); 
Tables in MySQL Databases (Working with MySQL Database Tables)
Run the following command to delete a database table’s primary key:
MariaDB [(none)]> ALTER TABLE table_name DROP PRIMARY KEY;
Run the following command to see the columns associated with a certain database table:
MariaDB [(none)]> DESCRIBE table_name; 
View MySQL Database Table Columns
Run the following command to see individual column information:
MariaDB [(none)]> DESCRIBE table_name table_column; 
View MySQL Column Info
Querying Data in a MySQL Database
You may run a number of interesting queries after you’ve discovered the different tables in your database.
Run the following command to get all the data connected with a given database table:
MariaDB [(none)]> SELECT * FROM table_name; 
Query MySQL Table
Run the following command to query data from one or more table columns:
MariaDB [(none)]> SELECT column_name1, column_name2, column_name3 FROM table_name; 
Query MySQL Column Data
If your database table query contains duplicate rows, use the command to remove them:
MariaDB [(none)]> SELECT DISTINCT COLUMN FROM table_name;
To create a new database table record, follow these steps:
MariaDB [(none)]> INSERT INTO table_name(column_list) VALUES(value_list); 
Insert New MySQL Table Record
To make changes to an existing database table record, follow these steps:
MariaDB [(none)]> Update table_name SET column1=value1; 
Update MySQL Database Table Record
Backup and Restore of Databases
The usage of the mysqldump command-line client application must be acknowledged here. The first step is to figure out which databases and tables you want to backup or restore.
MariaDB [(none)]> show databases; MariaDB [(none)]> show tables;
Use the following syntax to back up a single MySQL database:
$ sudo mysqldump -u [db_username] -p[db_password] [database_name] > [generated_db_backup.sql]
The following is how the above command syntax will be implemented:
$ sudo mysqldump -u root [email protected] demodb > demodb.sql 
MySQL Database Backup
If you’re working with many databases, the command will look something like this:
$ sudo mysqldump -u root [email protected] –databases demodb mysql > demodb_mysql.sql
To back up all MySQL databases, follow these steps:
$ sudo mysqldump -u root [email protected] –all-databases > all_databases.sql
To back up a single database table named tasks, follow these steps:
$ sudo mysqldump -u root [email protected] demodb tasks > demodb_tasks.sql
Multiple database tables (user and host) should be backed up as follows:
$ sudo mysqldump -u root [email protected] mysql user host > mysql_user+host.sql
To restore a MySQL database or database table(s), use the mysql command instead of the mysqldump command and switch the > symbol with the < symbol.
Implement the command: to restore a single MySQL database named demodb, for example.
$ sudo mysql -u root [email protected] demodb < demodb.sql
You’ll need to know the IP address of the distant server hosting MySQL to backup your database data to a local PC. For example, if you want to back up a single database on a distant server locally, use the following command:
$ sudo mysqldump -h 192.168.84.130 -u remote_user -p dbdemo > dbdemo.sql
The database user password will be required before the backup operation can begin. Make sure that remote access to the MySQL server is enabled.
MySQL Database Backup from a Remote Location
Here are some good MySQL-related articles to check out:
With the information in this article, you should be able to easily implement the MySQL commands required to keep your database-driven applications running.
Watch This Video-
The “mysql cheat sheet pdf 2020” is a command reference guide for MySQL database. It includes the most common commands that are used in MySQL, as well as their syntax.
Frequently Asked Questions
How use MySQL command line in Linux?
A: The MySQL command line utility is a tool that allows you to execute commands on your local or remote mysql server using the command line interface.
What are the commands in MySQL?
A: Please see the MySQL reference manual.
What is the command to list all MySQL commands in the command prompt?
A: The command to list all MySQL commands in the command prompt is ls mysql.
Related Tags
- mysql commands with examples
- mysql command line cheat sheet
- mysql commands list
- mysql workbench cheat sheet pdf
- mariadb commands cheat sheet
