MySQL | Linux Ask!

How to search man pages by keyword

Jan 212010

How to search man pages by keyword

Answer:

Sometimes, you want to search for all commands related to mysql, for example

You can use the following command

 # man -k mysql

Bundle::DBD::mysql (3pm) - A bundle to install Perl drivers for MySQL
DBD::mysql (3pm)     - MySQL driver for the Perl5 Database Interface (DBI)
DBD::mysql::INSTALL (3pm) - How to install and configure DBD::mysql
innotop (1)          - MySQL and InnoDB transaction/status monitor.
msql2mysql (1)       - convert mSQL programs for use with MySQL
mysql (1)            - the MySQL command-line tool
mysql_client_test (1) - test client API
mysql_client_test_embedded (1) - test client API for embedded server
mysql_config (1)     - get compile options for compiling clients
...

All the related man pages will be shown.

How to dump an MySQL/InnoDB database

MySQL 1 Response »

Jan 192010

How to dump an MySQL/InnoDB database

Answer:

To do a point-in-time SQL dump of a InnoDB database, use the mysqldump command with the --single-transaction flag

# mysqldump --master-data=2 --single-transaction huge_db > backup.sql

The binlog position will be written in the backup.sql, which is very useful for database recovery if needed.

How to format MySQL output as HTML

MySQL No Responses »

Jan 162010

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

MySQL No Responses »

Jan 162010

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

MySQL No Responses »

Jan 162010

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

Older Entries Newer Entries