Linux commands with mysql

Top 30 Examples of MySQL Commands in Linux and UNIX

Hello guys, if you are working with MySQL database in Linux and looking for MySQL commands to perform common tasks like starting and stopping a MySQL server then you have come to the right place. I have been working with MySQL since last 15 years as Java developer and it was actually the first database I used in a real-world project. Since I need to work with the MySQL database daily, I compiled a list of MySQL commands which I keep handy. This saves me a lot of time while doing development and support and that’s what I am going to share with you today.

Here is my list of some of the most useful MySQL commands which I have used in my day-to-day life while working with the MySQL database in Linux.

This comes straight from my notes so you may not find a lot of documentaries around it but they all are very useful. If you need to know more about any command you can always drop a note or look into resources I have shared at the end of this article.

If you need a course to start learning MySQL from scratch, I recommend The Ultimate MySQL Bootcamp on Udemy, one of the most comprehensive courses on MySQL for beginners. You can also buy for just $10 which is very cost-effective to learn a useful skill like MySQL.

29 MySQL Command Examples for Beginners

Here is my list of frequently used MySQL commands for beginners. If you are working with MySQL database in Linux then these commands will come in handy. The list is very exhaustive and gives you the command to start and stop the MySQL server, take backup, fix issues as well how to see and work with data inside your MySQL database.

1. For Checking Whether MySQL Server is running on Linux or not

If it returns any row then the MySQL server is running otherwise no. You can also check the output to verify it’s actually the MySQL server itself and not any other script which has MySQL in its name or command arguments.

2. Starting MySQL

Go to MySQL installation directory and execute the below command

3. Stopping MySQL

Here is a Linux command to stop your MySQL server in Linux, you need to run this from the bin directory of your MySQL installation.

$ cd mysql/bin
./mysqladmin -u root shutdown
./mysqladmin —host=localhost —port=3305 -u root shutdown //for second instance listening on port 3305

4. Viewing MySQL process list and killing the offending MySQL process

This is extremely useful to see which query is running on which host, from which location query has fired, which query has locked which table, etc.

$ cd mysql/bin
./mysqladmin -u root processlist

$ cd mysql/bin
./mysqladmin -u root kill ProcessID

But, if you are new to the SQL world, it’s better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

5. How to see MySQL help

You can see the MySQL help from the command prompt itself like the following, you just need to understand it.

6. Repair a table in MySQL

7. Copying data from one table to another

This is very useful when you are altering the table and you would like to take the backup of data.

insert into ORDERS_TMP select * from ORDERS

8. Dropping columns from the table

ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;

9. Adding Keys(Indexes) to a table

alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (here CLIENT_ID is a column in ORDers table)

10. modifying a column

This is useful in case you want to modify data type or size of a particular column

$ alter table ORDERS modify column BRAND varchar(15) default NULL

11. Rename Table

This is again a useful method for creating a backup of a table before playing with it.
Renaming the new table to the original name:

mysql> ALTER TABLE new_table RENAME old_table;

Here are some more MySQL commands from which are very useful for anyone working with the MySQL database. This is very useful for application developers who is going to use the MySQL database for their applications.

12. Increasing no of connections for MySQL

You can increase this value in the main config file (like /etc/my.cnf ) using this syntax:

13. Myisamchk command

if you run «‘myisamchk ORDERS.MYI» it will check whether ORDERS table is corrupted or not. if corrupted it will say

MyISAM-table ‘ORDERS.MYI’ is corrupted Fix it using switch «-r» or «-o»

If you want to learn more about this command other MySQL-specific commands, I also suggest you go through MySQL Fundamentals by Pinal Dave on Pluralsight. It is one of the best courses to learn MySQL if you have a Pluralsight membership.

14. UNIX_TIMESTAMP function

SELECT UNIX_TIMESTAMP(‘1997-10-04 22:23:00’); -> 875996580
give the date and will return no of seconds, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC

15. Diff between 2 dates in MySQL

16. Returns Time to seconds

17. UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since ‘1970-01-01 00:00:00’ UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.

The date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone

18. TAKING THE BACKUP OF A TABLE

$ CREATE TABLE ORDER_TEMP SELECT * FROM ORDER;

19. Running mysql query from unix command prompt

-h for host and –e for expression.

Источник

Linux mysql command

The mysql command is a simple shell for SQL commands. With user interaction, it can enter commands at a special prompt, or run a batch script containing your SQL commands.

