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. . 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
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
——————–
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 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:
[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"
14. UNIX_TIMESTAMP function
————————————-
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580give 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 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();
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.
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
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.
Published on System Code Geeks with permission by Javin Paul, partner at our SCG program. See the original article here: 30 Examples of MySQL Commands in Linux Opinions expressed by System Code Geeks contributors are their own. |
