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

$ ps -auxwww | grep mysql

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

$ ./bin/mysqld_safe &

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


Kill MySQL processes

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

mysql> help alter;

6. Repair a table in MySQL

REPAIR TABLE TableName

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:

[mysqld]
set-variable=max_connections=250

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"

to fix it you can run

"'myisamchk -r ORDERS.MYI"

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

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');

      -> '46:58:57.999999'

16. Returns Time to seconds

Returns the time argument, converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

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

mysql -u root -h <hostname>  <database name >-e "UPDATE ORDERT SET TYPE ='PARTIAL' WHERE TYPE='FULL'

-h for host and –e for expression.

20. Showing list of databases in MySQL

mysql> show databases;

Hi Guys, here are some more MySQL commands which are useful for day 2-day work.

21. Method for converting the current timestamp to date:

select from_unixtime(left(1201159475416, 10));

this method is used to convert the timestamp to the date-time format in MySQL, the left() method will return 10 char from the specified string if we store the timestamp value in a millisecond.

mysql> select from_unixtime(left(1210916129820  , 10))

    -> ;

+------------------------------------------+

| from_unixtime(left(1210916129820  , 10)) |

+------------------------------------------+

| 2008-05-16 01:35:29                      |

+------------------------------------------+

1 row in set (0.00 sec)

22. Viewing MySQL command history

There is one hidden file called .mysql-history, on which all commands are stored which we typed in  MySQL console. It generally resides in the home directory.

23. Taking a backup of the MyISAM database in MySQL

There are multiple ways to take a backup of  MyISAM tables in MySQL like using mysqldumb.

One way to take the backup of a database is to copy the files.MYD,.MYI and .frm, this way you can write scripts that can copy the database from one server to another, merge databases, etc.

24. To remove a column from a table

$ alter table ice cream drop column flavor ;

25. Changing the size of a column and datatype of a column in MySQL

$ alter table people modify name VARCHAR(35) ;

$ alter table ORDERS modify CLIENT  varchar(255) default NULL;

26. Displaying index from a MySQL table

mysql> SHOW INDEX FROM   database.ORDERS;

27. Getting MySQL server version

you can use a method called version()

mysql> select version();

+-----------+

| version() |

+-----------+

| 3.23.58 |

+-----------+

1 row in set (0.02 sec)

28. mysqldump Command

The mysqldump client is a backup program o It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. 

However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. You can further see a comprehensive course like The Ultimate MySQL Bootcamp course on Udemy to learn more about this MySQL utility. 

29. To take a dump of a MySQL table-use below command

~/MySQL/bin/mysqldump -u root  database_name ORDERS > orders.txt

1.  command to dumb  only tables definitions, not the data “ used the command”  if the path is not set then you need to run that command from MySQL/bin directory

./mysqldump -d -u root database_name  ORDERS , CLIENTS , COMPANY  > ~/tmp/test.database.sql

2. Command to recreate table from that file

mysql -u root database_name < ~/tmp/test.database.sql

Other Database and SQL Articles you may like

• How to find duplicate records from a table? (solution)
• 5 Web sites to learn SQL online for FREE? (resource)
• 5 Books to Learn SQL? (books)
• How to join more than two tables in one SQL query? (solution)
• How to find the length of String in MSSQL? (example)
• The difference between char, varchar, nchar, and nvarchar in SQL SERVER? (answer)
• How to create an Identity column in SQL Server? (example)
• 5 tips while migrating from Oracle to Microsoft SQL Server? (tips)
• How to replace NULL with empty String in SQL Server? (tutorial)
• Difference between row_number, rank, and dense_rank in SQL Server? (answer)
• What is the difference between WHERE and HAVING clause in SQL Server? (answer)
• How to split String in SQL SERVER? (answer)
• 50 SQL Server Phone Interview Question (SQL server questions)
• How to find the second highest salary of an employee in SQL Server? (query)

Thanks for reading this article so far. If you find these MySQL commands useful then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.