Description

When mysql is used interactively, query results are presented in a table format. When used non-interactively, the result is presented in tab-separated format. The output format can be changed using command options.

The simplest way to invoke mysql is to specify your MySQL username with the -u option, and to tell mysql to prompt you for your password with -p:

You are shown a brief introduction message and then placed at the mysql> prompt.

At the mysql> prompt, enter MySQL commands, such as:

mysql> SHOW DATABASES;

To list the databases which exist, or:

mysql> USE dbname;

. to begin using the database named dbname, or:

mysql> SELECT 21 * 2 AS meaning_of_life;

. to display the mathematical product of 2 and 21.

To end your mysql session and return to the shell prompt, use the command:

Running MySQL commands from a batch script

Instead of using mysql interactively, you can execute MySQL statements from a script file. For instance, if you have a text file named mysqlscript.txt containing MySQL commands, one per line, you could use this command:

mysql -u username -p db_name < mysqlscript.txt >output.txt

. and after prompting you for your password, mysql would execute the commands in mysqlscript.txt on the database db_name, writing the output to the file output.txt.

For an in-depth description of how to install MySQL on your system, and an overview of the basic interactive usage of mysql, see an introduction to MySQL.

Syntax

mysql [options] db_name

Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file.

Initially, mysql executes statements in the input because specifying a database db_name on the command line is equivalent to inserting USE db_name at the beginning of the input. Then, for each USE statement encountered, mysql accepts or rejects following statements depending on whether the database named is the one on the command line. The content of the statements is immaterial.

Suppose that mysql is invoked to process this set of statements:

DELETE FROM db2.t2;USE db2;DROP TABLE db1.t1;CREATE TABLE db1.t1 (i INT);USE db1;INSERT INTO t1 (i) VALUES(1);CREATE TABLE db2.t1 (j INT);

If the command line is mysql —force —one-database db1, mysql handles the input as follows:

The DELETE statement is executed because the default database is db1, even though the statement names a table in a different database.

The DROP TABLE and CREATE TABLE statements are not executed because the default database is not db1, even though the statements name a table in db1.

The following example demonstrates tabular versus nontabular output and the use of raw mode to disable escaping:

MySQL commands

mysql sends each SQL statement you issue to the server to be executed. Note that all text commands must first be online and end with ‘;‘ .

There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

?	(\?)	Synonym for ‘help’.
clear	(\c)	Clear command.
connect	(\r)	Reconnect to the server. Optional arguments are db and host.
delimiter	(\d)	Set statement delimiter.
edit	(\e)	Edit command with $EDITOR.
ego	(\G)	Send command to mysql server, display result vertically.
exit	(\q)	Exit mysql. Same as quit.
go	(\g)	Send command to mysql server.
help	(\h)	Display this help.
nopager	(\n)	Disable pager, print to stdout.
notee	(\t)	Don’t write into outfile.
pager	(\P)	Set PAGER [to_pager]. Print the query results via PAGER.
print	(\p)	Print current command.
prompt	(\R)	Change your mysql prompt,
quit	(\q)	Quit mysql.
rehash	(\#)	Rebuild completion hash.
source	(\.)	Execute an SQL script file. Takes a file name as an argument.
status	(\s)	Get status information from the server.
system	(\!)	Execute a system shell command.
tee	(\T)	Set outfile [to_outfile]. Append everything into given outfile.
use	(\u)	Use another database. Takes database name as argument.
charset	(\C)	Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings	(\W)	Show warnings after every statement.
nowarning	(\w)	Don’t show warnings after every statement.

Examples

For an in-depth description of installing MySQL, and an overview of basic commands, see An introduction to MySQL.

The following are a few other notable commands:

mysqldump -u hope -p -h localhost hope_SMF > smf.sql

Backup the database «hope_SMF» to the smf.sql file after the username and password were verified.

Running status while at the mysql> prompt would give you MySQL status results similar to what is shown below.

-------------- mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (i686) using readline 6.2 Connection id: 42 Current database: Current user: [email protected] SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.35-0ubuntu0.13.10.2 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 2 hours 3 min 4 sec Threads: 1 Questions: 577 Slow queries: 0 Opens: 421 Flush tables: 1 Open tables: 41 Queries per second avg: 0.078 --------------

Related commands

myisamchk — Check, repair, optimize, or fetch information about a MySQL database.
mysqldump — A tool for backing up or transferring MySQL databases.

Источник