Tuesday, March 17, 2020

30 Examples of MySQL Commands in Linux

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 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 in just $10 which is very cost-effective to learn a useful skill like MySQL.

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

$ ps -auxwww | grep mysql

If it returns any row then MySQL server is running otherwise no. You can also check the output to verify its 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 below command

$ ./bin/mysqld_safe &

3. Stopping MySQL


$ 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 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 command prompt itself like following, you just need to understand it.

mysql> help alter;

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



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 is very useful for anyone working with   MySQL database. This is very useful for application developers which is going to use MySQL database for there 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"

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.

30 Examples of MySQL Commands in Linux

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


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




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

Further Learning

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

No comments :

Post a Comment