When working with MySQL it is often necessary to execute SQL statements or scripts programmatically from the command line. SQL statements can be included in a SQL script (text) file and executed by the MySQL Client. This can be done in a few different ways.
Execute MySQL Script File from Standard Input
Statements in a SQL scripts can be read from standard input on a Unix/Linux command line and executed. Two examples of doing this can be seen below.
1
2
3
4
5
6
7
#!/bin/bash
# redirect standard input from sql script
mysql<sample_script.sql
# cat mysql script and pipe output to mysql client
cat sample_script.sql|mysql
Execute MySQL Script File from MySQL Shell
While running the MySQL shell, SQL scripts can be executed using the source or \. commands.
1
2
mysql>source sample_script.sql
mysql>\.sample_script.sql
Using the --verbose Option
Using the --verbose option when executing MySQL scripts on the command line will print the individual SQL statements before the are executed.
1
2
3
4
5
6
7
#!/bin/bash
# redirect standard input from sql script
mysql--verbose<sample_script.sql
# cat mysql script and pipe output to mysql client
How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.
Answers
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
For view procedure in name wise
select name from mysql.proc
below code used to list all the procedure and below code is give same result as show procedure status
select * from mysql.proc
As mentioned above,
show procedure status;
Will indeed show a list of procedures, but shows all of them, server-wide.
If you want to see just the ones in a single database, try this:
SHOW PROCEDURE STATUS WHERE Db = 'databasename';
My preference is for something that:
Lists both functions and procedures,
Lets me know which are which,
Gives the procedures' names and types and nothing else,
Filters results by the current database, not the current definer
Sorts the result
Stitching together from other answers in this thread, I end up with
select
name, type
from
mysql.proc
where
db = database()
order by
type, name;
... which ends you up with results that look like this:
mysql> select name, type from mysql.proc where db = database() order by type, name;
+------------------------------+-----------+
| name | type |
+------------------------------+-----------+
| get_oldest_to_scan | FUNCTION |
| get_language_prevalence | PROCEDURE |
| get_top_repos_by_user | PROCEDURE |
| get_user_language_prevalence | PROCEDURE |
+------------------------------+-----------+
4 rows in set (0.30 sec)
To show just yours:
SELECT
db, type, specific_name, param_list, returns
FROM
mysql.proc
WHERE
definer LIKE
CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'));
SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'
show procedure status;
using this command you can see the all procedures in databases
Use the following query for all the procedures:
select * from sysobjects
where type='p'
order by crdate desc
This post shows how to get some MySQL status information from the command line on a Linux/Unix machine using either the mysqladmin command or the mysql init script. Information returned includes the version, connection type, socket file location, uptime and some other stats.
Using mysqladmin
Using mysqladmin is the only way to get the same sort of information across distros. The example provided in the "using the init script" section below works on Debian based distros and may or may not yield the same information for other distros. For example, on Red Hat / CentOS based distros the init script will only return the pid number and whether MySQL is running.
To get a basic set of stats run this:
mysqladmin -u root -p status
This will show something like this:
Uptime: 1588357 Threads: 1 Questions: 52438664 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 384 Queries per second avg: 33.014
To get more comprehensive information run this:
mysqladmin -u root -p version
which will show something like this:
Enter password:
mysqladmin Ver 8.41 Distrib 5.0.22, for redhat-linux-gnu on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.22
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 18 days 9 hours 14 min 17 sec
Threads: 1 Questions: 52441939 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 384 Queries per second avg: 33.014
These first two examples are from MySQL running on a CentOS 5 server.
Using the init script
The init script at /etc/init.d/mysql on Debian based distros will return the same sort of status information as "mysqladmin status" when passed the "status" parameter. Run this:
sudo /etc/init.d/mysql status
This will show something like this:
/usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a-24
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 202 days 18 hours 35 min 10 sec
Threads: 1 Questions: 93883881 Slow queries: 26 Opens: 145610 Flush tables: 1 Open tables: 256 Queries per second avg: 5.359.
This example is from a Debian 5 server.
Using the init script instead of mysqladmin means it can be run as the root user or using sudo without needing to know the mysql root user's password.
Remember this init script example on a Red Hat or CentOS based system will not show the same information but instead just that MySQL is running along with the pid number.
If you have MySQL tables that grow large and then have a lot of deletes they will become fragmented and larger than they need to be. This post looks at how to look at check to see if a MySQL table is no longer optimal and how to optimize it.
Tomorrow's post will look at how to do the same thing with phpMyAdmin instead of issuing the commands yourself (i.e. you can just point and click) and then on Sunday a PHP script to automatically optimize all tables that need it within set parameters. Log into the MySQL command line interface, select your database and then issue the command below, where 'mytablename' is the name of the table you want to query:
show table status like 'mytablename'\G
You can omit the "like 'mytablename'" part and then it will show this information for all tables. However if you have a lot of tables and there's only one or two you want to examine then it's better to specify the particular table.
You can end you query with either ; or \G. I prefer \G for this particular query because it shows each column from the resultset on a new line, whereas ; will show the columns across the screen. This is OK for a resultset with only a few columns with only a small amount of information in each one, but it's not so good for this query.
The result from the above will look something like so:
The values that are important for working out if the table is non optimal is the "Data_free" value. If this is high, as in the above example where 564614700 bytes are free (538MB), then the table has a lot of space not being used and should be optimized.
To optimize the table, issue the following command, where "mytablename" is the name of the MySQL table to optimise:
optimize table mytablename;
After doing this (it may take a few seconds dpending on the size of the table, free space etc) and running "show table status" again, the result should look much better:
In the above example we can see the "Data_free" value is now zero so the table is nicely optimised.
Tomorrow I'll look at how to do the same thing with phpMyAdmin so all you need to do is point and click instead of having to remember the commands and type them in.
The MySQL command line utility allows you to run queries and view query results etc from an interactive command prompt. It is also possible to run a single query from the command line without actually going into the interactive prompt. This post looks at how to do this.
As an example, I needed to load some data into a new database the other day from a dump from another server. Normally I'd do something like this:
mysql -u [username] -p somedb < somedb.sql
The database didn't actually exist so I got this error:
ERROR 1049 (42000): Unknown database 'somedb'
The obvious solution is to create the database and then run the same command to load the data. I could either do this by adding the "create database somedb" to the start of the text file I was loading, fire up the MySQL command line, run the command and exit back to the bash shell, or use the -e flag to execute a single query.
So to run a single MySQL query from your regular shell instead of from MySQL's interactive command line you would do this:
mysql -u [username] -p [dbname] -e [query]
In my case I wanted to create the database so it looked like this (note I didn't need to specify a database because my query didn't affect a specific database):
mysql -u [username] -p -e "create database somedb"
You can run any valid queries against any databases you have permissions for, in the same way as running the query from the MySQL command line. Any output will appear on your shell's command line. For example:
$ mysql -u root -p somedb -e "select * from mytable"
Enter password:
+------------+-------------+----------------------------+
| mytable_id | category_id | name |
+------------+-------------+----------------------------+
| 1 | 1 | Lorem ipsum dolor sit amet |
| 2 | 1 | Ut purus est |
| 3 | 2 | Leo sed condimentum semper |
| 4 | 2 | Donec velit neque |
| 5 | 3 | Maecenas ullamcorper |
+------------+-------------+----------------------------+
Update and insert queries do not output anything if they are successful (displaying errors if not successful), but select queries do as shown in the above example.
The MySQL command line tool allows you to run queries and administer databases from the command line. In previous posts I have looked at the basics of using the MySQL command line tool and executing shell commands. In this post I will look at running queries and the output from these.
Running select queries
After you have logged into a database with the MySQL command line tool (covered in my using the MySQL command line tool post), you can run queries by simply typing them in at the command prompt. The query will not be executed until you either enter ; \g or \G and then press the <enter> key. This allows you to write a query across several lines and then execute it at the end by entering ; and then <enter>.
For example, if we have a table which stores country codes and names, we can query the data like so:
mysql> select country_code, name from countries order by name limit 0, 5;
The result set would then be displayed, showing something like below. It will be displayed in this horizontal format whether you use ; or \g to execute the query. We will look at what \G does in the next section below.
+--------------+----------------+
| country_code | name |
+--------------+----------------+
| AF | AFGHANISTAN |
| AL | ALBANIA |
| DZ | ALGERIA |
| AS | AMERICAN SAMOA |
| AD | ANDORRA |
+--------------+----------------+
5 rows in set (0.00 sec)
Displaying the result set vertically
If your query result contains a lot of columns then the data will wrap if it is too long. This can make it difficult to find the information you are looking for because the output can be quite messy. The MySQL command line tool allows you to show the output vertically which overcomes this issue, using \G to execute the query.
Running the same query as above with \G:
mysql> select country_code, name from countries order by name limit 0, 5\G
would look like this:
*************************** 1. row ***************************
country_code: AF
name: AFGHANISTAN
*************************** 2. row ***************************
country_code: AL
name: ALBANIA
*************************** 3. row ***************************
country_code: DZ
name: ALGERIA
*************************** 4. row ***************************
country_code: AS
name: AMERICAN SAMOA
*************************** 5. row ***************************
country_code: AD
name: ANDORRA
5 rows in set (0.00 sec)
MySQL Command Line Tool History
The MySQL command line tool keeps a history of the SQL queries you have run. On a Linux/Unix machine this is stored in your home directory in the file .mysql_history. When you are in the MySQL command line tool, you can go back through this history by using the up and down arrow keys. The up arrow moves you back through the history and the down arrow forward through the history again.
Clearing the current query
If you navigate through the history but decide you don't want to run any of the queries, or if you've typed in a query that you decide you don't want to run after all, you don't need to use the backspace key to clear the query. Enter \c and then press <enter> and the query will be cleared without running.
Summary
The MySQL Command Line Tool is a useful way to run queries from the command line. It's easy to run select queries and display the results in either a horizontal or vertical format, and the queries run are kept in a history file which you can navigate through. If you don't want to run a particular query after all you can use the \c command to clear it. In the next post I will look at displaying database tables and table structure with the MySQL command line tool.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
