Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
How to format MySQL output as HTML
Answer:
If you want MySQL to return query result as HTML format, you can use the -H option
# mysql -H -e 'select now()' -u root
<TABLE BORDER=1><TR><TH>now()</TH></TR><TR><TD>2010-01-16 20:30:45
</TD></TR></TABLE>
How to limit query results scrolling off the screen
Answer:
If you have a query which return a very large result, and you want to use a pager such as less to control, you can use the following command in the mysql client.
mysql> pager less;
PAGER set to 'less'
mysql> select * from very_large_table;
To turn off the pager
mysql> nopager;
PAGER set to stdout
How to execute SQL using command line
Answer:
Use the mysql client and -e option to execute query in shell
# mysql -e 'select now()' -u root -p
+---------------------+
| now() |
+---------------------+
| 2010-01-16 20:22:38 |
+---------------------+
How to execute SQL file with MySQL
Answer:
If you already inside the mysql client, type
mysql> source /tmp/data.sql
Or you can pipe the file to mysql client
cat /tmp/data.sql | mysql -u root
Enable slow query log in MySQL
Answer:
Turn on the Slow Query log in MySQL allow you to debug the sources of slow and problematic SQL.
To activate it, edit the MySQL configurations, e.g. /etc/my.cnf
[mysqld]
...
...
log-slow-queries = /var/log/mysqld-slow.log
long_query_time = 5
Restart MySQL and you will notice the file mysqld-slow.log will be created.
Any queries take more than 5 seconds to execute will be logged in this file.
